Managing Events and Registrations/Attendees with Microsoft Dynamics 365 CRM

I’m currently working on a project at Tecman with a client who is moving from two disparate systems, and neither of them is classified as a ‘CRM’ who want to track events that they run and that they push people to attend (organised by third-parties). The organisation is in the Not-for-Profit sector, but this could apply for more industries too.

With potentially lots of the same events each year at different locations we wanted to be able to provide a foolproof way to prevent individuals being registered on events that had already taken place – makes sense, right?

Initially, I created a ‘timeout’ workflow to update the Event record based on the ‘Event Date’ field, but after some thought, I realised this was going to potentially create hundreds of timeout workflows just sat waiting for their day to fire – not particularly a clean way to build a system! This was without adding the complexity that the event date might change after the event record was created.

I wanted to find a better and cleaner solution to this problem, so I looked to ‘Scheduled Cloud flows’ within Power Automate.

Simply the logic I was looking for was...

“Every day run a workflow at a specific time to check the event date on all active event records and if the date is in the past update the status reason of the event record to ‘complete’” 

The ‘Event’ table was a custom table, so I configured the status and status reasons as follows:

• State: Active 
• Status Reason: Active 
• State: Inactive 
• Status Reason: Complete 

1. Creating a Power Automate Flow: Set Schedule 

Power automate can set up a scheduled workflow to run on a regular repeating basis. 

To create a new flow, I navigated to https://make.powerapps.com and went to ‘My Flows’ tab on the left, then I selected ‘Scheduled cloud flow’. 

I gave my flow a nice log descriptive name of ‘Set Event to complete when the Event date is in the past’ and set the flow to run every day at 1.00am. 

2. List Records 

I wanted my flow to search the common data service for any ‘active’ event records where the event date was in the past. 

To find all records using these criteria, I used the ‘List Records’ action.
First, you need to click ‘New Step’ under your recurrence step, and type ‘list’ then find the connector ‘Common Data Service (Current Environment).

Now I selected my event table.

3. Filter Records with Fetch XML Query 

Using Fetch XML query, I needed to bring back a list of ‘active events where the event date was in the past’.

To do this, I used advanced find and entered the required criteria. 

To keep things simple, I selected Event date of yesterday. This meant that if my flow ran at 1 am in the morning then by the time staff started using the system at 9 am to register people to a new event, any events that were held yesterday would no longer be selectable.  

I then clicked the download the XML, opened it in notepad, copied and pasted it into my flow. 

4. Apply to Each 

Now all I needed to do was update the State and Status reason on all the records selected in the previous step.  To do this I used the ‘Apply to each’ action.

Under Dynamic content then ‘List records’, I selected the ‘value’ field. 

Now I added a new ‘update’ step within the ‘Apply to each’ step selecting the event value from Dynamic content and setting the status and status reason to Inactive/Complete.

5. Testing 

To test my flow, I needed to set the event date on an event record to yesterday within Dynamics 365 CE. Once I had done this within Power Automate screen, I checked my flow, pressed save and ran a test. 

For a scheduled workflow, if you click ‘test’, then it will just run on all records, you don’t need to do anything in Dynamics 365.  

Flow will then show you the steps it has passed/failed on and will show the inputs/outputs of each step it has run through so you can more easily troubleshoot any potential issues. 

The final piece of the puzzle was to update the lookup view when registering contacts to events to only show active events. 

Although this is a simple example of scheduled cloud flow, I hope it gives you an idea of what other scenarios this would be helpful when trying to add automation to your CRM solution – especially if you track event attendance in your organisation.