Is it possible to convert excel function if(and to a custom calculation script

[IDOTRhonda]

Our field staff use a lot of Excel files for project documentation.  Our organization is moving to iPads instead of laptops and we have found that fillable PDFs are a great resource for our staff.  I have created many of these forms which contain multiple rows and I'm just beginning to learn how to write custom calculation scripts.  I have a calculation that is more complex than most and I'm not sure if this can be written in script.

=IF(I6<0.7,G6*0.8,IF(AND(I6>=0.7,I6<0.8),G6*0.85,IF(AND(I6>=0.8,I6<0.9),G6*0.9,IF(AND(I6>=0.9,I6<=1.1),G6*1,IF(AND(I6>1.1,I6<=1.2),G6*1.1,IF(AND(I6>1.2,I6<=1.3),G6*1.15,"extra work"))))))

I6 is OverdepthRow1

G6 is WithDowelsRow1

I think I understand how to begin

var v1 = get.thisField("OverdepthRow1").value;

var v2 = get.thisField("WithDowelsRow1").value;

if(v1<0.7){

var result = v2 * 0.8

event.value = result}

else if(v1>=0.7 ----------------Now I don't know what to do!!!

Hopefully I've used the correct parenthesis and curly brackets in the right places.

I appreciate your help and guidance.

Thanks

Rhonda

[George_Johnson]

The first two lines should be:

var v1 = +getField("OverdepthRow1");

var v2 = +getField("WithDowelsRow1");

Your next 3 lines are correct and the code should continue on like:

else if (v1 >= 0.7 && v1 < 0.8) {

    event.value = v2 * 0.85;

} else if (v1 >= 0.8 && v1 < 0.9) {

    event.value = v2 * 0.9;

}  else if...

// and so on until...

} else {

    event.value = "extra work";

}

[IDOTRhonda]

Thank you so much, I will get right to work on this!

Rhonda

[IDOTRhonda]

I've been working on this calculation and I keep running into trouble.  I can't get this formula to calculate, at one point it showed "Extra Work" no matter what I entered into the form.  I've tweaked it and experimented but I'm not getting anywhere.  Here's what I have:

var v1 = +getField("OverdepthRow1");

var v2 = +getField("WithDowelsRow1");

if (v1 < 0.7){

var result = v2 * 0.8

event.value = result}

else if (v1
>= 0.7 && v1 < 0.8) {

event.value = v2 * 0.85;

} else if (v1
>= 0.8 && v1 < 0.9) {

event.value = v2 * 0.9;

} else if (v1>=0.9
&& v1 <=1.1) {

event.value = v2 * 1;

} else if (v1>1.1
&& v1<=1.2) {

event.value  = v2 * 1.1;

} else if (v1>1.2
&& v1<=1.3) {

event.value = v2 * 1.15;

} else {

event.value = "extra work";

}

At one point I got this error message:

The value entered does not match the format of the field [ SYRow1 ]

SYRow1 is the field where the calculation is located.  It is formatted as a number with 3 decimal places.  The fields (that are a part of the calculation) are formatted this way: v1 is formatted as a percentage to 1 decimal place. v2 is formatted as a number with 3 decimal places.  I messed around with formats and it didn't help - I put the formats back the way they were and the error message went away but this field does not calculate at all now. 

[George_Johnson · Accepted solution]

I'm sorry, I made a mistake with those first two lines. I corrected one problem but introduced another. They should be:

var v1 = +getField("OverdepthRow1").value;

var v2 = +getField("WithDowelsRow1").value;

[IDOTRhonda]

Thank you so VERY VERY much

[IDOTRhonda]

Darn it, just a little more help please?  When I enter a value which would generate the phrase "extra work" I get an error message that the value entered does not match the format of the field - I understand it's because I have it formatted to a number with 3 decimals but I really need it to be.  Is there someway to make this work?  Perhaps a custom validation script?  (Which I don't know how to write...)

[try67]

You would need to use a custom Format script for it to work, but an easier way is to simply set the field's Format to None.

[IDOTRhonda]

BLUSH - I guess I should have known it was format and not validate.

This is for a pay quantity and it needs to be formatted to 3 decimal places.

I did give it a try just to see - when set to none, it shows a 0 instead of Extra Work.

[try67]

There are other ways of doing that, for example:

event.value = (v2 * 1.15).toFixed(3);

[IDOTRhonda]

Thanks, I'll give that I try.  I really appreciate all the help and advise!

[IDOTRhonda]

I've finally had a chance to work on this again.  Now the field says "extra work" all the time.  Even when the form is not filled out.  When I do fill it out with numbers that would not be extra work is still says extra work.  It's as though it's not calculating at all.  here is my script.

var v1 = +getField("OverdepthRow1");

var v2 = +getField("WithDowelsRow1");

if (v1 < 0.7){
var result = (v2 * 0.8).toFixed(3)
event.value = result}

else if (v1
>= 0.7 && v1 < 0.8){
event.value = (v2 * 0.85).toFixed(3);

} else if (v1
>= 0.8 && v1 < 0.9){
event.value = (v2 * 0.9).toFixed(3);

} else if (v1>=0.9
&& v1 <=1.1){
event.value = (v2 * 1).toFixed(3);

} else if (v1>1.1
&& v1<=1.2){
event.value  = (v2 * 1.1).toFixed(3);

} else if (v1>1.2
&& v1<=1.3){
event.value = (v2 * 1.15).toFixed(3);

}else{
event.value = "extra work";
}