Need help with coverting excel if function to Javascript

[Guest]

I'm working on an expense form where I am having some trouble with figuring out how to code my form to correspond to the excel formulas. I want the GST in the 'Accounting Area' to calculate based on

1. If there is mileage entered above

2. if 'yes' selected in the meal/ent dropdown I want the GST to be 1/2 the actual GST amount.

I hope this makes sense?

The excel equivalent would be as below:

=IF(MEAL/ENT="Yes",1/2*GST/HST,IF(MILEAGE>=1,(TOTAL/1.05)*5%,GST/HST))

Thanks a million in advance!!

[try67]

See: https://acrobatusers.com/tutorials/conditional-execution

https://acrobatusers.com/tutorials/how-to-do-not-so-simple-form-calculations

[Guest]

Following this the best I can, I have come up with the following, but it doesnt work. Can anyone help me with where I have gone wrong?

var mileage = this.getField("MILEAGE.0").value;

var meals = this.getField("MEALS/ENT.0").value;

var gst = this.getField("GST/HST.0").value;

if( meals = "yes" ) event.value = gst / 0.50;

else if( mileage > 1) event.value = (total / 1.05)*0.05;

else event.value = gst;

Thanks again.

[try67]

Pretty good... I see one mistake, though. The comparison operator in JS is "==", so change this line:

if( meals = "yes" )

To:

if( meals == "yes" )

[Guest]

I have fixed it but I am still having trouble, the GST box doesn't show anything at this point. I don't know what I am doing wrong, this seems like it should work but it just wont and I'm so frustrated because I have literally spent days doing trial and error on this and getting nowhere. Am I missing something here? Does this make sense? Would anyone be willing to look at a sample? Its a calculation based on whether mileage or a meal is selected in the top portion, the bottom portion is the accounting entry part.

var total = this.getField("TOTAL.0").value;

var subtotal = this.getField("SUBTOTAL0").value;

var mileage = this.getField("MILEAGE.0").value;

var meals = this.getField("MEALS0").value;

var gst = this.getField("GST/HST.0").value;

if( mileage > 1) event.value = ((mileage*0.51) /1.05) - total;

if( mileage < 1) event.value = "";

else if( meals == "YES" ) event.value = total/.5;

else if( meals == "NO" ) event.value = gst;

else event.value = gst;

[try67]

Your if-else statements are messed up. You need to clearly define which value to use in each situation and then build the logic based on that.

[Guest]

I have no idea how to do that, I don't know anything about JavaScript or accounting, so I'm learning as I go here. In my original example I have both items on the same line, so the GST is conditional based on if a number is put in mileage OR if there is an amount put in the receipt total and it is defined as a meal or not. In my mind this looks like the right order, can you give me an example of how its wrong?

I appreciate the help, but I have no clue how to go about fixing it hence this forum post.

[try67]

Is this the calculation for "GST" itself? If so, you have circular logic. You can use a field's value in its own calculation script.

[Guest]

So I have one field that is 'entry only' GST and one below that is calculated GST.

If a # is entered in mileage, I want the lower GST to calculate this way based on what the account is telling me. This is the part that probably confuses me the most.

(# miles x 0.51) / 1.05 = total

total - (# miles x 0.51)

If nothing is entered in mileage then I want to calculate GST based on the receipt total entered above. The dropdown is either 'yes' or 'no' and I want it to calculate like so:

Yes = GST / 50%

No = GST entered above from receipt

Does that make sense?

[try67]

Kind of... Check the JS Console (Ctrl+J) for any error messages. Also, you need to handle the situation where mileage==1, which you're currently not doing.