cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ztb97
Helper II
Helper II

Performing Inner Join on Two Collections

Hello all, I am currently following along to Power Apps - Joining Collections - SQL (Inner) Join - YouTube in order to perform an inner join on two collections I have in my PowerApp.

 

The two collections I am attempting to perform this on are the resourcesColl collection and the orderIds collection. In the resourcesColl collection, there is an "Id" column which matches/maps to the "WorkCenterID" column in the orderIds collection.

 

Here is my formula/code:

innerJoinError.PNG

 

I am getting two errors:

1) The function 'Ungroup' has some invalid arguments

2) A column named 'CreatedBy' already exists (I am pretty sure this one is just being thrown because of the 1st error. I am not actually creating a column called 'CreatedBy' in this code)

 

Where am I going wrong?

Let me know if you need any further information.

 

Regards,

Zachary

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @ztb97 ,

 

I assume there are two tables:

ClearCollect(resourcesColl,{Id:"1",Column1:"1"},{Id:"2",Column1:"2"});
ClearCollect(orderIds,{WorkCenterID:"2",Column2:"1"},{WorkCenterID:"3",Column2:"2"});

You could use this formula to do the inner join:

ClearCollect(nameWithPoID,
   ForAll(
       Filter(resourcesColl,Id in orderIds.WorkCenterID) As TableA,
       Patch(LookUp(resourcesColl,Id=TableA.Id),LookUp(orderIds,WorkCenterID=TableA.Id))
   )
)

 

Best Regards,
Bof

View solution in original post

2 REPLIES 2
v-bofeng-msft
Community Support
Community Support

Hi @ztb97 ,

 

I assume there are two tables:

ClearCollect(resourcesColl,{Id:"1",Column1:"1"},{Id:"2",Column1:"2"});
ClearCollect(orderIds,{WorkCenterID:"2",Column2:"1"},{WorkCenterID:"3",Column2:"2"});

You could use this formula to do the inner join:

ClearCollect(nameWithPoID,
   ForAll(
       Filter(resourcesColl,Id in orderIds.WorkCenterID) As TableA,
       Patch(LookUp(resourcesColl,Id=TableA.Id),LookUp(orderIds,WorkCenterID=TableA.Id))
   )
)

 

Best Regards,
Bof

Great, that creates a new collection but only populates the "Id" and "WorkCenterID" columns in the nameWithPoID collection. How would I get this to populate two more columns in the collection? The column names I also wish populated are "Name" from resourcesColl and "ProcessOrderID" from orderIds collection

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,138)