Thursday, 28 November 2019

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 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 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!


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.

Thursday, 21 November 2019

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


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.
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.


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.


  • 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