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

Play the Playbook- Automation (Part-5)

One of the hindrances I have with the Playbook is that it is extremely manual. I find it somewhat unrealistic to think that a user would know when to launch the Playbook. As such, I see a tremendous amount of scope in automating the launching of the Playbook which we learnt in our last blog. In this one, we will review how to do it. Let’s get started!

The ability to associate a playbook with a Workflow and Business Process Flow (BPF) is an interesting feature. This enables the users to automate the Launching of a Playbook based on the Entry or Exit of BPF stage. The Workflow will automate the launching of the playbook.

Requirement: Lead qualification is an important process in the sales cycle, where the seller needs to evaluate various aspects, such as the buyer’s need and budget, product/service relevance, and competition, before qualifying the opportunity. We would like a playbook to be launched automatically every time a new lead is created, to guide the seller through the lead qualification process.

Prerequisites: To get started, first create and publish a playbook template containing a checklist of tasks for the seller when a new lead is created.

Steps: A workflow or an action can be used to automate the process of creating a playbook record

In the Create Process dialog box, start by filling the process name and then:
Category: Workflow
Entity: Lead
Run this workflow in Background: Tick it
Click OK.

Further Workflow Details

Available to Run – Set to ‘As an on-demand process’.
Scope: Set to Organization.
Start when: Record is created.
We want a playbook record to be created when this workflow runs. To do so,
Select Add Step > Create Record >select Playbook.

Click on Set Properties > Add a Name to the Playbook > Set the Lead to the Regarding field > Choose the playbook template > Save and Close.

Note that this playbook template was created with Track Progress set to No. As a result, once the playbook is launched, the activities created are linked directly with the lead record. If Track Progress is set to Yes, the activities created roll up to the playbook record, which, in turn, roll up to the lead.

Once everything is setup on the Workflow be sure to click Activate.

Once the workflow is activated, it can be added to a business process flow to execute and launch the playbook when the stage of the business process flow changes.

A business process flow can be edited to add a workflow. The following screen shows changes made to the out-of-the-box business process flow Lead to Opportunity Sales Process to add the workflow in the Qualify stage, which is the first stage of the business process flow.

Navigate to the Business Process Flow and select the stage and Click Add Workflow > First select the Workflow and then select the Trigger > The trigger can be setup either for Stage Entry or Stage Exit > Apply > Save & Validate.
Note that Workflow has been added to the Triggered Process below. Be sure to Save and Activate the Business Process Flow.

Demo: When a lead is created and enters the first stage of the Lead to Opportunity business process flow, a playbook is automatically launched, and activities are created for the seller to work on before progressing to the next stage. Thus providing a guided experience for the seller.

So now let’s test it by creating a lead.

Note that the Task, Phone Call, and Appointment that we created in the Playbook template is now added to the lead Timeline.

& the Power Quote of the day is:
“Creativity is the power to connect the seemingly unconnected.”
–William Plomer

Play the Playbook- Launch & Complete(Part-4)

Prerequisites: Firstly, make sure you have the Playbook user role or equivalent permissions. Also the “Launch playbook” button appears on an entity form only when there’s at least one published playbook template for that entity.

To Launch a playbook, follow the below steps:
Go to the record you want to launch playbook from. For example, we created playbooks for leads, hence I am opening a lead record.

On the command bar, select Launch playbook > In the Playbook templates dialog box, select a playbook that you want to use, and then select Launch > You’ll see a notification “Playbook launched successfully.”

When a playbook is launched, its related activities are created and associated with the record the playbook is launched from (also called the calling record). You must complete these activities to ensure every event is dealt with consistently. Mark them as complete as and when they are done.

The Open Activity Associated View lists all the activities for the calling record along with the playbook activities created since the playbook was launched. However, this view doesn’t show the total number of activities that have been created in the context of a playbook.

When you complete all the activities created for a playbook, you must mark the playbook as completed. This helps you to know if the playbook was successful or not.

To mark a playbook as completed, go to the record you launched the playbook from (calling record) > Select the Related tab, and then select Playbooks > Open the playbook record.
In the playbook record, on the command bar, select Complete as, and then select one of the following results:

• Successful
• Not Successful
• Partially Successful
• Not Required

A system administrator or customizer can add custom values to this field.

