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. The system uses the following keys: Customer number for customers in the Customers (AR303000) window, and Type and Reference no. for documents in multiple windows, such as the Sales invoices (AR301000) window. The order of key fields is important for mapping, so you should follow the order of key fields as they are displayed in Visma Net windows. In particular, for documents, you have to assign first the Type field and then the Reference no. 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 (AR303000) window. 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 number 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.
Auto-numbering of master records is configured in the corresponding segmented keys in the Segment keys (CS202000) window. 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 workspace preferences. For example: The ARINVOICE sequence is specified for the auto-numbering of invoices in the Customer ledger preferences (AR101000) window.
You can update records imported with automatically generated IDs by searching for them in the system by using their unique fields available 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 Manage import scenarios (SM206025) window,
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)
window. In this example, we suppose that the Excel file doesn’t contain the customer
number, and you are using the CUSTOMER NAMEvalue from the Excel file to find the customer
record in the system.
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 (AR303000) window as the custom key, but you cannot find the record by using the Email field of the Main contact group of the General settings tab as the custom key.
Selector column
Selector columns in an Visma Net
window 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 in the window 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 in the window, such as
Customer ID -> Email.
For example: The instruction with the following settings
maps the Email selector column of the Customer number
field in the Customers (AR303000) window to the EMAIL field available in
the source.
Selector command mapping
Target object Field/action name Source field/value Customer summary Customer ID -> E-mail
Parent topic: