Retrieving most recent or oldest records in Power Automate

“I only want the most recent Cases of a Contact so that I can email the customer a list of their cases at the end of the day.”

“I only want the oldest Cases of a Contact so that I can let my team know they need to attend to these Cases immediately to resolve them.”

Heard something similar before in regards to the above user stories around the requirement to retrieve the most recent or oldest records? Previously in Dynamics 365 classic workflows it’s hard to perform a query and then insert those results in some form of an email or notification to the Customer or colleagues in the company.

I use the above user stories as a means of understanding the method applied in Power Automate with Flow. This can be applicable to any entity and similar use cases that require retrieval of recent/oldest or even top/bottom type of data that you're familiar with in Microsoft Excel already.

I show how this is achievable using CDS and Power Automate with Flow in this WTF episode.

Most recent records

Since more than one record is to be retrieved the CDS action of “Get Record” is not valid since this action retrieves a single record. The CDS action to use is “List Records” where a query is performed to retrieve the records that meet the criteria. In the CDS List Records action there are a couple of fields in addition to the Filter Query field that will be used. These fields are

• Order By 
• Top Count 

Order By 

This is where you can define the field that will be used for the most recent parameter. Examples are using a Date field such as Created On from the Case entity or a Currency field that has a dollar amount such as an Estimated Revenue in the Opportunity entity. The values that can be entered is

• desc for descending – for example orders date results from oldest to newest or currency from highest to lowest 
• Asc for ascending – for example orders date results from newest to oldest or currency from lowest to highest

You can use more than one field as well when you want to order you results returned. This is the same principal as sorting a column in a system view, personal view or advanced find in a model-driven app. For more information on ordering you can review this docs.microsoft.com article.

Top Count 

This is where you can define the number of records to be retrieved. If you enter 1, only a single record will be retrieved and it would represent the most recent/oldest record. If you enter 5, five records will be retrieved and these would represent the five most recent/oldest records. For more information on top you can refer to this docs.microsoft.com article.

Use Case 

The user story I referred to in the vlog is the following which I'll refer to in the remainder of this blog post.

I only want the most recent Cases of a Contact so that I can email the customer a list of their cases to provide them with an update. 

Overall the Flow looks like this:

1.0 When a Contact is selected trigger 

For the purpose of explaining this How-To, I used an Instant Flow with the CDS “When a record is selected” trigger for the contact entity.

Other triggers could be Recurrence or the alternative is to have the actions downstream in a Flow based on preceding actions.

1.1 Retrieve top 5 most recent Cases 

The next action will be the CDS “List Records” action with the following

1. Entity will be Contacts 
2. Filter query is _primarycontactid_value eq trigger contactid 
3. Order By is createdon desc 
4. Top Count is 5. This will retrieve Cases where the Case Contact field value is equal to the Contact selected and only five cases with a most recent Created On date value. 

1.2 Create HTML table 

The next action to use is the Create HTML table that will allow the retrieved records to be displayed as a table in an email sent to the Contact. It’s a fairly simple action to understand,

1. From is the value dynamic content from the previous CDS List Records action 
2. In the Header is where you enter what you’d like text to appear as the Headings 
3. Value is the desired field values from the Case entity through the previous CDS List Records action

1.3 CSS style of table 

If you use the Create HTML table action as-is by inserting in the Outlook Send an email action, it won’t be formatted nicely. I learnt this tip from fellow MVP Andre Margono, refer to this blog post of his. A compose action will contain the CSS that will be applied to the output of the Create HTM action.

1.4 Send an email to the Contact 

The final action is the Outlook Send an email action where

1. The Contact’s email address from the Flow trigger is used in the To field 
2. The Subject is entered 
3. The content of the email message includes the Output from 1.3 CSS style of table which will have the table nicely formatted

What the Flow in action 

Open a Contact record that has more than 5 active cases associated to the Contact. In the command bar, click on the ellipsis and select the Instant Flow to run the Flow.

You’ll see the email with the nicely formatted table of the five most recent cases for the Contact. Awesome sauce.

Oldest records 

To retrieve the five most oldest Case records simply use asc instead of desc in your Order By field in the CDS List Records action.

When the email is sent it will show the five oldest Cases.

But wait 

How come the date is a bit funky in the email? In my next WTF episode I’ll show you how to overcome this issue. Stay tuned!

Summary 

It’s quite simple to query records by using a quantitative value (such as 5 or 1) and using desc/asc for retrieving the most recent (or top) and older (bottom) records of an entity using Flow. In classic Dynamics 365 workflows it’s not available out-of-the-box to query records and there is no type of formatting of tables in the Send email workflow action either. Flow removes these boundaries without the use of development.

In my next WTF episode I'll share with you how you can correctly display dates when using the Create HTML table action.

Read More

My first Microsoft Ignite - 2019

Late summary of my experience for my first time attending and speaking at Microsoft Ignite 2019 but I'm going to publish this anyway. It's about the experience rather than the content as post conference, the content was made widely available and also a few others in our Microsoft community have done a summary of the news announced at Microsoft Ignite such as fellow Microsoft MVP April Dunnam which you can find here.

I'll start from the beginning 😁

Submitting for Microsoft Ignite

An email came through which all Microsoft MVPs received regarding call for Microsoft Ignite. I think from memory it was around July. I was travelling the world at the time doing my #EWorldTour

Now Microsoft Ignite is not the only Microsoft conference that Microsoft MVPs can submit for, it's only one of them. In the past since being awarded as a Microsoft MVP, I did not submit for any Microsoft conferences because I didn't think where I worked previously would give me time off to present. There was a fine line when it came to personal branding vs what value it is to the company in presenting at a Microsoft conference. 

Even before I was a Microsoft MVP I did not attend Microsoft events like the old Microsoft Convergence or Microsoft TechEd. Someone in the team always went plus I saw myself as bottom of the food chain in the organisation hierarchy as it's usually Sales or Practice Leads to form relationships with people from Microsoft and other beneficial players in the market.

Anyways I was debating whether to submit. I was unemployed at the time since I was travelling for #EWorldTour so nothing was holding me back other than doubting myself. I reached out to Joel Lindstrom who is someone I respect and confide in at any time of the day. Joel Lindstrom encouraged me to submit and suggested I submit popular WTF episodes. I also wanted to submit a session on my #EWorldTour for the community track cause I thought it would be cool to share my journey.

Fast forward I'm in Vancouver staying at my friend's place and on the second to last day of the submission deadline I go through the process of filling in my submission form. I read my submission details back to my friend who at this point is laughing at me because I was tired and still getting used to the time zone - my words were not making sense. After tidying it all up, had I submitted multiple sessions + submitted to be a community reporter which required filming a short video for it.

Finding out I've been selected

The following month I was sitting on a beach in Playa Del Carmen in Mexico.

I went off the grid for a few days as I was enjoying my #wanderlust moment in Mexico. I had not been to Mexico previously and I wanted to live in the moment by being offline. I decided to check my emails that day since I hadn't checked in a while.

The first email I saw was about two of my topics not being selected and not being selected as a community reporter. My mind then raced around because I was thinking, OK does that mean my other three session have been accepted?! Found the next email and YES, three sessions accepted 😲 At this point I was thinking "oh man three sessions - I thought only one would be selected." I was really happy though and I shared my happiness with this tweet.

My three sessions that were accepted were

1. Travel the world, meet the community
2. Automatically update business process flow stages using Microsoft Flow
3. Delaying a birthday email based on the celebrants local time

Surprise!

I had organized my travel and accommodation early before the schedule was released. Towards the end of September I find out my schedule. The odds of having all three of your sessions on the same day, on Day 1 was me. I decided to change my flight as I was originally due to land Sunday evening and I knew I'd be jetlagged to be on my A-game to present the following day. Flights were amended and an additional 1 night of accommodation was sorted. Phew.

Preparing for Microsoft Ignite

Since I was still in my #EWorldTour and my next event to focus on was Dynamics 365 Saturday Washington DC at the time, I only started preparing for Microsoft Ignite in early October when I was back in New Zealand. I had already blogged and vlogged two of my three sessions.

• Automatically update the stage of a Business Process Flow with Flow 2.0
• Delaying a birthday email to the recipients’ local time

So I started with my Humans of IT track one which required a walk down memory lane digging out photos from my phone. Selecting the photos was hard as there was so many. 

Once that presentation was done, I focused on the other two. I tidied up my Power Automate with Flows and did end-to-end-testing a dozen times to fix anything that was not up to my standards. I also revised my Power Point slides multiple times to make sure the structure was solid but also made sure I didn't have too many slides.

Then came the rehearsals. I rehearsed and rehearsed and rehearsed. I didn't want to be "just good," I wanted to be great at my first time presenting at Microsoft Ignite. I had a timer going on my phone to ensure I wasn't going over the time limit. I did go over the time limit for two of my sessions a few times so I either removed slides or skipped some parts I thought I was going to focus on.

I also didn't want to let my audience down when it came to my live demos so I also did video recordings as backups. I spent time editing with the aim of having a short duration for the purpose of not loosing the engagement/interest of my audience. If my live demos did not go to plan I had my videos to fallback on.

At the same time I was nervous. I had messaged Jukka Niiranen a few times as he has presented previously in the past and the moral support from Jukka helped. I also had support from Megan Walker, Andre Margono, Natraj Yegnaraman and Lisa Crosbie.

Packing for Microsoft Ignite

I decided to pack light since I'd be travelling solo. I don't like travelling with a big bag so I packed my cabin sized wheelie luggage. 

In this photo I hadn't filled the other side but I did with my New Zealand treats to share with everyone at Microsoft Ignite. Checkout my LinkedIn post here on it.

The long journey to Orlando

Saturday morning of Nov 2 = Go time. I had breakfast, called Lisa Crosbie to congratulate her being awarded as a Microsoft MVP and was out the door to the Wellington airport. My parents came to bid me farewell and I knew inside they were proud that their daughter was doing something that was meaningful even though they don't fully understand what it is that I do 😅

My journey was this:

1. NZ domestic flight from Wellington to Auckland at 11.45am with a duration of 1 hour and 5 minutes
2. International flight from Auckland to San Francisco at 2.30pm with a duration of 12 hours and 10 minutes. Tweeted before I took off.
3. USA domestic flight from San Francisco to Orlandto at 8.30am with a duration of 5 hours and 4 minutes. This was a bit of a stressful moment due to aircraft issue on arrival to San Francisco but I made my connecting flight just in time.

I arrived in Orlando and I was relieved when I saw my bag at the carousel. I registered at the Microsoft Ignite booth at the terminal and ordered an uber to my hotel. Here's my tweet of me being in Orlando.

