• 0

Doing Calculation For Regular Fields In Forms And Reports

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.

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

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!

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.

Regards,

kristina

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. ×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.