Doing Calculation For Regular Fields In Forms And Reports

[DesiLogi]

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. 

 

 

 

[LWSChad]

I would use a Virtual field that feeds a Calculated Field then

//OnMousingOver the Submit or Update Button
document.getElementById('**SUBMIT_OR_UPDATE_ELEMENT**').onmouseover = function() { 

  //store the calculated data
  var vCalc1 = document.getElementById('**CALCULATED_FIELD**');
  //identify the field to push the calculated data to
  var vField1 = document.getElementById('**DATA_FIELD**');

  //change the data field
  vField1.value = vCalc1.value; (OR, vField1.innerHTML = vCalc1.innerHTML;)

}

Keep everything visible during dev; then you can hide the TextFields for deployment.

 

Hope This Helps

[Xiang]

Good morning, Desi!

 

How are you doing?

 

I think, you can use a JavaScript code. This code should work:

<SCRIPT LANGUAGE="JavaScript">
function costcalculate()
{
var cost = parseFloat(document.getElementById("InsertRecordCost").value);
var markup = parseFloat(document.getElementById("InsertRecordMarkup").value);
document.getElementById("InsertRecordPrice").value = cost*markup+cost; 
}

function markupcalculate()
{
var cost = parseFloat(document.getElementById("InsertRecordCost").value);
var markup = parseFloat(document.getElementById("InsertRecordMarkup").value);
if (cost==0)
{
document.getElementById("InsertRecordMarkup").value = 0.5;
alert("You cannot markup a 0 value");
}
else
document.getElementById("InsertRecordPrice").value = cost*markup+cost; 
}

function pricecalculate()
{
var cost = parseFloat(document.getElementById("InsertRecordCost").value);
var price = parseFloat(document.getElementById("InsertRecordPrice").value);
document.getElementById("InsertRecordMarkup").value=price/cost-1;
}

document.getElementById("InsertRecordCost").onchange = costcalculate;
document.getElementById("InsertRecordMarkup").onchange = markupcalculate;
document.getElementById("InsertRecordPrice").onchange = pricecalculate;
</SCRIPT>

I'm learning the JavaScript now, so, I'll be grateful, if you tell me if the code works.

 

Have a nice day!

[cheonsa]

Hi all,

 

Using calculated fields in the reports form, you can perform conditional cases. Also, Calculated Values in submission form is now available in the release 13.0.

You may check these links:

https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/#ConditionalCasesInCalculatedFields

https://howto.caspio.com/datapages/datapage-components/calculated-values/

 

Regards,

kristina