cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft Employee

Used nested ForAll to build many-to-many relation in CDS

Hello there!

 

I had the need to relate 2 tables/collections, lookup dependent tables, and calculate values to come up with a Collection that can then be used to report out forecast quantities of items.

 

Here's the code and it worked! However...

 

 Clear(CC_MegaData);
 ForAll(CC_AVQ_PLAN,
 ForAll(Filter(CC_AVQ_BOM,BOM_CompositeName=PLAN_BOM_CompositeName,LookUp(CC_AVQ_AVPROJECTS,PrimaryId=PLAN_AVQ_PrimaryID).AVP_Region=Region_dd.Selected.Value),
      Collect(CC_MegaData, {ID:CC_AVQ_PLAN[@PrimaryId]
                           ,Region:Region_dd.Selected.Value
                           ,SubDistrict:LookUp(CC_AVQ_AVPROJECTS,PrimaryId=PLAN_AVQ_PrimaryID).AVP_SubDistrict
                           ,RoomType:PLAN_BOM_CompositeName
                           ,RoomQty:PLAN_BOM_RoomQuantity
                           ,PLAN_BOM_DEV_ID:BOM_DEV_ID
                           ,Make:BOM_DEV_Manufacturer
                           ,Model:BOM_DEV_Model
                           ,UnitCost:Text(LookUp(CC_AVQ_DEVICES,PrimaryId=BOM_DEV_ID).DEV_MSCost,"[$-en-US]$###,##.00")
                           ,DvcQtyInBOM:BOM_DEV_Quantity
                           ,DvcOdrQty:BOM_DEV_Quantity * PLAN_BOM_RoomQuantity
                           ,TotOrdAmt:Text((BOM_DEV_Quantity*PLAN_BOM_RoomQuantity)*LookUp(CC_AVQ_DEVICES,PrimaryId=BOM_DEV_ID).DEV_MSCost,"[$-en-US]$###,##.00")
                            }
                )
    )
 )CC_AVQ_PLAN

 

Collection: CC_AVQ_PROJECTS can have multiple CC_AVQ_PLAN and CC_AVQ_PLAN can have multiple entries from CC_AVQ_BOM and CC_AVQ_BOM can have multiple entries from CC_AVQ_DEVICES vice-versa just for CC_AVQ_BOM as well as CC_AVQ_PLAN. FYI...BOM=Bill Of Material. The result of relation is captured in CC_MegaData collection. Now, the  requirement is to have various views to the MegaData collection, though redundant, but makes grouping and general report manipulation work much faster.

 

HOWEVER...since ForAll is NOT delegable, I run the risk of accessing limited set of records. What I am looking is for someone to help evaluate how many outer ForAll (CC_AVQ_PLAN) records I should have to reach all the relation limits. I know this is slightly complex but I have exhausted my brain power it trying to imagine how non-delegable commands behave when nested. Extreme case would be let's say, inner ForAll fetches 500 record for every outer ForAll. Should I limit the outer ForAll data to just 1 record (impractical) or is it safe to assume that I can have 500 records for the outer ForAll as well? if latter's the case then I could have 500 x 500=250,000 records in my CC_MegaData collection which would be awesome and I'll be able to release the App confidently.

 

Additionally, if anyone has a better way of achieving this result with no limits confirmed, I am all ears and ready to learn.

 

Thank you very much in anticipation.

Manish

 

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff ShantanuP
PowerApps Staff

Re: Used nested ForAll to build many-to-many relation in CDS

For outer ForAll, you will have access to first 500 rows of CC_AVQ_PLAN.  And Forall should enumerate each of such record. If you want to have access to any records beyond 500 then you will need to apply delegable filter expression CC_AVQ_PLAN which then will provide you access to records beyond 500 rows.

 

 

View solution in original post

3 REPLIES 3
PowerApps Staff ShantanuP
PowerApps Staff

Re: Used nested ForAll to build many-to-many relation in CDS

For outer ForAll, you will have access to first 500 rows of CC_AVQ_PLAN.  And Forall should enumerate each of such record. If you want to have access to any records beyond 500 then you will need to apply delegable filter expression CC_AVQ_PLAN which then will provide you access to records beyond 500 rows.

 

 

View solution in original post

Microsoft Employee

Re: Used nested ForAll to build many-to-many relation in CDS

 

@ShantanuPThanks for the response.

 

Sorry, I am not sure if I understand your response completely. I had given an extreme scenario to help me evaluate from that. So I understand that the outer ForAll will fetch me first 500 but will the inner ForAll also fetch me the first 500 from the Filtered recordset?

 

Additionally, to my surprise, I did not see the blue dot on this code which would indicate the 500 record limitation.

 

Can you please guide how would I use delegable commands to achieve the same result?

 

Thank

Manish

PowerApps Staff ShantanuP
PowerApps Staff

Re: Used nested ForAll to build many-to-many relation in CDS

As ForAll is not delegated, it will fetch first 500 rows from the filter record set and only work on those. It's the current limitation. Blue dot is a suggestion message. It appears when there is something you can do to restructure the expression to make it delegable. As ForAll is not delegable function, there is no such suggestion provided.

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: 250 members 5,168 guests
Please welcome our newest community members: