Files and Tables

Description

This document provides a list of the files included on the installation CD and a list of the SwiftPOS Database tables.

SwiftPOS Files

Installation Files included on the CD are as follows;

Application Name	Description	Installed Location
SQL Server 2008/2014 Express Edition	SQL Server Installation.	SQL Server
SwiftPOS Installation.msi	SwiftPOS Software Application including Services and SQL Tools	Workstation
Samford Software Web Services Installer.msi	SwiftPOS Web Application installer	SQL Server
SwiftPOS.MDF	SwiftPOS SQL Data File which is normally installed in the C:\SwiftPOS directory.
SwiftPOS_log.LDF	SwiftPOS SQL Database Log File is created when the SQL Software is running. You cannot attach to the SwiftPOS.MDF file without having the SwiftPOS_log.LDF file available.

SwiftPOS Database Tables

Database Tables included as part of the SwiftPOS database are as follows:

If you are importing or exporting data from SwiftPOS it may be useful to understand where the data is contained in the database tables.

This information and the structures within the tables is Copyright of SwiftPOS Pty Ltd and these tables can change at any time as new features are released in the software.

The SwiftPOS software will provide quotes for consulting and the development of custom features to meet specific requirements. There is no free consulting or support available for sites that choose to develop their own interfaces to SwiftPOS.

It is also important to use the WITH(NOLOCK) on each table in the from section as this will stop any deadlocks with sales processing. If you just put the table then all rows in the table are locked until the query is complete and if sales processing or some other function is trying to access the same data then you end up with Deadlocks.