I then tried to wait for Sarah Jelinek to arrive later that evening to see her (because I love her x 100) but the tiredness got the best of me.

Pre-Day

I slept through my alarm as I was worn out from travelling. I must have been out cold because I never sleep through my alarm. I jumped out of bed to get ready and then I was on a shuttle bus down to the Orange County Convention Centre [OCCC] where Sarah Jelinek was waiting to meet me.

Since I already picked up my badge at the airport, Sarah walked with me to collect my Microsoft Ignite swag. Along the way we ran into Sarah Critchley so of course we do a selfie and I tweet it. Sarah Critchley was selected as a Community Reporter which I was really happy about as she did an amazing job as one.

The Microsoft Ignite swag was a cool bag pack (or you could have a belt bag), water bottle and tshirt.

We then checked out The Hub as our badges allowed us to walk through. The Hub was the main area where all the different booths, theatres and hangout areas would be. Oh my goodness it was massive!

People were not kidding when they said bring comfortable shoes. The size of it was nothing I'd ever experienced before. We saw people rehearsing on the stage where the vision keynote would take place. They were also playing back videos for the following day (Day 1). It was quiet since hardly anyone was around but we saw all the booths, displays and areas being set up. We found the Microsoft MVP wall so we stopped to take photos.

I took more photos for others - in particular for Lisa Crosbie as she was awarded days previously so I wanted to share that special sighting with her.

We also went to check out the Microsoft Learning certification lounge. This was good because I had quietly scheduled to sit exams since they were free at Microsoft Ignite. I knew where I needed to go when it was time to sit my exams. I then meet Julian Sharp for the first time 😍 which of course I tweet about too and I tell him my favourite thing I know about him from social media - his time in Scotland for the Dynamics 365 Saturday event and as he's presenting, a famous public figure is on the other side of the window as a mural on a building.

We then meet up with Marco Amoedo and head over to the food hall for lunch. Food hall was massive too, truly amazed by the shear scale of the conference. Afterwards we headed to the Pre-Day MVP Data and AI sessions. Mohammed Mostafa and Shawn Tabor met us there later. At the end of the day we headed to the speaker orientation session that went through recommendations of everything from content in slides, how AV crew would be there to help set you up with audio etc, how to speak with confidence to your audience etc. It was a lot! Afterwards we went out to have dinner altogether at Disney Springs (which again, massive place) and Jerry Weinstock came to meet us there.

Day 1 - my day of presentations

I couldn't sleep the night before as I checked my registration numbers for one last time and the numbers were high. I was nervous and I had rehearsed three times before going to bed at around 11.30pm.

We all had agreed that we would try head to the vision keynote with Satya Nadella really early. Out of all of us, Joel was on the bus at 6.30am and Sarah Critchley was already there at 6.45am due to Community Reporter commitments. The rest of us were still waking up and getting ready. I headed in with Shawn and Sarah Jelinek gave us a tip on where to head to for security to let us in The Hub with our badges. We made it through and walked waaaay over to the other end of The Hub where the vision keynote would take place. Joel had secured a table so we were lucky enough to sit down. It was also my first time meeting Joel which was a tick off my bucket list.

Kent Weare also happened to be in the table next to us so I said hi and spoke with a couple of his colleagues. After a while the main doors opened and all the attendees came pouring in. The crowds started to gather by the vision keynote stage while others were dotted in different areas in The Hub to watch it live elsewhere.

Then it started, lights dimmed and the screen + stage lit up as Satya came out.

This was the first time I was seeing him from a distance and not through a recording of a conference! Throughout the vision keynote different product teams were given camera time across The Hub to demonstrate the latest and greatest features. The one that stood out for me is at 1:08:30 of the vision keynote where Customer Insights was demonstrated. The product visualize + mixed reality part in the demo was mind blowing. I was thinking this is next level. The use case I thought of immediately was of a company selling irrigators to organisations who have suppliers that own paddocks that produce crops. Organisations can view the irrigators using product visualize and mixed reality before deciding to purchase it. Irrigators are something that is not feasible to bring on a road show or frequently when visiting potential buyers so by the power of mixed reality an object can be assessed no matter how large or small it is.

After the vision keynote ended I headed to the speakers room to mentality prepare for my three sessions for the day and did a few more run throughs of my demo for my breakout 45 minute session. My first session was at 12.30pm, followed by 3.15pm and 6.20pm.

Show time

I headed over to Theatre 2 which was where my first presentation would be. Theatre's are dotted around The Hub and it's an open style where you have a stage and chairs are arranged in front of you. There will be lots of by passers since it's not in a closed off room.

I arrived 30mins before my presentation as recommended in the speaker orientation.

Shawn Tabor came over early for support since it was my first session and first time presenting ever at Microsoft Ignite. I waited for an AV crew person and was then set up with a mic. Here's a pic of me, the mic is to the right side of my face.

The AV person showed me how to switch between the presentation screen and my laptop. For this session I was not doing a live demo but it was good to know for my remaining two sessions of the day. The AV person then said to give him a thumbs up when I was ready to go at 12.30pm. Since I had time to kill Shawn and Sandy Ussia took a couple of photos of me before I started.

