Download Export Data - TEAM Systems Ltd

TEAM FOR WINDOWS
EXPORT REPORT GENERATOR
USER MANUAL
Manual Version 1.0
Yelverton Business Park, Yelverton, Devon, PL20 7PY
Web : www.teamsystems.co.uk
Email : [email protected]
Tel. 01822 855466
TEAM Systems
User Manual
CONTENTS
CONTENTS ...................................................................................... 3
1 WELCOME ................................................................................ 5
2 INSTALLATION.......................................................................... 6
3 THE WIZARD ............................................................................ 7
3.01
General Information......................................................... 7
3.02
How To Creating An Export File ........................................ 8
3.03
Find the TEAM for Windows Data ...................................... 9
3.04
Select The Table And Fields You Want............................. 10
3.05
Filter/ Tidy Up The Data ................................................. 12
3.06
Final Touches ................................................................ 13
3.07
Exporting The Information.............................................. 15
3.08
Saving Your Report........................................................ 16
3.09
Loading a Report ........................................................... 16
4 OTHER SCREENS..................................................................... 17
4.01
Options / Parameters Screen .......................................... 17
4.02
Report Notes................................................................. 18
5 COMMAND LINE PARAMETERS ................................................. 19
5.01
Running The Export Automatically................................... 19
6 SQL........................................................................................ 20
6.01
Using SQL Rather Than The Wizard................................. 20
7 FILE STRUCTURE INFORMATION.............................................. 21
7.01
Tables, Fields, Records & Relationships ........................... 21
8 FILE STRUCTURES................................................................... 23
8.01
TCUS - Customer File ..................................................... 23
8.02
STRANH - Invoice Header File......................................... 25
8.03
STRAND - Invoice Detail Lines File .................................. 27
8.04
TSUP - Supplier Details File ............................................ 28
8.05
PTRANH - Purchase Invoice Header File........................... 29
8.06
PTRAND - Purchase Invoice Detail Lines File .................... 30
8.07
TSTK - Stock File ........................................................... 31
8.08
TCAT - Stock Categories File........................................... 32
8.09
TSTKOB - Stock Levels at Other Branches........................ 33
8.10
TFIT - Fitter Details ....................................................... 33
3
TEAM Systems
User Manual
8.11
TMOT - MOT Details....................................................... 34
8.12
TPAY - Customer & Supplier Payments............................. 35
8.13
TSAL – Temporary Sales Summary Table ......................... 36
9 CONTACT / SUPPORT............................................................... 40
4
TEAM Systems
1
User Manual
WELCOME
TEAM Systems
2
User Manual
INSTALLATION
This booklet is designed to be a starting guide and reference to using the Team for
Windows Data Export Program.
Copy all the Files from the CD into the TEAM Systems Program Directory. This directory
is usually either:
You will find a run through of the
basic wizard, description of the built in
functions, and reference sheets
explaining the TEAM for windows file
structures.
For a Single Version of Team
For a Network Version of Team
C:\Program Files\Team Systems\Team\
T:\Team\
When running The Export Program for the first time, it will ask for an activation key.
Please call the TEAM Systems Helpdesk for the Activation Key. It will not appear again.
This Activation Key will allow the program to be run on the current machine.
Installing onto another machine will require another Activation Key. Please call for
details.
Please contact us with any questions
that you may have, we want your
experience with TEAM to be the best
we can make it.
Some
knowledge
of
computer
databases would be advantageous
when using the program. If you are
having problems please call for assistance in creating an export document.
TEAM Systems
5
6
TEAM Systems
3
User Manual
TEAM Systems
User Manual
3.02 How To Creating An Export File
THE WIZARD
Once the program has loaded, click Next or New Report to start.
3.01 General Information
The Export Report Generator wizard is designed to make it easy to produce an export
CSV file from TEAM for Windows data, without having to know the Ins and Outs of
database programming.
You will have to learn some very simple database basics and these are listed in a basic
glossary below, for a more in-depth explanation please read Chapter 7.
Table
:
Database:
Record :
:
Fields
:
:
Relation :
:
:
A computer file that contains data (i.e. TCUS.DB)
A collection of tables or files.
A single transaction of data (e.g. one customer record)
you could think of Records as the Rows in a table.
Boxes of specific information per record (e.g. customer name)
you could think of Fields as the Columns in the table.
Tables can be linked together by similar fields in both tables.
The Invoice Tables will have a Customer Account code
allowing you to link them together.
A table contains many records, all similar types or records are grouped together into a
table, all the customers are in one table, all the suppliers are in another table.
The wizard allows you to select the table you want to export, select the relevant
information from this table (Fields) and select specific records. For example you can get
the program to export only Michelin Tyres codes and prices, or only Account customers
and their addresses.
The inbuilt wizard displays a message on the right hand side giving a brief explanation
of what to do.
You can click the Next button to move onto the next stage or the Back button to go
back and change the setting you have selected on the previous screen.
7
8
TEAM Systems
User Manual
TEAM Systems
User Manual
3.03 Find the TEAM for Windows Data
3.04 Select The Table And Fields You Want
Select the directory where the windows data that you want to export is stored. The
Export Report Generator will default to the usual Team Systems data directory. If you
have TEAM installed somewhere else, you can select the drive and directory.
Select the Table you require from the drop down list.
After selecting a table the lower box will fill with the field names. You can then select
each field you require by clicking on the boxes to put a tick by the data you want to
export from that table. Click the Next Button when finished.
When the data directory has been highlighted, click the Next Button to continue.
9
10
TEAM Systems
User Manual
Fields from other linked tables will also be available for selection. If you select the
Invoice Detail lines Table STRAND.DB you will be able to select related information from
the Invoice header file (STRANH.DB), and the customer details file (TCUS.DB).
Not all the links that are in TEAM for windows are set up in the Export program, other
links will have to be achieved by exporting 2 sets of data and linking in another program
such as Microsoft Access.
TEAM Systems
User Manual
3.05 Filter/ Tidy Up The Data
The next screen allows you to apply 7 rules to your data to filter out information you
don’t want. If you want every record in the table just click next.
In the example below we have selected that the CUS_CASH field (is this customer a
Cash Customer) must equal False, meaning we only want Account Customer details.
You can specify more rules by selecting the logical pull down box and selecting ‘AND’ or
‘OR’ and entering another filter line. Here’s an example:
We only want Account customers with account codes between A and D.
11
12
TEAM Systems
User Manual
This example show that we want all records where:
CUS_CASH = False (all the account only customers)
TEAM Systems
User Manual
You could press the Back button a couple of times to go back to the select fields screen,
and add a field you’ve forgotten, and then keep clicking the Next Button to get back to
this screen.
and also these records must be where
CUS_NO >= A (customer account code is bigger or equal to the letter A)
and also these records be where
CUS_NO < E (customer account code is less than E )
This shows how you can build quite complex filters into your reports. When you have
entered all your filters, click next to continue.
3.06 Final Touches
The Final screen show you the result that you will be exporting. At this point you can,
order the information by CUS_NAM (customer Name) for example, or by PostCode.
If you want to change one of the filters, or add an additional filter you can click the Back
Button to return to the last page, and then click Next button again to see the results.
13
14
TEAM Systems
User Manual
TEAM Systems
User Manual
3.07 Exporting The Information
3.08 Saving Your Report
When you are happy with the information you have selected, you can press the Export
to CSV button.
If the report you have created, is something you are likely to want to do again, then you
can save your report by clicking the Save Report Button. Like the export saving a report
will bring up the Save As box and will ask you where to save the report details. Don’t
forget where you save it, and rename the file to something useful which tells you what it
does.
Please Note:
When you save a report, you are saving your instructions to build that report, and NOT
saving the data. Loading and running a saved report will produce different data, if the
TEAM for Windows data has changed since the last time you ran that report.
3.09 Loading a Report
If you previously saved a report that you want to run again, you can click the Load
Report button. An Open file select screen will appear and ask you to select the .REP file
you want to load. Find the file and load the report.
A Box will appear saying the report has been loaded, click ok, and then you can just
next through the pages, and you can see that all your previous selections have been
recovered. If you next to the end you can view your data, and export it if you wish.
This will bring up a Save As window allowing you to specify where you want to save the
export file to.
The Export will save a CSV format file, this stands for Comma Separated Variable. What
this means is that you will get a text file with an extension of .CSV which will have each
record on a separate line, and each field separated by a comma.
Please Note:
When you save a report, you are saving your instructions to build that report, and NOT
saving the data. Loading and running a saved report will produce different data, if the
TEAM for Windows data has changed since the last time you ran that report.
This format is very simple to modify, and can be loaded into numerous different
program, Microsoft Excel, Access, Lotus, Word, etc, or specific reporting tools.
15
16
TEAM Systems
4
User Manual
OTHER SCREENS
TEAM Systems
User Manual
4.02 Report Notes
As well as saving your report with a relevant file name so you can find it easily, you can
add notes to the report. To add Notes to a report click the Report Notes Button
4.01 Options / Parameters Screen
The Parameter screen allows you to setup some default information specific to each
report that you are creating.
You can type any details with information regarding the report or who it was for, or any
thing. To save the notes with the report you can click the Close Report Notes Button,
and then save your report.
Include Header Row on CSV Export. Ticking this box, adds a row to the top of the CSV
file when exported to display the Column or Field names.
Default Export Path and File Name. When Exporting your data, the program will ask
you for a path and filename to save the CSV file to. This box allows you to specify a
default path and filename.
To Save the Options for this report click Close Parameters button, and then save you
report.
17
18
TEAM Systems
5
User Manual
COMMAND LINE PARAMETERS
5.01 Running The Export Automatically
The Export Report Generator program also allows command line parameters to be
added, to allow the program to run automatically. To do this you must first do the
following:
1)
2)
3)
Create a report using the wizard.
Make sure the Report Options have the Default Export Path and File Set
Correctly.
Save the Report with a meaningful file name in an easily accessible place.
TEAM Systems
6
User Manual
SQL
6.01 Using SQL Rather Than The Wizard
The Export Report Generator creates an SQL (Structured Query Language) statement.
Which it then runs to collate the TEAM Data, the Export then exports these results.
Rather than using the wizard you could enter your own SQL statements and export the
results.
This function is not yet available.
Once you have a report set up and saved, you can then create either a batch file, or a
windows shortcut to the Reporter.exe program.
Edit the batch file or shortcut and add either the first or both parameters after the
filename
Parameter 1
: The Path and filename of the Report you want to load automatically.
: This is mandatory
: “C:\REPS\TYREREP1.REP”
Parameter 2
: This is optional
: /EXPORT
If you just have the first parameter specified the program will load as normal but with
the specified report already loaded.
If you have added the second parameter as well the program will load the splash
screen, and load the report, run the report, and export the report (to the default
specified) automatically.
Example of batch file … RUNME.BAT
"REPORTER.EXE" "C:\REPS\TYREREP1.REP" /EXPORT
19
20
TEAM Systems
7
User Manual
FILE STRUCTURE INFORMATION
7.01 Tables, Fields, Records & Relationships
TEAM for Windows stores all the information you enter into a database. The database
consists of a collection of tables which relate to specific data i.e. Customer Details are
stored in the TCUS.DB table.
All these tables are stored in one directory which is either:
For a Single Version of Team
For a Network Version of Team
C:\Program Files\Team Systems\Team\Data\
T:\Team\Data\
There are many different table *.db files usually around 50, this can be different
depending on which options in TEAM for Windows you are running or which version of
TEAM you are running.
TEAM Systems
User Manual
one of the first fields (a field is a box containing specific data Registration, Selling Price,
etc) in the table. In the case of Sales Invoices the fields are as follows:
STH_CODE in the header file, and STD_CODE in the detail file, which is the TEAM
Invoice Number. So the STRANH.DB table contains 1 record for Invoice Header Details
including the STH_CODE (TEAM Invoice Number), and the STRAND.DB table may
contain multiple details records with the STD_CODE (TEAM Invoice Number) which
matches field in the Header.
The Invoice Header STRANH.DB also contains a STH_ACCOUNT field and this one links
to the Customer account details TCUS.DB -> CUS_NO field. With these fields being the
same you can link the tables together.
DON’T PANIC
The purpose of this export program and is that its inbuilt wizard will handle most of this
linking for you. If you select the Invoice Details table it will automatically link in and
relate the relevant invoice header details, and Customer details.
Each table relates to specific team information for example TCUS.DB is the Customer
Table, TSUP.DB is the Supplier Table, TSTK.DB is the Stock Table. In most cases each
record in the table will relate to 1 transaction in TEAM for example:
Customer J0001 – Joe Bloggs and his address details will be 1 record or transaction
within the TCUS.DB
StockItem MI17565TR13 – 175/65 TR 13 MICHELIN XT1 and the quantitys/price details
will be 1 record or transaction within the TSTK.DB table.
Invoices and deliveries are different however. An Customer Invoice is split into two
separate tables STRANH.DB and STRAND.DB
STRANH.DB stores the Header information for the invoice, such as reference details and
whole invoice totals.
STRAND.DB stores the Detail or Line information for the invoice, such each stock item
and each quantity.
An Invoice will create 1 record in STRANH.DB, and multiple records in STRAND.DB
which is 1 record for each line on the invoice.
Because these details are stored in different tables, you need a way to know which
invoice lines match which invoice header, this is done by using a KEY or CODE, usually
21
22
TEAM Systems
8
User Manual
FILE STRUCTURES
Not all the team tables are listed below just the commonly used ones, all other
tables can be accessed. Please call for descriptions of fields.
8.01 TCUS - Customer File
Structure of file C:\Program Files\TEAM Systems\Team\Data\TCUS.DB
Field Name
CUS_NO
CUS_CASH
CUS_NAM
CUS_ADD1
CUS_ADD2
CUS_ADD3
CUS_ADD4
CUS_POST
CUS_TEL
CUS_FAX
CUS_EMAIL
CUS_VATNO
CUS_SRCE
CUS_CONT
CUS_MON0
Type
String
Boolean
String
String
String
String
String
String
String
String
String
String
String
String
Currency
Size
10
0
30
25
25
25
25
10
20
20
40
20
4
30
0
CUS_MON1
Currency
0
CUS_MON2
Currency
0
CUS_MON3
Currency
0
CUS_BAL
CUS_LIDAT
CUS_LINUM
Currency
Date
String
0
0
8
Description
Customer Code
Is this a Cash Customer (no Account)
Customer Name
Customer Address Line 1
Customer Address Line 2
Customer Address Line 3
Customer Address Line 4
Customer Address PostCode
Customer Telephone Number
Customer Fax Number
Customer Email Address
Customer VAT Registration Number
Customer Source (group of customers)
Customer Contact
Customer Balance Current Month
*NOTE - This Value is only updated after
an Aged Debtors Report has been run.
Customer Balance Current Month -1
*NOTE - This Value is only updated after
an Aged Debtors Report has been run.
Customer Balance Current Month -2
*NOTE - This Value is only updated after
an Aged Debtors Report has been run.
Customer Balance 3 Months and over.
*NOTE - This Value is only updated after
an Aged Debtors Report has been run.
Current Outstanding Balance
Date of Last Invoice for this customer
Last Invoice number for this customer
23
TEAM Systems
CUS_LPNUM
CUS_TERM
CUS_CRED
CUS_DISC
CUS_PCOD
CUS_SRTKEY
CUS_STOP
CUS_EXVAT
CUS_INTER
CUS_DNOTE
CUS_PRICE
CUS_OTHEC
CUS_NOINV
CUS_NOTES
CUS_NOSTAT
CUS_Update
CUS_DelAdd1
CUS_DelAdd2
CUS_DelAdd3
CUS_DelAdd4
CUS_DelAdd5
CUS_DelPost
CUS_DelCountry
CUS_Add5
CUS_Country
CUS_Insurance
CUS_Type
User Manual
String
SmallInt
Currency
Float
SmallInt
String
Boolean
Boolean
Boolean
Boolean
String
Boolean
Boolean
Memo
Boolean
Date
String
String
String
String
String
String
String
String
String
Boolean
Integer
8
0
0
0
0
20
0
0
0
0
1
0
0
1
0
0
25
25
25
25
25
10
15
25
15
0
0
Last Payment number for this customer
Payment Terms (no of days)
Customer Credit Limit
Customer Discount
*NOT USED*
Sort Key
Customer on Stop
Is this Customer an Ex VAT customer
Is this Customer Internal
Print Delivery Note?
Default Price list A,B,C,D
Is this Customer Outside EC
Don’t print invoices for this customer
General Customer Notes
Don’t include customer in Statement Run
Last Date Customer Details Updated
Customer Delivery Address
Customer Delivery Address
Customer Delivery Address
Customer Delivery Address
Customer Delivery Address
Customer Delivery Address
Customer Delivery Address
Customer Address Line 5
Customer Country
Customer is Insurance Company
Customer Type Code
24
TEAM Systems
User Manual
8.02 STRANH - Invoice Header File
Structure of file C:\Program Files\TEAM Systems\Team\Data\STRANH.DB
Field Name
STH_Code
STH_Type
STH_CodeNoSpaces
STH_Reference
STH_Account
STH_Site
STH_Date
STH_Time
STH_StatDate
STH_Ref1
STH_Ref2
STH_Source
STH_Reg
STH_Car
STH_AnnualMiles
STH_Mileage
STH_MOTDue
STH_ServiceDue
STH_MainFitter
STH_NetValue
STH_NetVal0
STH_NetVal1
STH_NetVal2
STH_TaxValue
STH_TaxVal0
STH_TaxVal1
STH_TaxVal2
STH_Cost
STH_GrossValue
STH_TaxFree
STH_BankRecd
STH_TaxRecd
STH_Printed
STH_Progress
Type
String
String
String
String
String
String
Date
Time
Date
String
String
String
String
String
String
String
Date
Date
String
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Boolean
Boolean
Boolean
Boolean
SmallInt
Size
8
1
8
8
10
4
0
0
0
20
20
4
10
20
10
10
0
0
3
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
25
Description
TEAM Sales Invoice Number
Invoice Type I=invoice A=Adjustment
TEAM Sales Invoice Number
Invoice Reference
Invoice Customer Account No
Branch where Invoice was created
Invoice Date
Invoice Time
Statement Date
Invoice Reference
nd
Invoice 2 Reference
Customer Source
Vehicle Registration (No Spaces)
Vehicle Type
Annual Miles
Current Mileage
Next MOT Due Date
Next Service Due Date
Fitter who did the work
Net Value of Invoice
Net Value of Invoice at VAT Rate 0
Net Value of Invoice at VAT Rate 1
Net Value of Invoice at VAT Rate 2
Total Tax Value of Invoice
Tax Value of invoice at VAT Rate 0
Tax Value of invoice at VAT Rate 1
Tax Value of invoice at VAT Rate 2
Total Cost of Items on Invoice
Gross Value of Invoice
Was this Invoice Tax Free
Has this invoice been Reconciled
*NOT USED*
Has this Invoice been Printed
Invoice Progress 0=invoice, 1=held,
2=quote
TEAM Systems
STH_Notes
STH_Balance
STH_Invalid
STH_AdjCreateDate
STH_RegDisplay
STH_NomTrans
STH_Update
STH_OldCus
User Manual
Memo
Currency
Boolean
Date
String
Boolean
Date
String
1
0
0
0
10
0
0
10
General Invoice Notes
Balance Outstanding on Invoice
Is There an Error with this Invoice
Date Adjustment Created
Vehicle Registration (includes space)
*NOT USED*
Last Time Invoice was updated
Customer Code (if Changed)
26
TEAM Systems
User Manual
TEAM Systems
User Manual
8.03 STRAND - Invoice Detail Lines File
8.04 TSUP - Supplier Details File
Structure of file C:\Program Files\TEAM Systems\Team\Data\STRAND.DB
Structure of file C:\Program Files\TEAM Systems\Team\Data\TSUP.DB
Field Name
STD_Code
STD_Line
STD_NomCode
STD_StockCode
STD_Desc1
STD_Desc2
STD_Category
STD_Quantity
STD_UnitPrice
STD_Discount
STD_Fitter
STD_Level
STD_NetValue
STD_TaxValue
STD_TaxCode
STD_TaxRate
STD_Cost
STD_GrossValue
STD_RRP
STD_GrossProfit
STD_Progress
Type
String
SmallInt
String
String
String
String
String
Float
Currency
Float
String
Float
Currency
Currency
String
Float
Currency
Currency
Currency
Currency
SmallInt
Size
8
0
8
16
30
30
5
0
0
0
3
0
0
0
1
0
0
0
0
0
0
STD_SerialNumber
STD_Type
String
String
20
1
STD_NomTrans
STD_Date
STD_Time
STD_Account
Boolean
Date
Time
String
0
0
0
10
27
Description
TEAM Sales Invoice Number
Invoice Line Number
*NOT USED*
Stock Code of Item
Description
nd
2 Description
Category Stock Code in
Quantity
Unit Price
Line Discount
Fitter for this line
Stock Level including STD_Quantity
Net Value of this invoice line
Tax Value of this invoice line
Tax Code for this invoice line 0,1,2
Tax Rate for this invoice line
Cost of this invoice line
Gross Value of this invoice line
Recommend Retail Price
Gross Profit of this line
Invoice Progress 0=invoice, 1=held,
2=quote
Stock item Serial Number
Line Type S=Stock, K=Kit Head,
L=Kit line R=Rem
*NOT USED*
Date Invoice line created
Time Invoice line created
Customer Account of Invoice
Field Name
SUP_KEY
SUP_NAME
SUP_SRTKEY
SUP_ADD1
SUP_ADD2
SUP_ADD3
SUP_ADD4
SUP_POST
SUP_TEL1
SUP_TEL2
SUP_FAX
SUP_CON1
SUP_EXVAT
SUP_OTHEC
SUP_INTER
SUP_EMAIL
SUP_NOTES
SUP_Update
Type
String
String
String
String
String
String
String
String
String
String
String
String
Boolean
Boolean
Boolean
String
Memo
Date
Size
6
30
20
30
30
30
30
10
15
15
15
20
0
0
0
40
1
0
Description
Supplier Account Code
Supplier Name
Supplier Sort Key (Search Field)
Supplier Address Line 1
Supplier Address Line 2
Supplier Address Line 3
Supplier Address Line 4
Supplier Address PostCode
Supplier Telephone Number
nd
2 Supplier Phone Number
Supplier Fax Number
Supplier Contact
Is This Supplier ExVAT
Is This Supplier Outside the EC
Is This Supplier an Internal Account
Suppliers Email Address
Supplier Notes
Last Date Supplier Details Updated
28
TEAM Systems
User Manual
8.05 PTRANH - Purchase Invoice Header File
Structure of file C:\Program Files\TEAM Systems\Team\Data\PTRANH.DB
Field Name
PTH_Code
PTH_SITE
PTH_SUPP
PTH_Fitter
PTH_INVNO
PTH_DELNO
PTH_PURNO
PTH_Confirmed
PTH_ForResale
PTH_Type
Type
String
String
String
String
String
String
String
Boolean
Boolean
String
Size
8
4
6
3
10
10
10
0
0
1
PTH_DATE
PTH_Time
PTH_STAT
PTH_UPD
PTH_NetValue
PTH_NetVal0
PTH_NetVal1
PTH_NetVal2
PTH_TaxValue
PTH_TaxVal0
PTH_TaxVal1
PTH_TaxVal2
PTH_GrossValue
PTH_Balance
PTH_TaxFree
PTH_Progress
PTH_BankRecd
PTH_TaxRecd
PTH_EditedByHO
PTH_OldSup
Date
Time
Date
Date
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Boolean
SmallInt
Boolean
Boolean
Boolean
String
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
10
29
Description
TEAM Purchase Invoice Number
Branch where created
Supplier Code
Fitter Code
Invoice Number
Delivery Number
Purchase
Are Prices confirmed
Are These Goods for Resale
Invoice Type R=Resale,
N=NotforResale, O=Outside VAT
Date of Purchase Invoice
Time of Purchase Invoice
Invoice Last Paid/Allocation Date
Last Date this was Edited
Invoice Net Value
Net Value at VAT Rate 0
Net Value at VAT Rate 1
Net Value at VAT Rate 2
Invoice Total Tax Value
Tax Value at VAT Rate 0
Tax Value at VAT Rate 1
Tax Value at VAT Rate 2
Invoice Gross Value
Balance Outstanding on Invoice
Is this a Tax Free Invoice
Invoice Progress 0=invoice, 1=held
Included on Bank Reconciliation
*NOT USED*
Was This Edited by Head Office
Supplier Code (if changed)
TEAM Systems
User Manual
8.06 PTRAND - Purchase Invoice Detail Lines File
Structure of file C:\Program Files\TEAM Systems\Team\Data\PTRAND.DB
Field Name
PTD_Code
PTD_Line
PTD_StockCode
PTD_Desc
PTD_Reason
PTD_Fitter
PTD_Quantity
PTD_Level
PTD_UnitCost
PTD_NetValue
PTD_Category
PTD_NomCode
PTD_TaxRate
PTD_TaxCode
PTD_TaxValue
PTD_GrossValue
PTD_Date
PTD_Time
PTD_NomTrans
Type
String
SmallInt
String
String
String
String
Float
Float
Currency
Currency
String
String
Float
String
Currency
Currency
Date
Time
Boolean
Size
8
0
16
30
30
3
0
0
0
0
5
8
0
1
0
0
0
0
0
Description
TEAM Purchase Invoice Number
Purchase Invoice Line Number
Stock Code for this line
Stock Description for this line
Reason text for this line
Fitter who created Invoice
Quantity of Stock items on this line
Stock Level including PTD_Quantity
Unit Cost of this stock item on this line
Net Value of this line
Category of Stock item on this line
*NOT USED*
Tax Rate of this invoice line
Tax Code of this invoice line
Tax Value of this invoice line
Gross Value of this invoice line
Date this invoice line was created
Time this invoice line was created
*NOT USED*
30
TEAM Systems
User Manual
TEAM Systems
STK_MURN
STK_OnOrder
8.07 TSTK - Stock File
User Manual
String
Float
20
0
Stock Manufacturers Reference Number
Stock Quanity on Order
Structure of file C:\Program Files\TEAM Systems\Team\Data\TSTK.DB
Field Name
STK_KEY
STK_DES1
STK_DES2
STK_SIZE
STK_DIAMETER
STK_SPEED
STK_PROFILE
STK_MAST
STK_CAT
STK_COST
STK_LCOST
STK_SELLA
STK_SELLB
STK_SELLC
STK_SELLD
STK_DISC
STK_VRATE
STK_PRATE
STK_QTY
STK_QTY2
STK_QTYH
STK_LREF
STK_MANF
STK_SUPP
STK_REORD
STK_ROMAX
STK_TYPE
STK_LINV
STK_SOLD
STK_VALUE
STK_LOC
STK_LUPD
STK_NOTES
STK_Analysis
STK_Search
Type
String
String
String
String
String
String
String
String
String
Currency
Currency
Currency
Currency
Currency
Currency
String
String
String
Float
Float
Float
String
String
String
SmallInt
SmallInt
Boolean
String
Float
Currency
String
Date
Memo
String
String
Size
16
30
30
4
4
2
3
16
5
0
0
0
0
0
0
4
1
1
0
0
0
10
6
6
0
0
0
8
0
0
10
0
1
10
25
Description
Stock Code
Stock Primary Description
Stock Secondary Description
Tyre Width
Tyre Diameter
Tyre Speed Rating
Tyre Profile
Stock Bar Code
Stock Category Code
Stock Average Cost
Last Cost Price Paid
Selling Price – Price List A
Selling Price – Price List B
Selling Price – Price List C
Selling Price – Price List D
Discount Code
VAT Rate for Sales
VAT Rate for Purchases
Stock Quantity
*NOT USED*
Stock Quantity on Hold
Last Invoice Reference for this stockcode
Manufacturer
Default Supplier
Stock Reorder Level
Stock Reorder up-to max level
Stock Item Active
Last Invoice for this stockcode
Last Sold Price
Stock Value
Stock Location
Last Date Stock Code Updated
Stock Notes
Stock Analysis (search field)
Stock Search (search field)
31
8.08 TCAT - Stock Categories File
Structure of file C:\Program Files\TEAM Systems\Team\Data\TCat.DB
Field Name
CAT_KEY
CAT_DESC
CAT_NOMCODE
CAT_MMGN
CAT_PRIO
CAT_COST
CAT_NODEL
CAT_QTY
CAT_VRATE
CAT_PRATE
CAT_ADD1
CAT_ADD2
CAT_ADD3
CAT_QTY1
CAT_QTY2
CAT_QTY3
CAT_TYPE
CAT_BulkCode
CAT_Update
CAT_PNomCode
CAT_Add4
CAT_Add5
CAT_Qty4
CAT_Qty5
Type
String
String
String
Float
Boolean
Boolean
Boolean
Boolean
String
String
String
String
String
Boolean
Boolean
Boolean
String
String
Date
String
String
String
Boolean
Boolean
Size
5
15
5
0
0
0
0
0
1
1
16
16
16
0
0
0
1
16
0
5
16
16
0
0
Description
Stock Category Code
Category Description
*NOT USED*
Category Minimum Margin
Category Priority
Ignore Costs
Don’t Track Levels of Stock Items
*NOT USED*
Default Sales VAT Rate
Default Purchase VAT Rate
Additional Invoice Line 1
Additional Invoice Line 2
Additional Invoice Line 3
Multiply by stock items quantity for line1
Multiply by stock items quantity for line2
Multiply by stock items quantity for line3
Category Type, Normal/Tyres
Bulk Buy Stock Code
Last Date Category Updated
*NOT USED*
Additional Invoice Line 4
Additional Invoice Line 5
Multiply by stock items quantity for line4
Multiply by stock items quantity for line5
32
TEAM Systems
User Manual
TEAM Systems
User Manual
8.09 TSTKOB - Stock Levels at Other Branches
8.11 TMOT - MOT Details
Structure of file C:\Program Files\TEAM Systems\Team\Data\TSTKOB.DB
Structure of file C:\Program Files\TEAM Systems\Team\Data\TMot.DB
Field Name
SOB_StockCode
SOB_Branch
SOB_Level
SOB_Updated
SOB_QtyH
SOB_Cost
SOB_ROMAX
SOB_REORD
SOB_SellA
SOB_LRef
SOB_LInv
SOB_LCost
Type
String
String
Float
Date
Float
Currency
Integer
Integer
Currency
String
String
Currency
Description
Stock Code
Branch Code
Level at Branch
Last Date this Stock Code Updated
Quantity on Hold at Branch
Cost of Stock item at Branch
Reorder Maximum (up-tp) level at branch
Reorder level at branch
Branch Sell A Price
Last Reference at Branch
Last Invoice Number at Branch
Last Cost at Branch
Size
16
4
0
0
0
0
0
0
0
10
8
0
8.10 TFIT - Fitter Details
Structure of file C:\Program Files\TEAM Systems\Team\Data\TfitB.DB
Field Name
FIT_INIT
FIT_NAME
Type
String
String
Size
3
30
Description
Fitter Code
Fitter Name
33
Field Name
DummyKey
MOT_KEY
MOT_DATE
MOT_CERT
MOT_TESTER
MOT_REG
MOT_CAR
MOT_WGHT
MOT_ENGINE
MOT_CHASS
MOT_GOTCAT
MOT_DIESEL
MOT_YEAR
MOT_CLASS
MOT_BAD
MOT_BLGTS
MOT_BSTEER
MOT_BBRAKE
MOT_BTYRE
MOT_BPLATE
MOT_BVIEWS
MOT_BEMPET
MOT_BEMCAT
MOT_BEMDIE
MOT_BOTHER
MOT_TYPE
MOT_RegNoSpaces
Type
AutoInc
String
Date
String
String
String
String
SmallInt
String
String
Boolean
Boolean
SmallInt
String
Boolean
Boolean
Boolean
Boolean
Boolean
Boolean
Boolean
Boolean
Boolean
Boolean
Boolean
String
String
Size
0
8
0
12
3
10
20
0
20
20
0
0
0
1
0
0
0
0
0
0
0
0
0
0
0
2
10
Description
Unique Transaction number
MOT Number
MOT Date
MOT Certificate Number
MOT Fitter who did test
Vehicle Registration (with spaces)
Vechile Description
Vehicle Weight
Engine
Chassis
Has it got a Catalytic Converter
Is it a Diesel Vehicle
MOT Year
Class of MOT
MOT Pass/Fail Flag
MOT Pass/Fail Flag
MOT Pass/Fail Flag
MOT Pass/Fail Flag
MOT Pass/Fail Flag
MOT Pass/Fail Flag
MOT Pass/Fail Flag
MOT Pass/Fail Flag
MOT Pass/Fail Flag
MOT Pass/Fail Flag
MOT Pass/Fail Flag
MOT Type
Vehicle Registration (no spaces)
34
TEAM Systems
User Manual
TEAM Systems
User Manual
8.12 TPAY - Customer & Supplier Payments
8.13 TSAL – Temporary Sales Summary Table
Structure of file C:\Program Files\TEAM Systems\Team\Data\TPAY.DB
Structure of file C:\Program Files\TEAM Systems\Team\Data\TSAL.DB
Field Name
DummyKey
PAY_KEY
PAY_TYPE
PAY_AMT
PAY_METH
PAY_DATE
PAY_TIME
PAY_CUST
PAY_REF
PAY_BAL
PAY_NO
PAY_STAT
PAY_BankRecd
PAY_Site
PAY_Update
PAY_OldCus
Type
AutoInc
String
String
Currency
SmallInt
Date
Time
String
String
Currency
String
Date
Boolean
String
Date
String
Size
0
8
1
0
0
0
0
10
10
0
8
0
0
4
0
10
Description
Unique Payment Number
TEAM Payment Number
Payment Type
Payment Amount
Payment Method
Date of Payment
Time of Payment
Customer/Supplier Payment is From/For
Payment Reference
Payment Balance (Allocated to invoices)
Payment Number
Date of Last Allocation of Payment
*NOT USED*
Branch Payment made/collected at
Date of Last Payment Update
Customer/Supplier Code (if changed)
35
Field Name
SAL_DATE
SAL_SITE
SAL_TOTAL
SAL_TVAT
SAL_TCOST
SAL_CASH
SAL_CVAT
SAL_CCOST
SAL_ACCNT
SAL_AVAT
SAL_ACOST
SAL_INTER
SAL_IVAT
SAL_ICOST
SAL_S1
SAL_S2
SAL_S3
SAL_S4
SAL_S5
SAL_S6
SAL_S7
SAL_S8
SAL_S9
SAL_S10
SAL_S11
SAL_S12
SAL_CSH1
SAL_CSH2
SAL_CSH3
SAL_CSH4
SAL_CSH5
SAL_CSH6
SAL_CSH7
SAL_CSH8
SAL_CSH9
Type
Date
String
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Currency
Size
0
4
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
Description
Sales Summary Date
Branch Number
Total Value for Day
Total VAT for Day
Total Cost for Day
Total Cash Value for Day
Total Cash VAT for Day
Total Cash Cost for Day
Total Account Value for Day
Total Account VAT for Day
Total Account Cost for Day
Total Internal Value
Total Internal VAT value
Total Internal Cost Value
Expecting Takings for Payment Type 1
Expecting Takings for Payment Type 2
Expecting Takings for Payment Type 3
Expecting Takings for Payment Type 4
Expecting Takings for Payment Type 5
Expecting Takings for Payment Type 6
Expecting Takings for Payment Type 7
Expecting Takings for Payment Type 8
Expecting Takings for Payment Type 9
Expecting Takings for Payment Type 10
Expecting Takings for Payment Type 11
Expecting Takings for Payment Type 12
Cash Up Entered for Payment Type 1
Cash Up Entered for Payment Type 2
Cash Up Entered for Payment Type 3
Cash Up Entered for Payment Type 4
Cash Up Entered for Payment Type 5
Cash Up Entered for Payment Type 6
Cash Up Entered for Payment Type 7
Cash Up Entered for Payment Type 8
Cash Up Entered for Payment Type 9
36
TEAM Systems
SAL_CSH10
SAL_CSH11
SAL_CSH12
SAL_REP
SAL_PTCSH
SAL_VATPT
SAL_BNKDAT
SAL_BNKAMT
SAL_ADDCUS
SAL_ADDSTK
SAL_DELCUS
SAL_DELSTK
SAL_JNLSTK
SAL_INVCT
SAL_CRDCT
SAL_CRQTY
SAL_CRAMT
SAL_ARQTY
SAL_ARAMT
SAL_IRQTY
SAL_IRAMT
SAL_Update
User Manual
Currency
Currency
Currency
Currency
Currency
Currency
Date
Currency
SmallInt
SmallInt
SmallInt
SmallInt
Float
SmallInt
SmallInt
SmallInt
Float
SmallInt
Float
SmallInt
Float
Date
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
TEAM Systems
User Manual
Cash Up Entered for Payment Type 10
Cash Up Entered for Payment Type 11
Cash Up Entered for Payment Type 12
Account Payments
Petty Cash
*NOT USED*
Banked Date – Same as SAL_DATE
Total Banked Value
Number of Customers Added for Day
Number of Stock Lines Added for Day
Number of Customers Deleted for Day
Number of Stock Items Deleted for Day
Number of Stock items bought
Number of Invoices/Credits for Day
Number of Credits for Day
Number of Cash Credits for Day
Net Amount of Cash Credit Invoices
Number of Account Credits for Day
Net Amount of Account Credits for Day
Number of Internal Credits for Day
Net Amount of Internal Credits for Day
Last Date Sales Summary was Updated
37
38
TEAM Systems
User Manual
TEAM Systems
9
User Manual
CONTACT / SUPPORT
We are open standard office hours Monday to Friday.
TELEPHONE / FAX
Sales / Support Switchboard
Emergency Out of Hours Mobile
General Fax
- 0044 (0) 1822 855 466
- 0044 (0) 7774 418 706
- 0044 (0) 1822 855 141
POST
Please address any correspondence to Sales, Technical Support, Consumables or
General Enquires at the address below:
Yelverton Business Park
Yelverton
Devon
PL4 8HS
UK
EMAIL / WWW
39
New sales or upgrade enquires
Technical Support
Consumables and Peripherals
General Enquires
-
[email protected]
[email protected]
[email protected]
[email protected]
Website
- www.teamsystems.co.uk
40