2 Replies Latest reply on Oct 17, 2013 6:08 AM by IDOTRhonda

# Complicated Excel form needs to be converted to PDF Fillable form

I have an Excel file have has hidden cheater fields (M&N) and a hidden lookup table (O:P).

The form fields that are used follow (using row 10 as an example, rows 10-25 are involved)

E10 = Lengthft (entry field)

F10 = Widthft (entry field)

G10 = Patchsy (formula)=ROUND(IF(F10="",0,(E10*F10)/9),3)

H10 = PlanPatchThicknessin (entry field)

I10 = MeasuredPavementThicknessin (entry field)

J10 = Adjustedsy (formula)=IF(F10="",0,IF(N10="Extra Work","Extra Work",G10*(N10/100)+G10))

M10 = hidden field (formula)=((I10-H10)/H10)*100

N10 = hidden field (formula)=VLOOKUP(M10,O1:P7,2)

Hidden Lookup (O1:P7):

 -99 -20 -30.0001 -15 -20.0001 -10 -10.0001 . 10.0001 10 20.0001 15 30.0001 Extra Work

I know very little about java script and have been learning as I go.  I have created some custom calculation fields but this is way over my head.  I have converted the pdf to a form and added the fields, I have already added calculations for Patchsy and ToDatesy (not shown in list of fields).

I am completely lost in how to proceed with the Adjustedsy field because it is using all these hidden fields to obtain the answer.

I hope someone can help me.

Thanks

• ###### 1. Re: Complicated Excel form needs to be converted to PDF Fillable form

Have you been able to figure out any of the code?

I would create variables for the Excel cells used in the formula and then set their value to the field of the field they represent. A Vlookup function could be written but one could also create a variable and set the value through the use of nested if statements or use the switch statement.

For example;

/*
E10 = Lengthft (entry field)
F10 = Widthft (entry field)
G10 = Patchsy (formula)= =IF(F10 = "",0, ROUND(((E10*F10)/9),3))
*/
function ROUND(nValue, nPlaces) {
// return nValue rounded to nPlaces number of decimal places;
return Number(util.printf("%,1 0." + nPlaces + "f", nValue));
} // end ROUND function;

// custom calculation script for field Patchsy;
var E10_Name = "Lengthft"; // field name for E10;
var E10 ="";
var F10_Name = "Widthft"; // field name for F10;
var F10 = "";
var oE10 = this.getField(E10_Name); // get field object;
if(oE10 == null) app.alert("Error getting E10 form field: " + E10_Name, 0, 1);
else E10 = oE10.value;
var oF10 = this.getField(F10_Name); // get field object;
if(oF10 == null) app.alert("Error getting F10 form field: " + F10_Name, 0, 1);
else F10 = oF10.value;
event.value = 0; // default value;
if(F10 != 0) {
// event.value = ROUND(((E10*F10)/9),3);
event.value = ROUND(((E10*F10)/9), 3);
}

Note this code includes some error checking for bad field names.

• ###### 2. Re: Complicated Excel form needs to be converted to PDF Fillable form

I have tried to write this out as a problem and not sure if it makes sense to anyone but me.  This is all used to fill one field (AdjustedSYRow1).  The last part of the problem is written as an if statement.  Not sure how else to write it out.

I understand the variable part and can write the first part of the problem - then I get to the lookup and don't know how to do that.

// Get field value

var v1 = getField("MeasuredRow1").value;

// Get field value

var v2 = getField("PlanRow1").value;

if (v1==0) event.value = "";

else event.value = (v1 - v2)/v2*100;

That will answer the first part of the problem, but then I get hung up on the rest of it.

First part of the problem:

((Measured Pavement Thickness - Plan Patch Thickness) / Plan Patch Thickness ) * 100

Example with form field names:  ((MeasuredRow1 – PlanRow1) / PlanRow1) * 100

This is where I get stuck:

If the answer to the above is…

< -30 then -20,

between -20.0001 and -30, then -15,

between -10.0001 and -20  then -10,

between -10 and 10, returns null(?), a period was used in the excel lookup because a zero would not work – but it means that there is no adjustment.

between 10.0001 and 20  then 10,

between 20.0001 and 30  then 15,

over 30 then “Extra Work”)

The above information (in bold) is then used to complete the rest of the problem.

=IF(F10="",0,IF(N10="Extra Work","Extra Work",G10*(N10/100)+G10))

Example with form field names:  If WidthftRow1=””,0,if(above answer is “Extra Work”, “Extra Work”, PatchRow1*(above answer/100)+PatchRow1