I stood patiently, I smiled and paced around, mentality telling myself that I got this. 12.30pm ticked around and away I went. Once you start presenting everything you practiced is mashed up with talking on the fly. It's funny - what you rehearse isn't play-by-play when you present live.

After I finished I answered some questions, spoke to people I hadn't met before and then took a couple of photos - one with someone who I met through twitter Akanksha Malik and the other with a fellow kiwi Microsoft MVP Paul Warren.

I thank not only the audience such as Salma Bekkouche and Gift Egwuenu but also the other Microsoft MVPs for their support - April Dunnam, Sandy Ussia, Sarah Critchley, Fausto Capellan Jr, Sean Bugler, Vivek Bavishi and Mohammed for coming. Jon Levesque came over at the end to give me a congratulations hug. Shawn told me I did great. It was an incredible feeling and I had two more to nail that day.

After lunch I watched the first half of Charles Lamanna's session which was in the Hyatt conference area which is connected to the OCCC by a bridge walkway. It had more large rooms spread out which meant more walking. I then walked back to OCCC for my 3.15pm presentation which was the main one. To my delight Angel Palomino came to my session. Angel Palomino was someone I met in the Microsoft Community at Washington DC from my #EWorldTour Also there for support was Brian Dang and Kent Weare.

Same routine - mic, go through the technique of switching from presenter mode to laptop mode and signal for when I'm ready. Away I went again with a larger audience and this time a more deep dive into my topic of how to automate moving from one business process flow stage to another using Power Automate with Flow in a model-driven app. The bonus was showing Power Apps Portals and Universal Resource Scheduling in action which I've covered previously.

Photo courtesy of Brian Dang

My live demo didn't turn out as planned. My schedule board was not displaying my resources properly and I think it was due to me over preparing earlier in the speaker room where I forgot to purge my data to reset it. Till this day I am quietly upset about it as I like my live demos to be 100%. Those who do live demos know that sometime it does not work out as planned no matter how many rehearsals you do.

I did receive three negative feedback about not being prepared which was fair yet it would have been worse if I did not have my back up recording which I did turn to. I empathize with speakers when live demos don't go well and they don't have a back up prepared. The funny thing is I said "Oh no" quietly which also appeared in the speech to text dialogue which Angel told me about afterwards which is why they all giggled. I then opened the floor to questions and answered them.

Afterwards I spoke to a few other audience members and took the time to speak with Michael O'Connor from the Mixed Reality Microsoft product team. Funny story - when I was in Charles' session I had a tweet appear and it was from Michael O'Connor. We hadn't met yet he was sitting a few chairs by me, which I wasn't sure at the time so asked him in the twitter thread 😂 He came to my 3.15pm session and after our chat I was off again to The Hub to go support Marco Amoedo in his session.

I stopped by the Power Apps Portals booth with Sarah Jelinek afterwards before my 6.20pm session. I caught up with Dileep Singh, Sandeep Dhanrajani and Smith Codio who were manning the Power Apps Portals booth.

They became busy with all the people stopping by so I walked around checking different booths out. I picked up my wrist band from AvePoint for their exclusive Paint the Town Red party which I found out about from when I met Dux Raymond Sy for the first time in Washington DC as part of #EWorldTour

5.50pm rolled around and I headed over to Theatre 9 which is where my last presentation was. I don't remember everyone who was there but I do remember Shawn Tabor, Sarah Critchley, Joel Lindstrom, Jon Levesque and Kent Weare there. At this point I was relaxed and decided to play my backup video recording for the purpose of time constraints which worked out well. Previously when doing my rehearsals with live demos I was going overtime and I was impacted mentality from my 3.15pm session not going well so I wanted to play it safe.

Then it was all over. Three sessions, one day, Day 1! We all went out for dinner to Universal CityWalk at Hard Rock Cafe. Even Angel Palomino came out too whom I sat next to.

After dinner Sarah Critchley, Joe Unwin, Joel Lindstrom, Shawn Tabor and myself stopped by Voodoo donuts. Om nom nom nom 🍩

It was so nice talking and hanging out altogether in the warmth of a typical Orlando evening. Everything in that moment of time was perfect.

Other highlights

Learning from an experienced presenter

The following morning I woke up early and I headed over to the OCCC in the shuttle bus to watch Dux Raymond Sy present in the Humans of IT track. By the way if you have not seen Dux Raymond Sy present live before you must do it at least once. I was in awe of his presentation manners and skills. He even played back a hilarious skit of himself to demonstrate a few points (watch the recording of his session) and made the whole audience dance. It was incredible.

Power Platform photo shoot

After Dux Raymond Sy's presentation I had to find the meeting spot for my ride to the venue of the Power Platform shoot. I was confused and was at the wrong end of the OCCC which was F and required walking to A. I was quickly racing to the other side. I waved to Andrew Welch on the way, I saw Ryan Cunningham and gave him a smile which he returned (where I thought OK he probably does not know who I am 😂 and I'm a random person in the crowd) and also saw Sarah Critchley amongst others walking in the opposite direction to me.

After a lot of walking I found my meeting spot and I saw my driver holding an ipad with my name on it by a black Lincoln car. What a VIP moment that was. I was thinking to myself that this never happens to me. I was whisked away to the venue and met Darcy alongside the crew for the shoot. I had my hair and make-up done followed by having discussions with the director and photographer. It was a lot of fun and I'm looking forward to the end result.

More Microsoft Community moments

