Topic: How to SQL from a parent grid.

Hi, as I am now up to the point of learning how  SQL works, my question is probably very basic but part of the learning curve.

I seem unable to find how you search for an individual from a parent grid.

I am trying to sort info form a table using a "Client name" that has been selected in an earlier grid/form.

See my pic for a visual example and here is my script.




form2.totalExpense.Value := SQLExecute('SELECT TOTAL(amount) FROM expense WHERE 'Status' = "expense" AND   ???? Name of client on form matches id of table expense ?????   ');

Post's attachments

Attachment icon tableExpl.jpg 194.49 kb, 4 downloads since 2020-12-05 

2 (edited by agusecc 2020-12-05 14:45:42)

Re: How to SQL from a parent grid.

Hello, jeffmtl@hotmail.com

my attachment  maybe can help you


Regard
agusecc

Post's attachments

Attachment icon jeffmtl@hotmail.com.rar 296.55 kb, 29 downloads since 2020-12-05 

3 (edited by jeffmtl@hotmail.com 2020-12-05 16:16:20)

Re: How to SQL from a parent grid.

Hi agusecc, and thanks but I need to have it done in SQL as I have other things that will follow.

I updated the visual helping to make myself clearer.

I can sort the EXPENSE in one grid, and sort the INCOME in a second grid.


Things get strange when I get into SQL and try to place each calculation separately for that specific client. I get all EXPENSE and all INCOMES for all clients.

Post's attachments

Attachment icon tableExpl2.jpg 263.89 kb, 2 downloads since 2020-12-05 

4 (edited by derek 2020-12-05 16:49:45)

Re: How to SQL from a parent grid.

Hi Jeffmtl,
Obviously what you are trying to do can be achieved without script but I appreciate that, as you stated earlier, you're trying to get to grips with that side of things.
Have a look at the script in the attachment - basically you make your calculation client specific by referring to the highlighted row in Form1.tablegrid1 and the results are output to form2.edit2 and form2.edit3.
Another option would be to use calculated fields (which is the same syntax as a script, albeit with fewer options);  the calculated fields are shown on form1.tablegrid1.
(I've only just seen your updated graphic but the script in the attachment should still be valid.  Shout out if anything's not clear and, if you can, attach the actual project you're working on).
Derek.

Post's attachments

Attachment icon jeffmtl.zip 339.1 kb, 48 downloads since 2020-12-05 

Re: How to SQL from a parent grid.

Hi Derek

Million thanks.

It worked !!

It took a while for me to figure out how it worked, but as always, the logic of it is simple once you break it down and understand what is going on.


Jeff.