Filling In Blank Fields in Report

[mikeinfbg]

Version: CR XI

Table: MENUITEM Table: EXCEPT
Fields: menuitem.PLU Fields: except.PLU
except.custno


The MENUITEM table contains all of our products that we sell.
The EXCEPT table contains any special customers that get discounts and the Items (PLU) on which those discounts apply.

The EXCEPT table has to be manually updated. So between time periods if a new MENUITEM is added, we have to manually update the EXCEPT table.

I have created a report that shows ALL the special customers and their items they are qualified for. In addition it lists any item on MENUITEM that isn't on the EXCEPT table, but in the Customer column it is of course blank.

My Current Report:
CustNo. PLU PRICE
a567 $7.00
a568 $8.50
c221 $2.50
. . . ..
C5 d999 $1.00
C5 e159 $9.00
C8 d999 $1.00
C8 e159 $9.00
. .. .
What I want the report to ultimately do is to reprint a report that 'pairs off' each line (or new PLU) that doesn't contain a CustNo with one of the existing CustNos within the report.

Desired Report:
CustNo. PLU PRICE
C5 a567 $7.00
C5 a568 $8.50
C5 c221 $2.50
C8 a567 $7.00
C8 a568 $8.50
C8 c221 $2.50
etc...
. . . ..
C5 d999 $1.00
C5 e159 $9.00
C8 d999 $1.00
C8 e159 $9.00

Thanks for your help.

Mike

 

[lbass]

You might try linking an alias (Except_1) to the Except table, and then use a formula like this for the customer number:

if isnull({except.custno}) then
{except_1.custno} else
{except.custno}

This will probably result in more rows than you really want, but it might get you closer to what you are trying to do.

-LB