(Example) Configure an Auto-Lookup Form Control with Microsoft Excel

This example shows how to configure the Auto-Lookup form control with Microsoft Excel as a data source.

Background and Setup

Prerequisites

Good to Know

Step 1: Create a Lookup with Microsoft Excel as the Data Source

First, create a lookup with a Microsoft Excel file stored in SharePoint as the data source based on multiple columns for the Auto-Lookup form control.

Good to Know

  • On the Configure Lookup screen, in the Data Mapping section:
    • The Source Column includes the column for the RecordID. The RecordID column is created by default to store the ID for the records.
    • You can configure the columns to show or hide in an eForm.
    • You can specify custom names for the columns to show on an eForm.

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
  5. On the Sales Invoice Request form, in the Toolbox, open the Advanced Controls tab.

    Advanced Controls tab
  6. On the Advanced Controls tab, drag a Auto Lookup Auto Lookup icon form control onto your eForm.

    Drag Auto Lookup Control

Procedure

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

    Data Grid Form Control Toolbar
  2. Click Edit Properties Edit Properties icon.

    Edit Properties icon
  3. On the Field Settings - Auto-Lookup screen, click the Configure tab.

    Configure tab
  4. On the Configure tab, in the Lookup Source field, click Add Lookup Add Lookup icon.

    Click Add Lookup
  5. On the Select Data Source Type screen, select Microsoft Excel.

    Select Microsoft Excel
  6. Click Next.
  7. On the Lookup Details screen, in the Lookup Name field, enter Sales Invoice Items.

    Enter Lookup Name
  8. In the Lookup Type field, select Multiple Columns.

    Select Lookup Type
  9. Click Next.
  10. On the Choose Document Repository screen, select SharePoint.

    Select SharePoint
  11. Click Next.
  12. On the Configure Action screen, in the Access Token field, select SharePoint for Sales.

    Select Access Token
  13. In the Site field, select Sales Invoice.

    Select Site
  14. In the Document Library field, select Documents.

    Select Document Library
  15. In the Folder field, select the Sales Invoice Items.xlsx file.

    As a prerequisite, the Sales Invoice Items.xlsx file must exist in SharePoint.


    Select Excel File
  16. Click the Quick Config tab.

    Click Quick Config Tab
  17. On the Quick Config tab, in the Table field, select Invoice_Items.

    As a prerequisite, the Invoice_Items table must create in the Sales Invoice Items.xlsx file.


    Select Table
  18. On the Data Mapping field, deselect RecordID.

    The RecordID column is not required to show at runtime.


    Deselect RecordID
  19. Click Finish.

    The Field Settings - Auto-Lookup screen shows the lookup Sales Invoice Items in the Lookup Source field.


    Shows Lookup
  20. To save the changes, click Close Screen Close Screen icon.

    The Sales Invoice Request form shows.


    Sales Invoice Request form

    To view the results for the lookup, you must configure the lookup with the supported form controls. For examples that use this lookup, refer to:

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.