How To Remove Repeating Blank Rows Due To Blank Records

[wrgt9740]

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?

 

[Mike Lewis]

This is exactly what I would expect. You have demonstrated that the query result does not contain null values (at least, not in the item code), so you can safely ignore that possibility.

By the way, when you do a SELECT and you don't include a TO or INTO clause, the results go to a cursor named QUERY, and VFP automatically opens a Browse window for that cursor. I think that explains your confusion when you mentioned opening the tables "into query".

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Thanks, wrgt9740, and also Mike, that concludes it.



Chriss

 

[wrgt9740]

I think that explains your confusion when you mentioned opening the tables "into query".

Yes, sorry for this.