• 0
Breee

Sum Payment Due based on search criteria

Question

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!

 

Share this post


Link to post
Share on other sites

1 answer to this question

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now