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
