Automated Bulk Qualification of Lead to Opportunity

Business Requirement: We need to automate on an hourly basis where based on the Payment Status , we will qualify leads to opportunity.

Solution: We are writing a Scheduled Cloud Flow here with a recurrence of 1 hour where we will extract all Open Leads having ‘Payment Done’ Status as “Yes” and qualify it to Opportunity.

My Flow goes as follows:

Write the Fetch XML Query here with filter:
Status = Open
Payment Done: Yes

Then perform a Bound Action a follows:

Please make sure you fill the below column otherwise you will get an error.

Here is an Open Lead with Payment Status as “Yes”

Let’s run the flow now:

The flow runs successfully and we see that the Lead Record is qualified.

& an Opportunity Record is created as follows:

Further to this you can apply logics of closing activities related to your Leads according to what your Business Requirement is.

Hope it helps!

& the Power Quote of the day is:

“Wake up with Determination, Go to bed with Satisfaction”

Create a Task from Team’s Message

Day-to-Day Requirement: We have loads of incoming messages in Teams that we want to create a task for so that we have a tab on things in one place.

Solution: “For a selected message” is a new trigger added for Power Automate to integrate better with Microsoft Teams. This will let you to manage actions for a specific message, selected by a user.

Most Important Tip! Currently, this trigger works only with flows that are created in the Default environment. Thus, it will be only visible in teams if the flow is created in the Default environment.

Steps: Sign in to Power Automate > My flows > +New Flow> Automated Cloud Flow > Enter a name for your flow > Select the “For a selected message” trigger.

The For a selected message trigger has an optional in-built input in the form of an Adaptive Card. You may use this to collect additional information from the user who triggers the flow. For instance, if the flow creates a task (which we are going to do for this blog), you can use an adaptive card to collect information like the comments, due date or the importance of it.

Click on Edit Adaptive Card–> This displays an inline adaptive card editor, where you can drag and drop card elements to construct your own form. You may use this card or create from Blank.

I haver designed my Adaptive Card as follows:

Each input within the adaptive card form has to necessarily have an ID and they must be unique. You can use this ID later in the flow through dynamic content to reference inputs that a user might have entered as part of running the flow. Here “duedate” , “choice” and “comment” are the IDs I have used for input.

Also several message elements are available as a trigger output for use within the flow. Here’s an overview of some of the properties:

Message content: The full HTML content of the Teams message.
Plain text message output: The plain text variation of the Teams message.
Link to message: A direct URL to reference the message.
Sender display name, Sender ID: The details about the user who sent the message.
Originating user display name, originating user ID: The details about the user who invoked the flow.

My next step goes as follows:

Let’s see it in action:

PS: The name of the flow is used to reference the flow within Teams, so be sure to provide a descriptive name for it.

One of the really important returned values is “LinkToMessage” – Once clicked, it navigates to the request message, highlighting it with yellow background so that user knows which message the request is about. Check it out here.

I know there is an out-of-box “Create Task” available in Teams- it’s just an illustration so as how you can extract details with such a trigger. Also, it’s just a matter of time that this trigger may be available to use in other Environments.

How do you like this feature? Let me know in the comment section.

& the Power Quote Of the day is:
“When Nothing is Sure, Everything is Possible”

Send Email to all Team Members via Power Automate

Scenario: I have chosen to send email to all team members of a particular team on creation of a Task. You can choose any condition or a dynamic team for any of your business requirements- here is just an example.

My Flow goes as follows:

Trigger : Create an Automate Cloud Flow when a new row is added, modified or deleted. I am doing it for Task

Then list down the team members for the required team you want. You can do this by accessing the table “teammemberships” – use it as a custom value as you will not get it in the drop down. Apply the required filter or Fetch XML Query according to your requirement.

Apply to each of the member records you get:
First get each of the rows by their ID as in the image below-

Then, access the Email ID of that Team Member (select Primary Email from dynamic content) and send an Email [Make sure the users have the Email Office365 E3 license assigned to them.]

Let’s test it:

I am creating a Task as follows:

This triggered a flow, which runs successfully.

I have two members in my team- the email has been sent to both of them.

You may design the email content as per requirement.

Hope it helps!

& the Power Quote of the day is:
“The real measure of your wealth is how much you’d be worth if you lost all your money.”

How to update stage in a Business process Flow via Power Automate?

Scenario : We will update the Lead To Opportunity process from to Develop to Propose when the Status is “In Progress”. Frankly you can choose any condition in any BPF you want , this is just an example.

Basis: We need to understand that the Stage ID, Process ID & the Traversed Path remains the same when we move to various environments. Thus we can use it as a static variable wherever required. No need to obtain them dynamically.

My flow is as follows:

Trigger: When the opportunity record is created or its Status is modified. Put the condition in the Row Filter or put the condition after this step explicitly.

List the row of “Lead To Opportunity Sales Process” where the opportunity id will match the id from where the trigger was generated.
[You must be aware that when we create a process, an entity is also created for it.]

Now comes the most important step – you have to fill the following fields necessarily:

