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 have a submission form with 4 numeric fields. I wish to add the four fields and add to by table. Any ideas?

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!

Hi I used total & aggregation default function to calculation average score for table that has 12 months. The answer calculated has 6 decimal place but I want to have it round up. How do I fix this? There is nothing on the datapage round it up. Example (the actual calculation will give 2 decimal place in the below scenario. just for the purpose of illustrate, i have put in a dummy average value) Name Jan Feb Mar Name1 4.22 4.23 6.3 Name2 4.12 4.11 6.44 Mth Avg 4.31123 4.1732 12.74234 how can i round up to 2 decimal point 4.31123 to 4.31?

I have a Submission form that has a Dropdown for "Product" and the next field uses a Cascading Dropdown for "Price". The following fields are "Additional Charges" and "Discounts". I want the next field to Total the previous three. I have placed the following code in the Footer of the Submission form: <SCRIPT LANGUAGE="JavaScript"> function summation() { var Cost = document.getElementById("InsertRecordPrice").value; var Additional_Charge = document.getElementById("InsertRecordAdditional").value; var Disc = document.getElementById("InsertRecordDiscount").value; var Total_Invoice = parseFloat(Cost) + parseFloat(Additional_Charge) + parseFloat(Disc); document.getElementById("InsertRecordTotal").value = Total_Invoice; } document.getElementById("caspioform").onsubmit=summation; </SCRIPT> When Price is a cascading dropdown, I do not get a value in the "Total" field. If I change price to a standard dropdown with custom values the above script works perfectly. In a perfect world, I really don't need a cascading dropdown for Price if I could figure another way to get the number into the field. The dropdowns are from a separate table called with the product name as column 1 and price as column 2. Any ideas on why this would be misbehaving? Thanks.

I am having trouble with a submission form getting a total amount. I can get the javascript to work if I enter values in standard fields. It works fine if I use custom values in a dropdown. Where the code does not work is when I use a cascading dropdown. The form is an internal billing request form. There is a price field which is based on the product selected in a previous form. When the select the product, the price goes into the cascading dropdown and fills in the form properly. After price is a field for additional charges and following that a field for discount. I have the following javascript in the footer of the submission form. <SCRIPT LANGUAGE="JavaScript"> function summation() { var Cost = document.getElementById("InsertRecordPrice").value; var Additional_Charge = document.getElementById("InsertRecordAdditional").value; var Disc = document.getElementById("InsertRecordDiscount").value; var Total_Invoice = parseFloat(Cost) + parseFloat(Additional_Charge) + parseFloat(Disc); document.getElementById("InsertRecordTotal").value = Total_Invoice; } document.getElementById("caspioform").onsubmit=summation; </SCRIPT> There's obviously something I'm not getting about the difference between entering a value and using a cascading dropdown. Technically, it doesn't even need to be a cascading dropdown if there is another way to get a default value from a table. If anyone can see where my error is, please let me know. I even created a stand alone form with only the 5 necessary fields just for experimentation and I'm stumped. Thanks in advance.