Over the years, we have written many different Import and Export Scenarios for Acumatica. We have learned many different things regarding Import and Export Scenarios, and so we wanted to share our knowledge with you. We hope you find this information helpful.
Tips in this Blog Post
Tip #1: Always Start with the Key Field(s) on Imports
When building an Import Scenario, you want to make sure that you always map the key fields first. For entity records such as Customers, Vendors, Employees, and Items, the key is usually the record's ID (e.g. Customer ID, Vendor ID, Employee ID, Item ID, etc.). For transaction records, the key usually consists of two fields. For example, the key of a Invoice is the Document Type and Reference Number. Most people forget about the Document Type field as a key because even though this is the very first field on the Acumatica form, its automatically selected and defaulted for you. It is extremely important that the key fields are the first fields selected and mapped in your Import Scenario, and they mapped in the order they appear on the Acumatica form for the Import Scenario to work properly.
Let's take a look at this in the context of an example. Below is a screenshot of the Invoices and Memos form from the Receivables workspace in Acumatica.
Notice how the very first two fields are "Type" and "Reference Nbr.". These are actually the key fields for the transaction. Now let's take a look at creating a new Import Scenario for an Invoice:
We selected the "Invoices and Memos" screen (i.e., the entry screen AR.30.10.00). Note that this is different than the screen AR.30.10.PL that has the same name. The PL screen is the list of invoices, but the AR.30.10.00 is the data entry screen. Make sure you always choose the data entry screen for Import Scenarios.
Watch what happens when we select the "Invoice Summary" Target Object and the "Type" field in the Field/Action name:
As soon as we tab out or click away after selecting the "Type" field, the system automatically adds the Key fields (in the order they should be mapped, starting with "<Key:") followed by an "<Action: Cancel>". This appears ABOVE the "Type" field added. Notice how <Key: DocType> (i.e. the actual name of the Type field in the database) appears before <Key: RefNbr> (i.e. the Reference Nbr. field from the Invoice).
If you selected the "Reference Nbr." first, the keys and action lines will still automatically appear. But unless you map the keys in the correct order, your Import Scenario may not work properly. (If you ever tried to start entering an invoice and then you changed the Type field in the UI, you are forced to start over.) If you add a key field and you notice that the <Key:> fields appear in a different order than the order you are adding the fields to the mapping, then change the fields so that the first <Key:> field is mapped first, the second <Key:> field is mapped second, and so on.
Since the Invoices and Memos screen has two keys, we also need to map the Reference Nbr field. When added to the mapping, you'll see the following:
Notice how the system inserts an additional "<Action: Cancel>" line above the Reference Nbr field (i.e., second key field mapped). While the automatically added lines may seem confusing, they are extremely important for the Import Scenario to work correctly.
What are the key and action lines doing? Remember that an Import Scenario is simulating user data entry. If you go to any Acumatica form, you must always select values for the key fields before you can do any data entry. This is even true with new records, which the system may places "<NEW>" as a value placeholder for autogenerated numbers. If you do not select values for these key fields and then try to enter data in Acumatica, you won't be able to save anything and you will lose the data you already entered. So the key fields are essential for setting up the form for data entry. Note that the <Action: Cancel> line is simulating the user clicking the "Cancel" button, which resets the form with the new key values specified. Both are required for the Import Scenario to work properly.
Once these key fields are mapped (and the key/action lines are automatically added), you can proceed with mapping any other desired fields in the Import Scenario.
Tip #2: Specifying Key Values for Records on Import
When importing new records, the key values you specify will determine Acumatica's behavior depending if the record you are mapping is using autonumbering or not.
When autonumbering is turned off, you must specify a value for the key. For example, if importing Invoices, you must specify the Reference Number to use. This will become the Reference Number of the transaction in Acumatica. If you are importing multiple lines for an Invoice, each line should have the same Reference Number so Acumatica knows to group those lines together on the same Invoice.
When autonumbering is turned on, you must also specify a value for the key. The difference is that the mapped value is used as a temporary placeholder to identify the record before the automatic number is assigned when the record is saved. Again, each line in your file that belongs to the same Invoice should have the same Reference Number so Acumatica knows which lines belong together on the same Invoice. Once the record is saved, the temporary placeholder is replaced with an auto-generated number.
Regardless if auto-numbering is turned on or off, if the key value specified matches an existing record in Acumatica, that record will be updated instead of creating a new one. Think of this as manually entering the Reference Number in the UI on the Invoices and Memos form - if you do this, the system will load that Invoice's data.
If you are updating Invoices that were auto-numbered and you do not have the Acumatica auto-assigned number in your source file, you may be able to use Acumatica lookup fields ("->") to find and update the existing record. For example, if you imported auto-numbered Invoices and then wanted to update them, and you mapped the "Customer Order" field on the Invoice, you can then use that field to find the Acumatica Reference number to update the Invoice, like so:
Notice in the mapping that in the Field/Action name column, we selected the "Reference Nbr. -> Customer Order". This instructs Acumatica to search for an existing Invoice Reference Nbr having the "Customer Order" field equal to the source "Invoice Nbr" field. If a matching record is found, then Acumatica will set the Reference Nbr field to the one that matches the "Customer Order" field. If a matching record is not found, then Acumatica will use the Invoice Nbr as the Reference Nbr, and then will either assign that number as the Reference Number (if autonumbering is turned off) or will use it as a placeholder for a new number (if autonumbering is turned on). Notice that on the very next line, the "Customer Order" field is also mapped, since this mapping is necessary to set the value of the Customer Order field (the mapping of the field "Reference Nbr -> Customer Order" sets the value of the Reference Number field based upon a lookup to the Customer Order field). You must map the "Customer Order" field separately in addition to doing the lookup so the value of the Customer Order field is actually set in Acumatica.
Unfortunately, not every record has a lookup field that can be used for matching on external reference numbers. Or you may be using the lookup field for a different purpose (e.g. Customer Order on an Invoice may not be your invoice number from another system but may be the Customer's purchase order number, which could be duplicated across Customers and therefore not unique). AcuSync translations provide more advanced features for searching and matching on existing records, including the ability to match using ANY field in the Acumatica database (not just lookup "->" fields), as well as matching records using a combination of fields. You can even do conditional matching (i.e. match on this, then if not found match on that, etc.) when you combine AcuSync translations with Acumatica formulas in your Import Scenarios.
Tip #3: Always End with the Save Action on Imports
Whenever you import a record, the very last line of your Import Scenario should be an "<Action: Save>", which can be found in the Summary Target Object. Just like the key and action lines at the start of an Import Scenario prepare the form for data entry, the save action instructs Acumatica to save the record upon completion of data entry. Remember that Import Scenarios simulate user entry, and the last thing you do after making changes to a record in Acumatica is click the Save button - Import Scenarios are no different.
Here is an example of an Import Scenario that simply set the Document Description of an Invoice:
Notice the very last line is an "<Action: Save>" event. This is extremely important to tell Acumatica to save the changes to the record you are adding (or updating). If you do not tell the Import Scenario to save the record, your Import Scenario will run but it will not create or update any records.
Tip #4: Specifying Answers to Popup Dialogs
If you've ever tried to update a record in Acumatica, you may have occasionally noticed that a dialog box appears asking you to confirm your selection. This may happen, for example, if you attempt to change a Item's class:
If you map the Item Class field using an Import Scenario, this dialog will be triggered when updating a record. If you do not instruct the Import Scenario to respond to this dialog, then the record will fail with the dialog question reported as an error.
Fortunately, there is a way to instruct the Import Scenario to provide an answer to this dialog, like so:
Notice just before the "Item Class" field is set, a line was inserted from the Summary section with a special field name called "<Dialog Answer>", and it appears BEFORE the line containing the field mapping that triggers the dialog to appear. You'll find this special command in the field dropdown. Simply select it, and then use a formula to specify the answer to the dialog question. In this case, we are using the formula ='Yes' to have the Import Scenario click the Yes button. We could also enter ='No' to have the Import Scenario click the No button, depending on the desired behavior. In either case, using "<Dialog Answer>" allows you to successfully run the Import Scenario and instruct Acumatica how to behave when a dialog box is triggered. Notice how the "<Dialog Answer>" is selected on the Summary Target object (i.e., Non-Stock Item Summary). This option may also appear when you select the "General Settings -> Item Defaults" Target Object, but it may not work. We recommend selecting the "<Dialog Answer>" on the Summary object first, as this is the one that typically works on most Acumatica screens.
Tip #5: Line Numbers for Document Lines
Whenever you map a field at the line level of a document (e.g. Invoice Lines), Acumatica automatically adds a special line above the first line level field with a command "<Line Number>" and a value of -1, like so:
The <LineNumber> command instructs Acumatica which line to select. A value of "-1" (the default) instructs Acumatica to add a new line. If you've ever tried to update an existing invoice using an Import Scenario using this option, you may have noticed that your lines are duplicated - this is because "-1" tells Acumatica to add a new line EVERY time you run the Import Scenario.
A value of "-2" instructs Acumatica to delete a line. We'll discuss this in more detail in another tip, since it is more involved to tell Acumatica which line to delete.
A value of 0 or higher tells Acumatica which line to add or update. So if in your import file, you have each of your invoice lines numbered starting at 1 (i.e. all first invoice lines are numbered 1, all second invoice lines are numbered 2, all third invoice lines are numbered 3), you can map this field to the <Line Number> field instead of using "-1", and Acumatica will either add a new line (if it doesn't already exist) or it will update the same exact line every time (preventing duplicates on updates). It is important that the line numbers in your file are sequential (do not skip numbers, and restart the numbering for each invoice). Keep in mind that Acumatica starts at 0 for the first line, not 1, so you'll have to do a little formula math to make this work, like so:
Notice how we are using a formula =CInt([LineNumber])-1. This may seem a bit complicated, so let's break it down. [LineNumber] is the name of the field in the file containing the line number on the invoice (i.e. 1, 2, 3, etc.). Field names in formulas should be surrounded by square brackets ([ ]) to specify that its a field. The CInt([LineNumber]) function around the line number field tells Acumatica to cast the value as an integer, so that we can perform the last function which subtracts 1 from the value. This makes line 1 = 0, line 2 = 1, line 3 = 2, etc., essentially converting the number from starting at a value of 1 to a value of 0. Of course, you could always manipulate the line number in your import file and renumber them to start at zero, but the formula allows you to use the file AS IS and manipulate it properly in the Import Scenario which is preferred.
Tip #6: Matching Records at the Line Level
Suppose that you want to update invoice lines (assuming the invoice is still on-hold), but you do not have a line number in your file. Instead, you know that the same item is never repeated on an invoice - in that case, the Inventory ID could be used to find the matching line to update. But how do you tell Acumatica to do that?
Now this is tricky, but we will explain this to you step-by-step. Take a look at this Import Scenario:
Notice the following:
We added the Inventory ID field mapping for the "Document Details" Target Object (i.e. the last line shown above in the Import Scenario). This automatically added the line above with the <Line Number> mapping.
We disabled the <Line Number> mapping by unchecking the Active box. The reason why we did this is because we don't want Acumatica to select the line based upon the line number (or use -1 to add a new line). Since the <LineNumber> line is automatically added and cannot be removed, so we make the line inactive instead.
We added a line above the <Line Number> field to instruct Acumatica how to find the matching line. We typed in a special Field/Action Name "@@InventoryID". The "@@" instructs Acumatica to perform a lookup, and the field name after it specifies to use the "InventoryID" field. Notice how this differs from the user-friendly name which has a space between the words "Inventory" and "ID". This is a very subtle difference but very important, as you must specify the exact database field name. Notice how we added the hidden column "Native Field/Action Name" so you can see the actual name of the field on the last line (also highlighted). Sometimes the field names are similar and are just missing spaces - other times they are completely different, so be sure to check the Native Field/Action Name column for the true field name.
The @@ line instructs Acumatica to look for a line having a matching InventoryID. If found, that line will be selected and the mappings that follow will apply to that line. If not found, a new line will be added.
This concept works for any line you wish to update on any record, as long a you can perform a lookup using a unique field value that identifies the line you wish to update.
Here is another example matching on a Discount Code on a Sales Order. Notice how the field name is DiscountID, but the field name in the UI is "Discount Code", and the source field name in the import file is called "OrderDiscount". The same principal applies, creating the @@ field and disabling the <LineNumber> field, like so:
Again, the most important part is that the field name specified after the @@ matches the Native Field/Action Name on the line where the field is actually mapped (i.e. the third highlighted line above), and the <LineNumber> field is deactivated.
Tip #7: Removing Lines from a Transaction
Once you understand how to match records at the line level (see previous tip), then you can use that knowledge to find and delete a line.
If you want to delete a line, you first have to instruct Acumatica to find the line, and then you can specify the command to delete it. Here's an example:
Let's take a look at what is going on here:
The @@InventoryID line, deactivated <LineNumber> (=-1), and the mapping of the InventoryID field is identical in setup to the previous tip (i.e. Matching Records at the Line Level). These three changes instruct Acumatica to find a specific line with an InventoryID equal to ACON (notice we are using a formula to specify the value we want to find instead of using a field - you could use a field from the source if desired).
Another <LineNumber> field is added at the end of the "Document Details" section, this time specifying a value of =-2. This instructs Acumatica to delete the line.
So to delete lines from a transaction, you first follow the tip to setup the Import Scenario to find the matching line, and then you add a <LineNumber> =-2 at the end to instruct Acumatica to delete the line it found. This Import Scenario will find an Invoice using the "Customer Order Nbr" (notice the lookup "->" on the Reference Nbr. field), and then it will delete the line containing the "ACON" Inventory ID. If you wanted to, you could skip the lookup using the @@ field and just set the <Line Number> to the number of the line you wish to delete. Then add the additional <Line Number> mapping with a value of -2 to delete the selected line.
The same exact concept applies if you want to delete lines from other tabs on records. For example, if you wanted to delete Cross Reference Bar Codes from Stock Items, you would first export a list of all items and barcodes to generate a file containing Bar Codes you want to delete, and then you would setup an Import Scenario like this:
Let's examine what is going on here:
We are looking for a Stock Item with a specific Inventory ID (as specified in the file).
We are then looking for a Cross-Reference having an Alternate Type = 'BarCode' and the Alternate ID from our file of bar codes we wish to delete.
At the end of the Cross Reference section, we add a <LineNumber> =-2 to instruct Acumatica to delete the line.
Notice in this import Scenario there is no need to disable the <LineNumber> =-1 line. This is because Acumatica doesn't have Line Numbers for this type of line data, but instead has key fields. So as soon as we add the "Alternate Type" and "Alternate ID" fields, the <Key:> fields show up automatically. Take a close look at the Native Field/Action name of a key field. Notice how it starts with @@ followed by the key field database name? This is identical to us manually adding a line and typing in @@AlternateType and @@AlternateID to find the bar code we wish to delete. In this case, Acumatica automatically adds these fields for us, so no need to manually add them ourselves like we did earlier with the InventoryID. It is not necessary for us to set values for @@BAccountID or @@SubItemID because in our use case, these fields are not required for us to identify the line we wish to delete (in other use cases values may be required for these fields).
Tip #8: Using Lookup Fields (i.e. fields with "->")
The use of lookup fields in Acumatica is not well documented. Anytime you see a Target Field containing an arrow "->", it instructs Acumatica to perform a lookup. Lets take a deeper dive into how this works.
If you were to go the Invoice and Memos form and click the Magnifying glass in the Reference Nbr field, a dialog would pop up showing a list of all the Invoices/Memos in the system so you can search and select. When you specify a field with an "->", you are instructing Acumatica to search for a matching record with a value in the field specified AFTER the "->", which retrieves the matching value of the field BEFORE the arrow. For example, if on an Invoice Import Scenario, you select "Reference Nbr. -> Customer Order", and then you map that to a field, you are instructing Acumatica to look for an Invoice having a specific Customer Order, and once found, find the corresponding Reference Nbr and select that invoice. If not found, then the Import Scenario assumes the record does not exist and instructs Acumatica to create a new record.
Many people make the mistake of mapping a field to a lookup instruction, thinking that the Import Scenario is setting the value of that field, instead of using that field to lookup the value of the other field. If you are mapping field values, you should NEVER map to fields containing "->", but instead map to the field directly. So if you want to map the "Customer Order" on the Invoice, you need to map the "Customer Order" field and not the "Reference Nbr. -> Customer Order" field. This is also why when using a lookup, you must also map the field (as seen in the examples above) so that you not only use the Customer Order to lookup the Reference Nbr, but then you actually set the value of the Customer Order field.
AcuSync has advanced translations that are superior to Acumatica's native lookup capabilities. AcuSync allows you to search and find data from multiple records, including matching on ANY field you wish (not just the ones available for lookup, i.e. with the "->"), pulling data from different records, and translating values. You can even do searches on Metadata, which allows you to search for records based upon the external IDs from another system without having to add custom field to store all of those IDs inside Acumatica.
Lastly, when using lookup fields with Export Scenarios, the value of the lookup field AFTER the arrow is retrieved instead of the field BEFORE the arrow. For example, if you select "Item Class", then the Item Class ID will be exported. But if you select "Item Class -> Class Description", then the description of the item class will be exported instead of the ID.
Tip #9: The Commit Checkbox
The purpose of this box may be confusing unless you are an Acumatica developer. (For those Acumatica Developers out there, the Commit box is automatically checked when the Commit Changes field is set to true on an Acumatica form).
For those of you who are not Acumatica developers, the Commit checkbox is automatically checked by the system depending on the Target Field selected. In most cases, you do not have to worry about setting the value of this field - just let Acumatica natively check or uncheck this box for you based upon the field selected.
The only reason why you would want to change the default value of this box is to alter the way Acumatica works when Importing Data. The easiest way to explain the Commit checkbox is to think about what happens when you enter data in Acumatica. If you've ever selected a value from a dropdown, or entered a value in a field and tabbed out, and then something happened, that is an Acumatica event firing behind the scenes. Whenever a field has an event tied to it, the system automatically checks the Commit box. The only reason why you would want to change the value of this box is if you want to prevent Acumatica from firing an event.
If you find that events are firing during your import that is causing the data to import incorrectly, first try to re-order the fields so that the data is being entered in the order in which the data would be entered correctly if entered by hand (i.e. manually by a user using the Acumatica form). For example, if you try to set the Account on a Invoice Line before setting the Inventory ID, the Account will be overwritten by the Sales Account on the Item record in Acumatica. To correct, map the Inventory ID field first, then map the Account to override (just change the order in which the fields are mapped in the Import Scenario). This usually corrects the problem.
if all else fails, you can always disable or enable a Commit for each field. This will prevent the events from firing. Just note that if you do this, you are overriding the native or customized behavior. Whenever possible, re-order the fields so they are entered in the correct order to achieve the desired result rather than checking/unchecking the Commit checkbox.
Tip #10: Using Formulas
Formulas allow you to manipulate or set the values of data fields when running Import or Export Scenarios. Since there are MANY different things that you can do with formulas, we are going to focus on common usages of formulas below. Formulas are extremely similar to using formulas in Excel.
Start with equals: All formulas start with the equal = sign. Just like Excel.
Setting constant values: If you need to set a value of a field for every record being imported, but the field is not located in your file, use a formula to set that field's value. If the field you are mapping to contains text, make sure you enclose the text in single quotes.
Setting conditional values: If the value of a field should be set conditionally based upon the value of another field, then use a formula like IIF, ISNULL, or SWITCH. This will allow you to control how values of fields are set without having to manipulate your raw data file.
Converting data types: If the value of the source field is different than the target field, you may have to convert the data type. There are many Acumatica functions to do this, such as CINT, CSTR, and CDBL.
Finding and updating records: When used in conjunction with AcuSync translations, you can use formulas to prioritize the results of finding and updating existing records. For example, you could create AcuSync Translations to lookup a customer by ID, email address, or alternate reference number, and then use a formula (similar to the way you set conditional values) to instruct the Import Scenario to either create a new customer record or update an existing customer record. For example, you may want to search by Acumatica ID first, then alternate reference number second, then email address third.
Using source fields: Whenever you use source fields in formulas, make sure you use the field name surrounded by square brackets ([ ]). This signifies that the text entered is a field. Although its possible to create formulas using fields without the square brackets, its best practice to surround field names with square brackets to signify that its a field.
Using target fields: You may wish to specify the value of the target field in a formula, especially when updating records. For example, say you are mapping a field from a file, but if the value in the file is blank, you want don't want to override the value in Acumatica. You can achieve this using the ISNULL formula, and then specify the target field as the value to use if the source field is blank. Note that target fields typically following the naming convention [TargetObject.TargetField], where TargetObject is the name of the object in Acumatica, and TargetField is the name of the field. Using the formula editor is extremely useful in finding the correct object/field name.
Use the formula editor: When importing records, formulas are typically used in the "Source Field/Value" field. When exporting records, formulas are typically used in the "Field/Action Name" field. If you click the pencil button next to these fields, the formula editor will open up. This will also you to navigate through the list of functions and fields and even insert them into your formula by double-clicking the function or field name. There is even a "Validate" button that helps you validate your formula syntax to make sure its correct and you are not missing any function parameters or parentheses.
Tip #11: Always Start with =Every in key fields on Exports
When creating export scenarios, you must start the Export Scenario off with the key field (similar to the export) with a special formula, like so:
In the example above, the Export Scenario is exporting Stock Items. Notice how the key field "Inventory ID" was selected (this automatically added the <Key:> and <Action: Cancel> lines similar to how it works with an Import Scenario). Also notice that the Target Field / Value has a formula in it, =Every. This instructs Acumatica to export every record that meets the criteria identified on the Source Restrictions tab. If no Source Restrictions are specified, then Acumatica will export every record. Every Export Scenario should start this way (i.e., key field mapped, with formula =Every).
Note in the Export Scenario above, the Inventory ID is also exported into the "sku" field. While its probably a good idea to include your key fields in exports, this Export Scenario mapping is purely optional.
Tip #12: Using an AcuSync Translation Table to Convert Data
Using native Acumatica Import/Export Scenarios, you can use formulas to convert data from one value to another. The only problem with this approach is that formulas can get very hard to read very quickly.
AcuSync offers the ability to create Translation Tables. In a Translation Table, you can create a table of rows and columns, and then specify a Translation on the Import or Export Scenario that looks up the value in one column and returns the value from another column. You can think of this as a VLOOKUP in Excel.
For example, say you were integrating Sales Orders from Magento to Acumatica. In Magento, you have several different shipment methods, but they are coded differently than how shipment methods are coded in Acumatica. You could create a Translation Table like so:
And then setup a Translation on your Import Scenario, like so:
In the highlighted row, you'll see a Translation Type of Table, which is using the SHIPPING translation table created above to convert the shipping method from Magento to Acumatica.
Using an AcuSync Translation Table greatly simplifies the process of translating data from one system to another. The advantage of AcuSync Translation Tables over Acumatica Substitutions is that you can translate the data in many different ways by adding different columns to the same Translation Table for integrating different systems or for different purposes. You can also create many different types of Translations for different purposes instead of being restricted to a Substitution that only works a certain way on a particular field in Acumatica. AcuSync also provides means to handle errors in Translations just in case the value you are attempting to translate does not appear in your Translation Table.
Tip #13: Using AcuSync Set Mode to Control When Fields are Set
Using native Acumatica Import Scenarios, when you map a field, the field value is always set. While it is true that you can use formulas to prevent a field from being updated, AcuSync offers an easy way for you to control when a field is set when importing data into Acumatica. These appear on the Mapping tab of the Import Scenario. The available Set Modes are as follows:
On Insert: The field is only set when inserting (or adding) a new record, but is left as-is when updating an existing record.
On Update: The field is only set when updating an existing record, but is left as-is when adding a new record.
On Change: The field is only set when AcuSync detects that the source field value is different than the target field.
If Empty: The field is only set when the target field is empty (or blank/null).
Always: The field is always set (the default Acumatica behavior)
By using AcuSync Set Modes, you can easily control when a field value is set. They are very useful when setting up integrations, especially bi-directional integrations with other systems when you many not want fields set depending on the field's state.