Overview of Importing Data

aACE allows you to import new data from a FileMaker or Excel spreadsheet into most modules in the system. The import process itself is standard across these modules. Note: The Line Item Codes module includes additional features for updating certain information on existing LIC records. 

Understanding the following aspects of the aACE and FileMaker import process can help you work more effectively.

Data to Import

Data you can import into aACE is divided into two general categories:

Master Data

These are records required to successfully complete a transaction. Examples include offices, team members (employees), GL accounts, line item codes (products / services), companies, contacts, etc. 

You can import this data using the standard process for importing records.

Transaction Data

These are records that usually track specific exchanges between your company and other organizations. Examples includes orders, invoices, purchase orders, purchases, GJ entries, counted items for inventory adjustments, etc.

You can import this data using the similar process for importing transactions.

Cached Data

All of aACE's import processes utilize temporary records in a FileMaker cache table. Using this cache table helps with validating the data prior to the actual import. This helps prevent users from importing incomplete records that might cause problems later. 

This careful approach requires that the Import Action (see below) be set to "Add". This refers to adding records to the cache table. This applies even when you are updating data on existing line item codes (LICs). 

Accordingly, the cache table must be cleared for each import. During the import process, you may see a dialog message about deleting records:

This step only affects the cached files from the previous import. Your actual business records are never deleted during an import.

Removing Data

The import process will not remove all data from a field. If cells in your import spreadsheet are blank, the import process simply ignores them. 

To remove data from a field, you can change numbers to zeroes and change text fields to 'null' or a similar term.

Steps of the Import Process

1. Download an import spreadsheet.
2. Prepare the data to be imported.
3. (For importing transaction data only) Create a header record and click Edit.
4. Import the records.
5. Resolve validation errors (if any).

Best Practices

• You can simplify an import spreadsheet by deleting columns that have no data.
• You add columns on certain import spreadsheets, then import additional data for the records. You must be precise with matching the column title to a field on the relevant module's detail view.
• The FileMaker import process can match spreadsheet column titles to record fields. This means it might be easier to edit the spreadsheet exported from your another system, copying the aACE spreadsheet column titles and pasting them into place above the corresponding data.
• Some spreadsheet editing software will not round by default. If you don't include a rounding instruction in the calculation, your data in aACE may have more decimals than you require. For example, if you need to update all costs by 5%, you can add a new column called "Rate Value Update" and enter a calculation such as: "Round(Rate Value*1.05,2)".