I was very humbled when I accidentally met Manuela Pichler at the dietary requirements section of the food hall (we are both vegan). I had been wanting to meet her in-person and it finally happened at Microsoft Ignite which I tweeted about. For those of you who don't know Manuela, she was a Power Apps champion at Virgin Atlantic before joining the Power CAT team at Microsoft. She was so kind and hearing her stories was a nice change to the hustle and bustle.

I caught up with Jeroen Stutterheim who I met when I was in the Netherlands as part of #EWorldTour We exchanged our local treats with each other, I gave him New Zealand Chocoade (which I like to think is our spin on the UK's jaffa treats) and he gave me vegan stroopwafels.

I met Shona Bang from the Humans of IT program. Shona was someone I had become friends with through twitter and grateful for the gifts.

I also met Taiki Yoshida from Japan where I shared some NZ Cookie Time goodness with him and Alan Chai from Houston. Both are prominent figures in our Microsoft Community for the Power Platform. Also had a brief chat with Stephen Siciliano from the Power Automate team after talking to Sarah Critchley for a community reporter video. In case you don't know, Microsoft Flow rebranded to Power Automate which you can watch from Stephen Sicliano's Microsoft Ignite session or listen to this CRM Audio podcast episode from Microsoft Ignite.

While I'm here - I'm hoping that one day I will also have the opportunity to meet Hiro too 🙂

Free to sit certification exams

One thing I really appreciated was that attendees could sit certification exams for free. I did MB-200 was early in the morning because of Sarah Critchley - she said she was doing an exam at 7am so I said "OK, I'll do one with you." Joel Lindstrom happened to be there too so three of us were doing different exams at 7am and we all passed. Hooray!

The day before I did the MB-900 Fundamentals exam and managed to get one of these cool badges/pins for passing.

Universal Studios night

Microsoft hired Universal Studios for about 4 hours and it was unlimited rides, drinks and food. It was awesome and generous of Microsoft to do so.

I had a great time with Shawn Tabor and Jerry Weinstock on the Hagrid’s Magical Creatures Motorbike Adventure ride. We were in line for 50 minutes but it was 100% worth the wait. It was so good that I lost my badge during the ride. The other memorable moment was going on the Rock It roller coaster with Shawn Tabor. Roller coasters are twice as fun when you have a friend to enjoy it with!

The knowledge gained

Prior to Microsoft Ignite, Joel Lindstrom gave us first timers a round of what to expect. He also recommended to attend sessions outside of your bread and butter which I followed. I went to the Teams Meetings session where the Product team covered the features of the Wave 2 release which I was actually impressed about. I also went to see the Microsoft 365 booths where I saw Visio integration with Microsoft Teams and the Microsoft Project for the web.

I also took a look at SharePoint and it hit me - that's why Unified Interface was rolled out for Dynamics 365. All of the products have the same UI. It then made sense as to why UI was released for Dynamics 365 and that there's a window for existing customers to move to UI by October 2020. All products are now somewhat consistent in terms of the look and feel. It's actually looking pretty slick across the Microsoft ecosystem from what I observed.

Unconference session

On the Thursday there was an unconference session called Power Platform for the CRM person which was led by Joel Lindstrom and joined by Shawn Tabor, Marco Amoedo and myself where we answered questions and discussed items raised from a group of Dynamics 365 professionals. Since the introduction of Power Platform there has been mixed feelings amongst the Dynamics 365 community because the Power Platform introduces new concepts that are different to what we have been used to since Dynamics CRM was a thing. It was very good to hear the pain points as well as have discussions on it.

Animal therapy area

There was an area in The Hub where rescue animals were brought in and attendees could stop by to hang out with them. It was really sweet and it was nice to pet the dogs that were there on the day I stopped by. It was one of my favourite things about Microsoft Ignite.

Summary

• Every place I went to was massive
• Sore feet
• Lots of support within the MVP family as well as in the Microsoft Community
• Presented for the first time ever at a Microsoft conference
• Power Platform photo shoot
• Memories for a lifetime

Would I do it again? YES

One thing I'd like to prioritize next time is attend more Humans of IT presentations as I did not do enough of it and also spend even more time sitting in Microsoft 365 sessions.

PS: Lots of stickers too from Microsoft Ignite

Read More

Delaying emails to certain individuals based on their time zone

For the second time running the free Flow Online Conference was on September 10, 2019. I was lucky enough to be included among the awesome line up of speakers. My model-driven app was Star Wars themed too 😁

I presented a use case where emails needed to be delayed for certain individuals based on their time zone. To be more specific as an example, delay sending emails to only primary contacts based on their local time zone. This use case came about because previous clients who I have worked with had customers globally or nationally and wanted emails to be delayed to a group of customers.

But we can already delay emails in Outlook? 🤔

Outlook will send the emails based on the single time selected which is also in the context of the person who is sending those delayed emails. Same applies to any email marketing software out there, delaying emails will be based on the person who is sending those delayed emails and the date and time is based on their time zone - not the recipients' time zone.

Furthermore in Outlook the functionality to query contacts and have those resulting contacts in an email where they don't see each other's names is not there.

As demonstrated in previous WTF episodes, through the power of Flow the art of sending emails based on local time zones is 100% possible.

Watch my Flow Online Conference session in the below YouTube video. I present at 2:30.

The remainder of this blog post will outline what was presented.

User Story

The following is the user story from my presentation.

