1 | TM1 Interface Setup

This article outlines a step-by-step guide to setting up the TM1 Interface through a Scheduled Job and other relevant configurations within BackOffice.

About

TM1 is a financial package used by large businesses; it is enterprise planning software used to implement collaborative planning, budgeting and forecasting solutions, as well as analytical and reporting applications
Using a Transaction Queue DLL, this interface copies two (2) file formats & contents, creates two (2) new database tables within the Bepoz Database with the data to be sent, and a Stored Procedure is created to allow for retrieval of the data from the tables

Prerequisites

Minimum Bepoz version of v4.7.1.5 and onwards
In order to access the device setup; the following Operator pre-requisites have to be fulfilled

Operator Maintenance 'General' Tab

System Setup Flags - "Access Venue Stores & Tills flag
System Setup Flags - "Maintain Venue Stores & Tills flag

TM1 Transaction Queue

The interface itself is maintained using a TransQueue Device running at the Head Office and processes every Transaction received
This Transaction Queue will Add and Update Records in the Database Tables called "SG_SalesDaily", "SG_DiscDaily" and "SC_PaymentHourly"

Device Setup Prerequisite

Store Custom Fields

To prepare for a setting within the Transaction Queue Device Options, a Custom Text field will need to be configured in Store Custom Info as follows:

Label the Custom Text Field "TM1 Revenue Type"
Options for the aforementioned Text Field must be "Mains", "Table", and "Items"

Device Setup

After logging into BackOffice, complete the following steps to create & configure a TM1 Transaction Queue:

Navigate to the System Setup component in the sidebar, expand, and select Venue, Stores & Tills to open the Venue & Till List Tab
Highlight the Head Office Server Workstation and click on the Add New Device button in the header; when prompted select 'Blank Device' and a New Device and Interfaces Maintenance window will open
Enter a Device Name, set the Device Type to 'Transaction Queue', the External DLL Name to 'Transqtm1.Dll', and the Class Name to 'TM1 Export'
Configure the Device Options as required:

Item Covers include Condiment - enable this flag to include the Condiments Sold in the count and balance to the Till Summaries Report; when calculating covers from Mains or Items, it's best not to do so
Log Processing - when enabled, two (2) Logs will be written each day to the following directory: LocalData\TM1Logs; 1 for DiscDaily and 1 for SalesDaily exports
Food SortGroups CSV - in a comma delimited string, list the System's SortGroups (from Global Settings) that constitute Food Items
Beverage SortGroups CSV - in a comma delimited string, list the System's SortGroups (from Global Settings) that constitute Beverage Items
Disc. Item Discount CSV - in a comma delimited string, list the Item Discount Totalizers that are to be reported as Discounts (as opposed to Comps); if left blank, ALL Discount Totalizers will be treated as Comps
Disc. Order Discount CSV - in a comma delimited string, list the Order Discount Totalizers that are to be reported as Discounts (as opposed to Comps); if left blank, ALL Discount Totalizers will be treated as Comps
Mains Sorts CV - in a comma delimited string, list the SortTypes (from Global Settings) that signify the Mains for the purposes of Covers Reporting
Custom TextField for Employee Code - using an Operator Custom Text Field as an "Employee Code", set the number of the Custom Text here
Custom TextField for Store CoversType - after configuring Store Custom Info with text options for "TM1 Revenue Type", set the number of the Custom Text option here

The Custom Text options to be used are: Items, Table, and Mains
If the correct value for the Store Export Codes isn't set, the TM1 Export will default to Items

Clicking the Create DataTables button will create the blank Tables in the Bepoz Database
Click OK to save the Transaction Queue

Database Tables

SG_SalesDaily

This is Sales Data by Location, by Serving Period, by Date
The Data contained is broken up by Cash / Comp / Total

Then each of those divisions is also broken up by Food / Beverage / Other / Covers

There is also a Discount Field
All Totals shown are Gross, with Discounts added back in
"RvcNo" is determined by the Venue Setting for Reslink Export Code (probably Store Export Code)
LocationID is a Venue SiteCode

SG_DiscDaily

This is Payment Data by Location, by Date, by Employee, by TransType. by Payment Type
The Data contained is LineCount and LineTotal
The TransType Field is one of Payment / Discount / ServiceCharge / Tip
The Payment Type is either one of the actual Bepoz Payment types, a Product Promotion name, a Voucher name (only non-ProdPromo types) or it is a Discount Totalizer for discounts that are not triggered by Vouchers or Product Promotions
"RvcNo" is determined by the Venue Setting for Reslink Export Code (probably Store Export Code)
LocationID is a Venue SiteCode
EmployeeID is Operator ID
Employee Code is determined by Device Setup
Custom Code is ProdPromotion or Voucher = Custom Text Field 1

SG_PaymentHourly

This is Payment Data generated based on TrxDate, Rvcno TimeBreakDown, LocationID, ProductID, Size
TrxDate is the datetime of the product sales for the time period
Rvcno is the Store Export Code_1 field of the SmartPOS till store for the transaction
LocationID is the Venue SiteCode
ProductID is the ProductID in Bepoz
Size is the Product Size 1-10

Developer Notes

Comp vs. Discount

Some organizations use a concept of Comps which are Complimentary items given to customers

These may be a Voucher (which produces a Discount), a simple Discount from Manual Key or Prod Promo, or a transfer to a Manager Card

As Bepoz tracks all discounts given via Discount Totalisers, these can be used to access the Data required
In the Device setup, there are 2 fields that are used to define which Item Discount Totalizers and which Order Discount Totalizers are to be considered as Comps - as opposed to Discounts

These fields can be left blank for All to be Comps

These fields are set up as Comma Separated Values (CSV)

E.g., to set values 1 and 2 and 4 and 8 then enter "1,2,4,8" into the field
Both Tables use these setups to determine whether a Discount applied is to be considered a Comp Payment and report accordingly

Food / Beverage / Other

In the Device setup, there are 2 fields that are used to define which Sort Groups constitute both the Food Items and Beverage Items
Any Item in a SortGroup other than those listed are deemed to be Other
These Fields are set up as Comma Separated Values (CSV)

E.g., to set values 1 and 2 and 4 and 8 then enter "1,2,4,8" into the field

Table Sales

The SalesDaily table will only include Sales for completed Transactions; including all simple Cash and Comp transactions
For Table Sales, however, the totals will not be affected until the Table is closed

At that time the whole of the Table Items are added to the Data Table in the current Shift/Till
The DiscDaily Table is basically a Payment report and will reflect the Table Payments as they are entered