Integration with Computers Unlimited 3.0

Overview

CU 3.0 runs on Microsoft SQL Server. TrackAbout customers interact with TIMS 3.0 by logging into a Remote Desktop connection, and running the TIMS front end directly on 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

VB Module

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

Queries

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

Adjust the query parameters for the dates.

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"

Includes closed invoices that will go to "invoiced" status overnight, but which 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;