Doing a AddColumns using LookUp tells me I'm comparing a Number to a Table

[Jsunnb]

Hello All, I was trying to follow the post here: Syntax for joining tables to emulate a SQL JOIN but somehow I just can't get it to work.

I have two tables as shown below

mT_SalesAttributeDB_FamilyMaterialGroupMaterials: ([ID], [MaterialGroupID], [MaterialID], [Comment], [Created], [Updated])

mT_SalesAttributeDB_Materials: ([ID], [MaterialID], [Description], [ShortDesc], [Abbr], [Superseded], [HasNoDrawing], [Created], [Updated])

However, when I try to add a column from the _Materials table to my existing, I get an error saying I'm trying to compare a Number to a Table. Here is what I"m trying to use

AddColumns(
    Filter(
        mT_SalesAttributeDB_FamilyMaterialGroupMaterials, 
        Text(tblMaterialGroups_Families.Selected.ID) = MaterialGroupID
    ), 
    "SpecName", 
    LookUp(
        mT_SalesAttributeDB_Materials, 
        ID = mT_SalesAttributeDB_FamilyMaterialGroupMaterials[@MaterialID], 
        MaterialID
    )
)   

I know the Filter() works just fine with testing, but when I embed it in the AddColumns and use that LookUp logic of "ID = mT_SalesAttributeDB_FamilyMaterialGroupMaterials[@MaterialID]" it tells me ID is a Number and the [@MaterialID] is a table. If I change it to "ID in mt_...." then it no longer errors, but only returns the first result from the _Materials Table instead of matching it and returning the JOIN.

How do I do a join to where they are linked without error?

I need to change it so it's less confusing but in the _Materials Table, MaterialID is the name and ID is the unique line number. And on the _FamilyMaterialGroupMaterial Table, it's MaterialID is the same as _Materials ID.

[WarrenBelz · Accepted solution]

Hi @Jsunnb ,

On the assumption that MaterialID is a Numeric column in mT_SalesAttributeDB_FamilyMaterialGroupMaterials, try this

AddColumns(
   Filter(
      mT_SalesAttributeDB_FamilyMaterialGroupMaterials, 
      Text(tblMaterialGroups_Families.Selected.ID) = MaterialGroupID
    ), 
    "SpecName", 
    LookUp(
       mT_SalesAttributeDB_Materials, 
       ID = MaterialID
    ).MaterialID
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Jsunnb]

THANK YOU.

I made a few other changes that I think also had an impact but I'm not clear which might have helped. 

1.) I changed the MaterialID in _Materials to MaterialName so I wouldn't have two fields with the same name and different meanings.

2.) _Material.ID is an INT in the SQL table, but for some reason I stored _FamilyMaterailGroupMaterials.MaterialID as a Varchar so I changed that to an INT as well.

At first when I implemented your solution it came back as blank which told me it wasn't linking at all, so I figured I'd best give it a try and change the tables themselves to be more clear. Thank you again.