cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
stephenJames
Level 8

Create Collection from 2 separate collections

Hello,

         I am having a bit of trouble collected data from 2 collections into a single collection.

So my process first collects two sharepoint lists into collections which happens without issue. What i am trying to do from here is match one record in List 1 with List 2 and merge those records into a new collection. 

 

Both collections have different columns names execpt for 1 which is a choice column in both collections. How do i get collection1 to match that column with Collection 2 column and then take all the data for the record in Collection1 and the same for collection2 and add it to collection3.

 

I hope i have explained that well. If not please let me know if any additional information is required. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Create Collection from 2 separate collections

Hi @stephenJames ,

Does the one record in Collection 1 only matches single one record in Collection 2?

 

I assume the one record in Collection 1 only matches single one record in Collection 2, I have made a test on my side, please consider take a try with the following workaround:

Set the OnSelect property of a "Merge" button to following:

ClearCollect(Collection3, Collection1, Collection2);   /* <-- Define the data structure for the Collection3 */
Clear(Collection3);
ForAll(
           Collection1,
           If(
               !IsBlank(LookUp(Collection2, ChoiceColumn.Value = Collection1[@ChoiceColumn].Value)),
               Collect(
                            Collection3,
                            {
                               Column1_Col1: Collection1[@Column1],            /* <-- Retrieve Columns data from Collection1 */
                               Column2_Col2: Collection1[@Column2],
                               ...
                               ChoiceColumn: Collection1[@ChoiceColumn],
                               ...
                               Column1_Col2: LookUp(Collection2, ChoiceColumn.Value = Collection1[@ChoiceColumn].Value).Column1,   /* <-- Retrieve columns data from your Collection2 */
                               Column2_Col2: LookUp(Collection2, ChoiceColumn.Value = Collection1[@ChoiceColumn].Value).Column2,
                               ...
                            }
               )
           )
)

After you press above "Merge" button, the matched records between Collection1 and Collection2 would be populated into the Collection3.

 

More details about the ForAll function, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-forall

 

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
mdevaney
Level 8

Re: Create Collection from 2 separate collections

@stephenJames 

I understand what you are trying to accomplish in general.  To give actionable advice I will need some more info from you.  Can you please post the following?

 

  • A sample of the data in Collection1
  • A sample of the data in Collection2
  • The result you'd like to see in Collection3
  • Notes on which columns are a complex SP type (Person, Lookup, Choice, etc).

 

Community Support Team
Community Support Team

Re: Create Collection from 2 separate collections

Hi @stephenJames ,

Does the one record in Collection 1 only matches single one record in Collection 2?

 

I assume the one record in Collection 1 only matches single one record in Collection 2, I have made a test on my side, please consider take a try with the following workaround:

Set the OnSelect property of a "Merge" button to following:

ClearCollect(Collection3, Collection1, Collection2);   /* <-- Define the data structure for the Collection3 */
Clear(Collection3);
ForAll(
           Collection1,
           If(
               !IsBlank(LookUp(Collection2, ChoiceColumn.Value = Collection1[@ChoiceColumn].Value)),
               Collect(
                            Collection3,
                            {
                               Column1_Col1: Collection1[@Column1],            /* <-- Retrieve Columns data from Collection1 */
                               Column2_Col2: Collection1[@Column2],
                               ...
                               ChoiceColumn: Collection1[@ChoiceColumn],
                               ...
                               Column1_Col2: LookUp(Collection2, ChoiceColumn.Value = Collection1[@ChoiceColumn].Value).Column1,   /* <-- Retrieve columns data from your Collection2 */
                               Column2_Col2: LookUp(Collection2, ChoiceColumn.Value = Collection1[@ChoiceColumn].Value).Column2,
                               ...
                            }
               )
           )
)

After you press above "Merge" button, the matched records between Collection1 and Collection2 would be populated into the Collection3.

 

More details about the ForAll function, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-forall

 

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

stephenJames
Level 8

Re: Create Collection from 2 separate collections

Thanks for the suggestion @v-xida-msft . I am currently trying to test this out and see if it works and will update when possible. 

 

@mdevaney  To give a little more information as you requested.

 

Collection 1 Contains data such as

First Name, Last Name, telephone Number, Job Title, Cube Number

Collection 2 Contains more specific location data

