Eliminate time consuming follow up and account servicing efforts.

Use WINSALES ACTION PLANS to systematically deliver the right information to the right people at the right time.

Find more WINSALES support information at www.winsales.com

WINSALES Support Document - sup110

Version: 5.0
Revised: 8/20/04

WinSales Report Database Schema

This document describes the realtionships between tables in the database created by the WinSales Report Database Utility (WSReportDB.EXE) in WINSALES 5.0.

To create the Microsoft Access report database or the SQL Server tables and views run the WSReportDB.EXE. For instructions on running the Report Database Utility see SUP107.

The Report Database Utility takes any WINSALES 5.0 database and creates a data structure that follows the format described below. Your database has customized activities and tables that will not be defined below, but the relationships between the tables will be the same for any activity or table that is in your database.

CONTACT INFORMATION – Contact information is stored in two main tables, then each multiple response field has their own table. WSRPT_CUSTOMER is the top ten fields in WINSALES with the CUSTID that uniquely identifies the contact record. That ID links to a record in WSRPT_CUST where all of the other non-muliple response fields are located.

When linking from contact to a reference table the column in the WSRPT_CUST will be the display name in WINSALES with _ID (for example SalesAgent_ID). The value in the records will be the ID of the record from the refere table (for example Sales Agent Jon Doe would display as value 124789, which is Jon Doe’s ID in the SalesRep table).

For multiple responses WINSALES will create a WSRPT_ table for each multiple response field and the record will be linked by the ID of the Contact with all responses listed as a separate record in the WSRPT_ table. Contact notes are located in WSRPT_NOTES linked by the CUSTID.

WSRPT_CUSTOMER
CustID - Unique ID for this contact
Company - Name of company
FirstName - First name
LastName - Last name
Street1 - First line of street address
Street2 - Second line of street address
City - City
State - State
Zip - Zip code
Country - Country
Phone - Phone number
HierV - Vertical position within OrgChart, or 0
HierH - Horizontal position within OrgChart, or 0
MatchCode - WinSales-computed used for checking for duplicate contacts
LastCampID - Tbl.ID (campaign table) of most recent activity.campaign for this contact
TimeStamp - Date/Time of last change

WSRPT_CUST
ID - Unique ID for this contact
TimeStamp - Date/Time of last change
Other fields you have created in the database on the table will be listed here. Where there is a reference to a table the unique ID for that record will be listed to reference the record in the reference table.

REFERENCE TABLES – Each reference table is created with a WSRPT_ naming convention, like WSRPT_SALESREP. In the table will be a ID column that is the unique identifier for the record that will link to other tables. All non-multiple response fields on the reference table will be listed here following the rules described above, and any multiple response fields will have their own WSPRT_ table created as descrived in MULTIPLE RESPONSES. The note linked to the reference table record is in the WSRPT_NOTES table linked by the ID from the reference table.

WSRPT_REFERNCE-TABLE-NAME
ID - Unique ID for each record
TimeStamp - Date/Time of last change
Other fields you have created in the database on the table will be listed here. Where there is a reference to a table the unique ID for that record will be listed to reference the record in the reference table.

ACTIVITIES – The WSRPT_ACTIVITY table (or view in SQL) has an ACTID as the unique identifier with the top six fields that users see in WS on every activity (Contact, Campaign, SalesRep, Date, Time, Status. The record is linked to the contact through the CustID. Other fields (below the top six fields) on activities are each in their own activity’s WSRPT_ table linked by ActID (for example WSRPT_SALE for the SALE activity fields). The note linked to the activity is in the WSRPT_NOTES table linked by the ActID. There are more then six columns in this table though, because there are values stored in the table that the WS user doesn’t.

WSRPT_ACTIVITY
ActDateTime - Combined ActDate and ActTime values of activity
EndDateTime - Combined EndDate and EndTime values of activity
ActID - Unique ID for each activity record
ClassID - ClassID - Unique ID for the activity type (review WS_CLASS table in WSD file to find ClassID)
CampID - Unique ID for the activity’s campaign (links to WSRPT_CAMPAIGN table)
RepID - Unique ID for the activity’s sales rep (links to WSRPT_SALESREP table)
Status - Status of the activity (Scheduled = 2, Resolved = 1, Cancelled = 0)
ActDate - Date on the WinSales Activity
ActTime - Time on the WinSales Activity
CustID - Unique ID for activity’s contact (links to WSRPT_CUSTOMER table)
PrintState - Status of activity if merged (for fulfillment center)
Priority - Priority of the activity for the calendar
LeadTime - Time before activity due date time to remind user with Alarm program
EndDate - End Date of activiy for the calendar
EndTime - End Time of activiy for the calendar
TimeStamp - Date/Time of last change

MULTIPLE RESPONSE FIELDS – Each multiple response fields on a activity or table has their own WSRPT_ table created using the naming convention of WSRPT_TableFieldIsOn_X_FieldName, for example WSRPT_CUST_X_PHONES for the PHONES multiple response field on the contact window. In the table each record has a CustID and a value.

WSRPT_TABLE-X-FIELDNAME
ID - Unique ID for each record
FieldName - Multiple resonse values
TimeStamp - Date/Time of last change

NOTES (Contact, Activity, and Reference Table) – Every note in WINSALES is in this table linked by the unique ID for the CONTACT (CustID), ACTIVITY (ActID), or REFERENCE TABLE record (ID – each table has a field simply called ID).

WSRPT_NOTES
NoteID - Unique ID for each record (links to Customer/Activity/Reference Table)
NoteText - Text of the note
ClassID - WS_CLASS ID of the note (Contact-22, Activity/Reference Tables other ID’s)
TimeStamp - Date/Time of last change

Contact Silent Partner for more information.