Search the Community

I would like to share a Task example to update the parent table with aggregated values from the child table. The tables used in this example are not ideal but anyway, they showcase the idea. For example, there is the ‘Students’ table that stores unique students. Students can make payments so the ‘Payments’ table stores data about the payments made by students. These tables are linked with the popular one-to-many type of relationships. Use case: I need to calculate the sum of payments per student to store in the Students table (in the 'Payments_sum' field). Let`s say recalculation on a daily basis is acceptable. The intuitive way to create a Task for this use case is the following: tables are joined, and the SUM() function is added. However, this Task is not valid because in SQL it is impossible to use aggregate functions in update SET list. Error message: An aggregate may not appear in the set list of an UPDATE statement. (error code: SQL157)

Hello. I have a timesheet report representing hours worked. Relevant field names are: StartTime, EndTime and TimeSpan (a calculated field - let Caspio sweat the routine calculations). I'm struggling with the syntax in the "Aggregate" function entry. My goal is an output which shows: HH:MM... For example, if my time entries are: StartTime EndTime TimeSpan 08:30 10:30 2:00 09:45 10:45 1:00 13:30 15:45 2:15 (sum = 4:15) If I use: sum(datediff(minutes, StartTime, EndTime )) I get 255... (4 * 60 + 15) But how in the world do I present the output as 4:15 and not 255. Yes, I've changed the 'Formatting' to Time (HH:MM), to no avail. Any help would be deeply appreciated!

I have a table which includes a timestamp and a type field (among others) I've created triggers and a view that combines this table into itself and displays a clock-in time, clock-out time, and a total time difference between them in HH:MM format. The formula in the calculated field is below: CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]), 0), 114) My issue is that I need to have a total/aggregate for this field, but it gives an invalid error if I just do sum, and I've been unable to figure out how to get it to add the time and display it in the HH:MM format. Any help would be great!

Hi, I'm new to using Caspio bridge. I am more accustomed to creating web forms directly using PHP and JQuery, but I need to make some updates to a Caspio form. I have a submission form that has 4 text fields. The first 3 fields will accept currency values. The last field sum sum the previous one in real time. For example, if in field 1 I enter value 25, then 25 should show in field 4 when event focus changes. If fields 1 and 2 have values 25 and 17, respectively, then when the event focus changes from field 2 the value in field 4 should be 42. I know how to d9 this using straight JavaScript or J-Query, but I am having trouble creating functions and adding triggers and events to the elements in my submission form. Any help would be greatly appreciated.

Hello, Issue: My trigger action is not processing the Having statement in the Where clause. I barely know SQL and while the 30 minute Caspio webinar on Trigger Actions was helpful, the action I am attempting was not covered. I have a parent table and a child table, their common field is NTAID. I am trying to use a trigger action on Inserted/Updated/Deleted to quantify the number of records in the child table for the NTAID and assign a category to each parent record. The purpose of the categories is I need to use the field in the parent record to create filter views of the parent table for another data page. Some portions of my Update trigger actions work - I have four Updates. The Update for categorizing "Regional" is not occurring under any circumstances, and "Local" categorization is occurring even if the Count in the Having clause exceeds the limitation. I reversed the order of these two Updates and that did not alter the outcome. The other two updates run flawlessly. I appreciate any assistance in the proper set-up of these trigger actions. Thank you!

Hi All I am trying to sum AND add a percentage to two separate columns in a tabular report. I can do this fine with two separate aggregates: SUM(cost_2017)*1.1 = aggregate 1 SUM(cost_2018)*1.1 = aggregate 2 But the results fall in two different rows for each aggregate. How do I just do one aggregate, but for column 2017 costs, use the cost_2017 number and for column 2018 costs, use the cost_2018 numbers for the column 2018 costs and they fall in the same row. I can select both the cost 2017 and cost 2018 numbers in the selected fields, but can only do a formula for EITHER of them... Doing a SUM this way works (I can pull over 2017 and 2018 costs, and get two different sums in one row), but this doesn't seem possible for formulae? Thanks in advance melissa Example attached

Hello, I'm having difficulty with my time formula for a specific scenario. It is working correctly but producing too much time in some instances. I'm using a calculated field with the following formula to find the total time worked each day: cast (Datediff(hour, [@field:Start_TIme], [@field:End_Time])%24 as varchar)+' hours,'+ cast (Datediff(minute, [@field:Start_Time], [@field:End_Time])%60 as varchar)+ ' minutes' The formula is working when: The time calculated is more than an hour (i.e. 2:00 PM to 4:45 PM) = 2hrs, 45 minutes (CORRECT RESULT) or The time calculated is less than an hour but within the same hour of the day (i.e. 2:00 PM to 2:23 PM) = 0hrs, 23 minutes (CORRECT RESULT) The formula is NOT working when: The time calculated is less than an hour across DIFFERENT hours (i.e. 2:50PM - 3:05PM) = 1hrs, 15 minutes (INCORRECT RESULT) The hour should be '0' as the total time worked is '15' minutes only. I'm not sure if I can build a condition into the already existing formula(s) or if a new formula(s) is required. The standard datediff function also produces the same result. I've tried other code located online but have not been successful at removing the hour that appears in error. I've searched the CASPIO forum but was not able to locate a like issue. Any help or suggestions would be much appreciated. Thank you!

Hello, I tried to apply the examples from other post but not sure if applies, so I will try if somebody can help me to address the problem. have a table of where one of the field is a "number". I can insert a new line on the top of the table, but I want that the value of the "number" field of the new line is automatically filled with the MAX+1 value of all the others number in the result table. I already have the aggregate with MAX+1 and I would like to pass this value in the hidden "number" field. Thanks for the help Bye, Sergio

Good morning all, I am running in to a bit of difficulty with the reporting and charting features of Caspio right now. I currently have a table that has 21 Yes/No fields that are based on a review process. We would like to have a summary that shows either a count or % of No's in a given month (going off the date created field. Now while an aggregation SHOULD work, it will only allow count, and not even of only No values and it will only allow 5 at a given time and we have 21 fields. Charting has run into the same issue. I can make a calculated field that pulls a count of only no values per field, but I haven't found a good way to make this calculation apply to only dates in a given date roll up month (I do really like the date roll up grouping) without having to manually apply date restrictions and even then it would only work if I wanted to pull one month at a time. I am hoping others have run into similar issues with Caspio's aggregation and charting and have found workarounds. If anyone is creative it is this community! Any an all help would be much appreciated. Thank you!

Hello; I am working with a report and aggregate functions. I am trying to get a percentage toward a goal. ((COUNT_NON_BLANK [@tblActionLog_ActionCompletionDT]) / (MIN [@pUser_QFGoalWeek])) Should result in a percentage for completed QuotesFinished to QuotesFinishedPerWeekGoal, but it results in zero. For diagnostic purposes I've checked the following formulas... COUNT_NON_BLANK [@tblActionLog_ActionCompletionDT] = 2 (correct) (MIN [@pUser_QFGoalWeek])) = 10 (correct) ((MIN [@pUser_QFGoalWeek]) / (COUNT_NON_BLANK [@tblActionLog_ActionCompletionDT])) = 500.00% (correct) ((COUNT_NON_BLANK [@tblActionLog_ActionCompletionDT]) / (MIN [@pUser_QFGoalWeek])) = 0.00% (incorrect) Can anyone spot what I'm doing wrong? Thank You!