Creating a Lead for every ClickDimensions Posted Form using Power Automate

Recently, my good friend Megan Walker did a guest post for ClickDimensions showing how to create a Lead for every ClickDimensions Posted Form and if you haven’t read it then you really should!

When I was reading this post one of the things I noticed was the requirement to use a Filter Array step and Compose step for each question in your Form in order to be able to use them when you created the Lead. This approach works perfectly, but it could be quite time consuming to create if your Form has lots of questions, so I wondered if there could be a way to simplify it a little bit.

Please note, for the purposes of this post I’m only going to be focusing on an alternative way to get the Posted Field data and use it to create your Lead, Megan has covered everything else in her post so please read it!

If you’d prefer to watch a video overview of this blog, click here.

The Solution

The first step is still to List your Posted Fields using the List Records action. If you’re using the Common Data Service (Current Environment) connector then you’ll be able to select the specific attributes you want returned. In this case we’re only interested in the Label (i.e. the Question) and the Value (i.e. the Answer). You should also set the Order By to the Label; this will ensure consistency in the returned results which is important for later steps in the Flow.

If you look at the Output for the List Records action, you’ll see that it gives a JSON array that looks something like:

We want to make matching pairs for the Questions (Labels) and Answers (Values) from each of the returned Posted Fields and combine them in an array. Fortunately for us, in Power Automate there is an Action called Select that allows you to “Select the specified properties from all elements of the ‘From’ array into a new array”. This is really just a fancy way of saying you can take the elements of the output above that you like and keep them, while disregarding the rest, and you can reshape them into pairs as required.

For the Select action, we take the data From the output of the List Records step, and then we create a map of the Label and the Value as a name pair

When this is done, the output from the Select action will look something like this:

This is much better!

As you can see, the Questions and Answers have been combined into array elements. You can also see that, as we set the Order By to the Label in the List Records Step above, the array elements are listed alphabetically. One important thing to note at this stage is that each element in an Array has an index number beginning from 0, so you can identify the array elements I’ve returned above as follows

The final step for creating the Lead is to add a Create a New Record action, and then we’re going to use an expression to pull the values from the output above into the fields we need to populate:

As you can see, for each field we are populating we have an expression with the following format:

@{outputs('Select')['body']?[0]?['Company Name']}

The key things to note in the construction of this expression are:

  1. for the Outputs expression, the name ‘Select’ must match the name that you’ve given to the Select step
  2. the Integer number [0] must match the element number for the array element as indicated above. For example, if we want to set the Telephone Number we will use [4]
  3. The final part of the expression [‘Company Name’] must match the text of the Question from the array.

If we wanted to get the Email Address instead of the Company Name then the expression would be:

@{outputs('Select')['body']?[5]?['Work Email Address']}

After we’ve completed all of the above our flow now only has three actions to retrieve Posted Fields and to create a Lead with the answers submitted:


Conclusions

The first thing to say is that none of this would have been possible without the valuable insights of Megan Walker and Rob Dawson. I keep saying this, but one of my favourite things about this community is the collaborative efforts we all make to help each other.

The second thing is that while this works, if the Questions on your form were to change, either by adding/removing questions or by changing the Label of the questions then this would probably affect the success of the Flow, so it might be worth putting some additional validation logic in to catch any potential issues like that.

The final thing is that Power Automate is so powerful and it’s amazing what you can achieve with some lateral thinking. This has been a fun little challenge for me, and hopefully you find it useful. Please reach out to me if you have any questions or comments.

Using Flic Buttons with CDS – Managing Fire Evacuations

I was asked to create a tech demo in work recently that could show off some of the capabilities of Power Apps and Power Automate, and I figured this would be a perfect opportunity to use the Flic button I got from Matt Beard of Data8 when I attended the User Group Summit in Amsterdam earlier this year .

The Scenario

Anyone who’s worked in an office will undoubtedly have experienced the fun of a fire drill (usually on a day where it is cold and miserable). I thought it would be a great demonstration of the Power Platform if we could create a system to record when a fire evacuation has occurred in one of our offices so we could notify the other offices, and to send notifications when the evacuation was over.