1. Choose the table name.
2. Row ID – BusinessProcessFlowInstanceID
3. Active Stage – StageID u want to move it.
4. Lead -The required record id from where the trigger was generated.
5. Process – Process ID
6. Traversed Path – the Traversed Path

You already know how to fill Lookups from my earlier Transformation Of Data blog .Just write the PluralTableName(GUID). Also you may store the Stage ID, Process ID & the Traversed Path in separate variables above and use it in the flow.

The run is successful:

Let’s see it in action:

Hope it helps!

& the Power Quote of the day is:
“You do not need a new day to start over, You only need a new mindset.”

Business Required Validation during Data Import

What if you want to upload an Excel Sheet for a Table and make certain fields as required only in case of Imports from Excel, otherwise those records should not be imported.

Solution: I added an Option “Import” to the OptionSet column “LeadSource” in the “Lead” table.

Further I am writing a workflow as follows:

You may put customized messages for the particular fields or else put all conditions under one check condition and give the message as “Required Field Empty”.

Activate the Workflow.

My Excel sheet looks like the following:

Import it and see the results- as you may see we have 2 success cases & 3 failure cases.

You can check the message in the “Failure Records”

I hope you find it useful! Stay Safe Champs.

& The Power Quote of the day is:

“What you think of yourself matters much more than what other think of you”

DATA TRANSFORMATION VIA POWER AUTOMATE – PART 2

Continuing from where I left, today we are going to learn about how to upload Date & Time datatype into Dataverse.
If you simply choose the date only data, the value comes like in integer as shown below:

Thus “22-Nov-2015” is shown as “42330” now. Basically, the integer shown is the no. of days counted since the end of 1899. Thus, we have to convert it accordingly.

Write the following expression if it’s a Date Only field :
addDays(‘1899-12-30’, int(items(‘Apply_to_each_2’)?[‘Expiry Date’]), ‘yyyy-MM-dd’)

Over here you are adding the required date (which is actually no. of day since end of 1899) to the original date “1899-12-30” as an integer in the required format.

Bu what if it’s a Date & Time field?
For this, the number comes as decimal as shown below.

Then simply write,
addMinutes(‘1899-12-30’, int(mul(float(items(‘Apply_to_each_2’)?[‘Expiry Date’]), 1440)), ‘yyyy-MM-dd HH:mm tt’)

Here we are converting the entire thing in minutes (We hav1 1440 minutes in a day and hence multiplying the resultant with this) and then adding it to the original date to get the final one. [Make sure to keep the Timezone same.]

You can also try doing this with “ticks” which I have explained it in one of my earlier blogs.

Shortcut Tip: Microsoft has now come up with the option of directly dealing with Date&Time when u upload it from Excel. Just select the Date Time Format as ISP 8601 and everything will be taken care off. [ISO 8601 uses the 24-hour clock system. As of ISO 8601-1:2019, the basic format is T[hh][mm][ss] and the extended format is T[hh]:[mm]:[ss].]

Output: The flow runs successfully.

You can see related Training Attendees Information for the contact Shalinee Goel(Member ID: M001)

All the data is smoothly transformed into Dataverse.

1. For Date only.

2. For Date & Time

& the Power Quote of the day is:

“It always seems impossible until it’s done”

DATA TRANSFORMATION VIA POWER AUTOMATE – PART 1

Today we are going to upload data from an excel sheet to a table “Training Attendee” in Dataverse for rows whose Member ID (in Contact table) matches with the table “Contacts” in D365.We are considering the following types of data types to be uploaded:

1. Single Line of Text
2. Whole Number
3. Lookup
4. Option Set
5. Date & Time

Firstly, my excel sheet looks like this:

It shows the Members who have done the Certifications for the given 3 courses and its associated details like Certification Number, Expiry Date & Grade. The Member ID is a unique identifier for identifying the contact.

& a Snapshot of the flow I created is as follow:

Now let me make you go through it step by step.

Create a flow and type initialize in the search bar and select “Initialize Variable” under “Action”

First, we initialize 3 array variables for storing the data of the 3 courses separately as follows:

Then we list the rows present in the excel sheet (make sure here that u have formatted your excel sheet as table otherwise in the Table section you won’t get an option to select). Choose your source location of excel and the required path.

Now for each row of the excel, let’s apply the condition:

Here we are checking if the Certificate Number1 & Member ID are not blank, and if it’s not blank, then we are adding the Member ID, Certificate Number1, Expiry Date1 & Grade1 from the excel into Course 1 variable array. Similarly, we will do for Course 2 & 3. You can select these dynamically from the drop down and append to array in the JSON format as shown in the image.

Now compose to save the array and select the variable to be composed. The details of the Members who have done Course 1 are listed now.

Apply to each row of this output and match it with the Member ID present in contact database by filtering rows by:
column_schema_name eq ‘items(Apply_to_each_2’)?[‘Member ID’]’ as shown below:

You will only get one row but the output can be more than 1 – hence it comes as Apply to each. Once the matching Member ID is found, it adds a new row to the table “Training Attendee” and add the required columns.

1. Single Line of Text [Certificate Number] –> (‘Apply_to_each_2’)?[‘Certificate Number]

