Summary of Event Participants after deadline + 1Day

[crevan]

Hi i have a tough one.

i have two SharePoint Lists.

The first one is the Event List. It contains Title, Desciption, StartDate, EndDate, Deadline, Trainer, EventID

The Second List is the Registration List. If a User signs in to an event he creates a new list Item. The Columns in this list are:
Title, Desciption, StartDate, EndDate, Deadline, Trainer, EventID and one column that contains the names of the participant. The User has the choice to only sign himself in or himself and additionally other colleagues.

Now i want a flow that sends an email after Deadline + 1 Day with all names from the registrations list to the trainer from this event.

i hope it is not to confusing.

[DamoBird365]

Hi @crevan 

Sounds like you want to have a recurrence trigger i.e. daily and get the event list items and then perform a filter array action on the date adddays(utcnow(),1)

You then want to get all registrants from the SP List.

Then you want an apply to each on the filtered array and you will again use a filter array but this time on the the Applicants value from get items with the current item EventID, you could then create a table and email the participant list to the trainer of the current event item.

If you're stuck and have sample data, copy it here and I will put something together.  Otherwise, give it a go and let me know how you get on.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[crevan]

Hi @DamoBird365 

thanks for the respond. I am stuck here:

[DamoBird365]

Hi @crevan 

Have a look here at FilterArray1Left3.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[crevan]

Okay now i have this @DamoBird365  but i just get emtpy body:

[crevan]

Hi @DamoBird365 ,

I am stuck here:

Then you want an apply to each on the filtered array and you will again use a filter array but this time on the the Applicants value from get items with the current item EventID, you could then create a table and email the participant list to the trainer of the current event item.

---

Now i am getting all participants from all events. I can'T find my error. Can you ellaborate this step a little further for me?

Thanks in advance

Current State where i get all participants from all events:

[DamoBird365]

Hi @crevan 

Something like this, you've got apply to each within apply to each which is not desirable.

The first filter array is on get items, filter array 2 is on get items 2 values and ID (albeit EventID) and then the expression item() is actually item()?['ID'] or in your case item()?['EventID'].

Each apply to each will list all users attending each of the events and you can then format your data as required and email to your trainer etc.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[crevan]

Hi @DamoBird365 

Thanks for your response.

i hope one last thing.

In your example Filter Array 2.
The First ID Value, is it the Event ID from the registrations list? In your example Get items 2.
And the second Value item()?['EventID'] from the Event list?

Again sorry but english is not my native language and i am trying to track your steps.

Thanks in advance.

[DamoBird365]

Hi @crevan 

I can only speak english, so I am impressed with anyone that can speak more than one language.  

Your first filter will be on the event list items on the date adddays(utcnow(),1).  This will return all events for a days time.

Within the second filter, the first ID will be the registrants list and the second the event list ID.  What you are doing here is filtering the list of registrants by the event ID from the even list.  Anyone that matches will be returned in a new array.

If you have 3 events tomorrow, the apply to each will run 3 times as the first filter array will return 3 events.  The 2nd filter will then filter the whole of the attendees list where the event id matches.

Hope this makes sense.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[crevan]

@DamoBird365 Okay thanks for the dtailed answer.
The First Array works perfect. With my Demo Data i have 9 Events und 2 match my filter and both are return.

the second Filter looks now like this:

and this only returns an empty output, but it should return 5 registrations. 3 for the first event und 2 for the second:

[DamoBird365]

Hi @crevan 

So close 🙂

Can you create a compose actions under your filter registrations with item()?['ID_x0020x_Schulung'] ?

I would guess that the dynamic value Schulungs ID in your filter will be working OK, but your manual expression above isn't.  If you create a compose and then run the flow, you can look back at the history and see what data is being returned.

What you're looking for is why the two values don't match and it will either by a typo in the expression, returning null or the strings are not the same, like CaSe SenSitIve or maybe leading or    trailing spaces    .

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[crevan]

@DamoBird365 

thanks i got the flow to run after i checked the history. It was item()?['IDSchulung'].

