BaccS allows to export any data into Excel format for exchanging information with external systems, transfer data between different databases, or simple editing in Excel environment for importing it back later.
Exporting mechanism is available from any list view. To access exporting function, open any list view (for example, list of customers, contractors, translation projects, jobs, invoices, assignments, etc.), select necessary rows and click Export/Import - Export selected rows button:
If you're using desktop version, a dialog will appear to select file name and path for saving exported file. If you're using web-version, a dialog will appear with a link to a file - click it to download exported data.
After that, you will be able to open exported file in Excel:
This file can be used to edit exported data and importing it back, or as a template to import new data into the system. In later case, delete/replace existing values in cells with new ones.
Working with data in Excel
Workbook contains two tabs: Data (which contains actual exported data) and Info (short information about working with data).
In general, Data worksheet contains a number of sections. First section (first table) contains actual information from rows you've selected before export. After first table, a number of nested tables are listed - it depends on type of object you exported. In the example above, translation job contains the following nested tables: Job tasks, Receivables, Checklist, Workload planner and Messages. Other entity tables will contain different nested tables (some reference data may not contain nested tables at all, for example countries or languages).
Structure of each table corresponds to a structure you see during working with the system. Each cell in Excel file will have the same data type which is used in BaccS: dates, numbers, booleans and string. While editing data, please remember about that and don't change cell types in Excel. If some field has reference data type (for example, Customer in translation job is reference data type, because you select customers via drop-down list), then you're allowed to enter only those values in such fields, which exist in BaccS database. For example, if customer name is Customer1, and you will change it to non-existing Customer12, then the system will ignore this change during import, which will lead to the following results:
- if you're importing existing record, customer won't be changed, and old value will be persisted;
- if you're importing new record, this record will fail and the whole import operation will be aborted.
Please remember about way of using reference fields while working with data in Excel - use only those values which actually exist in BaccS database.
Id column is also special column. When you export existing data, this column will always contain some GUID value. If you're filling a table with new data, you should always leave this column empty. During following import, if this column contains any value, the system will find existing record and wrote data from a row into that record. However, if Id value is empty, the system will create a new record.
Nested tables always contain a column which points out to master object. In the example above, you can see that each nested table has Job column. And value in this column has the following notation: DocumentNumber/DocumentDate. By this combination of document number and date the system will be searching for a master record of each child record. So, if you are creating a new job in Excel, then you have to define its number and date in the first table, and then use this combination of number and date in each nested table, to correctly link them (for example, to correctly link job task with master job).
Importing data from Excel
When you made necessary changes in Excel file, you can import it back to BaccS. To do this, open corresponding list view and click Export/Import - Import data. Select a file (in desktop version) or upload it (in web version), and click OK. The system will start importing selected file and then will display resulting message. Here are few points which you have to consider during importing:
- If imported file contains multiple entries and at least one entry was processed with mistake, the whole operation will be aborted and no changes will be made in database.
- If operation was successful, it is recommended to click Refresh (F5) after importing.
- If operation was successful, and if Excel file contained new data, don't try to import the same file again. The reason here is that new Id column for new records is empty in Excel file, and BaccS created new entries in database for such records. If you will try to import same file again, BaccS will again create entries for records with empty Ids. In such case, to repeat import, please export data to Excel again so Id columns will be filled with actual values.
- Don't create empty rows between sections of Excel file. Empty row will be perceived as end of file.
- Always keep the same data type in cells as in exported file. In other words, don't try to type characters in a cell which should contain number. Otherwise, import will fail.
- Carefully handle cells pointing on reference data. For example, value in Customer column should contain full name of customer which exists in BaccS database. If the system won't be able to determine customer during import, most likely import operation will fail, because Customer is a required field in most of documents.
Possible plans for future:
- Formulas where possible (like, Amount = Price x Quantity);
- Better order of fields (approximately fields should be listed in priority order, so less important ones will be in the end of a table in exported file)
- Additional worksheets with reference data (for example, Units, Work types, Customers, Customer contacts, etc).