Using LIKE and referencing data fields in other tables to find matches

[0sprey]

Some part numbers provided by different vendors are similar but may have a vendor specific prefix or suffix added for their unique purposes.

I want to compare vendor specific part numbers against generic named stock numbers currently held in stock...looking for matched items.

Would the following approach work ?


IF {vendor.part_num} LIKE '*' + {stock.part_num} + '*' THEN 1 ELSE 0


I will be able test this formula soon but wondering if the logic is correct and if perhaps there are other ways to do this task.

Thanks

 

[kray4660]

It looks correct to me (provided that the stock.part_num is a string). Seems like I have done something similar in the past.

 

[lbass]

How are you linking the tables?

-LB

 

[0sprey]

CR XI and both fields are text.

The tables (MS Access) are completely separate and linked only within the Database Expert of CRXI.

{stock.part_num} is linked to {vendor.part_num}

LEFT table is tbl.stock (So I have rearranged the position of the fields in the formula below so things look visually similar)

IF {tbl_stock.part_num} LIKE '*' + {tbl_vendor.part_num} + '*' THEN 1 ELSE 0


Tables/Fields are linked in the following manner:

Join Type : Left Outer Join
Enforcement : Not enforced
Link Type: “=”

(PS :I have only made minimal changes to the Join Types (changing between Left Outer, Right Outer mostly) when working with Joined Tables in the past… so knowledge somewhat limited on this topic.)


For this to work would I have to link “stock.part_num” to '*' + {vendor.part_num} + '*' ? I realize this cannot be accomplished since the formula is not selectable in ‘ Database Expert / Links ‘.
Perhaps I have to link on other fields common to both tables ?

 

[lbass]

You can’t link the tables on the part fields, because you will only get exact matches back. Instead, create a main report using only the stock table. Insert a group on part number and then insert a subreport in the group header. Use only the vendor table for the subreport, and group on the vendor.partnumber. Suppress the detail and group footer sections in both subreport and main report. In the subreport expert, select {stock.partnumber} as the linking field, but do not link it to any field in the vendor field list. Instead, in design mode, open the subreport and go to report->selection formula->record and enter:

{vendor.partnumber} like “*”+{?pm-stock.partnumber}+”*”

This will return a list of vendor part numbers that contain the stock.partnumber—one list for each stock part number.

When you implement this, do not copy my formula as the quotes are from a different font set—just manually type the formula into the selection formula area of the subreport.

-LB

 

[0sprey]

LB,

Thanks for this technique. It took me a couple of tries but in the end I got it to work.

For future reference, would this change to the formula work as well or are things more complex than that >> NOT ({vendor.partnumber} like “*”+{?pm-stock.partnumber}+”*”)

Osprey

 

[lbass]

What is the end result you want to achieve? If you use your not formula, ALL vendor part numbers that aren’t like the current partnumber will appear, so say you have stock partnumber = 123 and the current report shows vendor parts like 1-123-89, 92-123, 123-est. Using the not formula would return: 456, 789, 596, etc., for EACH stock part number.

-LB

 

[0sprey]

Ok I see what you mean.

I was thinking if this technique could be used to list those records where the match is determined by the Like operator and not by an exact match (equal).

Stock Vendor
A123 vs 123 ( Like ) - Select
F234 vs F234 ( Equal) - slip

Perhaps some type of record suppression would work for this ?

 

[lbass]

Within the subreport, change the record selection formula to:

(
{vendor.partnumber} like “*”+{?pm-stock.partnumber}+”*” and
{vendor.partnumber}<>{?pm-stock.partnumber}
)

-LB

 

[0sprey]

Perfect! Thank you for all this insight.