Search the Community

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!

I work for a small nonprofit that has just transitioned all of our database systems over to Caspio (and so far so good... really powerful tool). I have just run into a snag that I was hoping someone could help me out with. We provide services for students with behavioral challenges, so we collect a lot of different data we need to slice and dice to guide our practices. One of the big ones is tracking time out of class. We generally use a number of nested groupings to arrange the data and make it managable for our frontline staff. A standard report might have StudentName>Reason for Timeout>InClass vs. OutOfClass... to see what a students behavior has been like over some period of time. I have the Search and Report form set up, and the data retreival, search, and grouping all work great. But normally, we would have the time out of class subtotaled for each sub grouping (e.g. How much time did Johnny spend "OutOfClass" for "Disrupting" this week. I found the aggregation function for reports, but I can only get it to apply to the first grouping (e.g. How much total time did Johny spend in TimeOut). Is there a way to apply aggregation to sub-groupings, and if not is that something that could be submitted as a feature request? I imagine it's a more universal need (grouping and sub totaling sales, or projection for different associates or regions, etc), so if it is already in the system and I'm just missing something please let me know. Thanks so much in advance for your help. Best, James