Jump to content
  • 0

Sum Payment Due based on search criteria


Breee

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!

 

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...