Page 1 of 1

SUM lookup column ?

Posted: 2019-01-09 18:22
by mskuodas
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

Re: SUM lookup column ?

Posted: 2019-01-10 21:27
by pbottcher
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

Re: SUM lookup column ?

Posted: 2019-01-12 15:31
by mskuodas
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.

Re: SUM lookup column ?

Posted: 2019-01-12 18:58
by pbottcher
Hi,

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

Re: SUM lookup column ?

Posted: 2019-01-13 08:47
by mskuodas
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

Re: SUM lookup column ?

Posted: 2019-01-13 13:15
by pbottcher
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.

Re: SUM lookup column ?

Posted: 2019-01-13 13:31
by mskuodas
Nope, no other column has "Show column sum" - "set"
Should i set one of the column to sum ?

Re: SUM lookup column ?

Posted: 2019-01-13 17:57
by pbottcher
Yes, please try this.

Re: SUM lookup column ?

Posted: 2019-01-14 16:11
by mskuodas
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

Re: SUM lookup column ?

Posted: 2019-01-14 18:29
by pbottcher
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.

Re: SUM lookup column ?

Posted: 2019-01-14 21:01
by mskuodas
I have id field, we can see it in previous screenshot. I sent you a private message about my mysql querry.

Re: SUM lookup column ?

Posted: 2019-01-22 15:41
by mskuodas
SOLVED. Works even with filtered fields. Thanks, pböttcher , our SUPER HERO :)

Re: SUM lookup column ?

Posted: 2023-04-07 20:52
by Moh Youba
Hello
any help please, try to SUM lookup column with below code
table "soins" with field "total"
table "dents" with field "tarif"
table "actes_realises" with lookup field "tarif" with get value from "tarif" field in table "dents"
field "total" from table "soins"



UPDATE `soins`
SET `total` = (
SELECT SUM(`actes_realises`.`tarif`)
FROM `actes_realises`
INNER JOIN `dents` ON `actes_realises`.`tarif` = `dents`.`tarif`
WHERE `actes_realises`.`idar` = `soins`.`idsoin`
)