Whenever you are Importing or Updating Records, it is highly recommended to use the Format Table button on the ribbon prior to either of those Actions.
The Format Table button has the following features which are individually controlled by separate Options.
| Option | Description |
|---|---|
| Apply Conditional Formats | If you want conditional formats applied to the sheet leave this checked. |
| Add Default Values | Field Default values will be applied to empty cells only. Y/N will default for check boxes and 0.00 will default for most numeric fields. |
| Add Missing Co/Groups | Company/Group values will be applied to empty cells only. These values are controlled by the Destination Company. |
| Change Data Format | This converts certain fields to uppercase, changes Month fields to be Day 1 and applies the appropriate formats to Job, Phase, GL Account, etc. fields. |
The Conditional Format option are useful for identifying data errors prior to importing. The available Conditional Formats are comprised of the following.
| Format | Description |
|---|---|
| Required Cells | If a required cell is missing a value it will be red. |
| Max Length Exceeded | If a cell’s field length is exceeded it will be green. |
| Invalid Number or Date | If a cell has an invalid number or date it will be orange. |
| Number Outside Allowable Range | If a cell’s value is outside the allowable range it will be purple. |
| Duplicate Records | If duplicate records exist in the table they will be highlighted gray. |
The Add Default Values and Add Missing Co/Groups options are useful when importing new records as they fill in most of the required column defaults saving manual entry.
The Change Data Format option is especially important when dealing with fields such as GL Accounts, Jobs, Contracts, Phases, Equipment and Materials. Those fields have very specific formats that the data needs to be in otherwise you will run into batch errors or errors inserting the records.
Post your comment on this topic.