Using a Grid or List to display Customer Monthly Activity 1

[cgc22]

Hi,
I have an old Foxpro program using @ and SAY commands to
get rows to display on the screen for the USER to look
at their customers activity of all Invoices, Credit, and
Payments for the past 2-3 months. This program works very
well in FoxPro 2.6 but I'm now using "Visual FoxPro 9.0".

I don't want to use any @ or SAY commands and want to convert using VISUAL Objects! I've tried using a GRID
but I want "each seperate row" in the grid to insert fields
from two different(.DBF files). DBF (#1=Invoices) and .DBF (#2 = Payments).I already know how to store data into GRID but How do I get each seperate row in GRID to read customer
invoices & payments?? Below is a "portion" of the old ver 2.6 program:

SELECT 1
USE CUSLIST INDEX COMPANY ?? Customer List
SELECT 4
USE ARCHG2 INDEX DATE3,CUSTID6 ?? Invoices & Credits
SELECT 5
USE ARPAID2 INDEX DATE4,CUSTID7 ?? Payments
BAL = A-> HISTORY2
SEEK A->CUSTID
ROW = 5
TOT=A->HISTORY2
DO WHILE A->CUSTID=E->CUSTID .OR. A->CUSTID=D->CUSTID
SELECT 5
IF DATE<= D->DATE .AND. A->CUSTID=CUSTID
ROW=ROW+1
TOT = TOT - PAYMENT
@ROW,1 SAY DATE
@ROW,14 SAY COMMENT
@ROW,49 SAY PAYMENT
@ROW,61 SAY TOT PICTURE "999999.99"
SKIP
ELSE
SELECT 4
IF A->CUSTID=CUSTID
ROW = ROW+1
TOT = INVTOT + TOT
@ROW,1 SAY DATE
@ROW,14 SAY TERMS
@ROW,36 SAY INVNUM
@ROW,49 SAY INVTOT
@ROW,61 SAY TOT PICTURE "999999.99"
SKIP
ENDIF
ENDIF
SELECT 4
IF DATE <= E->DATE .AND. A->CUSTID=CUSTID
ROW = ROW+1
TOT = INVTOT + TOT
@ROW,1 SAY DATE
@ROW,14 SAY TERMS
@ROW,36 SAY INVNUM
@ROW,49 SAY INVTOT
@ROW,61 SAY TOT PICTURE "999999.99"
SKIP
ELSE
SELECT 5
IF A->CUSTID=CUSTID
ROW=ROW+1
TOT = TOT - PAYMENT
@ROW,1 SAY DATE
@ROW,14 SAY COMMENT
@ROW,49 SAY PAYMENT
@ROW,61 SAY TOT PICTURE "999999.99"
SKIP
ENDIF
ENDIF
ENDDO

If a GRID isn't the way to go on this-then what should I do?? Any help would be greatly appreciated !
Greg

 

[ilyad]

You may create a cursor of all customer's invoices and payments by using Select-SQL (left joins from Customer to Invoce and Payments) and then show this cursor in a grid. Not sure if this is what you're after, though.

You can also download grid class with Totals from Dorin Vasilescu at

http://www.alltrans.ro/Download/gridfooter.zip

or check

http://www.utmag.com/ViewPageArticl...556335550554479776C75325A4471504A444A48413D3D

 

[Mike Lewis]

Hi Greg,

If a GRID isn't the way to go on this-then what should I do??

There are many possible ways of displaying this kind of data, but a grid is as good as any -- especially as you already know how to use grids. A grid is particularly suitable in this case because there will be a variable number of rows to display.

As Ilyad suggested, the best way to populate the grid would be to create a cursor. The cursor would contain one record for each grid row (that is, for each invoice or payment), with one field for each column.

You can create the cursor using a SELECT (Ilyad suggested an outer join, but I would think a UNION might be easier). That would give you the first few columns. You will probably have to then do a small loop to populate the running total as the SELECT cannot easily to do that (in the SELECT statement, be sure to add a READWRITE clause).

The above is just meant to point you in the right direction. Hope it helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[dgrewe]

What About 3 grids Acting as one.
First grid is the Parent Table of Customers.
Second & Third are the child Tables Invoices and payments.

When You move records in the Parent Table you can refresh the to child grids to show only the records for the parent record.



David W. Grewe (Dave)