2. Whole Number [Rank] –> int(items(‘Apply_to_each_2’)?[‘Grade’])

3. Lookup (Attendee-Contacts) –> contacts(items(‘Apply_to_each_3’)?[‘contactid’]

For Lookups , it works like “plural_name_ of_that_table(guid)”

PS: You may select these items from the Dynamic Content. Be careful from which step are you choosing the result value of. In the above example, for 1 & 2, we are taking the result from Step ‘Apply_to_each_2’ but for 3, we are choosing the result from step ‘Apply_to_each_3’.

4. Option Set/ Two Options –> Simply select from the dropdown list available.

5. Date & Time –> The approach is a bit different for this datatype- will be explained in the upcoming blog.

& the Power Quote of the day is:

“You’re braver than you believe, and stronger than you seem, and smarter than you think.”

Power Automate – Ticks are fun!

So, what are ticks & why we need to know them?

These are important as long as we know that every date/time can be represented in “ticks” and can be converted from “ticks” to a useful unit of measure. This blog will help you calculate the difference in days between two dates in Power Automate as there is no datediff() function present.

Business Requirement: I wanted to know the no. of days that has been since the Lead was created and act upon if they cross more than 7 days. For this I wanted to calculate the no. of days from the created date till today and had to apply the logic in a recurrence cloud flow.

Theory: Ticks is a useful way to calculate the date difference between two dates.

It returns the number of ticks which are 100-nanosecond intervals, since January 1,0001 12:00:00 midnight up to the specified timestamp.

Solution: We write the following expression to calculate the no. of days between two dates

div(sub(ticks(formatDateTime(utcNow(), ‘yyy-MM-dd’)), ticks(item()?[‘createdon’])), 864000000000)

Humongous! (Not really), but let me break it down for you.

Firstly, I get the current time – utcNow() in ticks and then the created on date- item()?[‘createdon’]

So, by calculating the difference between these two date/times in that unit, we have a lot of flexibility. Thus, we subtract them using “sub”
Finally since ticks as a unit aren’t useful from our perspective of things, hence I divide the result by the magic number 864000000000 using “div”.

But what is this number? Why 86400000000. Well, here’s the Math – There are
(24*60*60*(10^9))/ (10^2) ticks in one day i.e., the number of 100-nanosecond intervals in a day. Thus, we divide our expression with this number to get the result back in days.

Use: So now you may easily compare between two dates, or find differences between them easily.

You may see the result here:

& the Power Quote of the day is:

“Winning doesn’t always mean being first. Winning means you’re doing better than you’ve done before”

To match the Security Role of the Current User to a desired Security Role via name

Continuing from my last blog, where we saw a method of getGlobalContext.userSettings, today we are going to see a property of it.

userSettings.roles : This returns a collection of lookup objects containing id and name of each of the security role or teams that the user is associated with.

PS: This method is supported only on Unified Interface.

Business Requirement: In one of the recent requirements, I had to match the name of the desired security role to the current user’s security role via name to lock and unlock certain secure fields.

Solution: You can write the following code –

var DesiredSecurityRole1 = “Name_of_the_Role1”;
var DesiredSecurityRole2 = ” Name_of_the_Role2″;

//On load event for required form to apply the desired logic
function onLoadofDesiredForm(executionContext)
{
var globalContext = Xrm.Utility.getGlobalContext();
var userRoles = globalContext.userSettings.roles.getAll();
var formContext = executionContext.getFormContext();
if (hasSecurityRole(userRoles, DesiredSecurityRole1) || hasSecurityRole(userRoles, DesiredSecurityRole2))
              {
                             //Apply Logic here

               }
else
{
//Apply Logic here
}
}

//Check if user has a role with certain name
function hasSecurityRole(userRoles, roleName)
{
var hasRole = false;
for(var i=0; i < userRoles.length ; i++)
{
var userRoleName = userRoles[i].name;
 if (userRoleName == roleName)
                           {
hasRole = true;
                             break;
                            }
  }
return hasRole;
}

Hope it helps!

& the Power Quote of the day is:

“If you’re waiting until you feel talented enough to make it, you’ll never make it.” – Criss Jami

Script to know User Time Offset from UTC in Model Driven PowerApps and Dynamics 365 Apps

Business Requirement: Sometimes we need to see the offset of the user timezone from UTC time zone in client-side script.

Solution: You may write the following javascript function to achieve this requirement:

function getOffSetValue()
{
var userSettings = Xrm.Utility.getGlobalContext().userSettings;
var OffsetMinutes = userSettings.getTimeZoneOffsetMinutes();
//Check if Offset is from IST
if (OffsetMinutes == 330)
{
//Apply logic
}
}

Theory:
Xrm.Utility.getGlobalContext().userSettings – This returns the information about the current user settings.
The userSettings object provides a lot of properties and methods, one of which is:
userSettings.getTimeZoneOffsetMinutes() – Returns the difference in minutes(format: number) between the local time and Coordinated Universal Time (UTC).

Hope it helps!

& the Power Quote of the day is: 

“I’m always one time zone behind myself.” – Eric Bana