Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

Create a Collection from Related Records In Another Collection

Hi Community,


I'm extending the Microsoft Return to the Workplace solution, and I'm trying to collect the Areas that are related to the Floors that are related to the Facility the user selected.


The first collection, which works fine, gets the Floors for the Facility:

ClearCollect(facilityFloors, Filter(Floors, Facility.Facility = CheckinBuilding.Facility));


Now to get the Areas related to those Floors, I did this as a test, and it worked fine, but only gets me the Areas for the first Floor record:

Collect(floorAreas, Filter(Areas, Floor.'Floor (msft_floorid)' = First(facilityFloors).'Floor (msft_floorid)'));


Building on the last formula, I try to use ForAll and that's where it breaks:

ForAll(facilityFloors, Collect(floorAreas, Filter(Areas, 'Floor (msft_floorid)' = ThisRecord.'Floor (msft_floorid)')))


The entire Filter statement has the wavy line, and Areas is underlined. When I hover over "Areas" it displays the message "The specified column is not accessible in this context".  


Anyone have any suggestions?

Super User III
Super User III

HI @Spamrancher ,

I cannot see your data source, so the validity of matching columns is something you will have to check, but you might consider this structure.

      Facility.Facility = CheckinBuilding.Facility
            Floor.'Floor (msft_floorid)' = wArea.'Floor (msft_floorid)'


Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.


@WarrenBelz , thanks for the input. I should have said that my data is CDS/Dataverse. The Facilities table has a one-to-many relationship with the Floors table, and the Floor table has a one-to-many relationship with the Areas table. If I'm understanding what I am looking at in your code, the LookUp would find the first Area record, but not all of them.

In the end I wrote a Flow to make sure the Facility is populated on all the downstream records and I can pull all of them into collections based on the Facility.

Helpful resources

PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!


Are Your Ready?

Test your skills now with the Cloud Skill Challenge.


Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (78,683)