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 Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,038)