Getting a specific value from a related table.
I'm creating a list view that will display various information about a defined list of products. To put it simply:
Table 1 (SKU LIST)
SKU -> a numerical product ID - This is the related field between the 2 tables
SKU - SKU DESCRIPTION
1001 - Product A
1002 - Product B
1003 - Product C
Table 2 (SKU DC - lists the stock levels of each SKU at each Distribution Center, SKU's are listed up to 5 times as there are 5 DC's)
DC - a specific DC
AV - units of SKU at Specific DC
IC - units of SKU incoming at Specfic DC...
SKU - DC - AV - IC
1001 - EAST - 10 - 5
1001 - WEST - 10 - 5
1001 - CENTRAL - 10 - 5
1002 - EAST - 20 - 0
1002 - WEST - 20 - 0
1002 - CENTRAL - 20 - 0
what I'm trying to do is put the following in a List Layout so that for each SKU listed in Table 1 (SKU LIST) I see the following information:
SKU - AV_EAST - AV_WEST - AV_CENTRAL - IC_EAST - IC_WEST - IC_CENTRAL
1001 - 10 - 10 - 10 - 5 - 5 - 5
1002 - 20 - 20 - 20 - 0 - 0 - 0
AV_EAST... are calculation fields I need to create. I'm just not sure how to specify to lookup the specific AV of each DC.
I was looking at different functions but can't find the one to display what I want.