cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lalit_Kadam
Helper III
Helper III

Join multiple entity in one collection

Hi,

I am trying to add multiple entities in one Collection.  but it is not working. I have four tables. (Chemical, Labs, chemicalOrder and Location).

Chemical and chemical_Order entities  are connected based on chemical_id.

Chemical_order and LABS entities  are connected based on Lab_ID.

LABS and Location entities are connected based on Location_id.

Attached code is not showing correct result.Need your help here to correct this code. Please suggest.

 

 

 

Regards,

Lalit Kadam

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Lalit_Kadam ,

Based on the scenario that you mentioned, I afraid that there is no better way to join multiple entities and show in the single gallery for performance purpose.

You could modify your formula as below:

ClearCollect(
             CombinedCollection, 
             AddColumns(Chemicals, "ChemicalOrderID", "", "Lab_ID", "", "Lab_Name", "", "Location_Name", "")
);
ForAll(
      ChemicalOrders,
      Patch(
            CombinedCollection,
            LookUp(CombinedCollection, ChemicalId = Chemical_ID),
            {
              ChemicalOrderID: crb05_chemicalorderid,
              Lab_ID: crb05_lab_id
            }
      )
);
ForAll(
       LABS,
       Patch(
             CombinedCollection,
             Patch(
                   CombinedCollection,
                    LookUp(CombinedCollection, Lab_ID = LABS[@Lab_ID]),
                   {
                      Lab_Name: crb05_lab_name
                   }
             )
       )
);
ForAll(
       Locations,
       Patch(
             CombinedCollection,
             LookUp(CombinedCollection, Lab_ID = Locations[@Lab_ID]),
             {
                Location_Name: crb05_location_name
             }
       )
)

please type above formula within the OnStart property of App, when you load your canvas app, above formula would be executed.

 

If you want to improve your app's performance, please check and see if the following article would help in your scenario:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/performance-tips

 

If you want to get rid of Delegation limit in your canvas app, please make sure you have applied delegable function to data source. More details about Delegable functions supported in CDS, please check the following article:

https://docs.microsoft.com/en-us/connectors/commondataservice/

Note: Please make sure you have set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app.

If there are more than 2000 records stored in your Entities, please check and see if the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

Note: The AddColumns function is not a Delegable function in PowerAppd, so you could not use it to process more than 2000 records once time.

 

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

4 REPLIES 4
gabibalaban
Dual Super User
Dual Super User

Try, first, to define your SearchResultCollection with all the necessary columns, something like this:

 

ClearCollect(SearchResultCollection,AddColumns(Chemicals,"ChemicalOrderID","","Lab_ID","","Lab_Name","","Location_Name",""))

 

and then do the patch thing (without calling anyother ClearCollect() function).

 

Please note that it is not recommended to do large processing  data on the client side.

Try do to this kind of things on the server side.

v-xida-msft
Community Support
Community Support

Hi @Lalit_Kadam ,

Based on the screenshot that you mentioned, I think there is something wrong with your third ClearCollect formula in your app. When you use ClearCollect function to create a connection, it would clear the collection data firstly, then populate it with new table value.

 

I have made a test on my side, please consider take a try with the following formula (modify your formula as below😞

ClearCollect(Col1, AddColumns(Chemicals, "ChemicalOrderID", "", "Lab_ID", ""));
ForAll(
      ChemicalOrders,
      Patch(
            Col1,
            LookUp(Col1, ChemicalId = Chemical_ID),
            {
              ChemicalOrderID: crb05_chemicalorderid,
              Lab_ID: crb05_lab_id
            }
      )
);
ClearCollect(
             Col2,
             AddColumns(Col1, "Lab_Name", "")
);
ForAll(
       LABS,
       Patch(
             Col2,
             Patch(
                   Col2,
                    LookUp(Col2, Lab_ID = LABS[@Lab_ID]),
                   {
                      Lab_Name: crb05_lab_name
                   }
             )
       )
);
ClearCollect(Col3, AddColumns(Col2, "Location_Name", ""));
ForAll(
       Locations,
       Patch(
             Col3,
             LookUp(Col3, Lab_ID = Locations[@Lab_ID]),
             {
                Location_Name: crb05_location_name
             }
       )
)

then multiple Entities data would be saved into the Col3, you could use the Col3 as data source in your canvas app.

 

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.

Yes this way of code is working. I tried and it is working  as acceptation.

Is it decrease the performance of application? Do you have any other way to join multiple entities and show in the single gallery for performance purpose?

Please also suggest if we can avoid delegation limit also here while joining entities.

 

Hi @Lalit_Kadam ,

Based on the scenario that you mentioned, I afraid that there is no better way to join multiple entities and show in the single gallery for performance purpose.

You could modify your formula as below:

ClearCollect(
             CombinedCollection, 
             AddColumns(Chemicals, "ChemicalOrderID", "", "Lab_ID", "", "Lab_Name", "", "Location_Name", "")
);
ForAll(
      ChemicalOrders,
      Patch(
            CombinedCollection,
            LookUp(CombinedCollection, ChemicalId = Chemical_ID),
            {
              ChemicalOrderID: crb05_chemicalorderid,
              Lab_ID: crb05_lab_id
            }
      )
);
ForAll(
       LABS,
       Patch(
             CombinedCollection,
             Patch(
                   CombinedCollection,
                    LookUp(CombinedCollection, Lab_ID = LABS[@Lab_ID]),
                   {
                      Lab_Name: crb05_lab_name
                   }
             )
       )
);
ForAll(
       Locations,
       Patch(
             CombinedCollection,
             LookUp(CombinedCollection, Lab_ID = Locations[@Lab_ID]),
             {
                Location_Name: crb05_location_name
             }
       )
)

please type above formula within the OnStart property of App, when you load your canvas app, above formula would be executed.

 

If you want to improve your app's performance, please check and see if the following article would help in your scenario:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/performance-tips

 

If you want to get rid of Delegation limit in your canvas app, please make sure you have applied delegable function to data source. More details about Delegable functions supported in CDS, please check the following article:

https://docs.microsoft.com/en-us/connectors/commondataservice/

Note: Please make sure you have set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app.

If there are more than 2000 records stored in your Entities, please check and see if the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

Note: The AddColumns function is not a Delegable function in PowerAppd, so you could not use it to process more than 2000 records once time.

 

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.

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (3,676)