Customer Data

When you right-click in Customer Excel Integration and load the customer data for the shared set of the current domain, you can also modify the loaded data directly on screen before exporting it to Excel. Your modifications are validated when you click Save.

Important: You should not customize the display by hiding columns before export. When you hide a column, the corresponding field is not exported to the spreadsheet. If the field is mandatory, the system attempts to validate it before saving the data to the database, and the absence of the column will generate validation errors.

Note: You do not need to exit the QAD application before working in Excel. For minor maintenance, it generally more convenient to run the applications simultaneously, and to switch back to your QAD application to import the saved data.

• The first row of every spreadsheet contains logical identifiers for the business component fields. You can edit these identifiers for maintenance purposes within Excel. When you import your saved data into the system, your edits are discarded.

• The second row contains technical identifiers for the business component fields. These identifiers correspond directly to the database tables, and must not be edited. Any change you make to a logical identifier generates an error during validation.

• The other rows contain your business component data.

• The spreadsheet contains business component ID columns, which identify the business component instances in the databases. You cannot edit these IDs, and you should leave these columns blank for any new rows you create. Each spreadsheet can contain a number of ID columns. For example, when you export business relation data to a spreadsheet, there are ID columns for business relation, address, default SAF, contact, and tax number.

• All rows are imported into the system, which lets you hide unnecessary rows while working with large spreadsheets. You should avoid hiding columns, however, as hidden columns are not imported. You can create extra columns for maintenance purposes, which are also not imported.

• Avoid using the Sort option in Excel.

Most business components contain sub-level information. For example, the business relation can contain separate address rows for head office, delivery, invoice, reminder and remittance, and can also contain contact details.

When you export to Excel, the main business component data and its sublevel data is grouped together in a hierarchy, with each main business component row followed by sublevel rows. If you sort the data in Excel, the sublevel rows are rearranged throughout the spreadsheet, and the hierarchical relationship is lost. This creates a conflict and prevents you from importing the saved data.

• Ensure that your column widths are set to Autofit before saving. If the column width is too narrow and the data is not readable, the data will not import correctly.

• The spreadsheet you create must have the Shared attribute, which lets other network users modify the data.

• Be aware of mandatory fields while you are modifying data. For example, you must specify a bank number, currency, and banking profile when you create a GL bank account. If you do not enter valid information for these fields, an error occurs after import when you try to save the data.