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!

I've created a few formulas and now require a field that uses some of the existing formula values in a calculation. Is there a way I can create a new formula similar to the following: [@field:Formula1] / [@field:Formula2] Reference: http://howto.caspio.com/tables-and-views/data-types/formula-fields/ "Other Formula Fields Not Compatible" Hoping for a way around this.... Thanks

Hi I am stuck with javascript doing multiple calculations in same datapage. 1st javascript is calculation of unit cost, no of units and total (in local currency) 2nd javascript is convert local unit cost to USD based on cascading dropdown field 3rd javascript is using unit cost (in USD), no of units to derive total cost (in USD) the 1st javascript seems to be working. But when I add on 2nd javascript, only one value shows and in the wrong field. There is also no error appearing in the web console. Eg javascript 1 calculation = virtual field 1 = virtual field 1 value javascript 2 calculation = virtual field 2 = virtual field 2 value Instead what i see is this javascript 1 = virtual field 1 = virtual field 2 value javascript 2 = virtual field 2 = blank I followed basically this script and did my own modification http://forums.caspio.com/index.php?/topic/6053-perform-calculations-in-submission-form/ I think the problem is I did not define 2 variable that points to virtual field 1 and virtual field 2 respectively. var totalField = document.getElementsByClassName('cbFormData')[0]; I need 2 of it, and I do not how to define for the 2nd virtual field. Also, how do I pass the the virtual field to a table field? I used the Pass field value as parameter, it does not seem to write into the table. Can anyone please help? <script type="text/javascript"> // Caspio form elements to calculate local total cost var unitcostField = document.getElementById('InsertRecordLocalUnitCost'); var unitsField = document.getElementById('InsertRecordUnit'); // var totalField = document.getElementById("cbParamVirtual1"); ** I tried using this to point to virtual field but it did not work ** var totalField = document.getElementsByClassName('cbFormData')[0]; // Caspio form elements to convert local to USD cost var exchangerateField = document.getElementsByName("InsertRecordExchangeRate")[0]; //var convertField = document.getElementById("cbParamVirtual2"); ** I tried using this to point to virtual field but it did not work ** var convertField = document.getElementsByClassName('cbFormData')[0]; var caspioForm = document.getElementById('caspioform'); // Event handler var calculateTotal = function (event) { // TODO: Do something on value change --> totalField.innerHTML = unitcostField.value * unitsField.value; } var calculateExchangeRate = function (event) { // TODO: Do something on value change --> convertField.innerHTML = (1/exchangerateField.value) * unitcostField.value; } // Run total calculation on input to any of these two fields unitcostField.addEventListener('input', calculateTotal); unitsField.addEventListener('input', calculateTotal); unitsField.addEventListener('input', calculateExchangeRate); unitcostField.addEventListener('input', calculateExchangeRate); </script> Thanks in Advance

I need to to perform functions that will calculate payments based on a loan amount, interest rate and term. This is an easy function in excel: =(MAX(Payments!M3:M32))/12 BUT, can I get some suggestions as to how to do this in Caspio? Secondly, I'd like to get an amoritzation schedule showing payments and declining balance over a period of time such as 1 year, 5 years, etc. Has anyone done this in Caspio?

I have a submission form where the user clicks a link and it's supposed to do a simple calculation based on a few fields. I can't seem to get it to work right though. The fields are: MRP (currency) Supplier_Discount (number) Cost (currency) It's a simple calculation I need to do. The user enters the MRP and Supplier Discount and clicks the link to calculate and fill in the Cost. The calculation is Cost=MRP-(MRP*Supplier_Discount) so if: MRP = 1000 Supplier_Discount = .3 Cost = 700 I tested the link calling the js and it works (used a message to test it) and also tested simply doing Cost= MRP and that worked also. It's when I try to call Supplier_Discount and do a calculation that it's not working. I'm not sure if it's because Supplier_Discount is a number field and not currency or if I'm just missing some syntax but any help would be greatly appreciated. I have this in an html block at the bottom of the submission form: <SCRIPT LANGUAGE="JavaScript"> $("#k_cyourcost").click(function cyourcost(){ var MRP = document.getElementById("InsertRecordMRP").value; var Supplier_Discount = document.getElementById("InsertRecordSupplier_Discount").value; var Cost = (MRP)-((MRP)*(Supplier_Discount)); document.getElementById("InsertRecordCost").value= Cost; }); </SCRIPT>

