Advanced Calcualtion Field

[utony]

Good day AG,

I am looking for some gudiance here on this calculation.

Code: Select all

SELECT coaches_meet_exp_mileage.totaldailymileage * coaches_meet_exp_main.mileagerate AS row_total
FROM coaches_meet_exp_mileage
JOIN coaches_meet_exp_main ON coaches_meet_exp_mileage.mileage_rate = coaches_meet_exp_main.pk
WHERE coaches_meet_exp_mileage.pk = '%ID%';

The return calculation for my row_total field is a multiplication of my FK by the total mileage driving. However, the calculation keeps using my FK INT instead of my true value for the "mileagerate" which I am trying to pull in from the parent table "coaches_meet_exp_main". I have tried a million different things and I can not get the calculation to pull the true vaule from the Parent Table to use for the calculation rather than the INT the child table is storing as the reference. What am I doing wrong? I have ran this through ChatGPT and other AI tools and they give me soultions, but everytime I add them to the calculation field in AG, nothing happens.

Tony

[saymaad]

The SQL you've written looks correct for a standard database query. The problem isn't the query itself, but likely what the coaches_meet_exp_main.mileagerate and coaches_meet_exp_mileage.totaldailymileage fields actually contains when the calculation runs.

I think the issue is a common AppGini quirk, whereby what you see on the screen for mileagerate is the actual value because AppGini is automatically "looking up" the value from the parent table (coaches_meet_exp_main) for you. However, the underlying data stored in your coaches_meet_exp_mileage table's foreign key field is not the value itself, but the Primary Key of the parent record. Please check if any of these are marked as Auto-fill, may sound dumb, but worth checking as a starting point.

Also try to run the query from the Admin > SQL Interface, check the results and revert so that we can further troubleshoot and resolve this.

PS: If nothing works, we can always calculate and store values from the update and insert hooks.