But now it seems inside the Filter it does not recognize my dynamic value for the Schulungs ID.
I changed it after i checked the inputs from the Filter to 'Schulungsauswahl:ID' but it still returns null.
The compose Actions for item()?['IDSchulung'] returns 7 und the dynamic Value from Schulungsauswahl:ID should also be 7 but i get null... i also tried item()?['Schulungsauswahl_x003a_ID']
What could be my error

Here are detailed Screenshots:

Spoiler

EDIT: i checked again. The dynamic value 'Schulungs ID' works outside of the apply each but not inside... 

[DamoBird365]

Can you go to Show Raw Input of the "filter registrations" action and copy / paste the JSON to the call?

Am i right in thinking that 1 and 2 are working but 3 is returning null?

And the expression returning Null, also returns Null in the Compose 2 action?

Damien

[crevan]

@DamoBird365 thanks for your patience.

it looks like 2 is not working. But only inside the Filter registrations. 3 returns the correct ID.

The Input for the Filter is here

https://pastebin.com/XXrLMCyy

EDIT:

Try 2: i can not poste Screenshots today. Compose for 3

Compose for 2

[DamoBird365]

@crevan Could this be it?

items('Apply_to_each')?['Schulungs_x0020_ID']

Damien

[crevan]

@DamoBird365 The Filter Output are all registrations and not only for the 2 events from the First Filter

[DamoBird365 · Accepted solution]

Hi @crevan 

I've done you a video of the solution here

This will hopefully allow you to see how it comes together as I appreciate this is a tricky one.  I also made a mistake early on, with item()?['EventId'] when it should be items('Apply_to_each')?['EventId'].

Also, to give you a copy of my solution, copy and paste the following in a new Cloud Flow:

{"id":"da07366e-6876-4917-a0a9-84e9-0607397d","brandColor":"#8C3900","connectionReferences":{"shared_sharepointonline":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-c5e113e4-3432-4cf0-bcc3-e1f7a72a25cb"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"DamoBird365_Find_Attendees_for_Event","operationDefinition":{"type":"Scope","actions":{"Events":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_sharepointonline","operationId":"GetItems","apiId":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline"},"parameters":{"dataset":"https://abdndamodev.sharepoint.com/sites/DamoBird365","table":"934e1797-2e8f-44e0-a5f4-83196971ae45"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{}},"Attendees":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_sharepointonline","operationId":"GetItems","apiId":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline"},"parameters":{"dataset":"https://abdndamodev.sharepoint.com/sites/DamoBird365","table":"57aa20fd-06bb-473e-ab4d-5149a817a152"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"Events":["Succeeded"]}},"FilterEvents":{"type":"Query","inputs":{"from":"@outputs('Events')?['body/value']","where":"@equals(item()?['Date'], formatdatetime(addDays(utcnow(), 1), 'yyyy-MM-dd'))"},"runAfter":{"Attendees":["Succeeded"]}},"Apply_to_each":{"type":"Foreach","foreach":"@body('FilterEvents')","actions":{"Create_HTML_table":{"type":"Table","inputs":{"from":"@body('FilterAttendees')","format":"HTML","columns":[{"header":"Attendee","value":"@item()?['AttendeeName']"}]},"runAfter":{"FilterAttendees":["Succeeded"]}},"FilterAttendees":{"type":"Query","inputs":{"from":"@outputs('Attendees')?['body/value']","where":"@equals(item()?['EventID'], items('Apply_to_each')?['EventId'])"},"runAfter":{}},"Trainer":{"type":"Compose","inputs":"@items('Apply_to_each')?['Trainer']?['Email']","runAfter":{"Create_HTML_table":["Succeeded"]}},"EventName":{"type":"Compose","inputs":"@items('Apply_to_each')?['Title']","runAfter":{"Trainer":["Succeeded"]}}},"runAfter":{"FilterEvents":["Succeeded"]}}},"runAfter":{}}}

I show you how to do this at the end of the video.

Please also like and subscribe to my YouTube 😉

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[crevan]

Thank you @DamoBird365 

finally it worked!

you are awesome!

[DamoBird365]

@crevan 🍻 thanks 👍 appreciated