Creating multiple drop down lists from data in an excel file?

[tva96]

Hello, I am relatively new to using the javascript editor in Adobe.

I want to create an editable pdf form with 3 drop down lists from data I have in an excel file. The excel file has multiple columns, but I only want to read three columns, columns B (ID), D (company) and E (sector). All entries in column B are unique, but entries in columns D and E can be duplicates. For example, the value in drop down 1 will be company, the value in drop down 2 will be sector and the value in drop down 3 will be ID.

- I want drop down 1 to display all entries in column D, companies, without displaying duplicates.

- Based on the selection made in drop down 1, assuming 'company A', I want drop down 2 to display entries in column E that are on the same row with the value selected in drop down 1; i.e. I want drop down 2 to display all sectors in 'company A'.

- Lastly, based on the values selected in drop down 1 and 2, 'company A' and 'sector B', I want drop down 3 to display entries in column B that have the selected values of drop down 1 and 2 on the same row; i.e. I want drop down 3 to display all IDs in 'sector B' of 'company A'.

All this data is stored in an excel file that has a lot of entries and is updated weekly. The way I am currently thinking of doing this is to hard code all the information in the 'custom validation scripts' of drop down 1 and 2, so that the selection in 1 controls the options displayed in 2, and the selection in 2 controls the options displayed in 3. The main issue I have with this is that there are too many entries, which will make it too time consuming, and the fact that the file is constantly update, means I would have to keep editing the validation scripts.

I want to know if it is possible to have the pdf form call/access/load the excel file, and have a javascript code sort through the file data and dynamically populate the drop down boxes when selections are made. If you have any idea if this is possible or if it is not, please let me know. Any help will be much appreciated.

[Thom Parker]

You have quite a few different issues here. You might want to consider breaking them down into simple, single items, and ask them individually on the forums.

But first, the answer is Yes, you can do all of these things with Acrobat JavaScript.  Some of these task are quite complicated and/or problematic. The trickiest is loading data from an Excel file. There are no scripting options for connecting Acrobat directly to an Excel file.  So the way to do this is to export the data from Excel as a Tab delimited or CSV file. There is an Acrobat JavaScript function for loading field data from a Tab delimited file, but that won't help you here, instead the entire data file needs to be loaded with the "util.readFileIntoStream()" function and then parsed to extract the column items. 

You can find out more on this topic, as well as all the tasks you need to perform here:

https://www.pdfscripting.com/public/ExcelAndAcrobat.cfm

https://www.pdfscripting.com/public/List-Field-Usage-and-Handling.cfm

[try67]

I've developed a tool that allows you to set up such drop-down fields very easily, based on a spreadsheet. You can find it here: Custom-made Adobe Scripts: Acrobat -- Create Cascading Dropdowns

[gerrye50334270]

Will your tool work for 3 lists?

[try67]

Yes, it will.

[gerrye50334270]

Can I use the same cascade in two different forms or is it just a one of?  Is $75 cad or us?

[try67]

- Yes, you can run the script on multiple files with the same input text file, if that's what you mean.

- USD

Please send me an email for further questions about this tool (try6767 at gmail.com).