The Setup

The first thing to do was create a new Evacuations entity in CDS where I wanted to record the following:

  1. Evacuation Start Date/Time
  2. Evacuation End Date/Time
  3. Office being evacuated
  4. Any comments/notes about the evacuation

It would be really simple to manually record the evacuations in here, but where’s the fun in that? I had the Flic buttons at my disposal, and I was going to use them!

Using a Flic Button

For those of you who are not aware, Flic buttons have three trigger events:

  1. Click
  2. Double Click
  3. Hold

Each of these triggers can be mapped to a specific action, but the exciting part for us #PowerAddicts is that you can use them to trigger a Flow.

The Flows

For the purposes of this tech demo, I wanted to have two flows:

  1. Create an Evacuation Record
  2. Close the Evacuation Record

Create an Evacuation Record

I created an Instant Flow that is triggered “When a Flic is Pressed”:

As you can see from the screenshot above, there are two options on this trigger. First you select the Flic Button you’ll be associating with the Flow, then you select one of the available Events; I’m going to be using a “Click” event for this Flow.

When you use a Flic trigger, it returns the Click Time in the following format:

2019-11-12T19:19:43Z

This isn’t particularly presentable, so I’ve added a Compose step to format the Date using the following expression:

formatDateTime(triggerBody()?['clicked_at'], 'dd MMMM yyyy')

This compose step formats the Click Time above as “12 November 2019“, and I’m going to use this when I create the record in CDS

The last step in the Flow is to use the Send an Email (V2) action to send an email to everyone in my organisation to let them know our office has been evacuated

And that’s it, we have a simple Flow configured run on clicking a Flic button to create a record in CDS when an evacuation is happening and to notify all employees in our organisation. The next step is to create a Flow to close the evacuation record when everyone returns to the office

Close the Evacuation Record

For this Flow, I’m using the Same “When a Flic is Pressed” trigger as above, but this time I’m going to use the Double Click event. For this Flow I want to retrieve the Active Evacuation records for this office and set them to Inactive, recording the time that the Evacuation was ended.

If you want to find out an easy way to generate OData queries to use in the List Records step, then I’d recommend reading Sara Lagerquist’s recent blog on this topic

Once the Evacuation records are closed, I use the Send an Email (V2) action again to send an update email to all employees to tell them that the Evacuation of the office is now over.

Conclusions

This is a relatively simple use case for using Flic buttons but it shows the power of combining IoT devices with the Power Platform and it reinforces my belief that “no UI is the best UI”. Through two actions we have a complete record in our database, and it’s required no input from the User other than a Click and a Double Click

In part 2 of this blog, I’ll show how I created a canvas app for reviewing and monitoring Evacuations, and how to use the location data so the button knows which office has been evacuated.

Filtered Lookup Field based on Linked Entity using North52

If you’ve ever had a requirement to filter lookup fields then you’ll no doubt be aware that this is possible in Dynamics 365, but that there are some limitations to the functionality.

Microsoft have done a great job of enabling out of the box filtering for simple scenarios using the “Related Records Filtering” options or by limiting the records returned using specific view(s)

To read more about the options available out of the box I’d recommend referring to Carl de Souza‘s blog post – https://carldesouza.com/filtering-lookup-fields-in-dynamics-365/

For the more developmentally minded amongst us there is also the option to use the addCustomFilter JavaScript function, more information on which can be found on the Microsoft Docs site – https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/controls/addcustomfilter

For those who are comfortable with JavaScript I’d recommend reading Aileen Gusni‘s posts about this for some tips and tricks – http://missdynamicscrm.blogspot.com/2016/09/utilize-custom-action-to-help-filtering-lookup-view.html

The Scenario

In my scenario we have a Peer Review entity to record the outputs of peer reviews carried out for activities related to an Account. The Peer Review entity has several Reviewer Roles which are lookups to the User entity. The lookups need to be filtered to only show Users who are in the Account Team. I’ve mapped the relationships between the entities below:

