Key Fields and Search in Import Scenarios

In the mapping of an import scenario, you have to map the key fields first, mapping the target key fields in the system to the source key fields. (Key fields are the fields whose values uniquely identify a particular record and are used to find the record within the database.) The system uses the following keys: Customer ID for customers on the Customers (AR303000) form, and Type and Reference Nbr. for documents on multiple forms, such as the Invoices and Memos (AR301000) form. The order of key fields is important for mapping, so you should follow the order of key fields as they are displayed on MYOB Acumatica forms. In particular, for documents, you have to assign first the Type field and then the Reference Nbr. field.

Auto-Numbering of Records

You can import records with their key values from the source, and the records will have the same key values in the system and the source. For example, suppose you are importing records to the Customers form. If you have the customer CUST000001 in the Excel file, you can import this customer to the system so that the customer record will have the same ID: CUST000001. Later, you can update customer records by mapping the Excel column with the ID to the Customer ID field in the system.

As an alternative to importing records with their key values from the source, you can import records and have the system automatically assign IDs. For example, you can import the customer records so that after the import, the customer will have an automatically assigned ID in the system that was not in the Excel file, such as C000000001. You can import master records with automatically assigned IDs in one of the following ways:

• You can enable auto-numbering of records in the system and map the key fields to unique identifiers of records in the source.
• If there is just one line for each unique record in the data source, you can enable auto-numbering of records in the system and import each line from the source file as an unique record in the system with an automatically assigned ID. With this way of mapping, you do not map key fields in the import scenario and add the <Action: Insert> instruction at the beginning of the mapping.
• You can map the key field to a formula that produces unique identifiers for each imported record in the needed format. For details on creating formulas, see The Use of Formulas.

Auto-numbering of master records is configured in the corresponding segmented keys on the Segmented Keys (CS202000) form. Documents are usually automatically assigned a reference number in the system. During the initial implementation, you can disable auto-numbering of documents in the system, import the documents with their IDs, and then turn on auto-numbering starting from the last imported ID. Auto-numbering of documents is configured in the numbering sequences that are specified for the corresponding document types on the preferences form of a functional area. For example, the ARINVOICE sequence is specified for the auto-numbering of invoices on the Accounts Receivable Preferences (AR101000) form.

You can update records imported with automatically generated IDs by searching for them in the system by using their unique fields available in the source. For example, you can identify customers by email addresses or phone numbers in the Excel file. To search for a record, you have to declare a custom key or use a column of a lookup table. Both of these methods are described in the following sections.

Custom Key

To specify a custom key, you have to define the key by using the @@ notation; directly after the notation, assign the custom key field a value. On the Mapping tab of the Import Scenarios (SM206025) form, you should select the Commit check box for this field to make the system update the form after the key is specified. Below is an example of the settings for a mapping instruction that declares the Customer Name as the custom key for customer records you are importing to the Customers (AR303000) form. In this example, we suppose that the Excel file does not contain the customer ID, and you are using the CUSTOMER NAME value from the Excel file to find the customer record in the system.

You can specify custom keys to search for a record or a detail line. You can use the fields of the Summary object and the detail objects as custom key fields, but you cannot use the fields of related objects. For example, you can find the needed customer record in the system by using the Customer Name field of the Customers form as the custom key, but you cannot find the record by using the Email field of the Main Contact group of the General Info tab as the custom key.

Lookup Table

Lookup tables on an MYOB Acumatica form appear when a user clicks the magnifier button of a key field of the form to open the Select dialog box. When you are creating a mapping for an import scenario, you can use any columns of a lookup table that are available on the form for the key field. To use a column of a lookup table for a search in an import scenario, you have to map this column to the matching external field. Column names start with the key field name, followed by -> and then the name of the column on the form, such as Customer ID -> Email.

The following table shows the instruction that maps the Email column of the lookup table of the Customer ID field on the Customers (AR303000) form to the EMAIL field available in the source.

Certain records in the system are visible in a lookup table of a box on a form only if a particular branch or company is selected on the Company and Branch Selection menu. To make these records available for selection during import, you need to ensure that the mapping of the import scenario includes the selection of the needed branch or company on this menu.

You can do this by adding a row with the <Set: Branch> command to the import scenario on the Import Scenarios (SM206025) form. In the row, you specify the settings as shown in the following table.

The following screenshot shows the use of this command in a scenario that imports AR invoices. If customer visibility is restricted to a specific branch or company group, the branch determines whether customers are shown in the lookup table of the Customer box and which set of customers is shown. As a result, you must select an appropriate branch when importing records by using the scenario.