Integration with Computers Unlimited 3.0

CU 3.0 runs on MS SQL server. Our customers interact with TIMS 3.0 by logging into a remote desktop connection, and running the TIMS front end directly from their server.

The local admin for the TIMS server should also have the administrator login for the TIMS server, and the SQL server login for the TIMS server.

So far, at 2 clients, the sql server login has been: user: sa Password: cusqlxxx with ‘xxx’ being some three letter abbreviation for the client.

What you’ll need on the client PC:

1. some version of Microsoft Access or other SQL query tool to handle pulling the information from the TIMS database, and exporting the resulting text to TrackAbout.
2. IP address of the TIMS SQL server Login information for the TIMS server, and SQL server.
3. Some facility for transferring the file to TrackAbout. Typically we've used Novell's free utility, NetDrive, which maps an FTP site to a network drive on the PC. You could certainly do the same with Windows XP/NT/2000/Vista's mapped network drive functionality. ftp login information for the particular client

In Access, you’ll need to create links to the following tables in CU’s database: LINPRM—permanent archived invoices LINHST—“invoiced” invoices from the current calendar month ORDHDR—orderline header info CUSMAS—customer master INVMAS—inventory master CY_BF—cylinder balances ORDLGAS—gas orderline ORDLIN—orderline

You’ll also need to create a VB module with the following: Visual Basic Function to Left pad a number with zeros, to match the format of CU’s scannable invoice numbers. '*********************************************************************

'Declarations section of the module.

'*********************************************************************

Option Explicit Dim x As Integer Dim PadLength As Integer

'=====================================================================

'The following function will left pad a string with a specified 'character. It accepts a base string which is to be left padded with 'characters, a character to be used as the pad character, and a 'length which specifies the total length of the padded result.

'=====================================================================

Function lpad(MyValue$, MyPadCharacter$, MyPaddedLength%)

     PadLength = MyPaddedLength - Len(MyValue)
     Dim PadString As String
     For x = 1 To PadLength
        PadString = PadString & MyPadCharacter
     Next
     lpad = PadString + MyValue

End Function

In all of the queries below, you’ll need to change the “JWE” to the three letter prefix for your client.

Historical invoice Query: for all invoices not in CURRENT CALENDAR MONTH

SELECT jwe_LINPRM.CUSNO, jwe_CUSMAS.LAST_NAME, lpad(jwe_LINPRM.ORDNO,0,8) & lpad(jwe_LINPRM.BAKNO,0,2) AS Expr1, jwe_LINPRM.LINHST_DATE, jwe_LINPRM.LINPRM_DATE, jwe_LINPRM.SHIP_DATE, jwe_LINPRM.PART, Replace([jwe_INVMAS].[DESCR1]+[jwe_INVMAS].[DESCR2],',',' ') AS Expr2, jwe_LINPRM.CYLSHIP, jwe_LINPRM.CYLRET, jwe_LINPRM.PONO FROM jwe_LINPRM, jwe_CUSMAS, jwe_INVMAS WHERE (((jwe_LINPRM.CUSNO)=[jwe_CUSMAS].[CUSNO]) AND ((jwe_LINPRM.LINPRM_DATE)>=20071001 And (jwe_LINPRM.LINPRM_DATE)<=20071015) AND ((jwe_LINPRM.PART)=[jwe_INVMAS].[PART]) AND ((jwe_LINPRM.GASFLG)=1) AND ((jwe_LINPRM.SUP)=[jwe_INVMAS].[SUP]));

Query for "invoiced" invoices in the current calendar month:

SELECT jwe_LINHST.CUSNO, jwe_CUSMAS.LAST_NAME, lpad(jwe_LINHST.ORDNO,0,8) & lpad(jwe_LINHST.BAKNO,0,2) AS Ordno, jwe_LINHST.INVDTE, jwe_LINHST. INVDTE, jwe_LINHST. INVDTE, jwe_LINHST.PART AS Expr3, Replace(jwe_INVMAS.DESCR1+jwe_INVMAS.DESCR2,',',' ') AS Expr4, jwe_LINHST.CYLSHIP, jwe_LINHST.CYLRET, jwe_LINHST.PONO FROM jwe_LINHST, jwe_CUSMAS, jwe_INVMAS WHERE (((jwe_LINHST.CUSNO)=jwe_CUSMAS.CUSNO) And ((jwe_LINHST.GASFLG)=1) And ((jwe_LINHST.SUP)=jwe_INVMAS.SUP) And ((jwe_LINHST.PART)=jwe_INVMAS.PART)) And jwe_LINHST. INVDTE >=20071218 And jwe_LINHST. INVDTE <=20071219 ORDER BY ordno;

Query for "Selected Invoices" (closed invoices that will go to "invoiced" status overnight, but that can still be changed):

SELECT jwe_ORDHDR.OCUSNO, jwe_ORDHDR.OCUSNM, lpad(jwe_ORDHDR.OORDNO,0,8) & lpad(jwe_ORDHDR.OBAKNO,0,2) AS Expr1, jwe_ORDHDR.OORDDT, jwe_ORDHDR.OORDDT, jwe_ORDHDR.OORDDT, jwe_ORDLIN.LITMNO_ITEM, jwe_ORDLIN.LDESCR1, jwe_ORDLGAS.LSHIP, jwe_ORDLGAS.LRET, jwe_ORDHDR.OPONO FROM jwe_ORDHDR, jwe_ORDLGAS, jwe_ORDLIN WHERE (((jwe_ORDHDR.OORDNO)=[jwe_ORDLGAS].[LGAS_ORDNO] And (jwe_ORDHDR.OORDNO)=[jwe_ORDLIN].[LORDNO]) AND ((jwe_ORDHDR.OBAKNO)=[jwe_ORDLGAS].[LGAS_SEQNO] And (jwe_ORDHDR.OBAKNO)=[jwe_ORDLIN].[LSEQNO]) AND ((jwe_ORDLIN.LITMNO_SUP)='GAS') AND ((jwe_ORDLIN.LINENUMA)=[jwe_ORDLGAS].[LGAS_LINENUMA])) and jwe_ORDHDR.OFLAG=1;

Query for Customer Balances:

SELECT [jwe_CYO_BF].[CUSNO], [jwe_CYO_BF].[SUP]+[jwe_CYO_BF].[PART], [QTY], [jwe_CUSMAS].[LAST_NAME] FROM jwe_CYO_BF, jwe_CUSMAS WHERE [jwe_CYO_BF].[CUSNO]=[jwe_CUSMAS].[CUSNO];

Query for List of customers with customer numbers:

SELECT [jwe_CUSMAS].[CUSNO], [jwe_CUSMAS].[LAST_NAME] FROM jwe_CUSMAS;