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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,683)