IM Multi-Table Imports

 Multi-Table Imports

If you find yourself importing AP, AR, or any other data which has a "Header" table and a detail / line table, the most concise way, involving the fewest steps for the user of the import, is a multi-table import. 

In most cases, "Multi" table means two tables, as many of the standard import routines are hard coded to handle two tables. They also assume that one table is a "Header" table and one is a "Detail" table.

Copying a Standard Import

Imports are complicated. As such, it is recommended that you start with an existing, standard import for the tables which you are importing.

To do this, navigate to the standard import in the IM module -> Programs -> IM Templates and locate one of the likely many standard templates.

Select the desired template, go to the top of the menu  click File -> Copy Template and enter the required fields 

Hit copy, and your new template will be created!

Modifying a Standard Multi-Table Import Template

There are a few important details when dealing with Multi-Table import templates (and sometimes imports in general).

1. If the import is going to a batch table (it creates a record in HQBC on import), you will need to include a month in the import. This can be derived from a date being imported regardless of whether it is formatted as a month, or it can be input by the user on each import.
   With the format displayed in the picture below, you can format any date as the first day of the month (i.e 07/31/2022 -> 07/01/22). Note, when using this method, if you have dates that span multiple months, it will create multiple batches! (cool!)
   
2. In order to relate header lines to their respective detail lines, the Import module cleverly includes a non-existent column in the import detail. It's called RecKey, and it always has the identifier 1000. As long as the import template contains multiple record types, this pseudo-column is included, once for each record type.
   When a header row has a RecKey set to a value, and corresponding detail lines have the same value in their RecKey field, the detail lines are linked to the header line upon import. For instance, an AP Transaction Header with a RecKey that matches seven detail lines, will end up with seven AP Lines underneath the transaction.

Make sure your header table RecordType is alphabetically earlier than the detail Record type. This is a requirement for many of the standard import / upload routines. If this is not adhered to, the detail lines may end up in the header table.