Hi. First of all, I'm currently making a Foxpro version of the Clipper program that our company is still using.
I have 2 tables: items.dbf and trans.dbf. Items is where we store information on our products, and trans is information on purchases and sales of our products.
In items.dbf, two of the fields are itemcode and itemdesc (item description). I don't know why, but there are 35 records where itemcode is blank, and the rest of the fields have differing data. For example:
ITEMCODE[tab]ITEMDESC[tab]PRICE
ballpen[tab][tab][tab]red[tab][tab][tab]10.00
[tab][tab][tab][tab][tab]long[tab][tab][tab]20.00
[tab][tab][tab][tab][tab]short[tab][tab][tab]15.00
[tab][tab][tab][tab][tab][tab][tab][tab][tab][tab]50.00
(35 records of blank itemcode in total)
In trans.dbf, the fields are date, invoice, itemcode, itemdesc, price. I also make a report to a cursor using SQL:
INSERT INTO CURSOR ;
SELECT DATE, NAME, ITEMCODE, ITEMS.ITEMDESC, PRICE ;
FROM TRANS ;
INNER JOIN ITEMS ;
ON TRANS.ITEMCODE = ITEMS.ITEMCODE
The problem is, the report in Foxpro shows all 35 blank itemcode while in Clipper only one shows up.
Foxpro report:
DATE[tab]INVOICE[tab]ITEMCODE[tab]ITEMDESC[tab]PRICE
01/20/21[tab]1001[tab]ballpen[tab][tab][tab]red[tab][tab][tab]10.00
01/21/21[tab]1002[tab]pencil[tab][tab][tab]wood[tab][tab][tab]5.00
01/22/21[tab]1003[tab][tab][tab][tab][tab][tab]long[tab][tab][tab]20.00
01/22/21[tab]1003[tab][tab][tab][tab][tab][tab]short[tab][tab][tab]15.00
01/22/21[tab]1003[tab][tab][tab][tab][tab][tab][tab][tab][tab][tab][tab]50.00
(35 records of blank itemcode in total)
01/23/21[tab]1004[tab]glue[tab][tab][tab][tab]small[tab][tab][tab]10.00
Clipper report:
DATE[tab]INVOICE[tab]ITEMCODE[tab]ITEMDESC[tab]PRICE
01/20/21[tab]1001[tab]ballpen[tab][tab][tab]red[tab][tab][tab]10.00
01/21/21[tab]1002[tab]pencil[tab][tab][tab]wood[tab][tab][tab]5.00
01/22/21[tab]1003[tab][tab][tab][tab][tab][tab]long[tab][tab][tab]20.00
01/23/21[tab]1004[tab]glue[tab][tab][tab][tab]small[tab][tab][tab]10.00
I made a workaround by including in the SELECT statement the WHERE clause: WHERE ISBLANK(TRANS.ITEMCODE) = .F.
This removed all records with blank itemcode.
I was wondering if it is possible to duplicate the Clipper report in Foxpro?
I have 2 tables: items.dbf and trans.dbf. Items is where we store information on our products, and trans is information on purchases and sales of our products.
In items.dbf, two of the fields are itemcode and itemdesc (item description). I don't know why, but there are 35 records where itemcode is blank, and the rest of the fields have differing data. For example:
ITEMCODE[tab]ITEMDESC[tab]PRICE
ballpen[tab][tab][tab]red[tab][tab][tab]10.00
[tab][tab][tab][tab][tab]long[tab][tab][tab]20.00
[tab][tab][tab][tab][tab]short[tab][tab][tab]15.00
[tab][tab][tab][tab][tab][tab][tab][tab][tab][tab]50.00
(35 records of blank itemcode in total)
In trans.dbf, the fields are date, invoice, itemcode, itemdesc, price. I also make a report to a cursor using SQL:
INSERT INTO CURSOR ;
SELECT DATE, NAME, ITEMCODE, ITEMS.ITEMDESC, PRICE ;
FROM TRANS ;
INNER JOIN ITEMS ;
ON TRANS.ITEMCODE = ITEMS.ITEMCODE
The problem is, the report in Foxpro shows all 35 blank itemcode while in Clipper only one shows up.
Foxpro report:
DATE[tab]INVOICE[tab]ITEMCODE[tab]ITEMDESC[tab]PRICE
01/20/21[tab]1001[tab]ballpen[tab][tab][tab]red[tab][tab][tab]10.00
01/21/21[tab]1002[tab]pencil[tab][tab][tab]wood[tab][tab][tab]5.00
01/22/21[tab]1003[tab][tab][tab][tab][tab][tab]long[tab][tab][tab]20.00
01/22/21[tab]1003[tab][tab][tab][tab][tab][tab]short[tab][tab][tab]15.00
01/22/21[tab]1003[tab][tab][tab][tab][tab][tab][tab][tab][tab][tab][tab]50.00
(35 records of blank itemcode in total)
01/23/21[tab]1004[tab]glue[tab][tab][tab][tab]small[tab][tab][tab]10.00
Clipper report:
DATE[tab]INVOICE[tab]ITEMCODE[tab]ITEMDESC[tab]PRICE
01/20/21[tab]1001[tab]ballpen[tab][tab][tab]red[tab][tab][tab]10.00
01/21/21[tab]1002[tab]pencil[tab][tab][tab]wood[tab][tab][tab]5.00
01/22/21[tab]1003[tab][tab][tab][tab][tab][tab]long[tab][tab][tab]20.00
01/23/21[tab]1004[tab]glue[tab][tab][tab][tab]small[tab][tab][tab]10.00
I made a workaround by including in the SELECT statement the WHERE clause: WHERE ISBLANK(TRANS.ITEMCODE) = .F.
This removed all records with blank itemcode.
I was wondering if it is possible to duplicate the Clipper report in Foxpro?