Get attendees from calendar events

[Anonymous]

This one is hurting my brain, kinda like watching 'Inception' with no sound and ASCII subtitles.

Using Office365 Outlook;

Collect(collectCalendarGetItems, Office365.CalendarGetItems("Calendar").value)

correctly stores calendar event in a collection for me.

If you navigate through the collection using the collection viewer;

• Attendees is a column in the collection containing tables.
• If you navigate into an Attendees table, it contain three columns EmailAddress, Status and Type columns.
• The values of the Email Address data is also a table.
• If you navigate into the Email Address table, it contains Address and Name columns.
• Address contains the value I'm looking for.

There is a 1-to-many relationship between events and attendees which means if you want to carry any info from the top level down to any of the sub levels, you will have to do it for each entry in a sub level. 

I have to carry the event ID all the way down with me, so I can check which event the email address belongs to at the end.

Each Attendee email address that I'm looking for is 2 levels down.

At the end of all this, I just want to be able to reference a collection that contains all attendees [Address]es and the [Id] of the calendar event for which that attendee exists.

I've tried a ForAll to collect the Attendees.EmailAddress values of each event into another collection and then another ForAll to collect the EmailAddress.Address values - but it's only pulling the first one...perhaps I'm not going far enough down the iterations, but the syntax is killing me.  How do I end up with a collection of all the attendee addresses for all my events and a UID linking them back to their relevant events?

 This is as far as I got before my brain started curling up into a little ball;

ForAll(collectCalendarGetItems, Collect(CollectValues, {Id_level2: Id, Subject_level2: Subject, Attendees_level2: LookUp(Attendees.EmailAddress, true, EmailAddress.Address)}))

I'm just carrying the subject to help me see the data - all it does is return one (presumably the first) attendee email per event, and I'm guessing this is because the ID and Subject items are in a 1:* relationship with Attendees or because of how I'm running the LookUp with Id1=Id1...

Either way, like I said, Inception with no sound ASCII subtitles....

Kind regards,

R

[Anonymous · Accepted solution]

Ironically enough, a decent night's sleep presented the solution.

Obviously the LookUp only returns one value - so I needed to iterate the LookUp through all Attendees by nesting another ForAll statement on Attendees - something I didn't know I could do.  It makes sense though as Attendees is a table so - it worked.

As an aside, I reduced the calendar entries to a 5-day spread to help make it quicker.

Clear(CollectValues);
ClearCollect(TodaysCalendar, Filter(CollectCalendarItems,DateDiff(DateValue(Start), Today(),Days) >-1 && DateDiff(DateValue(Start), Today(),Days) <5));
ForAll(TodaysCalendar, Collect(CollectValues, ForAll(Attendees, {Id_level1: Id, Subject_level1: Subject, Attendees_level1: EmailAddress.Address})))

Hope this helps someone!

R

[v-micsh-msft]

Hi @Anonymous,

Thanks for the Update and sharing!

Regards,

Michael

[MajaOlesen]

Thank you for the post.
Did you encounter that the response status was None or missing for items that had responses, when looking in outlook?
If so did you solve it and how?