However, once the playbook is launched the user can create and add any kind of activities.

As stated at the beginning, playbooks have a lot of potential to streamline sales activities. So, if you’re running Dynamics 365 for Sales, you’ll want to learn about them as soon as you can.

One possible real-world application of Playbooks is the following scenario: If a decision maker and top champion of the product leaves the organization in the middle of a deal, this can become an event with the potential to jeopardize the entire commercial transaction. With Playbooks, however, automation can trigger a play that creates a set of tasks and activities needed to remedy the situation. A task to reach out to current contacts at the customer account and identify the new stakeholder could be immediately followed by an introductory phone call to better understand the new stakeholder’s priorities. This carefully crafted orchestration of activities ensures that the new decision maker is successfully identified and turned into a new champion for the product so that the deal can be salvaged.

So, we have seen how a playbook can be utilized in ours D365 Sales journey & how is it helpful! But we must also notice that there is way too much navigation for most users

I’m not sure that a good use-case exists for playbooks. At least not out-of-the-box. But here are a few ideas:

• If you have a strategic selling team that is very well versed in Dynamics 365 and can adopt complex functionality pretty quickly, and you have playbooks where the activities are mostly all completed in one sitting, then playbooks may work for you.
• If you use workflows to trigger playbooks, and use workflows to populate the playbook with meaningful information so that records are a little easier to find, there may be a fit.

& the Power Quote of the day is:
“ Nothing is so fatiguing as the eternal hanging on of an uncompleted task.”
~William James.

Play the Playbook- Adding Documents to Activities (Part-3)

From my Playbook Series (Part-1), we got to learn in Step 4 that while adding activities to the template, we can attach documents too! So why is this helpful? This ensures sales representatives always have the latest sales and training materials to close a deal by associating documents with playbook activities that you add to a playbook template. It acts as a catalyst to speed up the process.

Prerequisite: In order to add documents, make sure that the document management for the playbook activity entity is enabled and for all the entities to which the documents must be copied, that is, task, appointment, and phone call.

Also make sure the SharePoint site is configured and validated otherwise you may receive the below error:

Steps: In the Playbook activities section of the playbook template, open the playbook activity to which you want to associate a document > In the activity form, select the Related tab, and then select Documents > The Documents list opens under the Document’s tab.

Select Upload to add documents that are required while working on this playbook activity.

You can also view and download the documents by the following process:

To view a document : In the activity form, select the Related tab, and then select Documents. The Documents showing documents associated with the activity list appears. In the Documents list, select the document name.

To download a document: In the Documents list, select a row, and then select Download.

Quick Tips:

• The document operations you can perform, such as creating a new document or uploading, checking out, editing, or deleting a document, depends on the permissions assigned to you.
• If a system administrator disables document management on a playbook activity for which it was earlier enabled, and documents were associated, the documents won’t be available to the associated activities when a playbook is launched for the sales entity.

Play the Playbook- Track Progress (Part-2)

Continuing from my last blog, where we came across “Track Progress”. So, what happens when you set the Track Progress to either a Yes or a No? Let’s see

In the Track Progress field, select whether to track the progress of the playbook by creating the activities under a playbook, which is in turn linked to the record type the playbook applies to.

Toggling to Yes sets all the activities created against the current playbook.

Toggling to No sets all the activities created directly against the record from which the playbook was launched.

Still confused? Let’s see an example

Consider a situation where you have a template created for a lead. If you set Track Progress to Yes, all playbook activities are created under the playbook that is launched from the lead record (following the hierarchy Lead record > Playbook record > Activities).

Step 1: Go to the record you launched the playbook from.

Step 2: Select the Related tab, and then select Playbooks.

Step 3: In Playbook Associated View, the playbook launched from the record is listed. > Select the playbook.

The playbook record shows all the details about the playbook and its associated activities. For example, it shows when the playbook was launched, how many total activities it has, how many of the activities are completed, and the estimated close date. It also shows all the related playbook activities in the Playbook activities section.

If you set Track Progress to No, the playbook activities are created directly under the lead record (following the hierarchy Lead record > Activities).

To see the activities, in the calling record, see the Timeline section. Alternately, in the calling record, select the Related tab, and then select Activities.

& the Power Quote of the day is:

“You are trying to fit in, whereas you were born to stand out”