Cube Number, xCoordinate, yCoordinate, Height, Width

Collection 3 i am wanting to combine these two

First Name, Last Name, Telephone Number, Job Title, Cube Number, xCoordinate, yCoordinate, Height, Width.

 

The Location data from collection 2 feeds an interactive office map i am creating. so the x,y,height and width all represent a plotting system.

The only reason i am looking at combining collection 1 and 2 is that i think it would be easier to have all the data needed for any screen in one collection. As the data in collection 1 changes based on a employee the user selects on previous screens. 

The idea is that as the data from collection 1 changes on each click the data in collection3 will update with all the new information.

So for example

On app start employee data is loaded into collection 1 

Location Data from a sharepoint list is loaded into collection 2

Collection 3 is created from the data of collection 1 and collection 2 data is added based on the matching of the cube number column from each collection. 

 

The app user then selects a new employee.

Collection 1 is cleared and the newly selected employee data is collected.

Collection 3 is cleared and updated with the new employee data plus new location data from collection 2 is matched and added to the collection. 

 

I am not sure if this is the most efficient way of doing it. If there is a way to display data on my office map by matching the cube number column from collection 1 and 2 instead of making a third collection to merge the data.  

 

Here is a snip of my on start action

//First Collect All Users
//ClearCollect(colAll, EmpListDir);
ClearCollect(colAll,ShowColumns(EmpListDir,"FirstName","Last_Name","FullName","JobTitle","Manager","WorkPhone","Email","Department","Division","OData__Location","SAOJobTitle","ImageURI"));

//Second Collect Current user from collection
ClearCollect(colEmployee, LookUp(colAll,Lower(Email)=Lower(currentUser)));
//Collect Map info ClearCollect(colSeating,ShowColumns(OfficeLocations,"OData__Location","Height","Width","xCoord","yCoord"));

The OData__Location column in both lists is the cube number column that is a choice field is the sharepoint lists. 

colAll is a collection that pulls all the data from the sharepoint list EmpListDir. 

Collections are made from this colAll collection as i thought it would be more effient to pull the data from the sharepoint list once and then filter,sort, lookup from the collection instead of connecting to the sharepoint list each time. 

 

I hope this provides a little more information 

mdevaney
Level 8

Re: Create Collection from 2 separate collections

@stephenJames 

I will wait to see your results with the solution provided by @v-xida-msft since as you mentioned you are already going down that path.

stephenJames
Level 8

Re: Create Collection from 2 separate collections

@v-xida-msft your suggestion worked great. I was able to plug in my collection data and retrieve the columns and merge the two collections. Thanks

 

@mdevaney Thank you for your help. 

 

For anyone needing this information here is my code plugged into @v-xida-msft suggestion.

 

ClearCollect(colEmpMaster, colEmployee, colSeating);   /* <-- Define the data structure for the colEmpMaster */
Clear(colEmpMaster);
ForAll(
           colEmployee,
           If(
               !IsBlank(LookUp(colSeating, OData__Location.Value = colEmployee[@OData__Location].Value)),
               Collect(
                            colEmpMaster,
                            {
                               FirstName: colEmployee[@FirstName],            /* <-- Retrieve Columns data from colEmployee */
                               Last_Name: colEmployee[@Last_Name],
                               FullName: colEmployee[@FullName],
                               JobTitle: colEmployee[@JobTitle],
                               Manager: colEmployee[@Manager],
                               WorkPhone: colEmployee[@WorkPhone],
                               Email: colEmployee[@Email],
                               Department: colEmployee[@Department],
                               Division: colEmployee[@Division],
                               ImageURI: colEmployee[@ImageURI],
                               Location: colEmployee[@OData__Location],
                               Height: LookUp(colSeating, OData__Location.Value = colEmployee[@OData__Location].Value).Height,   /* <-- Retrieve columns data from your Collection2 */
                               Width: LookUp(colSeating, OData__Location.Value = colEmployee[@OData__Location].Value).Width,
                               xCoord: LookUp(colSeating, OData__Location.Value = colEmployee[@OData__Location].Value).xCoord,
                               yCoord: LookUp(colSeating, OData__Location.Value = colEmployee[@OData__Location].Value).yCoord
                            }
               )
           )
);

 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 236 members 5,216 guests
Please welcome our newest community members: