AppGini Community Forums

Hello,
I have a DB that contains the names of farmers who receive inputs. I need to know which farmer receives what amount of input according to this query:

SELECT farmer.Name ,Input.input, sum(input.amount) FROM Farmer
LEFT JOIN Input ON Input.GN=Farmer.GN GROUP BY by input.input, farmer.GN

This does not to be possible with a calculated field.

I have circumvented this now by creating a view and then having a table in AppGini with the same fields as the view - which works.
Is there any wat to do this in AppGini directly?
Thanks
Thomas

Hi,
which is your calculated field and how is the calulaton done? You can try to reflect that in your query.

This is my query from the calculated field:

Select sum(`Input`.`amount`) from Farmer
LEFT JOIN `Input` ON `Input`.`GN`=`Farmer`.`GN`
WHERE `Farmer`.`GN`= %ID%

It will correctly give me the sum of inputs, but I still have to group by inputs to know the sum of each input.

Greetings
Thomas

HI Thomas,

maybe I'm not clear about your requirement. The calculated field needs to return one value. Can you please post the table structure and point to where the aggregated sum per input and farmer shall be stored.

Ok, I guess the basic problem is that AppGini works with tables only, while Mysql will generate a temporary table for a result of a query.

So, I can either work with views in the DB or create new calculated fields in the table for each instance like this:

Select sum(`Input`.`amount`) from Farmer
LEFT JOIN `Input` ON `Input`.`GN`=`Farmer`.`GN`
LEFT JOIN `input_list` ON `Input`.`input`=`input_list`.`id`
WHERE `Farmer`.`GN`= %ID% and `input_list`.`Id` = 1
and then 2,3,4,5 etc.

That does work and actually looks quite nice.
Greetings
Thomas