Import Manager - Advanced Import Definitions

Overview

The Import Manager allows users to import information from external files such as Excel spreadsheets to tables in SalePad by creating an import definition and a field mapping.

This article will cover features and security for Import Manager as of version 5.2.18 of SalesPad Desktop, released January 2022. For core features refer to Import Manager.

In SalesPad Desktop 5.2.18 and newer the Import Manager is able to import Sales Documents with Sales Document Lines, CRM Contacts with Addresses, and Sales Document Tracking Numbers.

Additionally, scripting hooks have been added to in order to populate certain pieces of data to the imported data rows prior to importing the document and to validate whether the importing record already exists in the system. 

Finally, new logic can be been added to the Import Manager to ensure that any duplicate records can be compared before deciding whether to cancel the import process or continue with import of the records.

Please submit a request for services for assistance with Import Manager Scripting needs.

Security

To properly configure securities for Import Manager, navigate to the Cavallo drop-down menu, left-click Security Editor, and search for the Import Manager in the Security pane on the right section of the Security Editor. Security or Script changes will take effect for the modified group(s) on next sign-in to SalesPad Desktop.

Import Manager - Enables access to the Import Manager

• Misc

  • Can Clear Import Table - When false, users will no longer be able to clear import tables. Defaults to True.

  • Can Delete Import Definitions - When true, users can delete import definitions in Import Manager. Defaults to True.

  • Can Edit Import Definitions - When true, users can edit import definitions in Import Manager. Defaults to True.

  • Can Run Imports - When true, users can run imports in Import Manager. Defaults to True.

• Scripts

  • Pre Import Script- C# script that executes before the Import Manager records are validated and imported.

  • Pre Load Existing Object Script- C# script that executes before the Import Manager determines if an import record already exists in the SalesPad environment.

Import Definition Setup

In the following Import Definitions, when using scripting to populate key fields or additional fields such as Sales Document Number or Address Code ID, these "scripted fields" must still be included in the Import Field Mapping, and columns for these fields must be included in the source import file.

It is important to select the appropriate business object prefix (SD., SDLINE., ADDR., CUST., etc.) in several of the updated Import Definitions.

Sales Document Without Sales Lines

Add a new Import Definition to the Import Manager that uses the Sales Document table. Ensure that all sales document key fields and required fields are present in the field mappings:

• Sales Doc Num
• Sales Doc Type
• Customer Num
• Sales Doc ID

And add any additional fields as needed for the import document, providing the import document column name in the corresponding Import Field that will be used to map the value to its business object value.

Sales Document With Sales Lines

Add a new Import Definition to the Import Manager that uses the Sales Document table. Ensure that all sales document key fields and required fields are present in the field mappings:

• SD.Sales_Doc_Num
• SD.Sales_Doc_Type
• SD.Customer_Num
• SD.Sales_Doc_ID

And that all sales document line key fields and required fields are present in the field mappings: 

• SDLINE.Item_Number
• SDLINE.Quantity
• SDLINE.Line_Num
• SDLINE.Sales_Doc_Num
• SDLINE.Sales_Doc_Type
• SDLINE.Component_Seq_Num

And add any additional fields as needed for your import document, providing the import document column name in the corresponding Import Field that will be used to map the value to its business object value.

Sales Document Tracking Number

Add a new Import Definition to the Import Manager that uses the Sales Document Tracking Number table. Ensure that all key and required fields are present in the field mappings:

• Tracking_Number
• Sales_Doc_Num
• Sales_Doc_Type

And add any additional fields as needed for your import document, providing the import document column name in the corresponding Import Field that will be used to map the value to its business object value.
CRM Contact

Add a new Import Definition field to the Import Manager that uses the Contact table. Ensure that all key and required fields are present in the field mappings for the Contact object (Contact_ID) and add any additional fields as needed for the import document, providing the import document column name in the Import Field that will be used to map the value to its business object value.

Usage

Examples use cases assume Import Manager Scripts have been customized to customer business logic needs. Please submit a request for services for assistance with Import Manager Scripting.

Import Sales Document Without Sales Lines And With Standard Key Fields Present

Using an import file that holds the mappings configured in the Import Manager, run an import for your Sales Document import definition by clicking the Run Import button at the top of the Import Manager screen and selecting the file to be imported. 

If a blank value is provided for the sales document number, an automatically generated sales document number would be created and used in the blank value’s place via the Pre Import Script and the Pre Load Existing Object Script.

If any duplicate values are detected in the import document, the user will be notified with a pop-up message that will allow the user to view the duplicate values by clicking the View Rows button and decide whether to:

• import the file as is
• continue to import the file after removing the duplicates
• cancel the import altogether

After performing the duplicate value validation, the import file will be read, the values will be populated on the business object, the business object will be saved, and the user should be provided a results screen that shows how many rows were successfully imported, as well as a list of any errors that may have occurred while attempting to import certain objects.  The resulting sales documents should have no sales lines present.

Import Sales Document Tracking Number With Standard Key Fields Present

Using an import file that holds the mappings configured in the Import Manager, run an import for your Sales Document Tracking Number import definition by clicking the Run Import button at the top of the Import Manager and selecting the file to import. 

Duplicate value validation will be performed on the import records, as will validation that no previously existing Sales Document Tracking Numbers on the import file already exist, and if there are no duplicate records, or the user selects an option other than outright cancellation of the import if duplicate records exist, then after importing the new business objects the import manager will display its results screen. The resulting Sales Document Tracking Numbers should be present and visible on the associated sales document(s).