I need help converting Excel Lookup tables to acrobat pdf.

I am working on converting a series of Excel forms to fillable Acrobat pdf forms. up til now I was able the calculations the client requested with my VERY limited javasript knowledge (i.e. basic boolean expressions I learned in tutorials here).

Now they are requesting a field be filled with an amount found in a table based on 2 conditions.

field1 - a dropdown menu which would determine which category the data comes from - first condition
field2 - numeric field - second condition
field3 - numeric field (column C in data table) result

There is no fixed mathematical relationship to the values of field2 and field3.

Is there a way to do this that does not involve a massive boolean expression?

I am using Acrobat 9 Pro on a Mac, though I may be getting X soon. This will be a distributed form.

Please speak to me as if I'm an idiot.

Thanks for your help.


alexandra smith

Voted Best Answer

A lookup table is essentially a table which you access by "house numbers". Transposed to (Acrobat) JavaScript, it is a multi-dimensional array. In your case, it would be 2-dimensional.

The easiest to implement would be if the second condition would also be a dropdown. In this case, you could set up both dropdowns with the face value (that's what you see in the dropdown) and the return value corresponding to the row or column number where you would find the resulting value. These two numbers would correspond to the indices in the two-dimensional array.

A very simplistic example:

Your first dropdown consists of the following pairs (combo box named dd1):
"my first", 0
"my second", 1
"my third", 2

Your second dropdown had the following pairs (combo box dd2):
"red", 0
"green", 1
"blue", 2

Your lookup table could then look like this (you will need some imagination to turn that into a table; this braindead forum software does not let me enter simple tables):

source red green blue

my first 12 34 187
my second 74 99 1
my third 122 9 231

Converting this to Acrobat JavaScript would give you the following (in a real form, that would be defined in a document-level script):

var myLUTarr = new Array() ;
myLUTarr[0] = [12,34,187] ;
myLUTarr[1] = [74,99,1] ;
myLUTarr[2] = [122,9,231] ;

The result would then be calculated (very roughly; you may have to add conditions for valid selections, such as adding a "select me", -1 selection to the dropdowns, making that entry the default)

if (this.getField("dd1").value >= 0 && this.getField("dd2").value >= 0) {
event.value = myLUTarr[this.getField("dd1"].value*1][this.getField("dd2").value*1] ;

And that should do it. Now, you can expand the whole thing. This can get pretty big, but arrays are handled quite well in Acrobat JavaScript; some time ago, we did a demo for a lookup table containing the distances between some 2500 points… which turned out into a file size of 6MB, but it loaded within a few seconds, and lookups were instanteanously.

hope this can help.

Max Wyss.

By Max Wyss   

Please specify a reason: