Importing Records

aACE allows you to import data from a FileMaker or Excel spreadsheet into most modules in the system. Before you start the import process, you should:

  • Prepare and clean the data you will be working with.
  • Prepare and populate an import mapping template that organizes your data in an Excel or FileMaker spreadsheet. (Note: XML-structured files should also be converted to a spreadsheet format.)
    Note: It is possible to use tab-delimited file formats, but this requires you to map the data to the import fields by hand.
  • Organize your schedule so you have enough time to complete the import. This is especially important if you have a large import spreadsheet.

Be aware of the following details of the import process:

  • Removing Data — The process will not remove all data from a field. If cells in your import spreadsheet are left blank, the import process simply ignores them. Instead you can change numbers to zeroes and change text fields to 'null' or other terms.
  • Cached Data — All of aACE's import processes utilize temporary records in a FileMaker cache table. This helps with validating the data prior to its being imported. This approach requires that the Import Action (see below) be set to "add" records to the cache table, even if you are actually updating existing records. Accordingly, the cache table must be cleared for each import. During the import process, you may notice a dialog message about deleting records:
    This step only affects the cached files from the previous import — actual business records are never deleted during an import.

Import Records

The import process is the same across all modules. This guide demonstrates the process using the Companies module. (Note: Importing line item code data includes additional details.)

  1. From the module's list view, click Actions > Import [records].
  2. At the confirmation dialog, click the appropriate option:
    • Step 1 — If you do not already have a prepared spreadsheet. This will generate a spreadsheet that includes the currently displayed records with the relevant column headings. Save this export in Excel or FileMaker format. Edit the spreadsheet with the needed updates. Then begin the import process again, but this time click Step 3.
    • Step 3 — If you already have an Excel or FileMaker spreadsheet with the needed data.
  3. At the Open File dialog, select the spreadsheet source file you wish to use and click Open.
  4. From the Import Field Mapping dialog, verify the following settings:
    1. At the row counter, move to the first row and specify Use as Field Names.
      This sets the spreadsheet header row as the source fields, making it easy to match the names of the target fields.
    2. At the Source—Target header, specify Add.
      Warning: "Adding" new records is required. The incoming data is brought in as new records to a cache table. After that stage is complete, the data is transferred to your existing business records.
    3. At the Target Fields column heading, specify Matching Names.
      This automatically matches the source field names to the database target field names.
      Note: If you are manually mapping the import fields, set these options, then continue with the process in the guide for Mapping Import Fields By Hand.
  5. Scan the list of fields to verify that Source Fields are matched correctly to Target Fields, then click Import.
  6. Use the Import Summary dialog to review the number of records updated to the cache, then click OK.
    aACE completes a validation check on the data:
    • If no violations are found, aACE imports the records. 
    • If violations are found, aACE displays a separate message about import validation errors.
  7. At the confirmation message, review the number of imported records and click OK.
  8. At the activation message, click Activate.
    Note: You can also leave imported records in Pending status by clicking Cancel. For any records that cannot be activated automatically, you can use the Actions () menu to activate them.

Best Practices for Importing Data 

  • If you have a spreadsheet exported from another system, you can transfer the column headings from the aACE template into that file.
  • 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)".