As a customer service technician,

I want to send delayed emails to primary contacts based on their time zone,

so that they can receive the email in their local time.

1.1 - The trigger

The trigger can be a recurrence or a Flow button. It is entirely up to how the Flow will be triggered.

1.2 - Retrieve Accounts

The email addresses of the individuals are stored in CDS in the contact entity. Since we are only sending the email to primary contacts of an organisation the CDS List Records action is used to retrieve the organisations from the account entity.

For the purpose of this Flow I did not enter a filter query. In the scenario where you need to email primary contacts of organisations that meet criteria such as primary contacts of VIP organisations, you can apply a filter query in the CDS List Records action against the account entity.

1.3 - Apply to Each

For every account returned the primary contact will need to be identified. The Apply to Each action is therefore used.

1.4 - Primary Contacts

The next step is to identify the primary contact of an organisation in CDS. The account entity has a single primary contact which is represented by a lookup field. The Apply to Each action is used alongside the CDS List Records action to retreive the contact entity with a filter query of

contactid eq @{items('1.3_Identify_Primary_Contact')?['_primarycontactid_value']}

This filter query will only return contacts based on the GUID in the primary contact field associated to the organisation. The dynamic content value is the Primary Contact field from the previous action that returns accounts.

1.5 - Apply to Each

For every identified primary contact from the previous action, a bunch of actions will be executed in order to send the delayed email to the primary contact. To ensure these actions are executed against each primary contact an Apply to Each action is used.

1.6 - Get location by address

As seen in a previous WTF episode there is a Bing Maps action "Get location by address" that allows the location latitude and longitude to be identified based on address information. The Address 1 fields in the contact record of the primary contact will be used. The assumption I made here is that the primary contact inherits the address of the account it is associated to.

1.7 - Identify local time zone of Contact

There are additional Bing Maps Time Zone APIs available which you can read from this Bing Maps blog post. These APIs are not available as Bing Maps action in Flow however the HTTP action can be used to call the APIs.

In order to find out the time in the primary contact's time zone, the "Given location coordinates, find the time zone of the place" Bing Maps API can be used through a HTTP action.

Simply reference the latitude and longitude outputs from the previous step followed by inserting a Bing Maps key.

https://dev.virtualearth.net/REST/v1/TimeZone/latitude, longitude?key=<bingmaps-key>

1.8 - Retrieve localTime property

To ensure the primary contact receives the email in their local time including the date such as September 21 at 4.30pm, we need to know what is their date and time in their local time zone.

To achieve this the localTime property in the response of the previous HTTP action can be used. Below is a screenshot of the response.

If you attempt to select the localTime as a dynamic content in a subsequent action you're out of luck as it won't be displayed. You will only see "Body" since the action used is HTTP as Flow only is aware of the Body output rather than the JSON properties.

There are two methods that can be applied to get around this.

1. Use the Parse JSON action which will allow you to view and select the properties as dynamic content values.
2. Use a Compose action which allows you to reference the property without using the Parse JSON action but simply an expression instead.

Both of these methods have been used and talked about in previous WTF episodes.

The Compose action with an expression is used in this Flow.

body('1.7_Local_time_of_Contact').resourceSets[0].resources[0].timeZone.convertedTime.localTime

Below is a screenshot to help you understand the expression used.

My way of explaining this expression is to work backwards:

From the HTTP action, get the localTime property from the convertedTime object, whose parent object is timeZone which is in the resources array whose parent object is resourceSets.

If you have a better way of explaining this to a non-technical soul, you can tweet to me 😁

1.8.1 - Split the date string

The format of the localTime property will be in UTC format.

The date is only required, not the time to understand the date in the primary contacts' local time zone.

To retrieve only the date string we need to separate the date from the time in the string value of localTime. the split function can be used in an expression within a Compose action where T is used to separate the two.

split(outputs('1.8_Retrieve_LocalTime'), 'T')

1.8.2 - Retrieve date only string

The output of the previous action will return the following,

This results in an array where the date and time is separated. The first row is the date and the second row is the time.

Another compose action is used to retrieve the first array in order to reference only the date of 2019-09-21.

outputs('1.8.1_Split_the_date_string')?[0]

1.9 - Retrieve windowsTimeZoneID

The same technique is used from 1.8 where the windowsTimeZoneID is referenced in a Compose action.

body('1.7_Local_time_of_Contact').resourceSets[0].resources[0].timeZone.windowsTimeZoneId

The two actions 1.8 and 1.9 are within a parallel branch as they are not dependent on each other. Both actions can be performed simultaneously.

1.10 - Convert time zone

The next step is important as the Convert Time Zone action is used to ensure the email is delayed based on the local time of the primary contact.

Flow uses UTC which means the intended local time of the primary contact (for example 9am) needs to be converted to UTC. I've covered this in a previous WTF episode which can be referred to for more learning.

For example in order to send the email at 4.30pm in the local time zone of a primary contact who is based in Wellington, it has to be converted to UTC which is equal to 4.30am. Below is a screenshot from Savvy Time to illustrate the equivalent time in UTC of the Wellington time zone.

1.11 - Delay until

The Delay Until action is used where the date and time is the output from the previous 1.10 Convert time zone action. The email will be sent when the date and time is met which is equal to the local time of the primary contact in their time zone.

1.12 - Send an email

