TSM API Version 2.11

(1)

TSM API Version 2.11

The TSM API provides a powerful tool to allow you to manipulate TSM data without directly accessing the TSM data files.

Your use of the TSM API is at your own risk and in using the TSM API, you agree to release TSM from any and all liability or claims whatsoever arising out of or related to the use of the TSM API, regardless of whether such loss is caused by the negligence of TSM or otherwise and regardless of whether such liability arises in tort, contract, strict liability, or otherwise, to the fullest extent allowed by law

You further hereby agree to indemnify and hold harmless TSM from any loss, liability, damage, or costs, including court costs and fees that TSM may incur due to your use of the TSM API, whether caused by the negligence of TSM or otherwise, to the fullest extent allowed by law.

Now that the legal stuff is out of the way, how can we access the TSM API?

The TSM API calls can be accessed via either the TSM.DLL or TSM.EXE. The TSM.DLL is a multi-threaded COM server DLL and TSM.EXE is an executable COM server.

Within TSM.DLL and TSM.EXE is a Global class that contains all the TSM functions.

These functions take care of all the low level database access, record locking and many of the business rules for data validation.

Whenever a TSM API function returns false (failure), refer to objTSM.LastError for a description of the problem.

Notes: You must always call the TSM_Init function at the beginning of your application to initialise the environment.

Initialising the TSM API object

(2)

To instantiate TSM.DLL object (VB code example):

' Initialise TSM.DLL object

objTSM = CreateObject("TSM61.Global")

To instantiate TSM.EXE object (VB code example):

' Initialise TSM.DLL object

PROCEDURE tsm_init

Purpose Initialises the environment for using the TSM API.

TSM_Init( cLogName, cPassword, [cPath] )

Parameters cLogName, Employee ID to log into TSM as cPassword. Password to login with

cPath Path to the location of the TSM tables. If not passed, tables will be opened from the directory that the TSM.DLL file is located in.

Return Values Returns true if successful Returns false if unsuccessful

Comments: You must run this function prior to using any of the TSM API functions. The login name and password must match a valid TSM Employee ID and password with suitable security to perform the functions being called.

(3)

Procedure tsm_addjob

Purpose: Add a new job card into servcard.dbf. Creates a blank job card with minimum field requirements and with a uniquely assigned job card # (servcardid)

TSM_AddJob ([cAlias],[lNoJobno],[nJobno],[lSkip],[cType])

Parameters cAlias, Table alias to add job to If different from Servcard. Servcard will be automatically opened in this alias if it is not currently open.

lNoJobno, Returns Logical success or failure if

lNoJobno = True otherwise returns the job number (default).

nJobno, Job number to assign if not using Auto Job numbering

lSkip Not Used

cType Type to assign job. ‘S’ = Job Card (default),

‘C’ = Contract Job

Return Values: If lNoJobno = False (Default)

Returns new Job Card number if successful Returns zero if unsuccessful

If lNoJobno = True

Returns True if job successfully added Returns False if unsuccessful

PROCEDURE tsm_adetl_timecalc

(4)

Purpose Recalculates time values on Assignments after entering start / finish time

TSM_Adetl_TimeCalc( ctype, cStart, cFinish, dDate )

Parameters ctype, “START” or FINISH”

cStart, Start time cFinish, Finish time dDate Assignment Date

Return Values Returns True if successful or False if unsuccessful

Comments: Call this function after assigning a start time or a finish time to an assignment. This function automatically sets the assignment.duration and assignment.jobdate value.

PROCEDURE tsm_adjuststock

Purpose Adjust the quantity of stock at a location.

