cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Spamrancher
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?

2 REPLIES 2
WarrenBelz
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.

ClearCollect(
   facilityFloors, 
   Filter(
      Floors, 
      Facility.Facility = CheckinBuilding.Facility
   )
);
Clear(FloorAreas);
With(
   {wAreas:Areas},
   Collect(
      floorAreas, 
      AddColumns(
         facilityFloors,
         "Area",
         Lookup(
            wArea,
            Floor.'Floor (msft_floorid)' = wArea.'Floor (msft_floorid)'
         ).YourAreaField
      )
   )
)

 

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

Announcements
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

PA Community Call

Power Apps Community Call

Next call is happening on April 21st at 8a PST.

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (62,316)