| This step allows you to connect the fields (columns) in an ODBC database to the tables and fields used within Salesmatrix.
To the left of the screen, we have the "existing tables" scroll box listing your current data source database tables, with the corresponding fields. In the middle of the screen, we can see the "Salesmatrix field buttons". To the right of each button is a white input box, which indicates what field is being imported into the Salesmatrix field.
Above the Salesmatrix field buttons and corresponding white input box, there are four tabs relating to four database tables used by Salesmatrix.
Placing the Data
To manually place the data, drill down and highlight the required field from the existing table scroll box, then click on the appropriate Salesmatrix field button. The highlighted field will appear in the input box next to the selected Salesmatrix field button.
For some Salesmatrix fields, the data in the chosen field may need to be in a specific format. Salesmatrix will alert you when an incorrectly formatted field is selected. Each of the required Salesmatrix fields for each of the Salesmatrix database tables need to be filled in before the data can be imported. Toggle between the different Salesmatrix database tables by selecting the corresponding tabs.
Option buttons:
- Select the Use Custom Script checkbox to change the display to a text input box that allows a full SQL script to be written and edited in this area to allow customised views of the imported data. See Customised Scripts for more details.
- Select the Change Data Source button to change the ODBC data source. It is important to remember that only one ODBC source can be used to create a Salesmatrix database.
- Select the Save Script button saves the selected field settings to a file that can be reused in the future. Salesmatrix will generate a text file with an *.SMD extension.
- Select Load Script to load a previously saved *.SMD Salesmatrix settings file. There are several examples included with the standard Salesmatrix System in the scripts folder.
- Select the Import Data button to start the Import procedure. Depending on the size of the original database, this step may take anywhere from a few seconds to several minutes. A progress indicator tracks the import procedure.
When the note "Import complete" is displayed, Salesmatrix will load the default Salesmatrix view.
Salesmatrix individual fields help:
Transactions database table
All fields must be imported from the same database table for the connection on this page to work properly.
The exception is if there is a separate line item table for the transaction lines on an invoice. If this is the case, the top three lines must be from the transaction header table and the lower four must be from the item table. The Salesmatrix link will create a new record from each line item if this is the case.
- Customers Link: This is the field in the transaction record that relates/matches the unique field in the customer table. Can be Alpha or Numeric
- Salespersons Link: This is the field in the transaction record that relates/matches the unique field in the Salesperson table. Can be Alpha or Numeric
- Transaction Date: This is the field in the transaction record that holds the date of the transaction. It must equate to a Date type in the ODBC connection
- Invoice to Line Item Link: If transactions are in a line items table and linked to an Invoice header record, this line defines the link between the two tables. Key the link equation into the adjacent white input box in the following format: invhead.refno=invline.refnowhere "invhead.refno" is the field in the invoice header table and "invline.refno" is the matching field in the line items table.
Salesmatrix expects the following four fields to be sourced from the invoice line item table.
If the transactions are all imported from one table, leave this line blank.
- Product Link: This is the field in the transaction record that relates/matches the unique field in the Salesmatrix Product table. Can be Alpha or Numeric.
- Transaction Value: This is the field in the transaction record that contains the Transaction Value. This field is required. Must be Numeric or Alpha field equal to a numeric value.
- Transaction Cost: This is the field in the transaction record that contains the Transaction Cost. This field is optional. Must be Numeric or Alpha field equal to a numeric value.
- Products Units: This is the field in the transaction record that contains the Transaction Units of Product. This field is optional. Must be Numeric or Alpha field equal to a numeric value.
Products database table:
All fields must come from the same ODBC data source for the connection on this page to work properly. Typically this will come from an inventory or stock system file and contain the details of products sold by the business.
- Products Link: This is the field in the Salesmatrix Products table that relates/matches the Products field in the Salesmatrix transaction table. Can be Alpha or Numeric, but must match the type from the Salesmatrix. Transaction table.
- Products Name: This is the Description or Product name field in the Products record. Should be Alpha.
- Products Value: This is the normal Selling Price of the Product. Must be Numeric or Alpha field equaling to a numeric value.
- Products Cost: This is the normal or average Cost Price of the Product. Must be Numeric or Alpha field evaluating to a numeric value.
- Products "Category 1": This is an optional field relating to the First category type for Products by which you wish to summarise the Salesmatrix data. Can be Alpha or Numeric.
- Products "Category 2, 3, 4, 5": This is an optional field relating to the Second category type for Products by which you wish to summarise the Salesmatrix data. Can be Alpha or Numeric.
Customers database table:
All fields must come from the same ODBC data source for the link on this page to work properly. Typically this will come from your debtors or Accounts Receivable Customer Names and Addresses data table, and contain the details of customers of the business.
- Customers Link: This is the field in the record that relates/matches to the Products field in the transaction table. Can be Alpha or Numeric, but must match the type from the Salesmatrix Transaction table.
- Customers Name: This is the Description or Product name field in the Products record. Should be Alpha.
- Customers "Category 1": This is an optional field for your products relating to the First category type for Customers by which you wish to summarise the Salesmatrix data. Can be Alpha or Numeric.
- Customers "Category 2, 3, 4, 5": These are optional fields relating to the Second and subsequent category types for Customers which the user can summarise the Salesmatrix data. Can be Alpha or Numeric.
- CContact Name of Customer Contact. Should be Alpha.
- CEmail Email of Customer Contact. Should be Alpha.
- CPhone1 Phone number of Customer Contact. Should be Alpha.
- CPhone2 Second Ph number for Customer Contact. Should be Alpha.
Salespersons database table:
All fields must come from the same ODBC data source for the links on this page to work properly. Typically this will come from a staff listing file and contain the details of Sales Staff, distributors or branches selling products for the business.
- Salespersons Link: This is the field in the record that relates/matches to the Products field in the Salesmatrix transaction table. Can be Alpha or Numeric, but must match the type from the Transaction table.
- Salespersons Name: This is the Description or Product name field in the Products record. Should be Alpha.
- Salespersons "Category 1": This is an optional field relating to the First category type for Sales Resources by which you wish to summarise the Salesmatrix data by. Can be Alpha or Numeric.
- Salespersons "Category 2, 3, 4, 5": This is an optional field relating to the Second category type for Sales Resources by which you wish to summarise the Salesmatrix data by. Can be Alpha or Numeric.
|