TSM_AdjustStock(cProductID, [nQty], [cLocation], [cTranType], [cReference], [nJobNo], [cComment], [dTrandate], [cEmployeeid], [nSevdetlSeq], [lCommit], [nCost], [nQty1], [cInvNo], [cSupplier],[cSerialnum],[nSerialSeq],[nOrdetSeq],[nOnha nd],[cUOM],[cSerialNotes]

Parameters cProductID Product ID to Adjust

[nQty] Quantity to adjust by. Default Zero.

[cLocation] Inventory location to adjust [cTranType] Transaction Type:

Q: Onhand Updated I: Issue

(5)

R: Receipt T: Transfer D: Delete U: Unknown [cReference] Update reference

[nJobNo] Job Card number to link record to [cComment] Comments

[dTrandate] Transaction Date (Default to today) [cEmployeeid] EmployeeID doing the transfer [nSevdetlSeq] Parts used sequence #

[lCommit] Commit changes with a TableUpdate [nCost] Cost Price (Default Average Cost) [nQty1] Old Quantity

[cInvNo] Invoice Reference # [cSupplier] Supplier Reference

[cSerialnum] Serial number to receive or issue if serialised item

[nSerialSeq] Serial record sequence number key (For issue)

[nOrdetSeq] Order item sequence number to link adjustment to

[nOnhand] Onhand level to use as starting point.

This overrides current stock onhand level.

[cUOM] Unit of measure. Will multiply the quantity received to determine unit quantity adjustment.

[cSerialNotes] Notes to add to serialised item.

Return Value None

(6)

Comments: Use this function to manually adjust stock levels.

PROCEDURE tsm_append

Purpose Appends records to TSM tables, setting initial default values.

TSM_Append( [cTableAlias], [nJobNo],[nDataSession

],[cEmployeeID],[cJobType],[lFromAddJob],[lIgnoreError]

,[cDefLoc])

Parameters cTableAlias, Name of table to append record to.

nJobNo. Job Card number to link record to nDataSession Datasession to append into if not the

default data session

cEmployeeID Employee ID who appended the record

cJobType Job Type. ‘S’ for job cards (default),

‘C’ for Contract Job.

lFromAddJob Internal

lIgnoreError Ignore Error messages cDefLoc Default Inventory Location

Return Value True if successful

Returns False if unsuccessful

Comments: Appends records to currently selected table if no table alias passed. Optionally, link the record to a selected job card number (sets other default values).

Should not be used for append records into SERVCARD.

Use TSM_AddJob.

(7)

PROCEDURE tsm_changepassword

Purpose Change the password for a TSM user

tsm_changepassword (cUsername, cPassword, cNewPassword)

Parameters cUsername Employeeid of the user for whom to change the password

cPassword Logged in User’s password cNewPassword New password.

Return Values Returns True on success, False on failure

Comments: Sets lasterror on failure

PROCEDURE tsm_close

Purpose Close an open table

TSM_Close( cTableAlias,[nDataSession])

Parameters cTableAlias, Name of table to close.

nDataSession Datasession to close table in if different from default data session

Return Value Returns True

Comments: Use this function to close an individual table that is already open.

(8)

PROCEDURE tsm_closeall

Purpose Closes all open tables

TSM_CloseAll()

Return Value Returns True

Comments: Use this function to close all open tables

PROCEDURE tsm_complete

Purpose Complete a job

TSM_Complete( lManua,,,[tCompleteDatel])

Parameters lManual, Force minimum margin checking even if job is already completed.

Param 2 Not Used Param 3 Not Used

tCompletDate Date/Time job was completed (Defaults to Now)

Return Value Returns True if successful else false

Comments: Use this function to complete a job. It runs any checks relevant before completing.

PROCEDURE tsm_count

(9)

Purpose Counts the number of records in the selected table that match a specified condition

TSM_Count( [cTableAlias], lExpression )

Parameters cTableAlias, Name of table to close.

lExpression Sequentially searches the table for the first record that matches the logical expression lExpression

Return Values Returns the number of matching records

Comments: Returns the total record count if no condition is passed.

PROCEDURE tsm_delete

Purpose Delete a record in a table

TSM_Delete( [cTableAlias] )

Parameters cTableAlias, Name of table to delete a record from

Return Values Returns Success or Failure

Comments: Use this function to delete the currently selected record in a table. The table must be open and positioned at the record you want to delete. Use the tsm_deljob function to delete a record in the Servcard table. Certain selected tables performs additional functions:

Product: Deletes group and location records as well.

(10)

Time: Reverses any PrePaid time and recalculates the job card total

Sevdetl: Reverses inventory levels

PROCEDURE tsm_deljob

Purpose Delete a job card in servcard.dbf

TSM_DelJob( nJobNo )

Parameters nJobNo. Job card number to delete

Comments: Deletes the select job number. Will also delete all related tables, updating stock, reversing PrePaid time etc.

PROCEDURE tsm_filter

Purpose Apply a filter to the selected table

TSM_Filter( [cTableAlias], lCondition )

Parameters cTableAlias, Name of table to apply filter to. If omitted, filter will be applied to the currently selected table

lCondition Logical filter condition to apply to the table

(11)

Comments: Does not change the record the table is positioned at so that the selected record might not match the filter condition after application.

PROCEDURE tsm_getlasterror

Purpose Return the last error in the error log

TSM_GetLastError()

Parameters None

Return Values Returns the text of the last error in the error log.

Comments: The error returned might not be for the current session as it is the last error logged by anyone. Use

TSM_VFPFUNC(“Global.LastError”) to return the last error returned for the current session.

PROCEDURE tsm_fromxml

Purpose Converts XML text into a TSM cursor or table.

TSM_FromXML( cXMLString | cXMLFile, oCursor, 0 | 1

Parameters cXMLString String containing XML code

cXMLFile Location of a file containing XML code oCursor Specifies the name of the cursor to contain

the result.

0 cXMLSting is passed

(12)

1 cXMLFile is passed

Return Values Numeric – the number of records created.

Comments: If a cursor is already open using the same name as the output cursor specified by cCursor, then the existing cursor is closed and the new cursor is created in the first available work area.

TSM interprets XML files with or without schema. If no schema is provided, TSM makes two passes through the XML data. The structure is determined during the first pass; the conversion is performed during the second pass. TSM uses either external or internal schema to determine the cursor or table structure before making a single pass through the XML data.

PROCEDURE tsm_gettsmxml

Purpose Reads setup values / job information for TSM Remote

TSM_GetTSMXml( [cEmployeeid,nServcardid,lSettings] )

Parameters cEmployeeID Logged in EmployeeID (optional)

nServcardid Job number to return XML for (optional) lSettings Return settings (nServcardid must be zero)

(optional) Return Values XML

Comments: TSM_GetTSMXml () returns XML for the employee, job or system settings.

PROCEDURE tsm_AddInvoice

(13)

Purpose Function to create an invoice in TSM.

TSM_AddInvoice( [mObject], [oThisForm], [lOpenPO], [lRecUnInvPO], [lCredLimitExc], [lInvDateLocked])

Parameters mObject Object, this is for desktop only. DLL call pass null value

oThisForm Calling form, this is for desktop only. DLL call pass null value,

lOpenPO: boolean, condition to apply if open purchase orders found, this is for DLL calls only,

lRecUnInvPO boolean, condition to apply if uninvoiced received purchase orders items found, this is for DLL calls only,

lCredLimitExc boolean condition to apply if credit limit will be exceeded, this is for DLL calls only,

lInvDateLocked boolean condition to apply if invoice date is locked and the date is prior to the locked date, this is for DLL calls only,

Comments: TSM_AddInvoice assumes invoice will be added to the current job pointer record.

It will return invoice number if succesfull Returns -1 for general failure

Returns -3 if open purchase orders found and no override parameter passed

Returns -4 if uninvoiced received purchase order found and no override parameter passed

Returns -5 if credit limit will be exceeded with current invoice and no override parameter passed

Returns -6 if invoice date is earlier than the invoice lock date and no override parameter is passed.

PROCEDURE tsm_labtype_valid

Purpose Used to update the rate, total and taxcode of a time detail

(14)

TSM_LabType_Valid( lNew, [nSeq] )

Parameters lNew, True if this is a new time detail or false if this is a change to an existing time detail.

nSeq Time record sequence number to position the time detail to

Return Value None

Comments: You must call this function each time you update the labour type on a time record.

PROCEDURE tsm_linkdoc

Purpose To create a job linked document from a Base 64 Encoded string.

TSM_LinkDoc(nJobNo, cContents, cFileName, [cLinkDesc])

Parameters nJobNo. Job card number to link the document to cContents Base 64 Encoded Content.

cFileName The name of the file to create.

cLinkDesc Description of the linked document

Return Value True if successful False if unsuccessful

Comments: The document will be placed in your default linked documents folder.

(15)

PROCEDURE tsm_linkdoclist

Purpose To return a list of all documents linked to a job.

TSM_LinkDocList(nJobNo, cCursor, [lToXML], [nStageseq], [nVarseq], [cGridCursor], [lnoQuote], [lStrip])

Parameters nJobNo. Job card number to return the list for

cCursor. OR cursor alias that contains the job pointer to use to return the list for

lToXML .T. to return XML, “A” to return Json Object array.

nStageseq. Stage Sequence number to return list for nVarseq Variation Sequence number to return list for cGridCursor. N/A

lnoQuote Do not include Quote characters in Json Object Array.

lStrip Strip undesirable characters

Return Value ‘|” separated list of documents linked to the job OR XML OR Json stringified object array

Comments: The list includes documents physically linked to the job plus associated documents from the Customer, Equipment etc.

PROCEDURE tsm_locate

Purpose Locate a record matching a condition

TSM_Locate( [cTableAlias], lCondition )

(16)

Parameters cTableAlias, Name of table to locate records in. If omitted, records will be located in the currently selected table

lCondition Logical filter condition to apply to the table

Return Values True if found False if not found

PROCEDURE tsm_net_use

Purpose Opens a table in a new work area

TSM_Net_Use( cTable, [cTableAlias], [cOrder], [lExclusive] )

Parameters cTable, Name of database table to open cTableAlias, Alias name to open the table as cOrder, Index order to assign

lExclusive, True if opened exclusively, false if shared (default shared)

Comments: Use this function to open tables for you to access.

Although a table can only be opened once, the Alias parameter allows you to open the same table more than once using a different name. The Order parameter allows you to assign any valid index tag to the table (see

separate table structure documentation).

PROCEDURE tsm_pqty_valid

(17)

Purpose Updates the total and tax amount for parts used

TSM_pQty_Valid( [cCheck] )

Parameters cCheck, Pass “CHECK” to optionally check for sufficient stock on hand levels

Return Value None

Comments: Use this function to update the total and tax amount of a part used on job cards whenever you change the

quantity. Optionally, the function will check for sufficient stock on hand. You must have the Sevdetl.dbf table open and positioned at the correct record to update.

PROCEDURE tsm_prodmarkup_valid

Purpose Calculates the sell price of an item based on its markup

TSM_ProdMarkup_Value( [nMarkup] )

Parameters nMarkup, Percentage to markup cost price by.

Return Values True if successful False if unsuccessful

Comments: You must have the Sevdetl.dbf table open and positioned at the correct record to update. This function also checks the assigned priority code of any lined job card to

determine if you are charging for parts.

PROCEDURE tsm_productid_lostfocus

(18)

Purpose Sets default Sevdetl values after selecting a ProductID

TSM_ProductID_LostFocus()

Comments: Call this function whenever you change the ProductID on a Sevdetl record. You must have the Sevdetl.dbf table open and positioned at the correct record to update.

PROCEDURE tsm_query

Purpose Call an SQL Select query on TSM tables

TSM_Query( cQuery, lToXML)

Parameters cQuery, SQL Select Query to perform.

lToXML, If true or ‘XML’ then XML will be returned. If

‘A’ then a JSON stringified object array will be returned.

Return Values Number of records in resultant data set.

Comments: Use this function to call any valid SQL query statement.

PROCEDURE tsm_read

Purpose Reads a value from a table

(19)

TSM_Read( [cTableAlias], cField, [nRecno] )

Parameters cTableAlias, Name of table to read data from cField, Name of the field to read data from

nRecno Record number of the table to return values from.

Return Values The field value specified.

Comments: Use this function to read a field from a table. If no record number is passed, the current record will be read.

PROCEDURE tsm_replace

Purpose Reads a value from a table

TSM_Replace( [cTableAlias], cField, cValue, [nRecno], [lAppendMemo], [lSeekKey], [lSeekValue] ,[lCommit] )

Parameters cTableAlias, Name of table to replace data in cField, Name of the field to replace data in cValue, Value to write to the field

nRecno Record number of the table to replace data in.

lAppendMemo If true and cField is a memo field then append the cValue to the end of memo field rather than replacing it

lSeekKey Index key to use for a seek lSeekValue Value to use for Seek

lCommit Commit buffered data to disk

(20)

Comments: Use this function to write data to a field in a table. The function will update the currently selected record in the table unless you pass Seek values which will position you on the record first. If the lCommit function is not passed, you will need to call TSM_TableUpdate later in your code to commit the records.

PROCEDURE tsm_savejob

Purpose Save a job, validate the data and update default values.

TSM_SaveJob( [nJobNo [, cTableAlias [,lIgnoreError [,,lDontRunTotalSC]]]])

Parameters nJobNo Job Card Number to save, if not passed current job in servcard table

cTableAlias, Name of table to replace data in, must be a servcard alias, if not passed assumes to have servcard open. Only needed if you give a different Alias to servcard when opening it in your application.

lEgnoreError, must be true as third party apps can’t display error messages from VFP.

lDontRunTotalSC True if don’t want to calculate the job totals. Default is false, meaning totals will be calculated.

Comments: Use this function to save a job after updating with data.

(21)

PROCEDURE tsm_savetime

Purpose Used to save time details,

TSM_SaveTime( lNew [,,, lIgnoreError], [lNoRecalcBillTime],[lIgnoreMinimum]

Parameters lNew, True if this is a new time detail or false if this is a change to an existing time detail.

lIgnoreError, must be true as third party apps can’t display error messages from VFP.

lNoRecalcBillTime, If True, do not recalculate the bill hours when saving.

lIgnoreMinimum, If bill hours are less than labour type minimum # hours set, saving will fail if this is False. If true, bill hours will automatically increase to the minimum.

Comments: You must call this function each time you save a time detail. It will validate values and set default values.

PROCEDURE tsm_sclucodeid_lostfocus

Purpose Used to set default values each time the CustomerID value is changed on a job card.

TSM_Sclucodeid_LostFocus( [oGlobal], [oThisForm], [nDatasession], [lAuto])

Parameters oGlobal Global Object. DLL call pass null value

(22)

oThisForm Calling form, this is for desktop only. DLL call pass null value,

nDatasession Numeric Datasession ID to set. Defaults to current Data Session ID

lAuto No UI interaction. True by default for DLL calls.

Comments: You must call this function each time you change the CustomerID on a job card.

PROCEDURE tsm_seek

Purpose Searches an indexed table for the first occurrence of a record whose index key matches a specified expression.

Returns a logical value indicating if the search was successful.

TSM_Seek(eExpression [, cTableAlias [, cTagName]],lNoMovePointer)

Parameters eExpression Specifies the index key expression for which you want TSM_Seek() to search.

cTableAlias Specifies the alias of the table that is

searched. If you omit cTableAlias, the table in the currently selected work area is

searched.

cOrder, Index order to assign

lNoMovePointer Does not reposition the record.

(23)

Comments: You can use TSM_Seek() on a table with an index order set, or if an index order is not set on the table, set the controlling index with the 3rd parameter, cTagName. The match must be exact unless SET EXACT is set to OFF.

If a match is found, TSM_Seek() returns true (.T.), and the record pointer moves to the matching record. If no match is found, TSM_Seek() returns false (.F.) and the record pointer moves to the end of the file.

If you omit the cTagName argument, TSM_Seek() uses the master controlling index or index tag to search for the index key.

If you only wish to determine of there is a matching record but do not wish to actually move the record pointer, set lNoMovePointer to True.

PROCEDURE tsm_setorder

Purpose Designates a controlling index file or tag for a table.

TSM_SetOrder( [cTableAlias], cOrder, [lDescending] )

Parameters cTableAlias Specifies the alias of the table that is

searched. If you omit cTableAlias, the table in the currently selected work area is searched.

cOrder, Index order to assign

lDescending Displays table records in descending order.

(24)

Comments: A table can have many index files open simultaneously.

However, only one tag from a compound index (.cdx) file (the controlling tag) determines the order in which the records in a table are displayed or accessed.

TSM_SetOrder makes it possible for you to designate the controlling tag. Certain commands (TSM_Seek, for

example) use the controlling tag to search for records.

By default, TSM_SetOrder designates the controlling tag for the table open in the currently selected work area.

PROCEDURE tsm_siteid_lostfocus

Purpose Used to update default values for a Job Card SiteID.

TSM_SiteID_LocatFocus( cSiteID )

Parameters cSiteID Site ID

Return Value None

Comments: You must call this function each time you change the SiteID on a job card.

(25)

PROCEDURE tsm_skip

Purpose Moves the record pointer forward or backward in a table.

TSM_Skip( cTableAlias, nRecords | TOP | BOTTOM)

Parameters cTableAlias, Name of table to skip

nRecords Specifies the number of records to move the record pointer

TOP Positions the record pointer on the first record in the table.

BOTTOM Positions the record pointer on the last record in the table.

Comments: If the table has a master controlling index tag or index file, SKIP moves the record pointer to the record determined by the index sequence.

PROCEDURE tsm_tablerevert

Purpose Discards changes made to a buffered row or a buffered table or cursor and restores the current disk values.

TSM_TableRevert( cTableAlias )

Parameters cTableAlias, Name of table to revert data in

Return Value True if successful

(26)

False if unsuccessful

Comments: If the cTableAlias is not passed, TSM_TableRevert discards changed in all open tables.

PROCEDURE tsm_tableupdate

Purpose Validates changes to the selected table and then commits changes made to a buffered row or a buffered table or cursor.

TSM_TableUpdate( [cTableAlias], [ADD | EDIT | ALL], [nJobNo], [lNoTotal], [lIgnoreError], [lNoLastUpdate], [lNoAutoAss], , , , lNoUpdate, lNoKitExplode, , lNoUDF, ,,nDataSessionID )

Parameters cTableAlias, Name of table to update data in.

cAction

ADD, Pass ADD when creating a new record in the Sevdetl table

EDIT, Pass EDIT when updating an existing record in the Sevdetl table

ALL, Updates data in all open tables nJobNo. Job Card number to reference when

updating

lNoTotal Do not recalculate Job Totals (SERVCARD table only)

lIgnoreError Ignore Error messages

lNoLastUpdate Do not change the last update date and user

lNoAutoAss Do not automatically assign an employee to a job (SERVCARD table only)

lNoUpdate Validate data but do not commit

(27)

lNoKitExplode Do not explode any kits or BOM’s added as items on a job (SEVDETL table only)

lNoUDF Set True if you want to bypass any user defined functions (hooks).

nDataSession Datasession to update table in if different from the default Data Session.

Comments: You should usually pass the name of the table to update as this function performs special validations for most of the main TSM tables.

PROCEDURE tsm_timecalc

Purpose Recalculates time values on Time Sheets after entering start / finish time

TSM_TimeCalc( cType, cStart, [nSeq], lNew )

Parameters cTime Time value

cType, “START” or FINISH”

cTime, Start time or finish time depending on cType nSeq, Time sequence number to position time

record on

lNew True if a new time sheet entry, false if editing an existing time sheet entry.

Return Values Returns True if successful or False if unsuccessful

(28)

Comments: Call this function after assigning a start time or a finish time to a time sheet entry. This function automatically sets the time.duration and time.jobdate value.

PROCEDURE tsm_timediff

Purpose Calculates the number of hours between two time entries

TSM_TimeDiff( cStartTime, cEndTime, WORK | BILL )

Parameters cStartTime Starting Time value cEndTime, Ending time value

WORK Calculate straight difference between start / finish times

BILL, Calculate difference taking into account time rounding.

Return Values Returns the difference between 2 time entries in hours

Comments: The format of the cstart / cfinish times must be “hh:mm’

PROCEDURE tsm_totscard

Purpose Recalculate the job card totals

TSM_TotScard( [nJobNo] )

Parameters nJobNo Job number to recalculate

Return Values True if successful,

(29)

False if unsuccessful

Comments: If nJobNo is not passed, the current job card is recalculated.

PROCEDURE tsm_toxml

Purpose Converts TSM table cursor to XML text.

TSM_ToXML( [cTableAlias], [cFilter], [cFileName] , [lEscape] , [cFieldList] , [lObject] )

Parameters cTableAlias Specifies the alias of the table from which to create the XML string

cFilter CURRENT – Current record pointer

Filter Condition – Where Clause to filter the data by

cFileName Optional file name to create containing the XML

lEscape If True, XML header will be removed from the XML

cFieldList Optional list of fields to return. If omitted, all fields will be returned.

lObject If true, a JSON stringified object array will be returned rather than XML

Return Values XML or JSON

Comments: TSM_ToXML() output adheres to the cursor index order and current filter settings. This function does not preserve the cursor location. After the call, the cursor record pointer will report EOF if all records are output, or will point to the last record output to XML if not all records were output.

(30)

PROCEDURE tsm_toxmlds

Purpose Converts TSM table cursor structure to XML text.

TSM_ToXML( [cTableAlias] )

Parameters cTableAlias Specifies the alias of the table from which to create the XML string

Return Values XML

Comments: TSM_ToXMLDS() returns the structure of the table as XML but does not include any row data.

PROCEDURE tsm_vfpfunc

Purpose Passes any VFP command or expression to TSM for evaluation.

TSM_VFPFunc( cExpression ,[cType],[nDatasessionID])

Parameters cExpression Specifies the expression to evaluate.

cExpression can be a literal character string, or a valid Visual FoxPro expression, variable, array element, or field of any data type, enclosed in quotation marks. cExpression cannot exceed 255 characters.

cType “M” – Expression is a script and not an expression. Will always return .T. If not passed, an Expression is expected

nDataSessionID Sets the selected Datasession before evaluating the expression. If not passed, will use the default datasession.

(31)

Return Values Character, Numeric, Currency, Date, DateTime, Logical, or Memo

Comments: TSM_VFPFunc() returns the result of an expression.

PROCEDURE tsm_webreports

Purpose To have the server report engine produce a report that can be displayed locally.

TSM_WebReports([lCond], cRepName, cRepType, [lSummary] , , [lRetFile], [nConversionSetting], [nFileType], [cJobType],

[cFileName])

Parameters lCondition Logical filter condition to apply to the table

cRepName Name of report file to run. Must be passed if cRepType is blank.

cRepType Type of report to run. Must be passed if cRepName is blank.

lSummary Summary reports. Default is Detailed (False).

nTimeOut Not Used

lRetFile True to save an output file into to the directory stored in setup as

I_SS_OUTPATH, False to return an encoded string (Default)

nConversionSetting Defined the encoding type below.

Default is 13, Encoded Base 64 Binary nFileType The type of report file to be

produced. Default is PDF. See List below.

cJobType ‘S’ for Job Cards only, ‘C’ for Contract Job reports only, blank for either.

Only to be used for Job Card reports.

(32)

cFileName Optional file name for generated temporary output file. Default blank for random file name.

Return Values Encoded string if lRetFile is False.

Name of output file including path if lRetFile is True

nConversionSetting Conversion

1

Converts single-byte characters in cExpression to double-byte characters.

Supported for Locale ID only (specified with the nRegionalIdentifier or nRegionalIDType parameters).

2

Converts double-byte characters in cExpression to single-byte characters.

3

Converts double-byte Katakana characters in cExpression to double-byte Hiragana characters.

4

Converts double-byte Hiragana characters in cExpression to double-byte Katakana characters.

5 Converts double-byte characters to UNICODE (wide characters).

6 Converts UNICODE (wide characters) to double-byte characters.

7

Converts cExpression to locale-specific lowercase.

(33)

8

Converts cExpression to locale-specific uppercase.

9 Converts double-byte characters in cExpression to UTF-8 10 Converts Unicode characters in cExpression to UTF-8 11 Converts UTF-8 characters in cExpression to double-byte

characters.

12 Converts UTF-8 characters in cExpression to UNICODE characters.

13 Converts single-byte characters in cExpression to encoded base64 binary.

14 Converts base64 encoded data in cExpression to original unencoded data.

15 Converts single-byte characters in cExpression to encoded hexBinary.

16 Converts single-byte characters in cExpression to decoded hexBinary.

nFileType Output Type

1 Unformatted text

3 Excel

4 PDF

5 MS Word

6 RTF

7 HTML

8 XML

PROCEDURE tsm_vfpfunc

Purpose Passes any VFP command or expression to TSM for evaluation.

(34)

TSM_VFPFunc( cExpression )

Parameters cExpression Specifies the expression to evaluate.

cExpression can be a literal character string, or a valid Visual FoxPro expression, variable, array element, or field of any data type, enclosed in quotation marks. cExpression cannot exceed 255 characters.

Return Values Character, Numeric, Currency, Date, DateTime, Logical, or Memo

Comments: TSM_VFPFunc() returns the result of an expression.

PROCEDURE XMLS_UpdFields

Purpose To update fields in the current record from XML data.

XMLS_UpdFields(cXMLString, cAlias,, cReturnStatus, lCommit, cCallingApplication, lRevertIfFail)

Parameters cXMLString String containing XML code cAlias Table alias to update cReturnStatus Not Used

lCommit True to commit the record

cCallingApplication The name of the calling application lRevertIfFail True if you want to revert changes if

update fails.

Return Values Logical, True if successful

Comments: Use TSM_Seek to position yourself on the correct record prior to calling this function. This function provides the most efficient method of updating multiple fields via the API.

(35)

PROCEDURE SendMessage

Purpose Sends emails using TSM email settings

SendMessage([cSubject], [cBody], [m.cEmail], [cmailserver], [cAccount, cPassword], [cfromaddr], [cCCAddress], [ cBCCAddress], [cAttachments], [ciCalAttachment], [nBodyFormat], [cCustomerid], [lNoBody], [nServcardid], [cRecipient], [lDoNotPopUpEmailClient], [mformType], [lNotification],

[cNotificationAddr], [lDelivery], [cDeliveryAddr],nStageseq,nVarseq,lNoAsync)

Parameters [cSubject] Email Subject

[cBody] Email body text

[cEmail] email address(es) to send to [cmailserver] email server or pop3

[cAccount] email user account name [cPassword] email user account password [cfromaddr] email from address

[cCCAddress] carbon copy email to address(es) [cBCCAddress] blind carbon copy email to

address(es) [cAttachments] list of attacments [ciCalAttachment] calendar file to attach

[nBodyFormat] email body format, 2 for HTML none or 1 for plain text

[cCustomerid] customerid for updating customer email history

[lNoBody] should email body be copied to message audit file in TSM? False or none to copy, true to not copy.

[nServcardid] TSM job number if exist

[cRecipient] email recipient, if this is entered cEmail is ignored.

(36)

[lDoNotPopUpEmailClient] always set this to true

[mformType] email form to be used (TSM, MAPI, GMAIL etc)

[lNotification] do you require read by recipient notification?

[cNotificationAddr] notification email address [lDelivery], do you require delivery status

notification?

[cDeliveryAddr] email address to notify the delivery status (6.86+)

[nStageseq] TSM Stage sequence number if exist (6.86+)

[nVarseq] TSM Variation sequence number if exist (6.86+)

[lNoAsync] Do not send via Async (6.86+)

Return Values Returns blank if email sent successfully or the error message from the mail delivery sub system

Comments: If you have multiple email addresses in either of the email address parameters or attachments enclose each with single quote and separate by a semicolumn.

To use TSM email settings leave cMailServer, cAccount and cPassword blank.

mformType should not be passed and let the TSM system default be used.

Procedure CreateObject

Purpose Creates a object from a specified class, in the datasession of the Global object referenced (if relevant). Also performs necessary activation of licenced components

CreateObject (cClassName, [params]…)

Parameters cClassName Name of the calss to create

(37)

params 0 – 24 parameters to be paseds to the object construcutor

Return Values Object reference on success, Null on failure

Comments: Useful for using Chilkat_9_5_0 objects in scripts

(38)

Additional Methods

Procedure AddTimeRecord

Purpose Add a time record to TSM.

AddTimeRecord (nServcardid,nStageseq,nVarseq,lcEmployeeid,lcType ,dDate,lcStart,lcFinish,lctscomment)

Parameters nServcardid Job number

nStageseq Stage Sequence # nVarseq Variation Sequence # lcEmployeeid Employee ID

lcType Labour Type

dDate Date

lcStart Start Time lcFinish End Time

lctscomment Time Sheet Comment

Return Values True if successful otherwise False. Check the value of LastError if unsuccessful.

Procedure Add_Part

Purpose Add a part to a job

Add_Part (lcProductID,lnQty,lnServcardid,lquote,lcdefloc, lnStageseq,lNoTotal_Servcard)

(39)

Parameters lcProductID Part Number lnQty Quantity used lnServcardid Job number

lQuote Quoted part? (True / False) lcDefLoc Parts used location

lnStageseq Stage Sequence #

lNoTotal_Servcard Do not recalculate job total (handy when you are adding a large number of parts to a job)

(40)

Procedure DeleteChecklist

Purpose Deletes a Group of Checklists, A Checklist question set or a single CheckList question

DeleteChecklist(lcParentTable, nServcardID, nStageSeq, nVarSeq, nAsgSeq, lcValue2Delete, lcDelType, lcWhere, nSeq)

Parameters lcParentTable Alias of parent record to Delete from nServcardid Job number

nStageseq Stage Sequence # nVarseq Variation Sequence # nAsgSeq Assignment Sequence #

lcValue2Delete Key value corresponding to the DelType value corresponding to group, chklistid or CheckSeq

lcDelType GROUP,LIST,SINGLE

lcWhere Additional WHERE clause if required nSeq Optional Sequence number of a specific

checklist question to delete

Procedure AddChecklist

Purpose Add a Group of Checklists, A Checklist question set or a single CheckList question

AddChecklist(lcChkListID, nServcardID, lnSevItemSeq, lcCheckListTable, lcParentTable,lIsServiceItem,tmpcursorname,nStageSeq, nVarSeq,lcGroup, nAsgSeq, llnCheckSeq,lcResult)

Parameters lcChkListID ID of Checklist to add

(41)

nServcardid Job number to link to lnSevItemSeq Job Equipment Seq # lcCheckListTable N/A

lcParentTable Parent Alias to attach checklist to lIsServiceItem Logical True if Equipment Checklist Tmpcursorname N/A

nStageseq Stage Sequence # to link to nVarseq Variation Sequence # to link to lcGroup Checklist Group to add (if ID Null) nAsgSeq Assignment Sequence # to link to llnCheckSeq N/A

lcResult Optional Result to set

Return Values Integer > 0 if successful, otherwise Zero if Unsuccessful.

Check the value of LastError if unsuccessful.

Function UpdCustomerKey

Purpose Update Customer ID across all tables

UpdCustomerKey (lcOldKey, lcNewKey, llMerge, lSilent)

Parameters lcOldKey Original key value lcNewKey New key value

llMerge Pass True if new Key is an existing record. Old Record will be deleted.

Leave False if you are just changing the key to a new value.

lSilent True to suppress pop up status window

(42)

Return Values None

Notes Similar functions include UpdProductKey, UpdEmployeeKey (lSilent not supported)

(43)

Global Functions

These are globally scoped functions available from all TSM scripts.

FUNCTION ISEQUAL

Purpose Determine if 2 values are equal or not, without risking datatype mismatch errors.

ISEQUAL(param1, param2, [lNullIsEmpty])

Parameters param1 the Field name param2 the Alias

lNullIsEmpty True to treat null values as equal to empty values (i.e. a null is equal to the empty value of any type)

Return Values Boolean

FUNCTION OLDEMPTY

Purpose Determine if the previous value of the buffered field was empty

OLDEMPTY (cField, [cAlias], [lNullIsEmpty])

Parameters cField the Field name cAlias the Alias

lNullIsEmpty True to treat null values as empty values

(44)

Notes Returns false if the table is unbuffered

FUNCTION BCLEARED

Purpose Determine if the uncommitted field value has just been cleared. Short for “has buffered value been cleared”.

BCLEARED(cField, [cAlias])

Notes Useful for triggering events or validations in udf_* scripts.

Pass lNullIsEmpty to ignore spurious changes on the first commit of a record.

FUNCTION BSET

Purpose Determine if a field has changed from empty to non empty. Short for “has buffered value been set”.

BSET(cField, [cAlias])

(45)

FUNCTION BCHANGED

Purpose Determine if changes are pending for the specified field.

Short for “is buffered value changed”.

BCHANGED(cField, [cAlias], [lNullIsEmpty])

lNullIsEmpty True to treat null values as empty values (i.e. a value changing from null to empty is not changed)

Pass lNullIsEmpty to ignore spurious changes on the first commit of a record.

FUNCTION AVAILABLEQTY

Purpose To return the quantity available to be used for any location / product combination. This function adjusts the Onhand value to take into consideration quantity on order or committed to jobs..

AVAILABLEQTY(cLocation,cProductid, [lIgnoreStockOrder], [lIgnoreNonStock])

Parameters [cLocation] Inventory location to query. If not included then will query the main product table cProductID Product ID to Query

(46)

[lIgnoreStockOrder] Ignore items on stock order. By default, available quantity includes items on stock order.

[lIgnoreNonStock ] Ignores non-stock items. By default, Non-Stock items return 9999

quantity.

Return Values Numeric

Notes Misc items not in the product database always return available quantity 0.

(47)

User Defined Scripts

UDF_DELETE_<tablename> (lIgnoreError,oGlobal) Run before commiting deletion a record

Return false to

UDF_New_<tablename> (lIgnoreError,oGlobal,oThisform) Run on appending a record

Return false to prevent appending of a record.

Used for custom security, or custom defaults.

UDF_NEW_Servcard (IgnoreError,oGlobal,oThisform) Run on appending a new job record

UDF_Servcard (lIgnoreError,oGlobal,oThisoform,cAction) Run during update of job

Return false to refuse to update

UDF_SAVE_SERVCARD (nServcardid,lNewJob,oGlobal,oThisform,lIgnoreError) Run after update of job records.

Used to trigger

UDF_Pre (lIgnoreError,oGlobal,oThisform,cAction) Run before update.

UDF_<tablename> (oGlobal,lIgnoreError,oGlobal,oThisform,cAction) Run before committing records.

Return false

(48)

Useful for validation and triggering event based on buffered changes

UDF_SAVE_<tablename> (lIgnoreError,oGlobal,lAdd,oThisform) Run after Save

UDF_Backup (oGlobal,lFtp,mCheckit) Run after backup

UDF_RunDocument (oGlobal,cFileName,cWorkDir,cOperation)

Run when running a document. Allows you to create your custom launcher

UDF_INIT_SS (oGlobal,cDomanUser,cDomain,cDomainPassword) Run when initialising INIT_SS

UDF_Customer_LostFocus (oGlobal,oThisform,oGlobal,lAuto) Run when customer ID changes

UDF_Site_LostFocus (cSiteID,oGlobal,oThisform,oGlobal,lAuto) Run when Site Customer changes

UDF_Complete_Required (oGlobal) Run when Completing

Return .T. to allow completion even if required checklist questions unanswered

UDF_Complete_Servcard (lAuto,oGlobal,oThisform,cAction) Run on complete

UDF_After_Complete_Servcard (lAuto,oGlobal,oThisform,cAction) Run after job complete

(49)

UDF_SendMessage (nServicardid,cCustomerID,cFromaddr,cEmail,cCCAddress, cBCCAddress,cSubject,cBody,cmailserver,cAccount,cPassw ord,cAttachments,nBodyFormat,lNoBody,cRecipient,mform Type,cFromAddrNoName,cfromname,oGlobal)

Run prior to sending an email. All paramaters passed by reference to allow changes any of the settings.

UDF_SendMail

(cSubject,cBody,m.cEmail,cmailserver,cAccount,cPasswor d,cfromaddr,cCCAddress,cBCCAddress,cAttachments,cIcal Attachment,nBodyFormat,cCustomerid,lNoBody,nServcardi d,cRecipient,lDoNotPopUpEmailClient,mformType,lNotifica tion,cNotificationAddr,lDelivery,cDeliveryAddr,cFromAddrN oName,cfromname,oGlobal)

Create your own custom email sending method

UDF_LinkDocList (oGlobal,nServcardid,nStageseq,nVarseq,cGridCursor) Used to create your own grid cursor or update the current grid cursor

UDF_PrintInv (oGlobal,oThisform) Run prior to printing invoice.

Return false to prevent printing of the invoice

UDF_PrintInv_After (oGlobal,oThisform) Run after printing invoice

UDF_MoveAsset (oGlobal,nSeq,nServcardid,lMoveChildren,nIncSchedules) Run prior to moving assets

UDF_ChkStru ()

Run at end of Structure Check

Enables you to to customer database cleanup, or enforce structural changes

(50)

UDF_SAVEEML (oGlobal,oMailObject,oMessageObject) Run after creating an EML file.

UDF_TOTALSERVCARD (lUpdate,nServcardid,nStageseq,lNoMinCharge) Run after calculating job total

NewCustCode (cName)

Custom code to generate a Customer ID on customer creation

UDF_SetMemvar (oGlobal)

Run at end of setting global memory variables / properties

UDF_UPDCUSTOMERID (cOldKey,cNewKey,cAPName,cAPListID) Run at end of updating Customer Key

UDF_UPDPRODUCTID (cOldKey,cNewKey,cAPName,cAPListID) Run after changing Product ID

TSMAfterLogin() Run after logging in

UDF_CustProdDisc()

Run after calculating product discount. Returns discount

udf_regocx()

Run after registering OCX files.

Use to register other things.

(51)

UDF_LAUNCH (oGlobal,cURL,cPArams,cDir,lRunAs,nView) Custom launcher

TSMStartUp()

Runs when TSM starts up but before login

TSMAfterLogin() Runs after login

UDF_Reindex() Runs after reindex.

Used for adding custom indexes, such as on user defined fields

XML File to Process

(emailbody,MailDate,htmlBodyStr,@cError,AttachmentXML ,MailAddress,MailSubject,MailFrom,@lNoEmail,emailtext,o Thisform)

Parses inbound data in XML Processor

UDF_XMLPROCESSOR.PRG (oGlobal,oThisform) Runs after processing XML inbound data

UDF_JCINV (lIgnoreError,oGlobal,oThisform) Runs after opening Contracts Jobs Invoice screen.

UDF_MakeCard (nServcardid,cAutoPONumber) Runs after processing maintenance schedules.

Used to trigger processes from maintenance jobs, but not other jobs.

TSMGRIDS_MAKECARD.INI (oGrid,oThisform)

(52)

Run directly after creating the Schedules grid to add additional columns to the grid

UDF_INIT_TSMFormSet (oGlobal)

Runs at beginning of Initialising TSM screen

UDF_REFRESH_TSMFormSet (oGlobal)

Run directly after refreshing the main TSM Window

(53)

The following is a sample VB application indicating the use of the TSM.DLL

'Visual Basic Sample code For TSM com interface

Dim objTSM as object Dim bResult as boolean

' Initialise TSM object

' Setup TSM object environment

If not objTSM.TSM_Init("JT","","C:\tsm") then MsgBox("Login Failed")

Exit Sub End If

' Read the name of the customer in the customer table in the first record text1.text = objTSM.TSM_Read("Customer","FirstName") & " " &

objTSM.TSM_Read("Customer","LastName")

' Close the customer table

bResult = objTSM.TSM_Close("Customer")

' Release the TSM object objTSM = Nothing

(54)

VB Sample Code for adding a job into TSM

Dim objTSM As object

repValue = objTSM.TSM_Init("JT","","C:\TSM")

‘ Add a new job and return the job number jobNo = objTSM.TSM_AddJob()

‘ Replace the fields in the job with desired values

repValue = objTSM.tsm_replace("Servcard", "COMPANY", Trim(tempRec("Name")))

repValue = objTSM.tsm_replace("Servcard", "JOBDATE", "%%CTOT('" &

tempRec("Commence_Date") & "')")

repValue = objTSM.tsm_replace("Servcard", "DATECOMP", "%%CTOT('" &

tempRec("Complete_Date") & "')")

repValue = objTSM.tsm_replace("Servcard", "DATE", "%%CTOT('" &

tempRec("Commence_Date") & "')")

repValue = objTSM.tsm_replace("Servcard", "CUSTCONT", Trim(tempRec("Tele_Comment")))

repValue = objTSM.tsm_replace("Servcard", "MOBILE", Trim(tempRec("Tele_Comment2")))

repValue = objTSM.tsm_replace("Servcard", "SERVREQUES", Trim(tempRec("Fault")))

repValue = objTSM.tsm_replace("Servcard", "WORKPERF", Trim(tempRec("Comment")))

repValue = objTSM.tsm_replace("Servcard", "STATUSCODE", Trim(tempRec("Status")))

‘ Save the job and check if successful If Not objTSM.tsm_savejob(jobNo) Then

MsgBox objTSM.lasterror End If

(55)

VB Sample Code for to initialise the TSM API session ' noTSM is the current session object

Private Function zInitTSM() As Boolean

' If there is already a current session then select it and do not open a new session.

If Not Session("moTSM") Is Nothing Then moTSM = Session("moTSM")

Return True End If

Dim bResult As Boolean = False Dim sFolder As String

' Initialise TSM object

moTSM = VB.CreateObject("TSM61.Global")

' msUSername is a variable holding the TSM login name ' msPassword is a variable holding the TSM login password ' msFolder os the path to the TSM directory

bResult = moTSM.TSM_Init(msUserName, msPassword, msFolder)

If bResult Then

' Create a new session Session("moTSM") = moTSM

End If

Return bResult

(56)

(57)

VB Sample Code to position the record pointer on a particular job '

TSM_Seek(12345,"ServCard","servcardid",false);

* Function to Seek a value in a table

Public Function TSM_Seek(ByVal eExpression As String, ByVal cTableAlias As String, ByVal cTagName As String, ByVal lNoMovePointer As Boolean) As String

Try

Dim bResult As Boolean = False If zInitTSM() Then

bResult = moTSM.TSM_Seek(eExpression, cTableAlias, cTagName, lNoMovePointer) End If

Return bResult.ToString + "," + moTSM.LastError Catch ex As Exception

Return ExceptionMessage End Try

End Function

VB Sample Code to return an SQL query from the TSM API as XML

XMLString = TSMQuery("SELECT * FROM Time WHERE ServCardID == " & servcardid & "

ORDER BY [Date],Start")

Public Function TSMQuery(ByVal Query As String) As String Try

Dim ds As Data.DataSet = Nothing Dim oXML As New XmlDocument If zInitTSM() Then

Dim iRows As Integer Dim sXML As String

(58)

' Pass the query to the API and create an API side cursor iRows = moTSM.TSM_Query(Query & " into cursor MyRes")

' Convert the results into XML and get XML sXML = moTSM.TSM_ToXML("MyRes") oXML.LoadXml(sXML)

' Don’t forget to release the API side cursor moTSM.TSM_Close("MyRes")

End If

Return oXML.InnerXml Catch ex As Exception Return ExceptionMessage End Try

End Function

VB Sample Code to update the customer note field

' First parameter is the CustomerID to look up, the 2^nd value is the note text to write

Update_CustNote(“ACME”, “This is the new note string”);

Public Function Update_CustNote(ByVal customerID As String, ByVal note As String) As String

Try

Dim bResult As Boolean If zInitTSM() Then

' Query the customer unique key Seq

(59)

moTSM.TSM_Query("SELECT seq FROM Customer WHERE CustomerID == '" &

customerID & "' INTO CURSOR MyRes")

' Read the Seq from the cursor

Dim seq As Integer = ToInteger(moTSM.TSM_Read("MyRes", "seq"))

'Close open cursor

moTSM.TSM_Close("MyRes")

' Position the actual customer record pointer

If moTSM.TSM_Seek(seq, "Customer", "seq", False) Then ' Replace customer note

moTSM.TSM_Replace("Customer", "NOTE", note) End If

' Commit your changes

bResult = moTSM.TSM_TableUpdate("Customer", "EDIT", , , True) End If

‘ The following is also functionally similar:

Public Function Update_CustNote(ByVal customerID As String, ByVal note As String) As String

Try

Dim bResult As Boolean If zInitTSM() Then

' Query the customer unique key Seq

moTSM.TSM_Query("UPDATE Customer SET note = “ & note & “ WHERE CustomerID == '" & customerID & "')

' Commit your changes

bResult = moTSM.TSM_TableUpdate("Customer", "EDIT", , , True)

(60)

End If