How to get data from nasted collection?

[Anonymous]

Hi All,
I'm very new to PowerApps. I want to ask how to get data from nested collection and save it to new collection ?
for example like this, I have collection (GetAvailableRoomCollection) that I took from Office365.FindMeetingTimes().

If I click on MeetingTimeSuggestion then there is another collection like below.

and then if I click on AttendeeAvailability then there is another collection like below.


and then if I click on Attendee then there is another collection like below.

and then if I click on EmailAddress then there is another collection like below.

My Question is how to get EmailAddress Where Availability = "Busy" From AttendeeAvailability Collection and save it to new collection?

Any help is appreciated. Thanks in advance

[v-yutliu-msft]

Hi @Anonymous ,

Do you want to get the emails that AttendeeAvailability is busy from the result returned from Office365Outlook.FindMeetingTimes()?

Now let us explain one by one nested table.

1)The first one is easy. Since Office365Outlook.FindMeetingTimes() will return a record with two columns, so you just need to use "Office365Outlook.FindMeetingTimes().MeetingTimeSuggestions" to get rid of the first nested table.

Formula like this is enough:

ClearCollect(GetAvailableRoomCollection,Office365Outlook.FindMeetingTimes().MeetingTimeSuggestions)

2)The second one is a little complex. Since the second part and the third part are both tables, so you need to get all the  Attendees and AttendeeAvailabilitys from a table nested a table.

The you should use formula like this:

Clear(test);ForAll(GetAvailableRoomCollection,Collect(test,AttendeeAvailability))

Test will be the new collection with all the  Attendees and AttendeeAvailabilitys.

3)The last part is easy.

Since Attendee  only has one record and EmailAddress only has one record too, so using AddColumns function is enough.

The you should use formula like this:

AddColumns(Filter(test,Availability="Busy"),"email",Attendee.EmailAddress.Address)

4)get all the emails

The new column email has the email data, you just need to use concat function to connect them.

Formula like this is enough:

Concat(
AddColumns(Filter(test,Availability="Busy"),"email",Attendee.EmailAddress.Address),
email&" ")

To sum up, please set your app like this:
set the screen's OnVisible:

ClearCollect(GetAvailableRoomCollection,Office365Outlook.FindMeetingTimes().MeetingTimeSuggestions);
Clear(test);ForAll(GetAvailableRoomCollection,Collect(test,AttendeeAvailability))

set the label's Text that you want to display the emails:

Concat(
AddColumns(Filter(test,Availability="Busy"),"email",Attendee.EmailAddress.Address),
email&" ")

Here's a issue about getting value from nested table which will help you a lot:
https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-access-nested-table-values/td-p/313821

Best regards,

[Anonymous]

Hii @v-yutliu-msft thanks for response my question,
In the first formula, is there no specific time? because I need to get a room based on the time input by the user.
I tried this formula to get a resource room with a specific time

ClearCollect(GetAvailableRoomCollection,
    ForAll(DateTimeBookingCollectionUTC,
       Office365.FindMeetingTimes(
          {
              RequiredAttendees: AllRoomsConnector,
              IsOrganizerOptional:true,
              Start: StartTimesUTC,
              End: EndTimesUTC,
              MeetingDuration: DateDiff(
                  StartTimeBooking,
                  EndTimeBooking,
                  Minutes
              ),
              MinimumAttendeePercentage: "1",
              ActivityDomain:"Unrestricted"
          }
       )
    )
);

then from this formula I get collection that has 2 column(EmptySuggestionsReason, MeetingTimeSuggestions). I adjust this formula with the first formula you gave me by adding MeetingTimeSuggestions after Office365.FindMeetingTimes() function (like this formula below). After I adjusted the formula there was an error in the second formula that you gave me.

ClearCollect(GetAvailableRoomCollection123,
    ForAll(DateTimeBookingCollectionUTC,
       Office365.FindMeetingTimes(
          {
              RequiredAttendees: AllRoomsConnector,
              IsOrganizerOptional:true,
              Start: StartTimesUTC,
              End: EndTimesUTC,
              MeetingDuration: DateDiff(
                  StartTimeBooking,
                  EndTimeBooking,
                  Minutes
              ),
              MinimumAttendeePercentage: "1",
              ActivityDomain:"Unrestricted"
          }
       ).MeetingTimeSuggestions
    )
);

[v-yutliu-msft · Accepted solution]

Hi @Anonymous ,

The reason why I gave you above formula is that I thought you only use formula like this "Office365Outlook.FindMeetingTimes()" to get GetAvailableRoomCollection.

"Office365Outlook.FindMeetingTimes()" will only return a record, so you could directly use Office365Outlook.FindMeetingTimes().MeetingTimeSuggestions to get nested value.

Since you use formula like  this to get GetAvailableRoomCollection, the formula should make a little change.

ClearCollect(GetAvailableRoomCollection,
    ForAll(DateTimeBookingCollectionUTC,
       Office365.FindMeetingTimes(
          {
              RequiredAttendees: AllRoomsConnector,
              IsOrganizerOptional:true,
              Start: StartTimesUTC,
              End: EndTimesUTC,
              MeetingDuration: DateDiff(
                  StartTimeBooking,
                  EndTimeBooking,
                  Minutes
              ),
              MinimumAttendeePercentage: "1",
              ActivityDomain:"Unrestricted"
          }
       )
    )
);

 This formula will return a table, you could not use tablename.fieldname to get all nested value.

Please distinguish whether there's nested record or nested table.

For nested record, you could directly use tablename.fieldname to get all nested value.

For nested table, you need to use ForAll function to get all nested value.

In your issue, you have two levels are nested table: GetAvailableRoomCollection, AttendeeAvailability.

Use Forall function for these two levels. 

Please set your app like this:
set the screen's OnVisible:

ClearCollect(GetAvailableRoomCollection,
    ForAll(DateTimeBookingCollectionUTC,
       Office365.FindMeetingTimes(
          {
              RequiredAttendees: AllRoomsConnector,
              IsOrganizerOptional:true,
              Start: StartTimesUTC,
              End: EndTimesUTC,
              MeetingDuration: DateDiff(
                  StartTimeBooking,
                  EndTimeBooking,
                  Minutes
              ),
              MinimumAttendeePercentage: "1",
              ActivityDomain:"Unrestricted"
          }
       )
    )
);
Clear(test1);ForAll(GetAvailableRoomCollection,Collect(test1,MeetingTimeSuggestion));
//get all the MeetingTimeSuggestion value
Clear(test2);ForAll(test1,Collect(test2,AttendeeAvailability))
//get all the AttendeeAvailability value

set the label's Text that you want to display the emails:

Concat(
AddColumns(Filter(test2,Availability="Busy"),"email",Attendee.EmailAddress.Address),
email&" ")

Best regards,