Question Details

avatar
You're on your way! It won't take you many more points to reach the next status level: Contributor. And as you answer other users questions and accumulate more points, you're also building your reputation with the community and with AcrobatUsers.com.

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.

-alex


alexandra smith 814 days ago


Voted Best Answer

avatar
<p>Congratulations! The Acrobat User Community selection team has approved your status as Expert in the Acrobat User Community. We appreciate all your past contributions and hope you will continue to support the community by answering questions and participating in the Acrobat Answers Q&A program as you've done in the past.</p>

<p>As an expert, you will get a badge to display on your profile with the level EXPERT next to your name and you have become eligible to be nominated to the Adobe Community Professional Program.</p>

<p>After reviewing the Adobe Community Professional Program information at https://www.adobe.com/communities/professionals/, please let us know if you are interested.</p>

<p>Someone from the selection team will contact you soon regarding the community professional program. Keep up the good work!</p>

<p>Regards,</p>

<p>The Acrobat User Community Team</p>


<p>**** Disclosure Requirements for Endorsements Received by Third Parties  ****</p>

<p>If you endorse or refer people to the Acrobat User Community in any venue,  it is our responsibility to ensure that you are aware of required compliance with the <a href="http://ftc.gov/os/2009/10/091005revisedendorsementguides.pdf">FTC’s Guidelines</a>. Both you and Adobe can be held liable under federal law for making unsubstantiated claims and for not disclosing your relationship with Adobe. In particular:</p>

<ol>
<li>Always disclose if you have received any type of incentive – free products, promotional items, travel, gifts, payment, and so on from Adobe. The disclosure must be on the same page as the endorsement.</li>
<li>Be truthful. Endorsements should not be misleading. Never make unsubstantiated or exaggerated claims about the benefits of Adobe products or programs. Any statements or endorsements about an Adobe product must be based on upon your actual experience.</li>
</ol>

Thank you again for your support of Adobe and the Acrobat User Community</p>

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 answered 814 days ago  |   Comments (4)  |  New Comment

avatar
You're on your way! It won't take you many more points to reach the next status level: Contributor. And as you answer other users questions and accumulate more points, you're also building your reputation with the community and with AcrobatUsers.com.

That makes sense, the problem is (and I should have said this before) the values for dd2 and therefore the results are different for each "category" in dd1. Following your example, if a person chooses red from dd1 the choices for dd2 need to be red1, red2 and red3 resulting in redA, redB and redC. If they choose blue for dd1, dd2 choices are blue1, blue2 or blue3, etc.

Can I make the choice of values of dd2 conditional on the choice made in dd1? Would I then set up a separate arrays for each result of dd1, or would I pull all results from one massive one?

Thanks so much!

alexandra smith Commented 814 days ago  |   Report


avatar
<p>Congratulations! The Acrobat User Community selection team has approved your status as Expert in the Acrobat User Community. We appreciate all your past contributions and hope you will continue to support the community by answering questions and participating in the Acrobat Answers Q&A program as you've done in the past.</p>

<p>As an expert, you will get a badge to display on your profile with the level EXPERT next to your name and you have become eligible to be nominated to the Adobe Community Professional Program.</p>

<p>After reviewing the Adobe Community Professional Program information at https://www.adobe.com/communities/professionals/, please let us know if you are interested.</p>

<p>Someone from the selection team will contact you soon regarding the community professional program. Keep up the good work!</p>

<p>Regards,</p>

<p>The Acrobat User Community Team</p>


<p>**** Disclosure Requirements for Endorsements Received by Third Parties  ****</p>

<p>If you endorse or refer people to the Acrobat User Community in any venue,  it is our responsibility to ensure that you are aware of required compliance with the <a href="http://ftc.gov/os/2009/10/091005revisedendorsementguides.pdf">FTC’s Guidelines</a>. Both you and Adobe can be held liable under federal law for making unsubstantiated claims and for not disclosing your relationship with Adobe. In particular:</p>

