ODBC Selection statement formats

Root Folder > Connections

Data Loading Selection Statements:

There are 4 selection statments required for loading data via ODBC to Salesmatrix SWOT.
  1. Customers
  2. Products
  3. Transactions
  4. Contacts
 
These are the structures to use to define the ODBC Selection statements:

1. Customers

Fields

Fieldname

Structure

Able to be NULL?

Comments
  [cid]
[nvarchar](36)
NOT NULL
Unique ID of customer from Accounting system
  [cname]
[nvarchar](60)
NOT NULL
Name of customer. Should also be unique
  [ccat1]
[nvarchar](22)
NULL
Category 1 of customer
  [ccat2]
[nvarchar](22)
NULL
Category 2 of customer
  [ccat3]
[nvarchar](22)
NULL
Category 3 of customer
  [ccat4]
[nvarchar](22)
NULL
Category 4 of customer
  [ccat5]
[nvarchar](22)
NULL
Category 5 of customer
  [nvalue1]
[numeric] (12, 3)
NULL
Free for customer value eg Balance outstanding
  [nvalue2]
[numeric] (12, 3)
NULL
Ditto 2
  [nvalue3]
[numeric] (12, 3)
NULL
Ditto 3
  [nvalue4]
[numeric] (12, 3)
NULL
Ditto 4
  [nvalue5]
[numeric] (12, 3)
NULL
Ditto 5
  [ccontact]
[nvarchar] (100)
NULL
Main FullName of Contact from Customer Record
  [cemail]
[nvarchar] (100)
NULL
Main Email from customer record
  [cphone1]
[nvarchar] (20)
NULL
Phone from Customer Record
  [cphone2]
[nvarchar] (20)
NULL
Phone2 from Customer Record
  [ExternalRef1]
[nvarchar] (36)
NULL
Additional Unique External Reference 1
  [ExternalRef2]
[nvarchar] (36)
NULL
Additional Unique External Reference 2
 
Selection Statement Example:
SELECT DISTINCT CM.Code AS CID, CM.Name AS CNAME, CM.Sort AS CCAT1, CM.Territory AS CCAT4,  CM.Cat as CCAT2, CM.SalesRep as CCAT3, CM.Contact as CContact, CM.Phone as CPhone1 FROM ADMIN.CUSTOMER_MASTER CM

2. Products

Fields

Fieldname

Structure

Able to be NULL?

Comments
  [cid]
[nvarchar](36)
NOT NULL
Unique identifier of the Product
  [cname]
[nvarchar](60)
NOT NULL
(Should be) Unique Name of Product
  [pcat1]
[nvarchar](22)
NULL
Product Category 1
  [pcat2]
[nvarchar](22)
NULL
Product Category 2
  [pcat3]
[nvarchar](22)
NULL
Product Category 3
  [pcat4]
[nvarchar](22)
NULL
Product Category 4
  [pcat5]
[nvarchar](22)
NULL
Product Category 5
  [nvalue1]
[numeric] (12, 3)
NULL
Stock on Hand Quantity    
  [nvalue2]
[numeric] (12, 3)
NULL
 UOM multiplier
  [nvalue3]
[numeric] (12, 3)
NULL
Stock Focus Indicator
0. Normal Stocked Status
1. Expired Stock
2. Expiring this month
3. Expiring in next 3 months
4. Obsolete but Stocked
5. OverStocked
6. NonStocked
 
  [nvalue4]
[numeric] (12, 3)
NULL
Location
  [nvalue5]
[numeric] (12, 3)
NULL
Current or Obsolete Product
  [nunitprice]
[numeric] (12, 3)
NULL
Latest Unitprice for Product
  [nunitcost]
[numeric] (12, 3)
NULL
Latest Unit Cost for Product
  [cmanager]
[nvarchar](22)
NULL
Manager reference
 [ExternalRef1]
[nvarchar] (36)
NULL
Additional Unique External Reference 1
 [ExternalRef2]
[nvarchar] (36)
NULL
Additional Unique External Reference 1

 

SELECT DISTINCT PM.Code AS CID, PM.Description AS CNAME, PM.SalesPrice1 AS NUNITPRICE, PM.UnitCost AS NUNITCOST, PM.ProductGroup AS PCAT1, PM.Supplier1 AS PCAT2 FROM ADMIN.PRODUCT_MASTER PM

3.Transactions

Fieldname

Structure

Able to be NULL?

Comments
  [cprodid]
[nvarchar](36)
NOT NULL
Unique identifier of the Product
  [ccustid]
[nvarchar](36)
NOT NULL
Unique identifier of the Customer
  [cperiodid]
[nvarchar](4)
NULL
Period Identifer if special period Ids are used.
  [csmanid]