I have an MS Access app I'm converting to Caspio that has some simple VB code in the AfterUpdate of a form's controls, that changes the values in other controls based on user input. For example, there are 3 fields/controls: Cost (currency), default value 0 Markup (percentage integer), default value .5 (50%) Price (currency), default value 0 In Cost AfterUpdate the code is: Me.Price= ([Cost]*[Markup])+[Cost] so if the user entered 100 for cost it would set the Price value at 150 In Markup AfterUpdate the code is the same: Me.Price= ([Cost]*[Markup])+[Cost] so if Cost = 100 and the user changed the Markup value to .3 (30%) then the Price = 130. (If Cost = 0 or null the code needs to run with a msg 'you cannot markup a 0 value). In Price AfterUpdate the code is Me!MarkUp = ([Price] / [Cost]) - 1 so if Cost = 100 and a Price value of 130 is entered it updates Markup to .3 (30%). These 3 fields need to be saved in the table and not be calculated fields because the user needs to be able to enter any combination randomly and have it calculate the values for the other fields. I have no idea how to do this in Caspio- any help would be greatly appreciated.

Hello, I have a submission form with 3 fields - Price, Qty and Total Amount. May you please help me in developing a script to get the Total Amount when Price or Qty is changed. (without submitting the dp) The formula for Total Amount. is Price * Qty. Thanks in advance.

Good morning , I have a Pivot Table Report . I'm looking for a solution to replace the "13". For the count of all records with kostentest_cat = 1 (that are 13 records). can someone help. CASE WHEN [@field:kostentest_cat] = 1 THEN [@field:kostentest_hoeveelheid]-([@voorraad_ruw]/13) ELSE [@field:kostentest_hoeveelheid] END regards Peter .

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 everyone, I started using Caspio only a couple of days ago. As a first test project I am trying to build an invoicing app. I am now facing a problem and I really hope that someone here can help me. Before generating a new invoice, I need to enter all data for it. Customers I pick from a drop down, invoice date from the date picker etc. That is all fine. Now I select the Service I am sending the invoice for. (Let’s assume it’s only one service in the invoice.) This is another dropdown and refers to a table with, let’s say, the following content ID SERVICE PRICE 1 Accounting 100 2 Consulting 120 3 Training 140 In my data page I pick the service from a dropdown, display and value are both set to the 2nd column shown above. I pick Consulting, Consulting is displayed. The problem is, I need the price to show up somewhere so I can later calculate the invoice overall sum with it. I know I could insert a 2nd dropdown with SERVICE as display and PRICE as value, but I don’t want to drop down twice. So here is my question: How can I, in my data page, display the price (col. 3) of the service (col. 2) that I just picked in the drop down field? (By the way, the same solution will help me to, in the next phase, display the customer’s address in the printable invoice page, after I selected the customer’s name from a drop down. Same thing I guess.) Thank you very much in advance. I am looking forward to receiving your posts. Anatol

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 datapage submission form which I need to make a counter based on an external parameter. Virtual2 receives an external parameter (authorization field) on loading. This works fine. Goods is a hidden field in the datapage. An updated value should be saved in this field on submission. The script goes like below, but it doesn’t work. Actually I cant get goods to receive and store any value. Please advise on solutions. <SCRIPT LANGUAGE="JavaScript"> Function calculate() { var number = parsefloat(document.getElementsByName('cbParamVirtual2')[0].value); var newnumber = number + 1; document.getElementById('InsertRecordgoods').value = newnumber; } document.getElementById("caspioform").onsubmit= calculate; </SCRIPT>

Hi all, If someone knows how to hide some records on the Tabular report if Calculation => 5 for example?

Good morning all, I am currently trying to build a tabular report that will display a certain field from multiple entries next to one another. Now I know how to select the top record and sorting by a certain field to be able to get one. But I am trying to select from a specific entry so that the table would display each appointment time, in order, in one row. Here is what I have: Select EstimatedAppDateTime OVER (Order By EstimatedAppDateTime desc ROWS between 1 and 1 ) Where Job_RID= target.[@field:MM_Job_Record_ID] AND Location_Not_Used_DateTime is null This returns the error that the syntax of the OVER function is incorrect. Any ideas? Alternatively, if you have another way to easily display the records in one row I would be very appreciative. Thanks in advance!