Export Data Designer
Summary
The Export Data Designer (EDD) has three main functions. These are:
Producing a set of records from AdminBase according to criteria that you set.
Exporting the resultant data with appropriate formatting.
Modifying records in the relevant tables after the data export.
The user can perform the task manually, or the process can be partially or fully automated. (See ‘Automating the Process’ below).
You can access the EDD through Tools → Query Export
Creating a Query
There are 3 ways to use the EDD
1.ICreate a query from scratch (Using the text editor or query builder)
2. Select and run a saved query
3. Open or Run a Pre-define query
NOTE: The Export Data Designer uses the same query builder as the AdminBase report designer. If you have report writing as part of your support contract we can also create export queries for you.
Exporting Data
Once a ‘query’ has been defined, the resulting set of records will be displayed in the “Export Data options” page of the Export Data Designer. Here you have the facility to format this data according to whatever format is required by the program that is to receive this exported file.
The following formatting options are available:
Reordering the columns (drag & drop)
Inserting a new column (right click)
Deleting a column (right click)
Filling a column with a new value (right click)
Changing the Date/Currency format of a column (right click)
Selecting a separator value for the export file
Choosing whether the row headers should be included in the export
Choosing whether each value should be enclosed with quotation marks
Once you have selected the appropriate formatting options, the actual export is performed by clicking the ‘Export Data’ button, and choosing a location and filename for this export file.
A copy of the data can also be printed out from the “Export Data Options”
Modifying Database Records
After exporting some data, the user may wish to modify some of the values of the records that have been exported.
For example, if the exported data are accounts related records to be imported later by an accounting package, then the user may wish to change the ‘Exported’ value of all these records to ‘True’ now that the export has been done. The “Modify Records Options” page gives this facility.
The selected records are shown in the top half of the screen, and the options for modifying records in the bottom half of the screen.
To modify records the user takes the following steps:
Select the table to be modified from the drop down list
Select the field in this table to be modified from the drop down list
Enter a new value.
This value will replace the existing value for this field, but only for those records in this table that have been selected. If the user wished to modify more than one field/table, simply use the keyboard ‘down arrow’ key to create a new line and repeat the process.
Changes to AdminBase will only take place when the user clicks on the ‘Modify Data’ button.
After data modifications have been made, the user has the option to view a log of data changes, by clicking on the ‘View Log’ button.
On closing the program, the user will be asked if they wish to save the steps they have taken. This is to allow automation of the process next time (see below).
Automating the process
The system has been designed to provide degrees of automation to the processes described above. The following options are available on start-up:
Create a query from scratch – Perform all the above tasks manually.
Run an existing query – Open a previously saved query and continue manually with the Data Export and Data Changes.
Open an existing script file - Select a script file which contains all the definitions for the query, data export and data changes. EDD does not run the file but opens with all these settings displayed.
Run an existing script file – Select a script file which contains all the definitions for the query, data export and data changes. This file will automatically perform all the operations described in 1, 2 & 3 above on the current data. As the script executes, the user will be asked:
For the location and filename for the data export file.
To confirm whether data changes should be made.