Show items NOT Found in table

[jjenright]

I need to display on a report all items that DO NOT exist in a table. The item is entered with characters that I remove. I then want to see if this item with the characters removed exists in a table. If it DOES NOT I want to display it on my report.

Fo example : The item is entered as X-123.4-56. Removing the characters leaves 123456. I then want to see if this item is found in another table. If it DOES NOT display it on the report. The lookup table contains six digit numbers starting with a 1.

I am linking the Order table to the lookup table with a left outer join from the Order to Lookup table on the related item field.

Thank You

 

[lbass]

One way would be to create the replace formula in the main report and then insert a subreport which links the field in the second table to the main report formula. I would group on the formula in the main report and place the subreport in the group header_a section.

Then in the subreport, write a formula:

whileprintingrecords;
shared stringvar item := maximum({table.item});

Place this in the subreport report footer.

In the main report, show the group name in the GH_b section, and in the section expert, format GH_a to "underlay following section". Then, still in the section expert, select GH_b->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar item;
trim(item) <> ""; //note no colon

You should also format the subreport to "suppress blank subreport" and format GH_a to suppress blank section. Also suppress all sections WITHIN the subreport.

-LB