[nvarchar](36)
NULL
Unique identifier of the Rep
  [cperiod2id]
[nvarchar](4)
NULL
Period Identifer 2 if special period Ids are used.
  [nunits]
[numeric] (18, 3)
NOT NULL
Units of the transaction
  [nvalue]
[numeric] (18, 3)
NOT NULL
Value of the Transaction
  [ncost]
[numeric] (18, 3)
NULL
Cost of the transaction
  [dtrandate]
[date]
NOT NULL
Date of the transaction
  [dtrantime]
[datetime] 
NULL
Time of Transaction (if available)
  [Ltag]
[int]
NULL
 
  [ExternalRef1]
[nvarchar] (36)
NULL
Additional External Reference 1. Could be used for Invoice Number
  [ExternalRef2]
[nvarchar] (36)
NULL
Additional Unique External Reference

Fo more information go this this link: https://help.salesmatrix.com/kb/a43/parameters-for-loading-data-to-swot-using-the-swotservice.aspx#
Selection Statement Example:
SELECT trade.account2 as CCUSTID, trade.invdate as DTRANDATE, trade.salescode as CSMANID, trade.qtyinv * -1 AS NUNITS, trade.amtext * -1 as NVALUE, (trade.qtyinv * trade.amtucost * -1) as NCOST, trade.accountid as CPRODID FROM trade trade WHERE left(trade.accountid, 1) = 'I' AND(trade.itsaquote = 0) AND(trade.ledgerno <> 0) AND(trade.ledger2 = 3)
Options:
  • DateField - enter in this field the name of the date field as it would appear in the transaction selection statement. This is used to create the date filters used to limit the data uploaded.
  • DateFormat - several options for the date format of the dtrandate field in the transaction selection statement. 
Format name
Format Style
[default]   
Standard MS ODBC Date format {d 'yyyy-MM-dd'} eg {d '2020-01-25'}
DATETIME
 uses SQL Convert(Date, Date.ToString('yyyyMMdd'))
TIMESTAMP
{ts 'yyyy-MM-dd HH:mm:ss'}as text eg  {ts 2020-01-25 18:12:11'}
ACCPAC
YYYYMMDD as a number eg 20200125
SQLDATE
 yyyy-MM-dd as text eg 2020-01-25
SQLTEXTDATE 
"yyyy-MM-dd" as text with single speechmarks eg '2020-01-25'
NODATE
No Date Filter is added to the Transaction Selection Query
Can be useful for the initial set-up phases 

4. Contacts

Fieldname

Structure

Able to be NULL?

Comments
  [contactid]
[nvarchar](36)
NOT NULL
 Unique key for the Contact from the Source data
  [contactGUID]
[nvarchar](36)
NULL
 Unique ContactGUID if available
  [customerid]
[nvarchar](36)
NOT NULL
 The ID of the customer that is connected with this customer
  [contacttype]
[nvarchar](30)
NULL
  
  [FirstName]
[nvarchar](30)
NULL
  
  [MiddleNames]
[nvarchar](30)
NULL
  
  [Surname]
[nvarchar](30)
NULL
  
  [Fullname]
[nvarchar](96)
NOT NULL
 The Fullname is a necessary field for the contact. If there are no FirstName and Surname the logic will try to create these.
  [JobTitle]
[nvarchar](30)
NULL
  
  [Email1]
[nvarchar](64)
NULL
  
  [Email2]
[nvarchar](64)
NULL
  
  [Phone1]
[nvarchar](24)
NULL
  
  [Phone2]
[nvarchar](24)
NULL
  
  [Address1]
[nvarchar] (128)
NULL
  
  [Address2]
[nvarchar] (128)
NULL
  
  [Suburb]
[nvarchar] (64)
NULL
  
  [City]
[nvarchar] (64)
NULL
  
  [PostCode]
[nvarchar] (32)
NULL
  
  [Country]
[nvarchar] (64)
NULL
  
Selection Statement Example:
SELECT C.UniqueID as customerid, CT.OutlookID as Contactid, CT.FullName, CT.BusinessPhone as Phone1, CT.MobilePhone as Phone2, CT.Email as Email1, CT.JobTitle, CT.Email2, CT.FirstName, CT.LastName as Surname,  CT.Street1 as Address1, CT.Street2 as Address2, CT.Suburb, CT.StateCounty as Suburb, CT.Postcode, CT.Country FROM CONTACTS CT, CUSTOMERS C WHERE CT.AccountID = C.UniqueID
Article ID: 21, , Modified: September 4 at 4:13 AM

Add Feedback

Was this article helpful?

Share this article

FacebookGoogle+TwitterOther Social NetworksPrint