Search the Community

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!

Hello, I'm using a tabular search and report for an invoicing application where I need to show aggregations of the RateAmount grouping by the RecIDL at the bottom of the RateAmount column. I want to pass the aggregated calculated field total to the invoice using an href link to my details page. Currently the link to the details page is showing an invoice for each RecIDL. I need the RecIDL to group on the tabular search and report with the aggregated total for each RecIDL. Here are the specifics: The Table name is 'Invoicing'. I have columns for 'RecIDL' and 'RateAmount'. For example: Table Name: Invoicing (Columns): RecIDL RateAmount (Vaules): L1 $100.00 L1 $100.00 L2 $150.00 L3 $175.00 I can aggregate totals and group by the RecIDL using the aggregate caspio field; however I need this information to pass to the actual invoice so a calculated field is required. I'd like to select the 'RecID column' and sum the 'RateAmount' and then group by the 'RecID.' I've tried the following select statements. SELECT RecIDL, SUM(IsNull(RateAmount,0)) FROM Invoicing_Loads GROUP BY RecIDL (*Expression Error Msg: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. ) SELECT RecIDL, SUM (RateAmount) FROM Invoicing GROUP BY RecIDL; (*Valid Formula per Caspio verify: When html page is opened "error in formula" message received; unable to view webpage) SELECT SUM(RateAmount) FROM Invoicing_Loads WHERE RecIDL = target.[@field:RecIDL] Group by RecIDL (*Statement is grouping the RateAmount for each RecIDL. The amount is appearing numerous times on the report; the total is correct.) This is what is showing: (The total should be $200.00 for L1) (Columns): RecIDL RateAmount (Vaules): L1 $200.00 L1 $200.00 L2 $150.00 L3 $175.00 This is what I need to see: Total Aggregations for each RecIDL; preferably at the bottom of each RecIDL group. (Columns): RecIDL RateAmount (Total L1): L1 $200.00 (Total L2) L2 $150.00 (Total L3) L3 $175.00 I'm not a strong sql user so I'm not certain what the "expression" error is speaking to. Based on the knowledge that I do have I believe it should work. I have searched the forums but could not locate any information specific to my need. I'd be grateful for any assistance provided. As always, Thank you! Bre

Hello, am totally new on this. and i am not an IT person in fact either. my account is also on a free plan. I wish I could get helps on how to insert aggregations, in this case Count formula into the header/footer or HTML block in my tabular/gallery datapage. I created a datapage based on a Views. According to Release Notes, Caspio Bridge 9.1 has a new feature to allow insert a field in Aggregations. But, unfortunately I couldn't see the Aggregations/Calculations on DataPage elements wizard. Any helps would be greatly appreciated. Thanks, Adhika.

Hi, as the Title suggests, I want to aggregate calculated fields. This is not allowed via a standard Aggregation. Is there a workaround for this? Thanks