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?

 

[Chris Miller]

The inner join filter out the empty itemcode rrecords, unless you also have a blank itemcode in trans.dbf, so what shows up when you

Use trans
browse for empty(itemcode)

Chriss

 

[Chris Miller]

And what about deleting the item records ithout itemcode? Do they still have any meaning for something else?

Chriss

 

[wrgt9740]

Yes, there are also blank itemcode records in the trans.dbf.

I don't know if the records without itemcode in both tables are relevant, and I'm afraid to delete them as it might cause an immediate or future undesirable consequence in the Clipper program. Asking the original programmer is not possible, as he has moved to a different country.

 

[Mike Lewis]

Have I understood this right? You want to eliminate all the records with blank item codes except the first?

If so, after you create your cursor, try this:

SELECT TheCursor
LOCATE FOR ISBLANK(ItemCode)
REPLACE ItemCode WITH CHR(160)

then produce the report as usual.

CHR(160) is a non-breaking space. VFP considers it to be non-blank, but it will appear as a normal space character in a report.

Apologies if I have misunderstood the problem.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[wrgt9740]

Mike, yes you are correct. I'll try your suggestion on my next free time.

Edit: I've given some thought about this without trying on Foxpro yet. Should the code be:

CREATE CURSOR...

INSERT INTO CURSOR ;
SELECT [fields] ;
FROM TRANS ;
INNER JOIN ITEMS ;
ON TRANS.ITEMCODE = ITEMS.ITEMCODE

SELECT CURSOR
LOCATE FOR ISBLANK(ITEMCODE)
REPLACE ITEMCODE WITH CHR(160)

Wouldn't this produce 35 blank records also?

 

[Mike Lewis]

Sorry, I should have added ....

After you have done the[tt] REPLACE ItemCode WITH CHR(160)[/tt], you then get rid of all the records which still have a blank ItemCode. Either delete records where [tt]ISBLANK(ItemCode)[/tt], or set a filter on [tt]NOT ISBLANK(ItemCode)[/tt]. That will just leave the first of those records, which I think is what you want.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Well, if both tables contain empty item codes there you have your reason for the inner join to still list them

Instead of the where clause add the condition to the join:

SELECT DATE, NAME, ITEMCODE, ITEMS.ITEMDESC, PRICE ;
FROM TRANS ;
INNER JOIN ITEMS ;
ON TRANS.ITEMCODE = ITEMS.ITEMCODE AND NOT EMPTY(ITEMS.ITEMCODE)

Chriss

 

[Chris Miller]

Maybe the fields only look empty but are null. If VFP has SET NULLDISPLAY TO '' you can't distinguish between null and blank.

Anyway, you used isblank(). I'm not sure if with success or not. What speaks for NULL also is that it is very sensible inner joins with nulls should not work but may make a difference in Clipper vs VFP.

Chriss

 

[wrgt9740]

Mike, I followed your advise, and I cannot understand the result. Before, the report produced two groups of 35-rows of blank itemcode. After your code, the report showed the first group with one blank itemcode, while the second group disappeared. What I don't understand is the reccount() on the report is 100, but only 31 showed up.

Chris, your suggestion on ON TRANS.ITEMCODE = ITEMS.ITEMCODE AND NOT EMPTY(ITEMS.ITEMCODE) produced the same results with my WHERE ISBLANK(TRANS.ITEMCODE) = .F., that all records with blank itemcode did not appear on the report. I don't know which code is faster or more efficient.

I didn't touch SET NULLDISPLAY TO " so it should still be at the default setting.

 

[Chris Miller]

produced the same results...that all records with blank itemcode did not appear on the report.

So isn't that the solution to your problem, either using your own or my suggestion?


Chriss

 

[wrgt9740]

Almost. The Clipper report only showed one blank row per group of 35 blank rows, and I was hoping to produce similar results.

 

[Mike Lewis]

The Clipper report only showed one blank row per group of 35 blank rows, and I was hoping to produce similar results.

That is what I understood that you wanted. And I thought my suggestion would achieve that. In what way didn't it work?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[wrgt9740]

