SUM lookup column ?

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
mskuodas
Posts: 22
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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: SUM lookup column ?

Post by pbottcher » 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
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mskuodas
Posts: 22
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.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: SUM lookup column ?

Post by pbottcher » 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?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mskuodas
Posts: 22
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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: SUM lookup column ?

Post by pbottcher » 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.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mskuodas
Posts: 22
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 ?

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: SUM lookup column ?

Post by pbottcher » 2019-01-13 17:57

Yes, please try this.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mskuodas
Posts: 22
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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: SUM lookup column ?

Post by pbottcher » 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.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mskuodas
Posts: 22
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.

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

Re: SUM lookup column ?

Post by mskuodas » 2019-01-22 15:41

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

Moh Youba
Veteran Member
Posts: 228
Joined: 2017-03-12 09:31

Re: SUM lookup column ?

Post by Moh Youba » 2023-04-07 20:52

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

Post Reply