(Example) Import Data to a Subform from Microsoft Excel

This example shows how to import data to a subform from a Microsoft Excel file at runtime.

Background and Setup

Prerequisites

  • An external software system with the following requirements:
    • Can export data to a Microsoft Excel or CSV file.
    • Has data fields or columns that provide data that is similar enough to the fields in the Subform control that the data can be mapped from one system to the other.

    This is the most common usage for Excel import, and it is the use case shown in this example. There are other ways to create an Excel file to import.

    For more information, refer to Rules for Using Microsoft Excel Files with AgilePoint NX.

  • This example uses the Sales Invoice app from the AgilePoint NX App Store.

    For more information, refer to How to Get the App

Good to Know

  • The Subform form control must be a repeating subform.
  • The Sales Invoice app creates an invoice for a product sale.

    For the use case in this example, this app provides a form to enter information for the invoice. A runtime app user can use a Microsoft Excel file to import the information to the Invoice Items field, which uses a Subform control.

  • In the version of the Sales Invoice app released on May 1, 2018, the eForms use the Subform (Legacy) form control for the Invoice Items field. In this example, the Subform (Legacy) form control is migrated to the Subform (v8) form control.

    To migrate the Subform (Legacy) form control to the Subform (v8) form control, refer to the Migrate Subform field in the Subform (Legacy) form control.

Step 1: Enable the Import Excel or CSV Option in a Subform

First, configure the Subform form control to show the Import Excel or CSV option on the Sales Invoice Request form.

Good to Know

How to Start

  1. Click App Builder.

    Click App Builder
  2. On the App Builder Home screen, click All Apps.

    Click All Apps
  3. On the All Apps screen, on the Sales Invoice app, click Edit Edit icon.

    Click Edit
  4. On the App Details screen, hover over the Sales Invoice Request form, and click Edit Edit icon.

    Click Edit

Procedure

  1. On the Sales Invoice Request form, put your cursor over the Subform form control, and the form control toolbar shows.

    Subform Form Control Toolbar
  2. Click Edit Properties Edit Properties icon.

    Edit Properties icon
  3. On the Field Settings - Subform screen, click the Advanced tab.

    Advanced tab
  4. On the Advanced tab, select the Allow Import From Excel or CSV field.

    Select Allow Import From Excel or CSV
  5. To save the changes, click Close Screen Close Screen icon.

    The Sales Invoice Request form shows.


    Sales Invoice Request form

Step 3: Export the Microsoft Excel File From an External System

Next, download the Microsoft Excel file from an external system to use to import to the subform.

Good to Know

  • Exporting data in an Excel file from an external system may be completed on demand by the runtime app user, or the external system may create the Excel file programmatically, such as on a configured schedule. This is determined by your business requirements.
  • To import the data to a Subform control, the Excel file must be saved on the local machine for the runtime app user. If the exported Excel file is saved to any other storage system, such as Microsoft SharePoint, it must be downloaded to your local machine before you can complete the import.
  • In this example, the Excel file name is Sales Invoice Items.xlsx.

    The example Sales Invoice Items.xlsx file is in this format.

    ProductPriceQuantity
    Laptop Computer12002
    Desktop Computer7505
    Printer50020
    Portable Hard Drive10050

    Sales Invoive Items Table Format

Step 4: Import Data to a Subform from Microsoft Excel at Runtime

Next, import the data to a subform form control from the Microsoft Excel file at runtime.

Prerequisites

Good to Know

How to Start

  1. Click Work Center.

    Click Work Center
  2. In the left pane, click My Apps.

    Click My Apps

Procedure

  1. On the My Apps screen, click Sales Invoice.

    Click An App
  2. On the Sales Invoice Request form, in the Invoice Items section, click Import Excel or CSV Import Excel or CSV icon.

    Click Import Excel or CSV
  3. On the Import Excel or CSV screen, click Select File.

    Click Select File
  4. Select the Sales Invoice Items.xlsx file from the local machine that contains the invoice items.
  5. Click Next.

    Click Next

    The Verify Column Mapping screen shows.

    This screen maps the column names in the Excel file with the subform fields. If required, you can change the default mapping.

    In this example, the TotalPrice field populates the value based on the TotalPriceFormula field. The TotalPriceFormula field is a hidden field and calculates the total price based on the values from the Price and Quantity fields. Because the eForm does these calculations, these fields are not mapped.


    Click Import
  6. On the Verify Column Mapping screen, click Import.

    Click Import

    The Sales Invoice Request form shows the invoice items from the imported Microsoft Excel file.


    Shows Data From Excel

How to Get the App

This example is based on the Sales Invoice app from the AgilePoint NX App Store. Use this procedure to get the app.

Prerequisites

Good to Know

  • This procedure is optional. It is provided so you can test the example with an out-of-the-box app.
  • This is part of the Background and Setup procedures, but many users preferred this step to appear after the example, not before it.

Procedure

  1. Click App Store.

    App Store
  2. In the CATEGORY section, click Sales.

    App Store screen
  3. Click the Sales Invoice app.

    Sales Invoice screen
  4. On the Sales Invoice screen, click Add.

    Sales Invoice screen
  5. On the Trust App screen, click Trust it.
  6. Click Return to site.