In the Clipper report, there two groups of blank itemcode and only one record per group are shown. In the Foxpro report with your code, the first group showed only one record, which is fine, but the second group disappeared.

At the end of the report, reccount() displayed 100 records, but the number of records that I see in the report is 31.

 

[Chris Miller]

When the clipper report has two groups with each one empty record, there would need to be a distinction in the empty itemcodes, but is there? Is there anything differrent in the itemcodes, Mike mentioned chr(160) also is a space character, more general white space also includes tabs, and may extend to llines feeds, carriage return and surely more I forgot.

If there is no such distinction, there won't be two groups.

At the end of the report, reccount() displayed 100 records, but the number of records that I see in the report is 31.

Reccount() is not the numbe of records the report printed. Usually last operation count is in _tally, but that'll not be counting report lines. Reccount() is simply the record count of the currently selected alias. Isn't your report using relations between the main report driving workarea and other workareas, or do you print the cursor alias "CURSOR"?

If you print one cursor, the current workarea, then the 100 reflects the number of records, if your REPORT FORM has a FOR condition that reduces the number of records printed, if the report has "remove line if blank" that can reduce the number of lines printed, dito when you work with supressing repeated values. The feature of repressing repeated values could cause just one empty itemcode printed, if record main sort order is the itemcode, if the main order is something else, the two empty groups could be separated by a non empty group and then would be printed, so the order of records also matters.


Chriss

 

[Mike Lewis]

When I suggested you use a non-breaking space, I also suggested that you delete or out-filter the remaining records with a non-blank item code. If you did that, that would explain the discrepancy in the record count. RECCOUNT() gives you the total number of records in the controlling cursor, including those that were deleted or filtered out. The report doesn't show those records.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

If you're wondering now, wrgt9740, we're both right. To add one more fact to it, RECCOUNT() will also refelect deleted records of a table. Since in your case it's about the cursor you prepared for the report this will not matter, since no records are deleted, you only insert records into an initially empty cursor.

To find out how many records are processed in the report you could add a report variable with initial value 0 and calculation type set to "count" and look at that. It should be 100, too, even if lines are suppressed by "remove line if blank" or "don't print repeated values".

Chriss

 

[wrgt9740]

Chris, I opened the items.dbf and trans.dbf into query (it looked like a grid). I clicked on some of the blank itemcode, and the cursor would appear at the beginning of the cells. So I guess they're really blank.

Mike, I followed your suggestion and deleted the records where ISBLANK(itemcode) after replacing the blank records with CHR(160). The first group showed only one record but the second group disappeared.

Sorry about my limited knowledge about RECCOUNT(), and also about Foxpro. I only learned how to program from books, forum discussions, and videos I found on the internet.

 

[Chris Miller]

To check for NULL you could ? ISNULL(field) or even simpler explicitly SET NULLDISPLAY TO '.NULL.' and then every null value wills show. I know you say you didn't set it to anything else, but any third party or Foxpro foundation class code could do, it's not a seldom setting to let users not see a puzzling value .NULL. but show it as blank.

You mention "query", it's a bit vague what you mean. A query, SQL or software or a tool named query, or a qry file?
Anyway, I think you mean a browse grid. That actually is a grid, indeed, a grid with its own window. You can reveal that when you BROWSE NAME oBROWSE and then ?oBROWSE.class. And yes, a grid doesn't have a title bar and Window buttons, so a browse window it's not really just the grid object the NAME clause allows you to access. oBROWSE.Parent is unset. But you can usually HIDE WINDOW aliasname, so it''s a VFP Window.



Chriss

 

[wrgt9740]

Yes, the "query" I mentioned is a browse grid. I typed in the command window SELECT * FROM TRANS ORDER BY ITEMCODE and a query appeared in the data session window. The results of ? ISNULL(itemcode) are .F. on the blank itemcode. Out of curiosity, I also tried ? ISBLANK(itemcode) and they came up as .T. I moved the record pointer to a record with letters on itemcode, and ISBLANK(itemcode) result is .F. When I SET NULLDISPLAY TO ".NULL.", nothing showed up on the blank itemcode.