The final action used is the Outlook Send an email (V2) (Preview) action where the content of the message is entered and the recipient is the Primary Contact from the 1.4 Primary Contacts action.

Show time!

When you run the Flow it will go ahead and do its magic.

I'll use a primary contact in Wellington, New Zealand from the Flow run history as an example. If we look at the convert time zone output, we can see that the UTC time has correctly been reflected as 4.30am. 

As seen in the earlier screenshot, 4.30pm in Wellington is equal to 4.30am UTC. Therefore the email is delayed until 4.30pm in the Wellington time zone. The Primary Contact will receive the email at 4.30pm in their time zone.

Summary

Delayed emails based on a individuals time zone can be achieved through the power of Flow. To go one step further you can also perform queries as well in the business scenario where only selected individuals need to receive the email.

Although after reading one of the reviews of my presentation there is one step that is missing in my original Flow. Maybe I was low in energy that day to recognize the gap. I used the date from the localTime which is great. However what should also be in the expression is to add 1 day so that the email is sent the following day.

My Flow is available for download from the TDG Power Platform Bank.

I also want to take the time to say big thanks to Jon Levesque and Gabriel for their massive effort in organizing and putting together a free Flow Online Conference for everyone. It was really great and I can't wait to see the next one with a new line up of speakers.

Read More

Developing your personal brand with Troy Hunt

Troy Hunt is a person who is well known by his creation of Have I Been Pwned and his presence on Twitter is also popular. He is currently a Microsoft MVP too. I recently had a chat with Troy about personal branding. For an explanation of what personal branding is, check out this blog post. Personal branding is common in the tech world. It naturally comes from the person's engagement through their online presence as well as in-person at conferences or events. If I use myself as an example, you can find my online presence on my blog site, YouTube, Twitter, and you'll also see me presenting at conferences and user groups.

Prior to my chat with Troy, I thought about questions that everyone can relate to - either as a person who is well known or thinking of increasing their presence. These are genuine questions and some of them I can relate to because I have friends in the community who have also experienced the '1%' that Troy refers to in our chat which you'll hear about. These questions also stem from my own curiosity of understanding what it is like when you are someone who has a high public profile because I don't have as much exposure. It was interesting to hear Troy's experience and I really enjoyed his honesty through out our chat.

Grab a coffee or tea and have a listen to our chat.

Note: this chat was done over Skype for Business and is raw footage. I was using WIFI from the Portugal Airbnb I was staying in at the time and the first couple of minutes are not the greatest video quality for some reason. Sometimes it is a bit laggy. It is fine to listen to and have it play in the background while you multitask 🙂

Questions asked

02:33 - What worked and what did not work for you along the way?

05:33 - Tall Poppy syndrome exists

10:56 - When you're well known, people may consider you or anyone else who has a high public profile as a celebrity. How do you remain intact in producing quality content to ensure that you are who you are, in terms of your knowledge and strengths and not just seen as a public celebrity

16:25 - When you're writing your content do you endeavour to keep to a schedule or do you write whatever comes into your mind or what's happening during that week

19:36 - With the content that you produce do you ever think about how to make it stand out or how to make it unique? As you know there's a lot of content that's getting written every week and there's more people out there who are starting blogs because technology in all fields is constantly evolving. Is it something you ever worry about it or you just shut it out and just mind your own content, and not worry about anyone else

22:06 - For content that was previously relevant at the time, let's say you wrote a blog post five years ago and now because of the evolving technology that's taking place, would you go back and revisit it, would you leave it as-is or would you write a new blog post to expand on it? To outline what has since changed or why it would not work or apply anymore to this day?

23:55 - Have you ever been called out by the 1% on something that you've written about in the past that's not relevant anymore? Has anyone in the last couple of years have been like - Hey Troy, by the way this thing you wrote back then is completely wrong 

25:06 - If you were the CEO or person in charge of LinkedIn, what would you do for a week in terms of changing it?

Key takeaways

• Consistency with how you conduct yourself online and in-person. There are people who behave differently online compared to how they are in-person which is not always a good projection of who they are. The brand that you represent online is in parallel to how you are in real life.
• Engaging in debates online such as Twitter can sometimes lead to no where and it's good to tune out early. When you stop engaging the debate usually dies down.
• Tall poppy syndrome can happen and is personally hard to manage at times. A person's achievements is not necessarily welcomed by everyone.
• People who respond in a negative manner online would not behave in the same way in-person.
• For the 1% who respond with irrelevant comments that are not directly associated to your message or blog post, either ignore once reading the comment or have a sensible neutral response.
• Learn to recognize the difference between the 1% compared to when you have said or done something that isn't well received on a broader basis that you would need to back track on and apologize for.
• Blogging is based on your technical or functional knowledge. Your public profile of being seen as a "celebrity" shouldn't influence you to write in a certain way for people to pay attention and think you're awesome.
• Writing (such as a blog post) does not always express sentiment well compared to a video. A video at times can be more convenient than writing a blog post especially if it is a busy week. Sticking to a set output is more likely to create unnecessary pressure. Videos take less effort which allows consistency in releasing content when time poor. Whereas a blog post can be more fitting when the information is better presented in words, such as a step-by-step guide on how to do something. The medium of choice can be dictated by the availability of your time and content.
• No need to be too concerned if someone else has written similar content as it's all based on your motivation for the content such as the previous example of capturing a step-by-step guide in a blog post. A guide is also something that can be continually looked at, especially if it is a process that is complicated. Not only can a blog (or a vlog) be useful for yourself but it can also help others too who are experiencing the pain of understanding how to do that same one thing.
• Writing a blog post about something new that pops up makes you take initiative in learning about it as well, even if you don't understand it to begin with. In Troy's case, if a notable security incident appears he'll learn it, research it and blog about it to have a finger on the pulse with what's happening right then and there.
• When you have a large volume of blog posts it can be hard to maintain them if the content was relevant then and not so much anymore. If someone takes the time to call something out that it is incorrect, then the content will be updated. If the topic is high profile, a breaking change or fundamentally wrong then in these scenarios it is good to revisit and evaluate whether new content is suitable as a follow-on.

