Home
Export Data - TEAM Systems Ltd
Contents
1. Field Name Type Size Description PTD_Code String 8 TEAM Purchase Invoice Number PTD Line Smaillint 0 Purchase Invoice Line Number PTD StockCode String 16 Stock Code for this line PTD Desc String 30 Stock Description for this line PTD Reason String 30 Reason text for this line PTD Fitter String 3 Fitter who created Invoice PTD Quantity Float 0 Quantity of Stock items on this line PTD Level Float 0 Stock Level including Quantity PTD UnitCost Currency 0 Unit Cost of this stock item on this line PTD NetValue Currency 0 Net Value of this line PTD Category String 5 Category of Stock item on this line PTD NomCode String 8 USED PTD_TaxRate Float 0 Tax Rate of this invoice line PTD_TaxCode String 1 Tax Code of this invoice line PTD_TaxValue Currency 0 Tax Value of this invoice line PTD_GrossValue Currency 0 Gross Value of this invoice line PTD Date Date 0 Date this invoice line was created PTD Time Time 0 Time this invoice line was created PTD NomTrans_ Boolean 0 NOT USED Field Name Type Size Description PTH_Code String 8 TEAM Purchase Invoice Number PTH_SITE String 4 Branch where created PTH SUPP String 6 Supplier Code PTH Fitter String 3 Fitter Code PTH INVNO String 10 Invoice Number PTH DELNO String 10 Delivery Number PTH PURNO String 10 Purchase PTH Confirmed Boolean 0 Are Prices confirmed PTH ForResale Boolean 0 Are These
2. 8 02 STRANH Invoice Header File 8 03 STRAND Invoice Detail Lines File 8 04 TSUP Supplier Details File 8 05 PTRANH Purchase Invoice Header File 8 06 PTRAND Purchase Invoice Detail Lines File 8 07 Stock File enn 8 08 TCAT Stock Categories File 8 09 TSTKOB Stock Levels at Other Branches 8 10 TFIT Fitter TEAM Systems User Manual 9 8 11 TMOT MOT 8 12 TPAY Customer amp Supplier Payments 8 13 TSAL Temporary Sales Summary Table CONTACT A SUPPORT idee curae c ewere debe tait vide TEAM Systems User Manual 1 WELCOME This booklet is designed to be a starting guide and reference to using the Team for Windows Data Export Program 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 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 Self help for tyres TEAM Systems User Manual 2
3. STD Category String 5 Category Stock Code in STD Quantity Float 0 Quantity STD UnitPrice Currency 0 Unit Price STD_Discount Float 0 Line Discount STD_Fitter String 3 Fitter for this line STD Level Float 0 Stock Level including STD Quantity STD NetValue Currency 0 Net Value of this invoice line STD_TaxValue Currency 0 Tax Value of this invoice line STD_TaxCode String 1 Tax Code for this invoice line 0 1 2 STD_TaxRate Float 0 Tax Rate for this invoice line STD_Cost Currency 0 Cost of this invoice line STD_GrossValue Currency 0 Gross Value of this invoice line STD_RRP Currency 0 Recommend Retail Price STD_GrossProfit Currency 0 Gross Profit of this line STD_Progress Smallint 0 Invoice Progress O invoice 1 held 2 quote STD_SerialNumber String 20 Stock item Serial Number STD_Type String 1 Line Type S Stock K Kit Head L Kit line R Rem STD NomTrans Boolean 0 NOT USED STD Date Date 0 Date Invoice line created STD Time Time 0 Time Invoice line created STD Account String 10 Customer Account of Invoice 27 28 TEAM Systems User Manual 8 05 PTRANH Purchase Invoice Header File Structure of file C Program Files TEAM Systems Team Data PTRANH DB TEAM Systems User Manual 8 06 PTRAND Purchase Invoice Detail Lines File Structure of file C Program Files TEAM Systems Team Data PTRAND DB
4. Technical Support Consumables and Peripherals General Enquires Website sales teamsystems co uk support teamsystems co uk shop teamsystems co uk info teamsystems co uk www teamsystems co uk 40
5. 18 TEAM Systems User Manual 5 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 Create a report using the wizard 2 Make sure the Report Options have the Default Export Path and File Set Correctly 3 Save the Report with a meaningful file name in an easily accessible place 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 TYREREP1L REP EXPORT 19 TEAM Systems User Manual 6 SQL 6 01 Using SQL Rather Than The Wizard The Export Report Generator creates an SQL Struc
6. Goods for Resale PTH Type String 1 Invoice Type R Resale N NotforResale O Outside VAT DATE Date 0 Date of Purchase Invoice PTH Time Time 0 Time of Purchase Invoice PTH STAT Date 0 Invoice Last Paid Allocation Date PTH UPD Date 0 Last Date this was Edited PTH NetValue Currency 0 Invoice Net Value PTH NetValO Currency 0 Net Value at VAT Rate 0 PTH_NetVal1 Currency 0 Net Value at VAT Rate 1 PTH_NetVal2 Currency 0 Net Value at VAT Rate 2 PTH_TaxValue Currency 0 Invoice Total Tax Value PTH TaxValO Currency 0 Tax Value at VAT Rate 0 PTH_TaxVal1 Currency 0 Tax Value at VAT Rate 1 PTH TaxVal2 Currency 0 Tax Value at VAT Rate 2 PTH GrossValue Currency 0 Invoice Gross Value PTH_Balance Currency 0 Balance Outstanding on Invoice PTH TaxFree Boolean 0 Is this a Tax Free Invoice PTH Progress Smalllnt O Invoice Progress O invoice 1 held PTH BankRecd Boolean 0 Included on Bank Reconciliation PTH_TaxRecd Boolean 0 NOT USED PTH EditedByHO Boolean 0 Was This Edited by Head Office PTH_OldSup String 10 Supplier Code if changed 29 30 TEAM Systems User Manual 8 07 TSTK Stock File Structure of file C Program Files TEAM Systems Team Data TSTK DB TEAM Systems User Manual STK_MURN String 20 Stock Manufacturers Reference Number STK_OnOrder Float 0 Stock Quanity on Order 8 08 TCAT Stock Categories File Structure of file C
7. User Manual 8 09 TSTKOB Stock Levels at Other Branches Structure of file C Program Files TEAM Systems Team Data TSTKOB DB TEAM Systems User Manual 8 11 TMOT MOT Details Structure of file C Program Files TEAM Systems Team Data TMot DB Field Name Type Size Description SOB StockCode String 16 Stock Code SOB Branch String 4 Branch Code SOB Level Float 0 Level at Branch SOB_Updated Date 0 Last Date this Stock Code Updated SOB QtyH Float 0 Quantity on Hold at Branch SOB Cost Currency 0 Cost of Stock item at Branch SOB_ROMAX Integer 0 Reorder Maximum up tp level at branch SOB_REORD Integer 0 Reorder level at branch SOB SellA Currency 0 Branch Sell A Price SOB LRef String 10 Last Reference at Branch SOB String 8 Last Invoice Number at Branch SOB LOost Currency 0 Last Cost at Branch 8 10 TFIT Fitter Details Structure of file C Program Files TEAM Systems Team Data TfitB DB Field Name Type Size Description FIT INIT String 3 Fitter Code FIT NAME String 30 Fitter Name Field Name Type Size Description DummyKey Autolnc 0 Unique Transaction number MOT KEY String 8 MOT Number MOT DATE Date 0 MOT Date MOT CERT String 12 MOT Certificate Number MOT TESTER String 3 MOT Fitter who did test MOT REG String 10 Vehicle Reg
8. 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 22 TEAM Systems User Manual 8 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 TEAM Systems User Manual CUS_LPNUM String 8 Last Payment number for this customer CUS_TERM Smalllnt 0 Payment Terms no of days CUS_CRED Currency 0 Customer Credit Limit CUS DISC Float 0 Customer Discount CUS PCOD Smalllnt 0 NOT USED CUS SRTKEY String 20 Sort Key CUS STOP Boolean 0 Customer on Stop CUS EXVAT Boolean 0 Is this Customer an Ex VAT customer CUS INTER Boolean 0 Is this Customer Internal CUS DNOTE Boolean 0 Print Delivery Note CUS PRICE String 1 Default Price list A B C D CUS OTHEC Boolean 0 Is this Customer Outside EC CUS NOINV Boolean 0 Don t print invoices for this customer CUS NOTES Memo 1 General Customer Notes CUS NOSTAT Boolean 0 Don t include customer in Statement Run CUS Update Date 0 Last Date Customer Details Updated CUS DelAdd1 String 25 Customer Delivery Address CUS DelAdd2 String 25 Customer Delivery Addre
9. INSTALLATION Copy all the Files from the CD into the TEAM Systems Program Directory This directory is usually either For a Single Version of Team C Program Files Team Systems Team For a Network Version of 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 TEAM Systems User Manual 3 THE WIZARD 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 A computer file that contains data i e TCUS DB Database A collection of tables or files Record A single transaction of data e g one customer record you could think of Records as the Rows in a table Fields Boxes of specific information per record e g customer name you could think of Fields as the Columns in the table Relation Tables can be linked together by similar fields in both tables The Invoice Tables will have a Customer Accou
10. Ltd 2004 A LosdRepot Sie Report Opere Rapai Notes Select Table Fsokds Please cobect the Tabie from the psit down list and thes tick the boxes al the felda yos require for your report 1 Chckiag Next will tet you ter the dete you D eiui D have selected DG 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 R TEAM Expert Report Generator t Team Systems Lid 2004 e Nx Report Save Report Notes Eont To CSV Select Table and Fi TOE pe E v CUS HD Please select the Table fros the psi down AN CASH list and thes tick the boxes al the fields yos CUS ADO requie for your report v CLS ano Select Table v CUS Chckisg Next will tet you Ster the dete you Y CLR 2004 have selected v CUI POST ii d TUS 3 CLS CONT 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 link
11. NKAMT Currency 0 Total Banked Value SAL ADDCUS Smalllnt O Number of Customers Added for Day SAL ADDSIK Smalllnt 0 Number of Stock Lines Added for Day SAL DELCUS Smalllnt 0 Number of Customers Deleted for Day SAL DELSTK Smalllnt 0 Number of Stock Items Deleted for Day SAL JNLSTK Float 0 Number of Stock items bought SAL INVCT Smalllnt O Number of Invoices Credits for Day SAL CRDCT Smalllt 0 Number of Credits for Day SAL_CRQTY Smallint 0 Number of Cash Credits for Day SAL CRAMT Float 0 Net Amount of Cash Credit Invoices SAL_ARQTY Smalllnt 0 Number of Account Credits for Day SAL_ARAMT Float 0 Net Amount of Account Credits for Day SAL_IRQTY Smalllnt O Number of Internal Credits for Day SAL_IRAMT Float 0 Net Amount of Internal Credits for Day SAL_Update Date 0 Last Date Sales Summary was Updated 37 TEAM Systems User Manual 38 TEAM Systems User Manual 39 TEAM Systems User Manual 9 CONTACT SUPPORT We are open standard office hours Monday to Friday TELEPHONE FAX Sales Support Switchboard Emergency Out of Hours Mobile General Fax POST 0044 0 1822 855 466 0044 0 7774 418 706 0044 0 1822 855 141 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 New sales or upgrade enquires
12. Program Files TEAM Systems Team Data TCat DB Field Name Type Size Description CAT_KEY String 5 Stock Category Code CAT_DESC String 15 Category Description CAT_NOMCODE String 5 NOT USED CAT_MMGN Float 0 Category Minimum Margin CAT_PRIO Boolean 0 Category Priority CAT_COST Boolean 0 Ignore Costs CAT NODEL Boolean 0 Don t Track Levels of Stock Items CAT QTY Boolean 0 NOT USED CAT_VRATE String 1 Default Sales VAT Rate CAT_PRATE String 1 Default Purchase VAT Rate CAT_ADD1 String 16 Additional Invoice Line 1 CAT_ADD2 String 16 Additional Invoice Line 2 CAT_ADD3 String 16 Additional Invoice Line 3 CAT QTY1 Boolean 0 Multiply by stock items quantity for line1 CAT_QTY2 Boolean 0 Multiply by stock items quantity for line2 CAT_QTY3 Boolean 0 Multiply by stock items quantity for line3 CAT_TYPE String 1 Category Type Normal Tyres CAT_BulkCode String 16 Bulk Buy Stock Code CAT_Update Date 0 Last Date Category Updated CAT_PNomCode String 5 NOT USED CAT_Add4 String 16 Additional Invoice Line 4 CAT_Add5 String 16 Additional Invoice Line 5 CAT_Qty4 Boolean 0 Multiply by stock items quantity for line4 CAT_Qty5 Boolean 0 Multiply by stock items quantity for line5 Field Name Type Size Description STK_KEY String 16 Sto
13. TEAM FOR WINDOWS EXPORT REPORT GENERATOR USER MANUAL Manual Version 1 0 amp Yelverton Business lverton Devon PL20 7PY Web www systems co uk Email Eng ANT M Qu Tel 01822 855466 TEAM Systems User Manual CONTENTS CONTENITS ortae tes texte vt Esse teta 1 WELCOME 2 INSTALLATION iicet eee 3 THEMILTZARD Zz s tebe tte te e aed ve ee etel General 1 3 01 3 02 How To Creating An Export File 3 03 Find the TEAM for Windows Data 3 04 Select The Table And Fields You Want 3 05 Filter Tidy Up The Data 3 06 3 07 Exporting The Information 3 08 Saving Your 3 09 Loading a 4 OTHER SCREENS 4 01 Options Parameters Screen 4 02 Report 5 COMMAND LINE 5 01 Running The Export Automatically Oe SO des Nae Les Loses st EE e EE AUS 6 01 Using SQL Rather Than The Wizard 7 FILE STRUCTURE 7 01 Tables Fields Records amp Relationships 8 FILE STRUCTURES 8 01 TCUS Customer
14. User Manual 3 07 Exporting The Information When you are happy with the information you have selected you can press the Export to CSV button 7 TEAM Luparl Report Generator Team Systema 116 2004 QM x New Heto Load Repot Sere 4 Oghons Repos Noies Export To CSV Ext Savon o TEAM Systema Finished pene You con now either Tex Export these update Results Save the 2 En Report tor Loter or 8 508632 nt Click Back nad amend report Fie nome ar MRC Onder Rexuts Ep 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 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 TEAM Systems User Manual 3 08 Saving Your Report 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 f
15. ata has simplified into this easy to use word However a basic uaderstondiag of computer databases will be adventogeous to use this program Please Click the Next Button to start the Wizard IM TEAM Systems User Manual 3 03 Find the TEAM for Windows Data 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 TEAN Export Hepert Generator cen Systens Lic 2004 Report Options Heb a New Repos Load Repos Save Repos Options Repos Notes Eu Team Data Locakor ec Please select the directory where the TEAM amp Program Fiss data you want ta report on is located E lem Spiers The default path for TEAM data is Taam Select Data Directory C Program Files Team Systems TeamData t Copy of DN EF If wow have multiple machwes the delault oe path will be t Pats O TATeam Data ff T arel Clicking Next will let you select the required CAPR GRAM FRESNTEAM SYSTEMSST EAM DATA ODENE T dutu When the data directory has been highlighted click the Next Button to continue TEAM Systems User Manual 3 04 Select The Table And Fields You Want Select the Table you require from the drop down list TEAM Expert Report Generator t Team Systems
16. ave Ragot Options Barat Notes Erpat To CSV incide Heada ficw on CSV Expat Export Optians Parameters 5 Path and Nane C Expat CSV 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 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 TEAM Export Report Generator Systems Lid 2004 File Report Options Heb A X Brag Loadfiapos Save Repot Options Barat Notes Erpat To CSV Report Notes This box is supplied so you enter decnpbon 0f the report you have cre ned this will be saved with the report This re por wee first tasi of fie Rapor Exper thingy program Clone Papat Netes You 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
17. ck Code STK_DES1 String 30 Stock Primary Description STK_DES2 String 30 Stock Secondary Description STK_SIZE String 4 Tyre Width STK_DIAMETER String 4 Tyre Diameter STK_SPEED String 2 Tyre Speed Rating STK_PROFILE String 3 Tyre Profile STK_MAST String 16 Stock Bar Code STK CAT String 5 Stock Category Code STK COST Currency 0 Stock Average Cost STK LCOST Currency 0 Last Cost Price Paid STK_SELLA Currency 0 Selling Price Price List A STK_SELLB Currency 0 Selling Price Price List B STK_SELLC Currency 0 Selling Price Price List C STK_SELLD Currency 0 Selling Price Price List D STK_DISC String 4 Discount Code STK_VRATE String 1 VAT Rate for Sales STK_PRATE String 1 VAT Rate for Purchases STK_QTY Float 0 Stock Quantity STK_QTY2 Float 0 NOT USED STK_QTYH Float 0 Stock Quantity on Hold STK_LREF String 10 Last Invoice Reference for this stockcode STK_MANF String 6 Manufacturer STK_SUPP String 6 Default Supplier STK_REORD Smalllint 0 Stock Reorder Level STK_ROMAX Smalllnt O Stock Reorder up to max level STK TYPE Boolean 0 Stock Item Active STK LINV String 8 Last Invoice for this stockcode STK SOLD Float 0 Last Sold Price STK VALUE Currency 0 Stock Value STK LOC String 10 Stock Location STK LUPD Date 0 Last Date Stock Code Updated STK NOTES Memo 1 Stock Notes STK Analysis String 10 Stock Analysis search field STK Search String 25 Stock Search search field 31 32 TEAM Systems
18. es TEAM Systems Team Data STRAND DB TEAM Systems User Manual 8 04 TSUP Supplier Details File Structure of file C Program Files TEAM Systems Team Data TSUP DB Field Name Type Size Description SUP_KEY String 6 Supplier Account Code SUP_NAME String 30 Supplier Name SUP SRTKEY String 20 Supplier Sort Key Search Field SUP ADD1 String 30 Supplier Address Line 1 SUP ADD2 String 30 Supplier Address Line 2 SUP ADDS String 30 Supplier Address Line 3 SUP ADD4 String 30 Supplier Address Line 4 SUP POST String 10 Supplier Address PostCode SUP TEL1 String 15 Supplier Telephone Number SUP TEL2 String 15 2 Supplier Phone Number SUP FAX String 15 Supplier Fax Number SUP CON1 String 20 Supplier Contact SUP EXVAT Boolean 0 Is This Supplier EXVAT SUP OTHEC Boolean 0 Is This Supplier Outside the EC SUP INTER Boolean 0 Is This Supplier an Internal Account SUP EMAIL String 40 Suppliers Email Address SUP NOTES Memo 1 Supplier Notes SUP Update Date 0 Last Date Supplier Details Updated Field Name Type Size Description STD Code String 8 TEAM Sales Invoice Number STD Line Smalllnt O Invoice Line Number STD_NomCode String 8 NOT USED STD_StockCode String 16 Stock Code of Item STD Desc1 String 30 Description STD Desc2 String 30 2 Description
19. ing in another program such as Microsoft Access 11 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 xpor Rep ri Generator ic New Repat Loni Berc Save Repeat Repos Expat To CSV ta Select Op ra Fekitofile Compstron Velum M cormpere CLP CASH F ob n Filter Selected Onin Thes semea nilows you to filter the date yov have selected r r Select the Geld yos wish to filtar Selact fe comparison for he field Select the valve rir ar to campare to the Sold d A valse af DATE 8 is todays date Clicking Next will show you your resetts ms 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 Select Required Filter Options Field to Filter Comparison Value to compare Logical ses gt gt s s cus b s AND Cus NO lt v E X 12 TEAM Systems User Manual This e
20. istration with spaces MOT CAR String 20 Vechile Description MOT WGHT Smalllnt 0 Vehicle Weight MOT ENGINE String 20 Engine MOT CHASS String 20 Chassis MOT GOTCAT Boolean 0 Has it got a Catalytic Converter MOT DIESEL Boolean 0 Is it a Diesel Vehicle MOT YEAR Smallint 0 MOT Year MOT CLASS String 1 Class of MOT MOT BAD Boolean 0 MOT Pass Fail Flag MOT_BLGTS Boolean 0 MOT Pass Fail Flag MOT_BSTEER Boolean 0 MOT Pass Fail Flag MOT_BBRAKE Boolean 0 MOT Pass Fail Flag MOT_BTYRE Boolean 0 MOT Pass Fail Flag MOT_BPLATE Boolean 0 MOT Pass Fail Flag MOT_BVIEWS Boolean 0 MOT Pass Fail Flag MOT_BEMPET Boolean 0 MOT Pass Fail Flag MOT_BEMCAT Boolean 0 MOT Pass Fail Flag MOT_BEMDIE Boolean 0 MOT Pass Fail Flag MOT_BOTHER Boolean 0 MOT Pass Fail Flag MOT_TYPE String 2 MOT Type MOT_RegNoSpaces String 10 Vehicle Registration no spaces 33 34 TEAM Systems User Manual 8 12 TPAY Customer amp Supplier Payments Structure of file C Program Files TEAM Systems Team Data TPAY DB TEAM Systems User Manual 8 13 TSAL Temporary Sales Summary Table Structure of file C Program Files TEAM Systems Team Data TSAL DB Field Name Type Description DummyKey Autolnc Unique Payment Number PAY KEY String TEAM Payment Number PAY TYPE String Payment Type PAY AMT Currency Payment Am
21. ng Takings for Payment Type 4 SAL_S5 Currency 0 Expecting Takings for Payment Type 5 SAL_S6 Currency 0 Expecting Takings for Payment Type 6 SAL_S7 Currency 0 Expecting Takings for Payment Type 7 SAL_S8 Currency 0 Expecting Takings for Payment Type 8 SAL_S9 Currency 0 Expecting Takings for Payment Type 9 SAL_S10 Currency 0 Expecting Takings for Payment Type 10 SAL_S11 Currency 0 Expecting Takings for Payment Type 11 SAL S12 Currency 0 Expecting Takings for Payment Type 12 SAL_CSH1 Currency 0 Cash Up Entered for Payment Type 1 SAL_CSH2 Currency 0 Cash Up Entered for Payment Type 2 SAL_CSH3 Currency 0 Cash Up Entered for Payment Type 3 SAL_CSH4 Currency 0 Cash Up Entered for Payment Type 4 SAL_CSH5 Currency 0 Cash Up Entered for Payment Type 5 SAL_CSH6 Currency 0 Cash Up Entered for Payment Type 6 SAL_CSH7 Currency 0 Cash Up Entered for Payment Type 7 SAL_CSH8 Currency 0 Cash Up Entered for Payment Type 8 SAL_CSH9 Currency 0 Cash Up Entered for Payment Type 9 35 36 TEAM Systems User Manual SAL_CSH10 Currency 0 Cash Up Entered for Payment Type 10 SAL_CSH11 Currency 0 Cash Up Entered for Payment Type 11 SAL_CSH12 Currency 0 Cash Up Entered for Payment Type 12 SAL_REP Currency 0 Account Payments SAL_PTCSH Currency 0 Petty Cash SAL_VATPT Currency 0 NOT USED SAL_BNKDAT Date 0 Banked Date Same as SAL DATE SAL B
22. nt 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 TEAM Systems User Manual 3 02 How To Creating An Export File Once the program has loaded click Next or New Report to start amp EAM Export Report Generator eem Systems Ltd 2004 Non Load upet Sie Regen Upon Notes Welcome to the Export Report Generator for TEAM for Windows This program is designed to produce aa Export File of TEAM for windows data to a CSV Comma Seperated Vanable tile The CSV tle produced con be loaded mto many other datadase or sprendsheert programs such as Microsoft Excel Microsof Access Works or Lotus etc or even full accounting software such as Sage The pracesees involved ip creating CSV file from TEAM d
23. orget 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 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 16 TEAM Systems User Manual 4 OTHER SCREENS 4 01 Options Parameters Screen The Parameter screen allows you to setup some default information specific to each report that you are creating TEAN Export Report Generator Team Systems Lic 2004 Fle Report Optens Heb a RJJ x Brog LoadRepat S
24. ount PAY METH Smallint Payment Method PAY DATE Date Date of Payment PAY TIME Time Time of Payment PAY CUST String 0 Customer Supplier Payment is From For PAY REF String 0 Payment Reference PAY BAL Currency Payment Balance Allocated to invoices PAY NO String Payment Number PAY STAT Date Date of Last Allocation of Payment PAY BankRecd Boolean NOT USED PAY Site String Branch Payment made collected at PAY Update Date Date of Last Payment Update PAY OldCus String 0 Customer Supplier Code if changed Field Name Type Description SAL DATE Date 0 Sales Summary Date SAL SITE String 4 Branch Number SAL TOTAL Currency 0 Total Value for Day SAL_TVAT Currency 0 Total VAT for Day SAL_TCOST Currency 0 Total Cost for Day SAL_CASH Currency 0 Total Cash Value for Day SAL_CVAT Currency 0 Total Cash VAT for Day SAL CCOST Currency 0 Total Cash Cost for Day SAL_ACCNT Currency 0 Total Account Value for Day SAL_AVAT Currency 0 Total Account VAT for Day SAL_ACOST Currency 0 Total Account Cost for Day SAL_INTER Currency 0 Total Internal Value SAL_IVAT Currency 0 Total Internal VAT value SAL_ICOST Currency 0 Total Internal Cost Value SAL_S1 Currency 0 Expecting Takings for Payment Type 1 SAL_S2 Currency 0 Expecting Takings for Payment Type 2 SAL_S3 Currency 0 Expecting Takings for Payment Type 3 SAL_S4 Currency 0 Expecti
25. ss CUS DelAdd3 String 25 Customer Delivery Address CUS DelAdd4 String 25 Customer Delivery Address CUS DelAdd5 String 25 Customer Delivery Address CUS DelPost String 10 Customer Delivery Address CUS DelCountry String 15 Customer Delivery Address CUS Add5 String 25 Customer Address Line 5 CUS Country String 15 Customer Country CUS Insurance Boolean 0 Customer is Insurance Company CUS Type Integer 0 Customer Type Code Field Name Type Size Description CUS NO String 10 Customer Code CUS CASH Boolean 0 Is this a Cash Customer no Account CUS_NAM String 30 Customer Name CUS_ADD1 String 25 Customer Address Line 1 CUS_ADD2 String 25 Customer Address Line 2 CUS_ADD3 String 25 Customer Address Line 3 CUS_ADD4 String 25 Customer Address Line 4 CUS_POST String 10 Customer Address PostCode CUS_TEL String 20 Customer Telephone Number CUS_FAX String 20 Customer Fax Number CUS_EMAIL String 40 Customer Email Address CUS_VATNO String 20 Customer VAT Registration Number CUS_SRCE String 4 Customer Source group of customers CUS_CONT String 30 Customer Contact CUS_MONO Currency 0 Customer Balance Current Month NOTE This Value is only updated after an Aged Debtors Report has been run CUS MON 1 Currency 0 Customer Balance Current Month 1 NOTE This Value is only updated after an Aged Debtors Report has been run CUS_MON2 Currency 0 Customer Balance Current Month 2 NOTE This Val
26. t 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 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 STH ACCOUNT field and this one links to the Customer account details TCUS DB gt 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
27. tured 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 20 TEAM Systems User Manual 7 FILE STRUCTURE INFORMATION 7 01 Tables Fields Records amp 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 C Program Files Team Systems Team Data For a Network Version of Team 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 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 J 0001 Joe Bloggs and his address details will be 1 record or transaction within the TCUS DB Stockl tem MI 17565TR13 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 differen
28. ue is only updated after an Aged Debtors Report has been run CUS MONS Currency 0 Customer Balance 3 Months and over NOTE This Value is only updated after an Aged Debtors Report has been run CUS BAL Currency 0 Current Outstanding Balance CUS LIDAT Date 0 Date of Last Invoice for this customer CUS LINUM String 8 Last Invoice number for this customer 23 24 TEAM Systems User Manual 8 02 STRANH Invoice Header File Structure of file C Program Files TEAM Systems Team Data STRANH DB TEAM Systems User Manual STH_Notes Memo 1 General Invoice Notes STH_Balance Currency 0 Balance Outstanding on Invoice STH_Invalid Boolean 0 Is There an Error with this Invoice STH_AdjCreateDate Date 0 Date Adjustment Created STH_RegDisplay String 1 Vehicle Registration includes space STH NomTrans Boolean 0 NOT USED STH Update Date 0 Last Time Invoice was updated STH_OldCus String 1 Customer Code if Changed Field Name Type Size Description STH_Code String 8 TEAM Sales Invoice Number STH_Type String 1 Invoice Type l invoice A Adjustment STH_CodeNoSpaces String 8 TEAM Sales Invoice Number STH_Reference String 8 Invoice Reference STH_Account String 10 Invoice Customer Account No STH_ Site String 4 Branch where Invoice was created STH_Date Date 0 In
29. voice Date STH_Time Time 0 Invoice Time STH_StatDate Date 0 Statement Date STH Heri String 20 Invoice Reference STH_Ref2 String 20 Invoice 2 Reference STH Source String 4 Customer Source STH String 10 Vehicle Registration No Spaces STH Car String 20 Vehicle Type STH AnnualMiles String 10 Annual Miles STH Mileage String 10 Current Mileage STH MOTDue Date 0 Next MOT Due Date STH ServiceDue Date 0 Next Service Due Date STH MainFitter String 3 Fitter who did the work STH NetValue Currency 0 Net Value of Invoice STH NetValO Currency 0 Net Value of Invoice at VAT Rate 0 STH_NetVal1 Currency 0 Net Value of Invoice at VAT Rate 1 STH_NetVal2 Currency 0 Net Value of Invoice at VAT Rate 2 STH_TaxValue Currency 0 Total Tax Value of Invoice STH TaxValO Currency 0 Tax Value of invoice at VAT Rate 0 STH TaxVall Currency 0 Tax Value of invoice at VAT Rate 1 STH_TaxVal2 Currency 0 Tax Value of invoice at VAT Rate 2 STH_Cost Currency 0 Total Cost of Items on Invoice STH_GrossValue Currency 0 Gross Value of Invoice STH_TaxFree Boolean 0 Was this Invoice Tax Free STH_BankRecd Boolean 0 Has this invoice been Reconciled STH_TaxRecd Boolean 0 NOT USED STH_Printed Boolean 0 Has this Invoice been Printed STH_Progress Smallint 0 Invoice Progress O invoice 1 held 2 quote 25 26 TEAM Systems User Manual 8 03 STRAND Invoice Detail Lines File Structure of file C Program Fil
30. xample show that we want all records where CUS_CASH False all the account only customers and also these records must be where CUS NO gt A customer account code is bigger or equal to the letter 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 TEAN Report Generator c cem Syslems Lic 2004 Fle Report Options 2 M x Load Repos Save Repos Options Notes Expat To COV Ex Cus ADDI Finished ROAD You cun now either ROAD Export these DAD 4 Results Save the Report ior Loter or Click Back aad amend this report B lt Order Recuts Bp 148 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 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 14 TEAM Systems
Download Pdf Manuals
Related Search
Related Contents
User Manual for www.controlami.com login Instructions S8610U Módulo Universal de Piloto Intermitente TranSync Compliance Monitoring User Manual 2 - Kyosho D059-RGXZ Gerador Síncrono 調光コントローラ取扱説明書 M - Distrimed Elo Touch Solution 1529L IntelliTouch 15" LCD Desktop Touchmonitor Copyright © All rights reserved.