Retrieve data from a secondarily related table

Question

I'm still learning. Fortunately I have this forums help!

The ITEM tables has products in it. It is related to the ITEM MATERIAL tbl by ITEM ID. The ITEM MATERIAL table is related to the MATERIAL table by MATERIAL ID. I want to bring the NAME from the MATERIALS table into the ITEM table. Note that there may be multiple MATERIAL [Names] associated with a single Product (row) in the ITEMS tbl. I can either bring all of the "related" material names into new columns for each product but preferably they could populate a single column and be comma delimited.

Hopefully I have provided enough infromation. Thank you.

theoc · Answer

Hi&nbsp;Dave-ExpSC&nbsp;&nbsp;You can try use the following:&nbsp;Formula =CALCULATE (&nbsp;&nbsp;&nbsp; FIRSTNONBLANK ( MATERIAL[name], 1 ),&nbsp;&nbsp;&nbsp; FILTER ( ALL ( MATERIAL ), Material[material_ID] = ITEM MATERIAL[material_id] )&nbsp;)&nbsp;Hope this helps!&nbsp;&nbsp;Theo&nbsp;

dave-expsc · Answer

I can't quite make it work. I get this far and can not get the ITEM MATERIAL tbl to show --- do you see what the issue is? Thank you for your help.

Materials Count = (FIRSTNONBLANK(MATERIAL[name], 1),

FILTER( ALL(MATERIAL), MATERIAL[material_id] = ITEM MAT

theoc · Answer

Hi Dave-ExpSC&nbsp;&nbsp;Can you use the format that I have used in the original measure? The brackets and commas, etc, all need to match.&nbsp;Let me know how it goes and I can adjust if it doesn't work.&nbsp;Theo 🙂&nbsp;&nbsp;

dave-expsc · Answer

Oops I left off CALCULATE - that was important.However, if I copy your format exactly I still can't get the ITEM Material table to show up in the last part.&nbsp;

dave-expsc · Answer

I was playing around and I closed up the ")" on Material[material ID] and I was able to get the ITEM Material table. See formula below.&nbsp; This formula returns a single (same) value from the MATERIALS table for every row of products.&nbsp;&nbsp;Materials Count = CALCULATE (FIRSTNONBLANK ( MATERIAL[name], 1 ),FILTER ( ALL ( MATERIAL ), MATERIAL[material_id]),'ITEM MATERIAL'[material_id])&nbsp;"&nbsp;I want to bring the NAME from the MATERIALS table into the ITEM table. Note that there may be multiple MATERIAL [Names] associated with a single Product (row) in the ITEMS tbl.&nbsp; I can either bring all of the "related" material names into new columns for each product but preferably they could populate a single column and be comma delimited."&nbsp;To explain in another way. The MATERIALS table holds all of the materials that may be associated to a product through the material ID link and then to the ITEMS table (products) via the ITEM ID. There could be 3, 4, 5 materials in the Material table that are associated with a single product (row) in the ITEMS tbl. Can I bring them into a single column as comma delimited values OR bring in 3, 4, 5 additional columns for each product with the specific materials in each. Thank you for your help.&nbsp;UPDATE: I left off the "=" I put it back and I am back to having problems getting the ITEMS tbl.&nbsp;&nbsp;

Forum Discussion

Retrieve data from a secondarily related table

6 Replies

Related content

Filtercontext with date relation

Retrieving data via HTML

Cannot retrieve Contacts from API

Retrieving deleted notebook

Relation