Tables	Comments
AccountHeadersTable	Stores the headers for all account transactions, this data also appears in the appropriate EJ table.
AccountItemsTable	Stores information relating to the items purchased through account transactions, this data also appears in the appropriate EJ table.
AccountMediaTable	Stores the media type used for each account transaction.
AccountQuoteHeaderTable	Stores BOPOS Quotes
AccountQuoteItemsTable	Stores BOPOS Quotes Items
AccountTempTable	Temporarily Stores Account Payments and removes them when the payment has been allocated to a charge
BatchReportsTable	List of reports that can be printed daily, weekly, monthly etc
BatchTransferItems	Stores Data for the Inventory Transfers screen
BatchTransfers	Stores Data for the Inventory Transfers screen
CacheTable	Stores RPL data prior to sending it to QUEST-Connect
CategoryTable	Category Table (1-253)
ClerkGroupTable	Clerk Group Table (1-255)
ClerkGroupVenueTable	Keeps track of which Clerk/Staff Groups are assigned to each Location Group (Venue).
ClerkTable	Clerk Table (1-999999)
ClerkTimeClockTable	Clock-on, start-break, end break and Clock-off information
CustomReports	INTERNAL USE ONLY
DBTableVersions	INTERNAL USE ONLY
EJItemsTable	Electronic Journal PLU Items Table that contains the sales data of every PLU
EJMediaTable	Electronic Journal Table that shows GST/Taxes and payment methods
EJStringTable	Electronic Journal Table that shows the text PLU's sold at the SwiftPOS Touch terminal.
EJTable	Electronic Journal Header Table that contains the date of every transaction
EntryTypeDescriptionsTable	CURRENTLY NOT USED
EventLogTable	History of events that occur within the software
FamilyTable	Stores the list of Families available in the Inventory system
FamilyVenueTable	Records which Families have been assigned to specified Location Groups (Venues).
GL_JournalTransactionTable	Summary of transactions for exporting to the General Ledger
GLAccountTable	Stores imported General Ledger Account List
GlobalConfigTable	Stores user defined application settings.
HalfHourDescriptionsTable	CURRENTLY NOT USED
HappyHourHeaderTable	Stores Happy Hour Information
HappyHourItemsTable	Stores Happy Hour Information
HistoryTable	Stores summarised sales and stock data.
HourDescriptionsTable	CURRENTLY NOT USED
InterestGrpDescTable	Descriptions of available Members Interest Groups
InterestGrpTable	Interest groups that each Member has
InventoryGrpTable	Product Groups that are linked to PLU/Products. Each PLU/Product must assigned to a Product Group.
LabelImageTable	Stores Product Label Images
LabelItemsTable	Stores Product Label Items
LabelTable	Stores Product Labels
LocationBudgets	Stores Budget information for each Location
LocationCSTable	Stores Closing Stock information for each Location
LocationFilterTable	Defines the different filters available on all the Locations
LocationStkTable	Where the Stock on Hand and Opening Stock values are held
LocationTable	List of Inventory Locations (0001-9999)
ManufacturerTable	Stores all the Product manufacturers separately from the Product Suppliers/wholesalers.
MasterProductGroupTable	List of Product Groups. Each Product Group can be assigned a Master Group
MediaDescriptionTable	Descriptions of all the different Media used at the POS
MemberCardItemsTable	Stores Member Card Items
MemberCardTable	Stores Member Cards
MemberRectTable	Members Receipt Table
MemberTable	The Main Membership and Accounts Table. Members must have a valid Classification in the MemberTypeTable (from 1-100)
MemberTypeTable	Member Classifications Table (1-100)
ModifierTable	Category Modifiers
MonthsTable	CURRENTLY NOT USED
MPRS Tables	INTERNAL USE ONLY
PBTables	All of the tables that start with PB are tables used by price books
PDEOrderTable	Temporarily stores PDE order date before until the purchase order has been committed.
PDEStockCountTable	CURRENTLY NOT USED
PdeStocktakeTable	Temporarily stores PDE stocktake data until the stocktake has been committed.
PostingTypeTable	CURRENTLY NOT USED.
PriceLevelNamesTable	The different price levels that are assigned to each Location
PrintGroupingTable	The Category Print Grouping (1-15)
ProductBarCodeTable	The PLU Barcode file that is linked to each Product.
ProductFamilyTable	Stores the Products in the different Families
ProductionBuildTable	Stores the ingredients for Products that are manufactured in the production facility
ProductionTable	Stores the Products that are manufactured
ProductLabels	Stores information relating to data used by the Product label designer.
ProductMultiSupplierTable	Stores the Supplier records information for each Product.
ProductPricesTable	Holds the different price levels for every Product.
ProductSellUnits	CURRENTLY NOT USED
ProductTable	The Master Product Table
PurchaseItemsTable	Items on Purchase Orders that have been raised.
PurchaseOrderTable	The Supplier that the Purchase order has been raised against
RecipeTable	The Inventory Recipe Table (Portion Control)
RegistrationTable	This table stores your registration information.
SecurityGroupObjectTable	All security group permissions are stored in this table.
SecurityGroupReportTable	All security group Report permissions are stored in this table.
SecurityGroupTable	The details of each security group are held in this table.
Sizes	Stores the Product Size information such as UOM (units of measure), etc.
SoftwareUpdateTable	Stores Connection information for downloading updates
SpecialsHeaderTable	Stores the dates and names of all promotions.
SpecialsItemsTable	Lists all of the items setup in promotions, also holds the post price of each item.
StockCountTable	Stores temporary information with regards to the Inventory Counts screen
StockMovementsTable	History of all Inventory Movements
SupplierTable	The Master Supplier Table
SupplierTransItemsTable	The Items received from Suppliers
SupplierTransTable	The Supplier that inventory has been received from
TaxTable	Stores the tax settings for each of the 4 available taxes
TerminalReconciliationHeaderTable	Stores Cash Off Information
TerminalReconcilitationMediaTable	Stores Cash Off Information
TerminalTable	List of Terminals stored in the software
TimePeriodGroups	Specifies all of the custom time period groups.
TimePeriods	This table determines the start/end times for each period from all time period groups.
TimeZones	Stores time zone information from zones all around the world.
UserLoginTable	Tracks the users currently logged onto the database.
VenueTable	The VenueTable stores all of the settings for each Location Group (Venue).

SwiftPOS Database Table Structures

Member Table Structure

