cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
crevan
Helper I
Helper I

Summary of Event Participants after deadline + 1Day

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.

1 ACCEPTED SOLUTION

Accepted Solutions
DamoBird365
Community Champion
Community Champion

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

 

View solution in original post

18 REPLIES 18
DamoBird365
Community Champion
Community Champion

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
Helper I
Helper I

Hi @DamoBird365 

thanks for the respond. I am stuck here:

1.png2.png

DamoBird365
Community Champion
Community Champion

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
Helper I
Helper I

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

crevan
Helper I
Helper I

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:

4.png

DamoBird365
Community Champion
Community Champion

Hi @crevan 

 

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

 

DamoBird365_0-1618997387370.png

 

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
Helper I
Helper I

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
Community Champion
Community Champion

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
Helper I
Helper I

@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:

5.png

 

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

 

6.png

 

 

DamoBird365
Community Champion
Community Champion

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
Helper I
Helper I

@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

 

 

 

 

 

7.png8.png

 

Here are detailed Screenshots:

Spoiler
9.png

 

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

DamoBird365
Community Champion
Community Champion

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?

 

DamoBird365_0-1619013599088.png

 

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

 

Damien

crevan
Helper I
Helper I

@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

10.png

 

Compose for 2

11.png

DamoBird365
Community Champion
Community Champion

@crevan Could this be it?

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

 

Damien

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

DamoBird365
Community Champion
Community Champion

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

 

View solution in original post

Thank you @DamoBird365 

finally it worked!

you are awesome!

DamoBird365
Community Champion
Community Champion

@crevan 🍻 thanks 👍 appreciated

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (1,166)