Linking Two Fields in One Table to Another Table and One Field

[aj3221]

I'm not sure if this can be done or if it would end up being a formula & a subreport but I thought I would ask before I start.

I have two tables that have a few common fields to link on - Item # for one. However, one table has two different unit of measure fields. One is the stocking unit of measure and the other is the selling unit of measure. The table I want to link them to has only one unit of measure field as it can have multiple records.

Table One: Item#, Stocking UOM, Selling UOM
Table Two: Item#, UOM

Table One: ABC123, BX, CS

Table Two: ABC123, BX
ABC123, CS

Is there a way to link these 2 in one report?

Thank you in advance for any assistance/advice.

 

[Charliy]

LInk on itme number, then include something like:
{table1.unit_of_measure} in [{table2.stocking_uuom},{table2.selling_uom}]

 

[aj3221]

Thank you for the reply Charliy. I tried that and get a true/false result but I think I might have done it wrong or explained it wrong.

Table 1 has two units of measure in two different fields (unitstock and unitsell are the actual field names). I wasn't able to create the exact formula you gave as I don't have a table1.unit_of_measure field (this has 2 fields per item). Table 2 has one field for unit of measure and will have 2 separate records for the same item.

I ended up doing:

{table2.units} in [{table1.unitstock},{table1.unitsell}]

That's when I get the true/false result.

Thank you again!

 

[Charliy]

that formula needs to be part of your record selection. It will select the ones that you're currently seeing as True