Field Name	Description	Data Type	Length	Default Value
Member_Number	The main Member identifier.	int	4	Not Allow Null
Member_Alpha_Code	Member Alternate ID(numeric only)	nvarchar	12	Allow Null
Member_Firstname	Member first name	nvarchar	30	Allow Null
Member_SecondName	Member second name	nvarchar	30	Allow Null
Member_Surname	Member surname	nvarchar	30	Not Null
Member_Sex	Member gender	nchar		Allow Null
Member_Salutation	Salutation	nvarchar	6	Allow Null
Member_Initials	Member Name Initials	nvarchar	6	Allow Null
Member_Address1	Address 1	nvarchar	30	Allow Null
Member_Address2	Address 2	nvarchar	30	Allow Null
Member_Address3	Address 3	nvarchar	30	Allow Null
Member_State	State	nvarchar	3	Allow Null
Member_Postcode	Postcode	nvarchar	8	Allow Null
Member_Business_Phone	Business phone	nvarchar	12	Allow Null
Member_Business_Fax	Businesses fax	nvarchar	12	Allow Null
Member_Points	Member Points	float	8	Not Null
Member_Points_YTD	Not Currently Used	float	8	Not Null
Member_Sales	Member Sales	money	8	Not Null
Member_Sales_YTD	Not Currently Used	money	8	Not Null
Last_Transaction_Date	Member Last transaction occur date	datetime	8	Allow Null
Member_Notes	Notes	ntext		Allow Null
Membership_Type	Member Classification - Linked to MembershipTypeTable	smallint	2	Not Null
Membership_Group	Not Currently Used	smallint	2	Not Null
Membership_Status	Not Currently Used	smallint	2	Allow Null
Member_Occupation	Custom Field 3	nvarchar	30	Allow Null
Member_Interests	Custom Field 2	nvarchar	30	Allow Null
Mailing_Flag	Flag to indicate if Member/Account can receive mail.	smallint	2	Allow Null
Member_Home_Phone	Home Phone	nvarchar	12	Allow Null
Member_Mobile	Custom Field 4	nvarchar	12	Allow Null
Member_Internet_Address	Custom Field 1	nvarchar	40	Allow Null
Member_Birth_Date	Date of Birth	datetime	8	Allow Null
Member_Aniversary	Not Currently Used	datetime	8	Allow Null
Member_Join_Date	Date Joined	datetime	8	Allow Null
Member_LastPaid_Date	Last Date Membership Renewed	datetime	8	Allow Null
Member_Renewal_Date	Next Renewal due date	datetime	8	Allow Null
Member_Receipt_No	Last Renewal Receipt Number	int	4	Allow Null
Member_Amount_Paid	Last Renewal Amount Paid	money	8	Allow Null
ChangeFlag	Record has changed flag	smallint	2	Allow Null
FolioNumber	Not Currently Used	nvarchar	10	Allow Null
Account_Limit	Account Credit Limit	money	8	Allow Null
Account_Opening_Balance	Not Currently Used	money	8	Allow Null
Account_Balance	Current Account Balance	money	8	Allow Null
Account_Last_Sale	Last Sale Date/Time	datetime	8	Allow Null
Account_Last_Payment	Account last payment date	datetime	8	Allow Null
Account_Current_Period_Sales	Not Currently Used (0)	money	8	Allow Null
Account_Total_Sales	Total Sales To Date	money	8	Allow Null
Detailed_Statement	Not Currently Used set to 1	smallint	2	Allow Null
Last_Statement_Date	Not Currently Used	datetime	8	Allow Null
Member_Price_Level	Not Currently Used	smallint	2	Allow Null
Mail_Address_Active	Different Mailing Address	smallint	2	Allow Null
Mail_Address1	Mailing Address 1	nvarchar	30	Allow Null
Mail_Address2	Mailing Address 2	nvarchar	30	Allow Null
Mail_Address3	Mailing Address 3	nvarchar	30	Allow Null
Mail_State	Mailing State	nvarchar	3	Allow Null
Mail_Postcode	Mailing Postcode	nvarchar	8	Allow Null
Print_Card	Not Currently Used set to 1	smallint	2	Allow Null
MemberABN	ACN/ABN/Company No.	nvarchar	20	Allow Null
FirstTransaction	Date/Time of first transaction	datetime	8	Allow Null
ActivateFrom	Member Active Start Date	datetime	8	Allow Null
ActivateTo	Member Active Expire Date	datetime	8	Allow Null
tblOpen	Not Currently Used	datetime	8	Allow Null
PtsOnNextPurchase	Not Currently Used	bit	1	Not Null
BonusPoints	Not Currently Used	int	4	Not Null
Nominated	Not Currently Used	nvarchar	30	Allow Null
Seconded	Not Currently Used	nvarchar	30	Allow Null
Spouse	Member's Spouse	int	4	Allow Null
RewardCounter	Used for Barrel Draw to store counter or Dollar value	float	8	Not Null
Favourite	Not Currently Used	nvarchar	16	Allow Null
PostBarCode	Not Currently Used	nvarchar	50	Allow Null
eMail_Flag	Flag to indicate if Member/Account can receive Emails	bit	1	Not Null
MemberChangeDate	Last Date/Time this record changed	datetime	8	Not Null
Account_Active	Flag to indicate if Account is active or only Membership	smallint	2	Allow Null
CardCounter	Not Currently Used	int	4	Not Null
CardPrinted	Not Currently Used	datetime	8	Allow Null
rowguid	Internal Use Only auto generated on record creation.	uniqueidentifier		Not Null
MemberPhoto	Member photo	image		Allow Null