Looking up data in one table from a VC reference

[odarkthirty]

Okay I am sure there is a simple answer but I have been trying to no avail

I have a VC that is a reference to a table with SKUs that are related to a product its a 1:n relationship where 1 is the inventory and n is the related SKUs. So for a product say there could be 2 relationships.

I need another column that would take the VC values (the 2 relationships from above) of vendor_sku AND check if it exists in another table that is a reference of the SKU to the vendor SKU and that way I can count how many stock items we have.

Maybe I am making this complicated, here is the formula I am using:

SELECT(inventory_adjustment[adjustment], IN([vendor_sku], [related_vendor_inventory_skus][vendor_sku]))

Expression clears but then what happens is EVERY row comes back instead of what is actually just in the related skus - the 2 results.

Hopefully I am explaining this right.

[Suvrutt_Gurjar]

I believe you may wish to elaborate a bit more on table structure. Are there 2 or three tables involved and how these are referenced to each other.

[odarkthirty]

Absolutely. So we have several tables:

inventory = inventory table
vendors = vendors table
vendor_sku = relationship of a vendor sku to inventory sku as there can be multiple vendors that fill in the inventory part
inventory_adjustment = inventory adjustments for stock +/- and it uses vendor_sku as the relationship

In the inventory table I have a VC REFROW that is showing Vendor SKU relationships, now I have another column for STOCK that is also VC in this is where the formula is where I want to search the inventory_adjustment to check for every Vendor SKU in that related inventory and count the adjustments and this is the formula:

SELECT(inventory_adjustment[adjustment], IN([vendor_sku], [related_vendor_inventory_skus][vendor_sku]))

related_vendor_inventory_skus is a VC of REF_ROWS(“vendor_inventory_sku”, “inventory_inventory_id”)

[Suvrutt_Gurjar]

Hi @odarkthirty,

Thank you for more details. Could you please add the description in following manner because I believe you may still need to elaborate on table relationships between various tables.

1. Inventory parent ( SKU) to Vendor_SKU ( Child)
   and so on for all tables

odarkthirty:

SELECT(inventory_adjustment[adjustment], IN([vendor_sku], [related_vendor_inventory_skus][vendor_sku]))

In which table the above expression is ?

odarkthirty:

REF_ROWS(“vendor_inventory_sku”, “inventory_inventory_id”)

In which table above expression is?

[odarkthirty]

Thanks again for quick response, how about some photos maybe that will help.

Inventory table

[odarkthirty]

vendor_inventory_sku

[odarkthirty]

inventory_adjustment

And the formula I am using is to SUM the total stock in stock adjustment based on what stock skus are associated with the inventory part

Let me know if this helps or not?

[Suvrutt_Gurjar · Accepted solution]

Hi @odarkthirty,

Thank you. The images definitely helped.

I believe you may do the needful in two steps-

1. Create a VC in the vendor_inventory_sku table with an expression something like below

SUM ( SELECT (inventory_adjustment[adjutment], [vendor_sku] = [_THISROW].[vendor_inventory_sku_id]))

Say this VC is called [vendor_sku_total]

2.Create another VC in the inventory table with an expression like

SUM([related_vendor_inventory_skus][vendor_sku_total])

This VC is the [stock_level] VC you wish to create in the inventory table

[odarkthirty]

Thank you! Way to make this simple I knew I was in my head with it. Okay one more similar I need to add location tracking and there can be multiple locations for each sku

Would I just create a location table reference it in the adjustment table and then do the same thing and bring rows into the vendor sku table then list in inventory?

Just thinking sounds like I would do it same as this.

Appreciate the help!

[Suvrutt_Gurjar]

Thanks for the update and good to know that it works the way you wish.

Regarding location, you may wish to share more details on the location table etc. so that community could offer more definitive suggestion. In general, if the construct is similar as sku quantity, you could use similar approach.