I tried to get this to work with the OOTB options, but found that I couldn’t quite get them to work for this scenario. I also looked at the JavaScript options but again ran into issues, primarily because I need the filtering criteria to be dynamic on each Peer Review record depending on the selected Account, whereas the JavaScript was a bit prescriptive for me. (Note: I’m not a coder, so someone cleverer than me could probably get it to do what they needed).

However, in exploring the JavaScript i stumbled upon a potential solution. You can use an “in” operator in a condition in your FetchXML to specify the list of values to be returned, like so:

<filter type='and'> 
        <condition attribute='YOUR_FIELD_HERE' operator='in'>
          <value>{YOUR_GUID_HERE1}</value>
          <value>{YOUR_GUID_HERE2}</value>
          <value>{YOUR_GUID_HERE3}</value>
        </condition>
</filter>

If I could figure out a way to make this list of values dynamic then that would solve my problem!

The Solution

To solve this issue I turned to my trusty old friend North52. I’ve written previously about using looping functions and I’ll be doing something similar here.

The first step is to get the FetchXML to get the Users from the Team, which I’ve done using advanced find to output:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
    <entity name="systemuser">
        <attribute name="systemuserid" />
        <link-entity name="teammembership" from="systemuserid" to="systemuserid" visible="false" intersect="true">
            <link-entity name="team" from="teamid" to="teamid" alias="ab">
                <filter type="and">
                    <condition attribute="teamid" operator="eq" value="{0}" />
                </filter>
            </link-entity>
        </link-entity>
    </entity>
</fetch>

As you can see above the value in the teamid condition is set to {0}, and we’ll set this dynamically in the ClientSide – Perform Action formula, which is below:

Smartflow(

  ForEachRecord(

    FindRecordsFD('TeamMembers', true, SetParams([ryan_peerreview.ryan_accountid.ryan_accountteam.teamid.?])), 

    Case(RecordIndex(),

      When(0), Then(SetVar('teammembers', StringFormat('<filter type="and"><condition attribute="systemuserid" operator="in"> <value>{0}</value>', CurrentRecord('systemuserid')))),

      When(RecordTotal()-1), Then(SetVarConcat('teammembers', StringFormat('<value>{0}</value></condition></filter>', CurrentRecord('systemuserid')))),

      Default(SetVarConcat('teammembers', StringFormat('<value>{0}</value>', CurrentRecord('systemuserid'))))

    )

  ),

  AddPreFilterLookup('ryan_primaryreviewerid', 
    'q1z', 
    GetVar('teammembers'), 
    'systemuser')

)

I’ll explain the key elements of this formula below:

SmartFlow: SmartFlow allows you to run multiple actions in one Formula

ForEachRecord: ForEachRecord is a looping function, and it iterates through the output of the FetchXML query we created above using the FindRecordsFD function and carries out the actions specified. As mentioned above, I set the value of the TeamID to be {0}, and now I use the SetParams function to define the value that will be put in here.

As we’re using ForEachRecord to loop through the records returned by the FetchXML, I will use the Case function to create a variable for the filter that I will be putting on the lookup field using the SetVar/SetVarConcat functions

The Case function works by splitting the Filter FetchXML into 3 parts:

  1. The Opening section, which includes the open tags for the Filter and Condition, and the first value returned from the FindRecordsFD function
  2. The Looping section, for all the values between the first and last values returned from the FindRecordsFD function
  3. The Closing section, which includes the closing tags for the Filter and Condition, and the last value returned from the FindRecordsFD function

To make this work with the Case function, we use the RecordIndex function, which contains an integer with the current index number of the loop, so the Case function can be described in plain English as:

WHEN we are on the first loop, THEN create a variable with the opening section of the Filter FetchXML;
WHEN we are on the last loop, THEN concatenate the variable with the closing section of the Filter FetchXML;
OTHERWISE if we are not on the First or Last loops, THEN concatenate the variable with another value

When we have created the Filter FetchXML we use the AddPreFilterLookup function to add the filter to the selected field.

Once we’ve done all of this, the field will show only the people who are in the Team related to the Account on the Peer Review record:

Conclusions

I think this is a good method of dynamically altering the available options in a lookup field, and I can envision a number of useful scenarios for this functionality, but please leave a comment below or reach out to me on social media with your thoughts.

Postcode Region Mapping via Workflow

I recently delivered a session at Dynamics 365 Saturday Scotland covering some advanced functionality you can implement in your Dynamics 365 environment using free custom workflow activities.

To read my thoughts on #D365SatSco and how amazing it was, see the article I posted on LinkedIn

One of the scenarios I covered in my session was looking at how we can carry out regional analysis of our account using workflows, and I’ve outlined my solution below.

The Scenario

For this scenario I wanted to be able to check if the postcode that had been entered for the address on an Account was valid, and if so I wanted to be able to extract the outward code and use this to map the Account to it’s postcode area, locale, sub-region and region.

The Setup

For this scenario I added the following to my environment:

  • A new Entity called Region Mapping, containing
    • An Option Set with 4 options:
      1. Postcode Area
      2. Locale
      3. Sub-Region
      4. Region
    • A hierarchical Parent lookup field
  • Added fields to the Account entity
    • A single line of text field called “Extracted Postcode”
    • 4 lookup fields to the Region Mapping entity (one for each Option in the Option Set

Once this is all created, I imported my dataset, which I derived from data sources from the Office for National Statistics. You can download a copy of my dataset below:

The Workflow

To create my workflow I used tools from two different custom workflow assemblies:

  1. Jason LattimerString Workflow Utilities
    1. Regex Match
    2. Regex Replace with Space
  2. Alex ShlegaTCS Tools
    1. Attribute Setter

Step 1 – Postcode Verification

In the UK, all postcodes follow standard formats, so it makes it relatively easy to determine if the postcode is valid or not. For my workflow I’m using the Regex Match step, so I need a Regex pattern to use. I wanted to be able to separate out the outward and inward sections of the postcode, so the expression I ended up with is:

((?:(?:gir)|(?:[a-pr-uwyz])(?:(?:[0-9]?)|(?:[a-hk-y][0-9]?)))) ?([0-9][abd-hjlnp-uw-z]{2})

I am not an expert at Regex, but I am very good at googling! I added this pattern to Regex101, which does a great job of explaining the component parts if you’d like to understand it further

The output from a Regex Match step will be True or False. If it returned false you could use a cancel step in your real-time workflow to display an error message to your user informing them that their Postcode was not valid

Step 2 – Extract Postcode Area

As I mentioned above all UK Postcodes follow standard formats, and this particularly true for the second part of the postcode which is always one number followed by two letters. To carry out my region mapping I needed to be able to extract the first part of the postcode, so I used the Regex Replace with Space step to replace the second part of the postcode with 0 spaces, in effect just deleting it.

From my Regex pattern in the previous step, I used the second capturing group to match with the second part of the postcode:

?([0-9][abd-hjlnp-uw-z]{2})

The output from this step leaves us with the first part of the postcode, so we update the Extracted Postcode field on the Account entity with this, and we’ll use that in the next step.

Step 3 – Run the Attribute Setter

I’ve previously discussed Alex Shlega’s Attribute Setter, and it’s one of my favourite custom workflow activities. It’s super easy to work with and allows you to dynamically set lookup fields from within your workflow.

The first thing to do is to create a Lookup Configuration with a FetchXML query to find the record you will be setting in the lookup field. For mine, I’ll be looking for the Region Mapping record that matches the extracted postcode. As I’ve discussed before, the magic in the Lookup Configuration is the ability to dynamically pass values to the FetchXML query by putting the schema name of the field that contains the value inside a pair of # marks.

The key part of the FetchXML query abouve is the second condition:

<condition attribute=”ryan_name” operator=”eq” value=”#ryan_extractedpostcode#” />

By putting the schema name of my Extracted Postcode field, whatever value is in there will be added to my query when it is run by the workflow. The Attribute Setter will output the GUID of the Region Mapping record (i.e. the Fetch Result Attribute) and it will set it in the Postcode Area lookup field on the Account (i.e. the Entity Attribute)

Step 4 – Update Account

The final step, now that the Postcode Area has been updated, is to run a child workflow to update the Locale, Sub-Reigon and Region fields. For each of these fields, we’ll run an Update Record step, and select the Parent of the predecessor (i.e. for the Locale we will find the Parent of the Postcode Area field value

Conclusion

This is a relatively simple approach to allow you to carry out regional segmentation of your Accounts, which can be used for marketing purposes or for reporting.

If you’ve found it useful, or if you have any other ideas then please reach out to me on Twitter or LinkedIn

Excel Project Plan

This isn’t strictly a CRM/D365 post, but I think it could provide some assistance for planning CRM related projects, so I thought I’d share.

Any good CRM project, whether that be a new deployment or a small change, requires planning to ensure it is effective; the 5 P’s cliché “Proper Preparation Prevents Poor Performance” exists for a reason.  I am aware that plenty of people use Microsoft Project or use D365 Project Service Automation (if you want to learn more about this I’d highly recommend reading Antti Pajunen‘s excellent blog posts about PSA), however I am also aware, from my experience of working in small companies, that the licence costs for these products can be prohibitive.

A Simple* Solution

Any company that utilises the Microsoft Office technology stack as part of their business will have access to Excel, and therefore they’ll be able to utilise the vast array of templates that Microsoft have made available to help them in their business.  I’ve used many of them in the past, and continue to do so today.

There are many Project related templates available for Excel, and I recently saw the Agile Gantt Chart template.  This template is great because it provides a decent foundation for a Gantt chart, but there a number of areas I felt it was lacking, so I’ve modified it to try and make it more suitable for my purposes.

My Template

My concerns with the template available from Microsoft are:

  1. There is no ability to automatically schedule task completion dates
  2. There is no ability to include predecessors for tasks
  3. There is no ability to effectively resource manage tasks

With all of this in mind, I thought it would be a fun task to see if I could implement some improvements.

Project Plan Template

I’ve included a link below to download my version of the template.  The key features I’ve added are as follows:

Activities are added by:

  1. Selecting a Component from the drop-down selector in the Component column
    1. The Component drop-down is populated from the Component Column in the High-Level Summary Dates table on the Project Summary worksheet
  2. Manually inputting an Activity description in the Activity Column
  3. Selecting a Task from the drop-down selector in the Task column
    1. The Task drop-down is populated from the Task Column in the Mid-Level Summary Dates table on the Project Summary worksheet
  4. Selecting a Category from the drop-down selector in the Category Column
    1. Goal marks the Activity with a Goal marker on the Gantt chart
    2. Milestone marks the Activity with an Activity flag on the Gantt chart
    3. On-Track, Low Risk, Med Risk and High Risk format the cells on the Gantt chart in accordance with the format on the Legend at the top of the sheet

 

Start Dates are calculated as follows:

  1. Each Activity starts on the End Date of the preceding Activity in the list, unless:
    1. A Predecessor is selected by inputting the ID of the predecessor in the Predecessor column; and/or
    2. A number of “Lag Days” in working days is input in the Lag Days column
    3. An Actual End Date is entered for either the preceding task or the predecessor

 

End Dates are calculated as follows:

  1. The estimated effort in Working Days is input into the Effort (Working Days) column
  2. Responsibility for the task is allocated to a person using the drop-down selector in the Responsible column
    1. The Responsible drop-down is populated from Name column in the table on the Project Personnel worksheet
  3. The Task Duration is automatically calculated as the Estimated Effort / Effort Profile (from the Profile column in the Project Personnel Sheet), and is rounded up to the nearest ¼ day
    1. E.g. a task with an estimated effort of 1 day, allocated to a person with an Effort Profile of 50%, would have a Task Duration of 2 days
  4. Any holidays to be accounted for are documented in the Holidays table on the calcs worksheet
  5. The End Date is therefore Start Date + Task Duration (in working days), and ignores any holidays

 

If you want to use this template you can Project Plan Template

Let me know if you find it useful!