Multiple LookUps in One Table - Creating Relationships for Individual Fields
I am trying to create a table that pulls specific data from another table.
Here are my current fields in the NEW table:
SKU Week1 Week2 InStock1 InStock2
I need the NEW table to populate InStock1 based on SKU and Week1 matching the original table and InStock2 based on SKU and Week2 matching the original table. I was trying to do this with 2 separate LookUps, one for each field, but it seems that I cannot create the appropriate relationships between the tables to do this.
Ex) If I create a relationship for SKU and Week1 then I can get the correct Lookup value for InStock1; but if I add a relationship for Week2 so that I can LookUp InStock2 niether field works.
Is there a way to create relationships between two tables for individual fields?
I have tried various calculations instead of a lookup but none seem to work without the correct relaiontships either.