Jump to content
  • 0

Doing Calculation For Regular Fields In Forms And Reports


DesiLogi

Question

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. 

 

 

 

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

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

Link to comment
Share on other sites

  • 0

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!

Link to comment
Share on other sites

  • 0

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...