Copy link to clipboard
Copied
Is there a way to write a custom calculation script to create a nested if statement? I have been asked to convert an Excel file into a PDF fillable form.
I tried to break it down the best I could. Please don't laugh at my efforts, I really am a newbie and hope to learn more as I work with these files.
The error I get is SyntaxError: missing ) after condition 3: at line 4.
var v1 = getField("OverdepthRow1").value;
if (v1 !>= 1.3){
var result = v1*1.2;
event.value = result;
if (v1 !>= 1.2){
var result = v1*1.15;
event.value = result;
if (v1 !>= 1.1){
var result = v1*1.1;
event.value = result;
}else(event.value = v1;)
I appreciate any help and advise you can offer.
Thanks!
Copy link to clipboard
Copied
Couple of errors:
There's no such operator as "!>=". If you meant to say "not bigger than or equal to" then simply use the "smaller than" operator: "<"
Also, you should put an else before each if statement after the first one. Otherwise all of your conditions except for the last will be useless.
And you need to put curly brackets after the else statement, not parentheses.
Copy link to clipboard
Copied
Where you have something like this:
if (v1 !>= 1.3)
Do you mean: "If the variable v1 is not greater than or equal to 1.3"
or something else?
Copy link to clipboard
Copied
Also, if you have a working Excel formula, it would be helpful if you included it here.
Copy link to clipboard
Copied
Here is the excel formula: =IF(I6>=1.3,G6*1.2,IF(I6>=1.2,G6*1.15,IF(I6>=1.1,G6*1.1,G6)))
Not sure what I was thinking when I added ! (blush)
I forgot to define one of the variables (blush again)
I6 = OverdepthRow1
G6 = WithDowelsRow1
Here's my second stab at it...
var v1 = getField("OverdepthRow1").value;
var v2 = getField("WithDowelsRow1").value;
if (v1 >= 1.3){
var result = v2*1.2;
event.value = result;
else if {v1 !>= 1.2}{
var result = v2*1.15;
event.value = result;
else if {v1 !>= 1.1}{
var result = v2*1.1;
event.value = result;
}else(event.value = v1;)
I don't really understand what you mean about the curley brackets but I did try to put them where I thought you mean. I haven't got it working yet.
Copy link to clipboard
Copied
You actually made it worse now... 🙂
This line (and the others like it):
else if {v1 !>= 1.2}{
Needs to be:
else if (v1 !>= 1.2) {
And this line:
}else(event.value = v1;)
Needs to be:
} else {event.value = v1;}
Copy link to clipboard
Copied
I've tried both of these solutions but I am still having trouble. I made some changes to correct some of my goofs:
if v1 is greater than or equal to the values listed then v2 times the other values listed.
From try 67 - (When I click ok I get a syntax error 17:at line 18) I'm really confused about this - when I put the cursor at the end of the script it says Ln 17, Col 25 - there does not appear to be a line 18.
var v1 = getField("OverdepthRow1").value;
var v2 = getField("WithDowelsRow1").value;
if (v1 >= 1.3){
var result = v2 * 1.2;
event.value = result;}
else if (v1 >= 1.2){
var result = v2 * 1.15;
event.value = result;}
else if (v1 >= 1.1){
var result = v2 * 1.1;
event.value = result;}
}else{event.value = v1;}
From gkaiseril - I also tried this solution:
var v1 = getField("OverdepthRow1").value;
var v2 = getField("WithDowelsRow1").value;
event.value = v1;
if (v1 >= 1.3) event.value = v2 * 1.2;
if (v1 >= 1.2) event.value = v2 * 1.15;
if (v1 >= 1.1) event.value = v2 * 1.1;
This did not give any errors but it also did not calculate when I entered numbers into the form.
Copy link to clipboard
Copied
You did not copy the code myself or George gave you correctly. You need to read our replies more carefully and follow the instructions in them EXACTLY.
Copy link to clipboard
Copied
I'm sorry, my initial inquiry did not include the second variable which was a pretty big mistake. So I tried to add it to make it work.
I thought you told me to remove the ! point from !>= so I did. You said it was not a valid operator.
I included the excel formula in an earlier comment but it doesn't appear that anyone took notice to it because the solutions did not include v2 and I assumed I had to figure that out for myself.
>= is not the same as < in my world but I really don't know much about this world. If I was supposed to use it I apologize, I'm really trying to learn.
Copy link to clipboard
Copied
I never said that ">=" is the same as "<". I said that "!>=" is the same as "<" ... But since the former doesn't exist you have to use the latter.
Copy link to clipboard
Copied
I didn't mean to accuse you of anything. I interpreted it to mean the same because George used < in his answer. I need to use >=
I'm sorry I'm doing this all wrong.
Copy link to clipboard
Copied
You need to think about the logic of the code. Take this code you posted:
if (v1 >= 1.3) event.value = v2 * 1.2;
if (v1 >= 1.2) event.value = v2 * 1.15;
if (v1 >= 1.1) event.value = v2 * 1.1;
What will happen here if v1 is 1.5 and v2 is 5, for example? Go over it line by line and see if the result is what you intended it to be.
Copy link to clipboard
Copied
This is being used in a form that is used in construction inspection for over-depth patching. This is figuring the adjusted square yards for payment based on over-depth patches. Say for instance that the Plan Patch Thickness is 11" and the measured patch (with dowels) is 40 sq. yds (v2). But the Measured Patch Thickness is 15" The Over-depth amount is 136.4% (measured thickness/plan thickness = 1.36 (this is all figured in other fields of the form.
But now I have this nested if statement: =IF(I6>=1.3,G6*1.2,IF(I6>=1.2,G6*1.15,IF(I6>=1.1,G6*1.1,G6)))
I6 = Overdepth (v1)
G6 = WithDowels (v2)
Because the over-depth amount is 1.36 (v1) the measured patch (with dowels) is multiplied by 1.2. so 40*1.2= 48 Sy Yds.
In answer to your question "what would happen if v1 is 1.5 ad v2 is 5", this is unlikely because of the use of this form, but because v1 is greater than 1.3, v2 would be multiplied by 1.2 (5 * 1.2)
Copy link to clipboard
Copied
I seem to have gotten it to work.
var v1 = getField("OverdepthRow1").value;
var v2 = getField("WithDowelsRow1").value;
if (v1 >= 1.3){
var result = v2 * 1.2;
event.value = result;}
else if (v1 >= 1.2){
var result = v2 * 1.15;
event.value = result;}
else if (v1 >= 1.1){
var result = v2 * 1.1;
event.value = result;}
else{event.value = v2;}
Copy link to clipboard
Copied
You are also missing the closing "}" for each block of code after each if statement. Because of how you have ordered the test, the else might not be necessary since each if will execute in order from the highest value to the lowest value until the if test fails. If you change the order of the test then you need to test the upper and lower values to be tested.
var v1 = getField("OverdepthRow1").value;
event.value = v1;
if (v1 < 1.3) event.value = v1 * 1.2;
if (v1 < 1.2) event.value = v1 * 1.15;
if (v1 < 1.1) event.value = v1 * 1.1;