cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jsunnb
Frequent Visitor

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

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User
Super User

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

 

View solution in original post

2 REPLIES 2
WarrenBelz
Super User
Super User

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

 

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.

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (2,710)