Finding text using a reference table

[BeeleJa]

Within my Table1 I have a MainWo, ItemID-Standard and Product Group.

I want to add 1 comlumn that displays the alternatie Item based on a reference table2

The relation between the Table1 and Table2 is a many to many

Note: The RecID of Table2 is unique for each combination ItemID  - AltItemID

Example:

ItemID_Standard 7000066A has 2 alternatives 7900001 and 7900050

In this example 7900001 is also present in the ItemID_Standard

Output1 for ItemID_Standard 7000066A is 7900001 and 

Output1 for ItemID_Standard 7900001 is 7000066A

The product group for ItemID_Standard 7000066A is Service and 7900001 is Part

Output2 for the combination 7000066A - 7900001 - (79000010)  Part = 0

Outpur2 for the combination 7900001 - 7000066A - (7000066A) Service = 1

Table1 & desired output

WOMAIN	ItemID_Standard	ProductGroup	Output1 - alternative is also present in ItemID_Standard	Output2 - if the product group of the present alternative is not Service than value is 0
W0001	4951014	Parts	#N/A	#N/A
W0001	4952384	Parts	#N/A	#N/A
W0001	7000066A	Service	7900001	0
W0001	7000104	Parts	#N/A	#N/A
W0001	7000104	Service	#N/A	#N/A
W0001	7006318	Parts	#N/A	#N/A
W0001	7006653	Parts	#N/A	#N/A
W0001	7006653	Service	#N/A	#N/A
W0001	7006771	Parts	#N/A	#N/A
W0001	7006772	Parts	#N/A	#N/A
W0001	7006887	Service	#N/A	#N/A
W0001	7006887	Service	#N/A	#N/A
W0001	7900001	Parts	7000066A	1
W0001	7900007-1	Parts	#N/A	#N/A
W0001	7900007-2	Parts	#N/A	#N/A
W0001	M83485/1-011	Parts	#N/A	#N/A
W0001	M83485/1-012	Parts	M83485-1-012	0
W0001	M83485/1-013	Parts	#N/A	#N/A
W0001	M83485-1-012	Parts	M83485/1-012	0
W0001	MS15795-848	Parts	#N/A	#N/A
W0001	MS21043-3	Parts	#N/A	#N/A
W0001	MS21043-4	Parts	#N/A	#N/A
W0001	MS21043-6	Parts	#N/A	#N/A

Table 2 - Reference table 

ItemID	AltItemID	RecID
7000066A	7900001	5637146272
7000066A	7900050	5637146275
7003793	7006318	5637146805
7004291	7006887	5637147606
7004365	7006771	5637147647
7004477	7006653	5637147756
7004477A	7001576B	5637146637
7004477A	7004477	5637147738
7004477A	7006653	5637147744
7006318	7003793	5637146806
7006653	7001576B	5637224078
7006653	7004477	5637147757
7006653	7004477A	5637147746
7006771	7004365	5637147648
7006773-1	7001126-1	5637146537
7006773-1	7900007-1	5637147880
7006773-2	7001126-2	5637146541
7006773-2	7900007-2	5637147887
7006887	7004291	5637147607
7006887	7004291A	5637147587
7006887	7007072	5637147952
7006887	7900013	5637147897
7006887	7900044B	5637147949
7900001	7000066A	5637146274
7900001	7900050	5637147872
7900007-1	7006773-1	5637147879
7900007-2	7006773-2	5637147886
M83485/1-011	M83485-1-011	5637152111
M83485/1-012	M83485-1-012	5637152117
M83485/1-013	M83485-1-013	5637152121
M83485-1-011	M83485/1-011	5637152112
M83485-1-012	M83485/1-012	5637152118
M83485-1-013	M83485/1-013	5637152122
MS21043-3	MS21046C3	5637205386
MS21043-6	NAS1291C6M	5637152155
MS21044N3	MS20365-1032C	5637152425
MS21046C3	MS21043-3	5637205385
NAS1291C6M	MS21043-6	5637152154

[v-henryk-mstf]

Hi @BeeleJa ,

It is not yet clear what you are looking for. Are you able to provide test data (remove sensitive information) and desired results, I will answer you as soon as possible.

Similar to Find, SEARCH, CONTAINS, TREATAS and other query functions are helpful to you. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BeeleJa]

Dear @Henry,

I have revised the Table1 information and added the table 2 information.

In short wording:

Check #1

From Table1 [ItemID_Standard] find the related Table2 [AltItemID] - can be multiple

Check if the found Table2 [AltItemID] is present in the Table 1 [ItemID_Standard]

If present than the output is Table2 [AltItemID] else leave blank

Check #2

From the output in Check#1 get the Table1 [ProductGroup] that is connected to the Table2 [AltItemID] in the Table1 [ItemID_Standard]

Output Table1 [ProductGroup] or when Table1 [ProductGroup] is 'Service' then 1 else 0

Hope this is more clear

Regards

Beeleja