<ol>
<li>Always disclose if you have received any type of incentive – free products, promotional items, travel, gifts, payment, and so on from Adobe. The disclosure must be on the same page as the endorsement.</li>
<li>Be truthful. Endorsements should not be misleading. Never make unsubstantiated or exaggerated claims about the benefits of Adobe products or programs. Any statements or endorsements about an Adobe product must be based on upon your actual experience.</li>
</ol>

Thank you again for your support of Adobe and the Acrobat User Community</p>

As said, I described the most simple set up. However, nothing prevents you from modifying your table so that the number of options in dd2 varies with the dd1 selected. If you look at it, you can consider the options for dd1 a an array. When you then look closer, you will notice that it is not only an array, but an array of arrays (the options of dd2 depending on the selected dd1.

Now, to get back to the question: you don't need to maintain totally different independent tables for every dd1; you have your master table which contains everything.

For variying dd2 options, you would, however, have to expand the information in your master table. Instead of just the result, you would use value pairs: dd2 "name" and "result" (which you represent an array as well). … and that gets you a simple 3-dimeinsional array :).

To get practical, you have two ways to select the options for dd1 and dd2:

1. you use dropdowns (combo boxes), where you update the options of the of the dd2 combo box dynamically based on the selection of the dd1 box. When you set up your master array, make sure that the structure of the dd1 arrays corresponds to the structure of an array needed to set up a combo box (see documentation). Then when you make a selection for dd1, you use setItems() to load dd2, and you have your lookup.

2. you use popups (with the popUpMenuEx() method). It is essentially the same; you set up the syntax for the popup by using the information in the master table. The advantage of the popup is that it will not have to deal with modifying combobox field options, and that it requires less real estate on your form.

Hope this can help.

Max Wyss Commented 813 days ago  |   Report


avatar
You're on your way! It won't take you many more points to reach the next status level: Contributor. And as you answer other users questions and accumulate more points, you're also building your reputation with the community and with AcrobatUsers.com.

I think I understand what you're saying.

Thanks so much for your help Max.

alexandra smith Commented 813 days ago  |   Report


avatar
<p>Congratulations! The Acrobat User Community selection team has approved your status as Expert in the Acrobat User Community. We appreciate all your past contributions and hope you will continue to support the community by answering questions and participating in the Acrobat Answers Q&A program as you've done in the past.</p>

<p>As an expert, you will get a badge to display on your profile with the level EXPERT next to your name and you have become eligible to be nominated to the Adobe Community Professional Program.</p>

<p>After reviewing the Adobe Community Professional Program information at https://www.adobe.com/communities/professionals/, please let us know if you are interested.</p>

<p>Someone from the selection team will contact you soon regarding the community professional program. Keep up the good work!</p>

<p>Regards,</p>

<p>The Acrobat User Community Team</p>


<p>**** Disclosure Requirements for Endorsements Received by Third Parties  ****</p>

<p>If you endorse or refer people to the Acrobat User Community in any venue,  it is our responsibility to ensure that you are aware of required compliance with the <a href="http://ftc.gov/os/2009/10/091005revisedendorsementguides.pdf">FTC’s Guidelines</a>. Both you and Adobe can be held liable under federal law for making unsubstantiated claims and for not disclosing your relationship with Adobe. In particular:</p>

<ol>
<li>Always disclose if you have received any type of incentive – free products, promotional items, travel, gifts, payment, and so on from Adobe. The disclosure must be on the same page as the endorsement.</li>
<li>Be truthful. Endorsements should not be misleading. Never make unsubstantiated or exaggerated claims about the benefits of Adobe products or programs. Any statements or endorsements about an Adobe product must be based on upon your actual experience.</li>
</ol>

Thank you again for your support of Adobe and the Acrobat User Community</p>

you're most welcome. Otherwise, keep asking… that's what this forum is here for.

Max Wyss Commented 812 days ago  |   Report


avatar

  |   Report
Was this answer helpful?  
1
0


Download
Acrobat XI trial

Get the trial now


Other Questions Needing Answers