Search the Community

I am trying to create a log sheet of sorts that shows the users transaction history. I have the table (as below) showing different transactions debit for income and credit for expenses but I want the balance column to show how the balance is increasing or decreasing as the transactions are coming in. ie User makes a deposit of 1000 and the balance also displays 1000, but next line he has an expense in the Credit column of 100 so balance should reflect 900. The table houses multiple users as seen above so that another users credit or debit doesn't influence other users balances.

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!

I have 12 fields in total - IG1 - IG6, which are dropdown fields and IG1_Qty - IG6_Qty - number input fields, they coincide with each other. The IG fields have 3 possible values - 1, 2, or 3. The Qty field is a number field to state how many of those IGs they want. In Example, the user would select '2' for IG1 and put in 4 for IG1_Qty, showing that they want 4 of the type 2 IGs. Now, the sum field for type 2 IGs would show 4. If we went on to give IG2's value to 2 as well, with a quantity of 1, then the sum field for type 2 IGs would show 5 (4 + 1). Essentially, I want to sum up the selections for the 3 possible IG dropdown values with the quantities selected for each. I was able to create an array with the 6 IG dropdown fields and count how many of each possible values were selected. However, adding in the Quantities for each IG into that number is where I ran into the problem, so I had to change the script entirely. This is my latest try: var ig1v = document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG1_"]'); //IG1 field - possible values are 1, 2, or 3 var vf1 = document.getElementById("cbParamVirtual22"); //This is where the sum value for IG Type 1 will go var vf2 = document.getElementById("cbParamVirtual23"); //Type 2 var vf3 = document.getElementById("cbParamVirtual21"); //Type 3 var ig1q = document.getElementById("InsertRecordIG_Sheet_Table_IG1_Qty"); //How many of the selected IG type var addNew1 = true; vf1.value = 0; //Setting the value to 0 so that the other calculated fields in the form don't load with an error. //This first eventListener/function is to automatically give 'Qty' a value if the IG dropdown isn't null anymore. This is just to automatically set Qty to 1 instead of blank when the user selects an IG value for the first time - UX document.querySelector('select[id^="InsertRecordIG_Sheet_Table_IG1_"]').addEventListener("change", function () { if (addNew1){ ig1q.value = 1; addNew1 = false; } ig1q.dispatchEvent(new Event('change')); }); //This function is what I am using to count the number of each IG value. If IG = 1, then add the IG1_Qty to the type 1 sum field (vf1), etc. document.getElementById('InsertRecordIG_Sheet_Table_IG1_Qty').addEventListener("change", function calcGroups1(){ if (ig1v.value == 1){ vf1.value = +vf1.value + +ig1q.value; } else if (ig1v.value == 2){ vf2.value = +vf2.value + +ig1q.value; } else if (ig1v.value == 3){ vf3.value = +vf3.value + +ig1q.value; } }); I have 6 of both of those functions for each IG field. Please forgive the ugly JS, I am sure there is a much better way to write this, but I have no clue how. The problems I am now having is that the Sum fields (vf1-vf3) are adding every change. See how the first function in the script sets Qty to 1 when the corresponding IG field is given a value - this is correctly being added to the vf1 totals field. But, if I change the quantity from 1 to another number, it adds it instead of just changing the value. So, when the Qty field is automatically set to 1, and then you change it to 4, the vf1 totals field would show 5 instead of 4. If you changed the 5 to a 4, it would show 9. Also, if the IG type is changed, the totals values are not updated for the previously selected value. Say I have IG1 = 1, Qty = 4. Now VF1 (sums of 1) will show 4. But, if I change IG1 = 2 with the same Qty, now VF2 will show 4, but VF1 will also still show 4. I'm not sure how to go about deleting the value from the previous sum field when the IG type is changed, or when the quantity is changed. I apologize for this being so confusing, but it is making my head turn into mush and I could use any of the help I can get.

I am trying to create an app for horse show points tracking. The table Results has the fields Rider ID, Class ID, Class Type, and Points Rider1 | Class02 | ClassTypeA | 4 Rider1 | Class03 | ClassTypeA | 5 Rider1 | Class04 | ClassTypeA | 5 Rider1 | Class05 | ClassTypeA | 5 Rider1 | Class11 | ClassTypeA | 5 Rider1 | Class12 | ClassTypeA | 5 Rider1 | Class06 | ClassTypeA | 3 Rider1 | Class08 | ClassTypeA | 1 Rider1 | Class09 | ClassTypeA | 3 Rider1 | Class13 | ClassTypeB | 5 Rider1 | Class14 | ClassTypeB | 4 Rider1 | Class15 | ClassTypeB | 2 Rider1 | Class16 | ClassTypeB | 1 When we do our year end calculations, I need to find out the sum of each Rider's top six (highest Points value) Results for each Class Type (ie for Class Type A the bolded values). What would be ideal would be to have this eventually input into a new table with the following columns: Rider ID, Class Type A (sum of top 6), Class Type B, etc Rider1 | 29 | 12 | Etc I have been trying to use a Task to input the values I can use into a new table but I can't seem to get values that account for needing the top 6 for _each_ Rider and _each_ Class Type, unless I do one for each rider and class type combination, which would be waaaay too many tasks to do. (see image, nomination_id is Rider ID in my example). Is there some other way I should be going about this that I am missing?

