Date filters do not work for SQL Server in PowerApps

Today I faced an issue to get the records in PowerApps from SQL database by applying date filters (From and To Dates). I spent so much time to retrieve the records by using the below command “ClearCollect(FilteredWorkReports,Filter(‘[SQL_Table_Name]’, ReportDate >= FromDate.SelectedDate && ReportDate <= ToDate.SelectedDate))“, but I didn’t get the expected result. The reason is simple “Direct date filters do not work for SQL Server” based on this link from MS. So, the solution for this issue is to create a calculated column for the date column in SQL table with Integer datatype. The formula for this calculated column is “YEAR([your_date_field]) * 10000 + MONTH([ your_date_field ]) * 100 + DAY([ your_date_field ])“. The data in your table will look like below with new field

CustomerReportDateReportDateAsInt
Cust 12019-05-0120190501
Cust 22018-11-0820181108
Cust 32015-12-1520151215
Cust 42018-06-0720180607

Finally, I applied the date filter with new column like below which is working like a charm 🙂 “ClearCollect(FilteredWorkReports,Filter(‘[SQL_Table_Name]’,ReportDateAsInt >= Value(Text(FromDate.SelectedDate,”[$-en-US]yyyymmdd”)) && ReportDateAsInt <= Value(Text(ToDate.SelectedDate,”[$-en-US]yyyymmdd”))))“.

I hope it helps…..

MS Flow is an alternative for Dialog

We all know that dialogs should be replaced with either BPF or Canvas App as dialogs were deprecated in Dynamics 365 CE, but what if we want a simple dialog which will accept some custom parameters and use those values to process the business logic? The best alternative for this scenario is MS Flows. We can create a Flow and call explicitly from “Flow” button on the CRM record. For this, we need to create a Flow with the trigger “Common Data Service –> When the record is selected”. Take an example to convert a lead to opportunity based on some parameters. The below is the Flow that I developed.

After the Flow creation, you can see this Flow from the Lead entity records like below.

The Flow will open the dialog like below with set of questions that we defined above once you click on “Qualify Lead” button.

Click on “Run Flow” button to execute the Flow

Your flow executed successfully and created an opportunity record.

With this, we can create simple dialogs very easily with out any extra efforts. Hope this will be useful for other developers.

How to find parent record with at least one child record or with no child records from Advanced Find

Now it is very easy to get the parent records with at least one child record or with no child records from Advance Find like below

Before this feature got introduced, it was not possible to query the records from Advanced Find, you have to download the fetchxml and modify the filter type to “outer” and create a view or report with the modified fetchxml.

Hope it makes easier everyone’s life……

CRUD Operations with javascript from V9.0

Microsoft introduced an awesome feature for developers with V9.0 called Xrm.WebApi. With this feature it is very easy to perform CRUD operations using javascript code. Earlier to v9.0, to perform CRUD operation in Dynamics CRM we need to create our own library or used other third party libraries or used XmlHttpRequest and ajax request. Example code to perform CRUD operations using Xrm.WebApi feature

  1. Create
This image has an empty alt attribute; its file name is image.png

2. Update

3. Delete

4. Retrieve

How to use special date operators (last-month for ex) in OData and MS Flow

Today we got a requirement to fetch all the records that are created in the last month by using MS Flow. We can do this by using “Dynamics 365 List records” action and we have to provide OData filter query for this action. I tried to use the same “last-month” operator that we use in the fetch xml. But this will not work for OData query. We need to use “LastMonth” function for this as defined in this link. The below are the examples to use these date functions.

In OData Query

?$filter=Microsoft.Dynamics.CRM.LastMonth(PropertyName=@p1)&@p1=’createdon’

In MS Flow

Hope it is useful….

Custom code in “Convert To Case” OOB ribbon button

Today we got a requirement to perform some logic on the create of case by using “Convert To Case” button on email activity. The logic shouldn’t execute if the case is created manually (Service –> Case –>  +New). I decided to write a plugin on create of case and perform my logic but the problem is to identify whether the case is created manually or using “Convert To Case” button. Case record is not holding any reference of source email to distinguish. After some research I decided to write a plugin on “Update” of “Email” entity (filtering attribute: regardingobjectid). Whenever an email is converted to case, the platform updating the regarding field with the case that is newly created. So I felt this way I can identify the case which is created by using “Convert To Case” button. However, my plugin executes whenever some one update the regarding field manually on email entity, so I used the following conditions in my plugin to avoid this scenario.

  • Depth should be more than 1
  • Regarding shouldn’t hold any value in PreImage and should hold the value in PostImage
  • “Subject” of email should be same as the “Title” of the case     

Code

              IOrganizationService organizationService = localContext.OrganizationService;
            ITracingService tracingService = localContext.TracingService;
            IPluginExecutionContext context = localContext.PluginExecutionContext;
            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
            {
                Entity targetEntity = (Entity)context.InputParameters["Target"];
                var targetRegardingObject = targetEntity.Contains("regardingobjectid") ? targetEntity.GetAttributeValue<EntityReference>("regardingobjectid") : null;
                if (targetRegardingObject != null && targetRegardingObject.LogicalName == "incident")
                {
                    Entity preImage = (Entity)context.PreEntityImages["PreImage"];
                    Entity postImage = (Entity)context.PostEntityImages["PostImage"];
                    var preRegarding = preImage.Contains("regardingobjectid") ? preImage.GetAttributeValue<EntityReference>("regardingobjectid") : null;
                    var postRegarding = postImage.Contains("regardingobjectid") ? postImage.GetAttributeValue<EntityReference>("regardingobjectid") : null;
                    var postSubject = postImage.Contains("subject") ? postImage.GetAttributeValue<string>("subject") : string.Empty;
                    var incidentRecord = organizationService.Retrieve("incident", targetRegardingObject.Id, new ColumnSet("title"));
                    var caseTitle = incidentRecord != null && incidentRecord.Contains("title") ? incidentRecord.GetAttributeValue<string>("title") : string.Empty;

                    if (context.Depth > 1 && preRegarding == null && postRegarding != null && caseTitle == postSubject)
                    {
                        //Your code is here
                    }
                }
            }

The above code is perfectly working for the scenario that I explained above but I am happy to hear suggestions and any scenarios that I have not considered.