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 several ways, which are described below:
  • 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 in module preferences. 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 Select dialog box. Both of these methods are described below.

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 doesn't contain the customer ID, and you are using the CUSTOMER NAME value from the Excel file to find the customer record in the system.
Table 1. Custom Key Mapping
Target Object Field / Action Name Native Field / Action Name Commit Source Field / Value
Customer Summary @@AcctName @@AcctName =[BAccount.AcctName]
Customer Summary Customer Name AcctName Selected CUSTOMER NAME

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, but not the fields of related objects, as custom key fields. 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.

Selector Column

Selector columns on an MYOB Acumatica form appear when a user clicks the Magnifier icon of a key field of the form to bring up the Select dialog box. When you are performing mapping for an import scenario, you can use any selector columns that are available on the form for the key field. To use a selector column for a search in an import scenario, you have to map the selector column to the matching external field. Selector column names start with the key field name, followed by -> and then the name of the selector column on the form, such as Customer ID -> Email. For example, the instruction with the following settings maps the Email selector column of the Customer ID field on the Customers form to the EMAIL field available in the source.
Table 2. Selector Command Mapping
Target Object Field / Action Name Source Field / Value
Customer Summary Customer ID -> Email EMAIL