SUM lookup column ?

[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

[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

[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.

[pbottcher]

Hi,

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

[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:

[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.

[mskuodas]

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

[pbottcher]

Yes, please try this.

[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 ?

[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.

[mskuodas]

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

[mskuodas]

SOLVED. Works even with filtered fields. Thanks, pböttcher , our SUPER HERO

[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`
)