SUM lookup column ?

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
mskuodas
Posts: 11
Joined: 2018-12-16 19:53
Location: Lithuania
Contact:

SUM lookup column ?

Post by mskuodas » 2019-01-09 18:22

Hello, all,

SUM options doesnt work if column is lookup. I pick numbers from other table, but my column doesnt SUM them :( (checkbox is checked).
Is it possible to solve this ?
Thanks

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 315
Joined: 2018-04-01 10:12

Re: SUM lookup column ?

Post by pböttcher » 2019-01-10 21:27

Hi,
I think per default, only real values (no lookup values) are considered for the sum.

You can try this if you want to get the sum of your lookup field.

In the hooks/<TABLENAME>.php file, go the the <TABLENAME>_header function and put in the case tableview statement

Code: Select all

case 'tableview':
	$sum=sqlvalue("SELECT sum(LOOKUPTABLE.LOOKUPFIELD) from TABLENAME, LOOKUPTABLE where TABLENAME.FIELD = LOOKUPTABLE.id");
	$header="<%%HEADER%%><script type=\"text/javascript\">
				\$j(function(){
					\$j('td.TABLENAME-FIELD').last().text(".$sum.").addClass('text-right');
				});
		</script>";
	break;
Where you need to replace
TABLENAME with your tablename that you are looking at in the tableview
FIELD with the fieldname you want the sum to be displayed for

LOOKUPTABLE with the tablename of the lookup table
LOOKUPFIELD with the fieldname in the lookuptable that you have choosen for the as the source for your FIELD.

Hope that helps

mskuodas
Posts: 11
Joined: 2018-12-16 19:53
Location: Lithuania
Contact:

Re: SUM lookup column ?

Post by mskuodas » 2019-01-12 15:31

Hey, pböttcher,
Thanks for the help, but I still cant get it working.
Looks like the last record in the table is set at "0" in that field I needed to sum, and I cant change that zero to any other number from my lookup field. Maybe i made some misstake in replaceable word, but not sure.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 315
Joined: 2018-04-01 10:12

Re: SUM lookup column ?

Post by pböttcher » 2019-01-12 18:58

Hi,

can you post the code you put in your app? Also a screenshot of what you try to acheive?

mskuodas
Posts: 11
Joined: 2018-12-16 19:53
Location: Lithuania
Contact:

Re: SUM lookup column ?

Post by mskuodas » 2019-01-13 08:47

Hi, pböttcher,

This is my code:

Code: Select all

case 'tableview':
	                 $sum=sqlvalue("SELECT sum(pasirinkimai.pasirinkimai) from dalyviai, pasirinkimai where dalyviai.sumoketa = pasirinkimai.id");
	                 $header="<%%HEADER%%><script type=\"text/javascript\">
	                 \$j(function(){
		         \$j('td.dalyviai-sumoketa').last().text(".$sum.").addClass('text-right');
			});
	               	</script>";
	                        break;
And here is screenshot:
Image

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 315
Joined: 2018-04-01 10:12

Re: SUM lookup column ?

Post by pböttcher » 2019-01-13 13:15

Hi,
can you please verify/confirm if you have another column which has the "Show column sum" set.
If you do not have another one, AppGini will not create the row displaying the sum at the bottom of your table.

mskuodas
Posts: 11
Joined: 2018-12-16 19:53
Location: Lithuania
Contact:

Re: SUM lookup column ?

Post by mskuodas » 2019-01-13 13:31

Nope, no other column has "Show column sum" - "set"
Should i set one of the column to sum ?

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 315
Joined: 2018-04-01 10:12

Re: SUM lookup column ?

Post by pböttcher » 2019-01-13 17:57

Yes, please try this.

mskuodas
Posts: 11
Joined: 2018-12-16 19:53
Location: Lithuania
Contact:

Re: SUM lookup column ?

Post by mskuodas » 2019-01-14 16:11

Hi, pböttcher,

I changed my other field to SUM it, and it SUM's ok. But on our field I see - 0 (zero), it didnt SUM data. Maybe i wrote code bad ?
Image

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 315
Joined: 2018-04-01 10:12

Re: SUM lookup column ?

Post by pböttcher » 2019-01-14 18:29

Hi,

can you run the SQL directly on the database? Do you get the correct sum? if this is not the case,
can you check if the table pasirinkimai contains the primary key id. Maybe it is named differently.

mskuodas
Posts: 11
Joined: 2018-12-16 19:53
Location: Lithuania
Contact:

Re: SUM lookup column ?

Post by mskuodas » 2019-01-14 21:01

I have id field, we can see it in previous screenshot. I sent you a private message about my mysql querry.

Post Reply