I also asked my last question of what he would do if he was the CEO or person in charge of LinkedIn for a week and what he would to change it. You'll have to listen to find out his response 😁 It is refreshing to hear someone talk about the aspects of a platform that is both useful and unhelpful at the same time.

My thoughts

The one item that I don't relate to is how videos for him are convenient. I am the complete opposite, I spend a lot more time producing my vlogs than blogging 😅

Those of you who have followed me for a while know that my YouTube channel is primarily focused on sharing concepts and functionality of Dynamics 365, Dynamics 365 for Portals, CDS and Flow. I demonstrate to you face-to-face and walk you through the process which is also supported by a blog post for those who would rather read than watch. Those are a lot of hours sunk in from my own personal time. However I can relate to the feeling of pressure because I do put pressure on myself whenever I plan, film, edit and write my content. It is hard when you have had a busy period juggling a project or any other work related matters, plus life outside of work. I know there are others in the community who go through this as well and I am not alone. Troy goes through it, I go through it, and you may go through it too.

It was cool to hear Troy speak of his experiences and appreciate that he took some time out of his day for us to chat. Currently we're both travelling and luckily we were both in time zones that worked!

Hope you enjoyed this vlog and blog post! Thanks for tuning in and remember to be kind to each other 😊

Follow Troy

Twitter - @troyhunt
Blog - https://www.troyhunt.com
YouTube - Troy Hunt

Read More

Automatically update the stage of a Business Process Flow with Flow 2.0

From my last WTF episode:

I decided to use two Compose actions because I don't know how to store the Dynamics 365 API request in a Flow action. If you know how to do this, feel free to blog and tweet to me so that I can check it out.

I heard back from two people, hooray!

The first person I heard from was David Yack, he was wondering why I had chosen my uncanny method when really I could achieve the same outcome using the List Records action. When he explained it I was thinking to myself, hmmm... 🤔 oh... YEAH!!! It was so obvious only after hearing it from him haha. Like any other person who goes through the process of solution design - sometimes the answer is staring at you the whole time.

The second person I heard from was Yasir Safeer who I currently work with. He was going to suggest another method however I mentioned I already heard from David. However, he gave an awesome tip which made me go, really?! I tried it and to my surprise it worked.

This WTF episode talks about the two improvements I made to my Flow thanks to David and Yasir
#iAppreciateYou 🙏

Using a CDS List Records action

My Flow trigger is when the status reason of the Application has been updated. Next, I was using a CDS List Records action that would retrieve the Application Business Process Flow record so that I could identify the  associated Process Stage ID.

Rather than storing the Review stage ID and the Approved stage ID in two Compose actions after calling the API request in a separate browser using the Process stage ID, I can simply use the CDS List Records action against the Process Stage entity. I was able to use a filter query from David's suggestion that would only return the Process Stage record that had the stage name of "Review."

It's so funny to me because it was staring at me when I was reviewing the API request. It clearly tells you the entity to reference and the field as well. Thanks David for showing me way 😁 It was not obvious at the time but now it is.

I then applied a Parse JSON action where I used the first function. 

first(body('1.4.1_Get_Process_Stage_Review_record')?['value'])

I'm only after after a single record and the first function prevents the Apply to Each from appearing downstream in the Flow. I am using the properties later on in the Flow. I briefly covered the first function in another previous WTF episode so take a look when you can.

I applied the same methods for the Approve stage however what you'll see differently this time is that I am using parallel branching. Parallel branching is useful when you have actions that can be executed that are not dependent on the previous action or each other. Since I am retrieving the stage IDs through the List Records action both actions can run at the same time.

Changes to the last action

My very last action in the Flow is updating the Application Business Process Flow record. In the Active stage field of the action I am referencing the stage ID through the Parse JSON action. Previously I was achieving this through my two compose actions.

The cool tip that Yasir shared with me is that you don't need to update the Traversed Path value. I was puzzled because I followed what was in the docs.microsoft.com article but I trust Yasir's advice given that he's an awesome consultant so I gave it a go. I cleared my previous expression and left it blank.

Flow in action

When you submit an Application, the Flow succeeds as I expected.

When there are two approvals (one from the External Reviewer and the other form the Internal Reviewer) the Flow succeeds as I expected.

In both use cases the traversed path correctly updates. Amazing. Thanks Yasir, good tip man.

Summary

By using a CDS List Records action against the Process Stage entity, it will allow you to reference to the stage IDs of your Business Process Flow entity. You also don't need to use an expression to update the Traversed Path field either, it magically works 🧙‍♂️

My WTF Flows is available for download on the TDG Power Platform Bank.

Till next time, toodles.

Read More