Using an Excel spreadsheet to populate fields

[C_m_white]

Hello,

I am trying to create a Form with LiveCycle Designer 8.2 where the user selects one field, and then many fields are automatically populated based on that selection.  For example, the user selects the name of the project they are working on and then project details (such as location, owner, contact, industry, etc.) are automatically filled in with data from an Excel worksheet.

I will be distributing this form to a wide audience, so the data that populates the form will need to "stay" with the form after distribution.  Does the Excel worksheet have to be sent along with the form to make this happen?  Or does the data automatically move with the form after it is sent out?  If not, is there a better way to accomplish such a task?

Examples would be extremely helpful.

Thanks!

[Former Community Member · Accepted solution]

While you can create a PDF from an Excel spreadsheet the intention is to capture the look and feel of the spreadsheet and not any of the behaviour associated with the spreadsheet. You have to add objects in Designer to make the form interactive. Additionally, you can use Excel data to populate list boxes and drop-downs at design-time but the spreadsheet has no relevance at run-time.

The simplest solution in my mind is to export the data from Excel in a comma-delimited format and use the data to create one-dimensional arrays in Designer. The arrays can be used to populate drop-downs and drive form behaviour.

For example, for a drop-down list called 'projectName' create an initialize event which calls a function on a script object called 'loadProjects'.

// form1.page1.subform1.projectName::initialize - (JavaScript, client)

myScriptObject.loadProjects(this);

The function loads the drop-down with the values from the array called 'projects'. On the 'projectName' exit event, if a value is selected from the drop-down the function 'loadData' is called to populate the fields 'location' and 'owner' based upon the relative array indexes. If a value is not selected from the drop-down the fields 'location' and 'owner' are reset.

// form1.page1.subform1.projectName::exit - (JavaScript, client)

if (this.isNull) {

  form1.page1.subform1.location.rawValue = null;

  form1.page1.subform1.owner.rawValue = null;

}

else {

  myScriptObject.loadData(this);

}

// form1.#variables[0].myScriptObject - (JavaScript, client)

var projects = new Array("","ABC","DEF","GHI");

var locations = new Array("","Sacramento","Ottawa","Fernie");

var owners = new Array("","Anne","Bruce","Carl");

function loadProjects(dropdownField) {

  for (var i=0; i < projects.length; i++) {

    dropdownField.addItem(projects[i]);

  }

}

function loadData(dropdownField) {

  form1.page1.subform1.location.rawValue = locations[dropdownField.selectedIndex];

  form1.page1.subform1.owner.rawValue = owners[dropdownField.selectedIndex];

}

Steve

[C_m_white]

Thanks Steve, this worked great!