Skip to main content
tva96
tva96
New Participant
March 22, 2018
Question

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

  • March 22, 2018
  • 2 replies
  • 3688 views

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.

    This topic has been closed for replies.

    2 replies

    try67
    try67
    Adobe Expert
    March 22, 2018

    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
      gerrye50334270
      Known Participant
      November 9, 2018

      Will your tool work for 3 lists?

      try67
      try67
      Adobe Expert
      November 9, 2018

      Yes, it will.

      Thom Parker
      Thom Parker
      Adobe Expert
      March 22, 2018

      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

      Thom Parker - Software Developer at PDFScriptingUse the Acrobat JavaScript Reference early and often
      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices