Sum Payment Due based on search criteria

[Breee]

Good Morning, 

 

I'm need the sum of a 'paymentdue' field based on the date range (criteria) selected by the end-user.

 

I'm currently using a sql select statement which is providing the grand total on the details page (formatted for use as the invoice). The sql statment is providing the grandtotal without any date criteria. 

I need the grand total to change (increase amount or lessen amount) based on the end-user's date range selections. 

Current Formula used in calculated field on details page: 

select
sum(PaymentDue)
from Test_Table
where Name=target.[@field:Name] group by Name

 

Tested the following statement which is not grabbing the date information selected from the caspio form thus returns the grand total w/o date criteria again: 

SELECT 
sum(PaymentDue)
FROM Test_Table
WHERE Start_Time >= [@field:Start_Time] AND End_Time <= [@field:End_Time]

 

Is there anyway to have a details page pull the 'paymentdue' from the table based on the date range searched on the Caspio search form (I configured the details page to allow users to select data using the search form? I essentially need an aggregation function on the details page itself. I understand that this information cannot be saved...I'd just like for the grand total data to dynamically display on the invoice based on the end-user's selection. 

 

As always, I greatly appreciate any feedback that can be provided. 

 

Thanks!

 

[Vitalikssssss]

Hello Breee,

 

You can use simple Total&Aggregation field to sum paymentdue values if your Datapage source table is the same as the one which you are referencing in your SQL expression. 

 

Hope this helps.

[NailDyanC]

For other Caspio Formula, you may consider checking the function reference: https://howto.caspio.com/function-reference/