Acumatica Import and Export Scenarios Tips and Tricks
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: