SELECT SUM(Amount) FROM tblRepayments WHERE LoanID = [@field:LoanID] ........returns same number no matter the loanID ??

[Quinn]

Hi All,

I have the below SQL statement:

SELECT SUM(Amount) FROM tblRepayments WHERE LoanID = [@field:LoanID]

tblLoans is the parent table for tblRepayments both linked by a LoanID field. Autonumber in tblLoans and Integer in tblRepayments.

The statements above should return a different value per LoanID as each loan has a different number of repayments and different repayment amounts.

however, the exact same number is returned each time.

Any help would be appreciated.

[CoopperBackpack]

Hello @Quinn,

Please use the 'target' keyword to get the correct result.

The statement should be     SELECT SUM(Amount) FROM tblRepayments WHERE LoanID = target.[@field:LoanID]

You may check an example in this article under the 'Subqueries in Calculated Fields' paragraph
https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/

The part of the statement after the equal sign is a dynamic one. So, a parameter is replaced by a real value when the DataPage is loaded. The parameter in your example is [@field:LoanID]

When the 'target' keyword is used the parameter is replaced with the value stored with this particular record.

In your example that will be:

SELECT SUM(Amount) FROM tblRepayments WHERE LoanID = 71 (for the 1st record)

SELECT SUM(Amount) FROM tblRepayments WHERE LoanID = 72 (for the 2nd record), etc.

Hope this helps!

[NiceDuck]

In addition to what @CoopperBackpack said, you can also use 'target.' not only on calculated fields but on calculated values as well. 

When you are having doubt if your calculations is getting the proper field values, try putting that as a prefix.

 

[Flowers4Algernon]

Hello! This link should also be helpful as you go along: https://howto.caspio.com/function-reference/