I am new so please excuse my SQL ignorance: My trigger is working, EXCEPT for the fact that it is NOT INCLUDING the most recently inserted value in the CommentHRS field in the sum (CA_TOTALhrs). Trying to be clear here: It's properly calculating the SUM on the insert event, but just not including the newly inserted value as part of the sum. (The actions with the red X work fine. No problems there.) Why would it not include the value that was just inserted in the SUM? Thanks. Mike

I am trying to craft a trigger that will sum inserted rows (multiple rows) and put the total in another table. I cannot seem to find a way to select multiple rows from the #inserted table though. To explain further, I have three tables, Master, Details, Totals. When a new row is inserted into the master table an insert trigger fires on the master table and several detail rows are copied from a template table into the detail table, where each row contains a price and quantity. The price and quantity are multiplied to create a total for each row through a formula field in the details table. On the details table there is a triggered action that on insert, update or delete will sum the cost of the total formula column for all rows and put that total into a totals table. I have to sum the #insert and existing detail rows separately and add them together to get the total. This all seem to work well when inserting one row but when inserting multiple rows only the first row from the #inserted table is totaled. That makes sense because the trigger on the detail table doing the sum specifies Select top 1 from #inserted when summing. However, I need to sum multiple rows from #inserted and the trigger is not letting me remove the top 1 clause stating that it only expects one row from the select query. I have tried putting the select query for the inserted table within a sum block and alternatively putting a sum expression on the result of the select query. In both cases the trigger will not save stating that the select must specify top 1. I have attached some screen prints of what I am doing in the trigger and the issues with each scenario. TriggerValidationError.docx

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.

Hi, I need help with calculating total sum based on values, entered in fields. I have 3 fields on the submission form, where users enter 3 values. I would like to calculate total in the forth field. Also, I want to show the value to the user. E.g when user enters value "5" in the first field, he will see "5" in the field Total then user enters "10" in the second field, he will see "15" in the field Total and finally user enters "7" in the third field, he will see "22" in the field Total And I wand to store value 22 in the table. 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

This old post from Caspio should be updated with the following changes so that it works when Grouping is enabled: http://forums.caspio.com/index.php/topic/3171-js-calculate-column-sum-in-the-result-page/ Aggregation should be used instead, but if you want to SUM a Calculated Field which in turn is based on a sub-query, it will not be available for Aggregation. See the second paragraph here for details on this case http://howto.caspio.com/release-notes/caspio-bridge-9-0/9-0-known-issues/ I've highlighted the changes below </div> <script> function NumberFormatted(amount,decimal) { if(isNaN(amount)) i = 0.00; else { var v_number = parseFloat(amount); var v_minus = ''; if(v_number < 0) v_minus = '-'; v_number = Math.abs(v_number); v_number = Math.round(v_number*Math.pow(10,decimal)); v_number = v_number/Math.pow(10,decimal); v_numStr = new String(v_number); v_decStr = new String(Math.pow(10,decimal)); if(v_numStr.indexOf(".") < 0) v_numStr = v_numStr + "." + v_decStr.substr(1,v_decStr.length); else v_numStr = v_numStr + v_decStr.substr(1,v_decStr.length); return (v_minus + v_numStr.substr(0,v_numStr.indexOf(".") + decimal + 1)); } } function f_calTotal() { var v_totalRev = 0; var v_rev = 0; var cas_form = document.getElementById("cb_resultTotal"); if (cas_form.getElementsByTagName("table").length > 0) { //For counting all rows, so that we can use the count as the index for inserting a new row. var countRows = cas_form.getElementsByTagName("table")[2].getElementsByTagName("tr"); //For reading just the data rows, not the Grouping rows, to avoid null values. var cas_rows = cas_form.getElementsByTagName("table")[2].querySelectorAll('tr[data-cb-name=data]'); for(var rowIndex=1; rowIndex < cas_rows.length; rowIndex++) { //Narrows down to just data cells. var cells = cas_rows[rowIndex].querySelectorAll(".cbResultSetData"); /* 1 – change value inside brackets to choose column to calculate sum */ v_rev = cells[3].innerHTML; if ( v_rev != " " && !isNaN(v_rev.substr(1))) v_totalRev = v_totalRev + parseFloat(v_rev); } //New row will be based on count of all rows, not count of rows searched. var v_nrow = countRows.length; cas_form.getElementsByTagName("table")[2].insertRow(v_nrow); var o_lastRow = cas_form.getElementsByTagName("table")[2].rows[v_nrow]; o_lastRow.style.background = "#385C7E"; o_lastRow.insertCell(0); o_lastRow.insertCell(1); /* 2 – Display the “Total†label (2 lines below) */ var v_colText = o_lastRow.insertCell(2); v_colText.innerHTML = "<div style='padding:5px;color:#ffffff;font-size:14px;font-weight:bold;font-family:Arial'>Total</div>"; var v_colValue = o_lastRow.insertCell(3); /* 3 – Display the result of the calculation (2 lines below) */ v_colValue.innerHTML = "<div style='padding:5px;color:#ffffff;font-size:14px;font-weight:bold;;font-family:Arial'>$" + NumberFormatted(v_totalRev,2) + "</div>"; o_lastRow.insertCell(4); } } </script> <script> f_calTotal(); </script>

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.