Customer Engagement & Dynamics CRM Forum

I searched for this extensively with no luck for our particular issue. 

We are trying to build a workflow to deal with some peculiarities around how ClickDimensions handles Posted Form data for Lead or Contact records that it creates versus ones that already exist in the database.

The problem right now is we need a workflow to compare the "Created On" date of the Posted Form record to the "Created On" date of the linked Lead/Contact.

1. If the Lead/Contact are new and were created AFTER the Posted Form record was created, reassign the Lead/Contact to the owner of the Posted Form data (in a different business unit)
2. If the Lead/Contact already existed in the system, and were created DEFORE the Posted Form record, don't reassign ownership, because this puts it in the wrong Business Unit. Instead, send an email to the owner of the posted form telling them the record already exists, but they can't have it reassigned. 

The reason why this happens is each of our Business Units represents a different Agency in Big Brothers Big Sisters. Each is a siloed business unit in their own community across Canada, and we can;t have people snatching records from each other automatically. It's a long story. 

What we can't figure out consistently is how to write the Check Condition logic to compare the two Created On date fields. WE have <=, >=, On or Before, Not on, On or After, and more... The crux to this is if those comparisons are just look at Created On as a Date field. or if it is looking at it as a Date/Time field. That makes a big difference between whether we have a whole day to work with, or if records are being compared to the second.

------------------------------
Peter Gulka
National Director, Information Solutions & Data Analytics
Big Brothers Big Sisters of Canada
Burlington ON
------------------------------

Hi Peter,

I'm not sure I completely understand the problem you're experiencing, however, when I find limits with standard workflow I often reach for one of the open source workflow libraries.  In this case, @Aiden Kaskela's excellent Workflow Elements might help you with date comparisons.  There are various date functions in Aiden's tools:

https://kaskelasolutions.com/workflow-elements-documentation/

As an alternative, is Microsoft Flow a possibility?

Best regards,
Andrew.​

------------------------------
Andrew Bibby
Dynamics 365 Consultant & Project Advisor
Evesham, United Kingdom
------------------------------

Original Message:
Sent: May 14, 2019 03:54 PM
From: Peter Gulka
Subject: How to compare the earlier of two date/time fields in a Workflow?

I searched for this extensively with no luck for our particular issue.

We are trying to build a workflow to deal with some peculiarities around how ClickDimensions handles Posted Form data for Lead or Contact records that it creates versus ones that already exist in the database.

The problem right now is we need a workflow to compare the "Created On" date of the Posted Form record to the "Created On" date of the linked Lead/Contact.

1. If the Lead/Contact are new and were created AFTER the Posted Form record was created, reassign the Lead/Contact to the owner of the Posted Form data (in a different business unit)
2. If the Lead/Contact already existed in the system, and were created DEFORE the Posted Form record, don't reassign ownership, because this puts it in the wrong Business Unit. Instead, send an email to the owner of the posted form telling them the record already exists, but they can't have it reassigned.

The reason why this happens is each of our Business Units represents a different Agency in Big Brothers Big Sisters. Each is a siloed business unit in their own community across Canada, and we can;t have people snatching records from each other automatically. It's a long story.

What we can't figure out consistently is how to write the Check Condition logic to compare the two Created On date fields. WE have <=, >=, On or Before, Not on, On or After, and more... The crux to this is if those comparisons are just look at Created On as a Date field. or if it is looking at it as a Date/Time field. That makes a big difference between whether we have a whole day to work with, or if records are being compared to the second.

------------------------------
Peter Gulka
National Director, Information Solutions & Data Analytics
Big Brothers Big Sisters of Canada
Burlington ON
------------------------------

Hi Peter

Date and Date/Time fields are essentially the same type of field when they are stored in the database, they are both date and time. The only difference is the way Dynamics shows/interprets the date. So a date field is stored as 01/01/19 00:00:00.000 whereas a date and time field is stored as 01/01/19 10:15:00.000 or similarly. This is compounded by the way the date field is created i.e. user local, time zone independent etc. So if the date field is stored as user local, then a different user in a different timezone looks at the field, it adjusts for that time period e.g. 10am is converted to 7pm when a user looks at it. Then there's daylight savings time which causes other issues. My point is, date and date/time fields are complicated.

I believe the comparison would be done at a SQL level, so take the whole date/time field into account to see whether the date (and time) are before or after what you are comparing. So if you look at both date fields you want to compare, just ensure they are both the same type, either date and time or date, if so, the compare you do is based on this type. You can also add in multiple checks like less than in one row AND Not On, on another row, to ensure the date is earlier but not on the same day.

I think this would require a bit of trial and error or Andrews suggestions are also quite useful if you can use them.

thanks
Matt

------------------------------
Matt Collins
CRM/D365 Consultant
Technology Services Group
Newcastle upon Tyne
------------------------------

Original Message:
Sent: May 14, 2019 03:54 PM
From: Peter Gulka
Subject: How to compare the earlier of two date/time fields in a Workflow?

I searched for this extensively with no luck for our particular issue.

We are trying to build a workflow to deal with some peculiarities around how ClickDimensions handles Posted Form data for Lead or Contact records that it creates versus ones that already exist in the database.

The problem right now is we need a workflow to compare the "Created On" date of the Posted Form record to the "Created On" date of the linked Lead/Contact.

1. If the Lead/Contact are new and were created AFTER the Posted Form record was created, reassign the Lead/Contact to the owner of the Posted Form data (in a different business unit)
2. If the Lead/Contact already existed in the system, and were created DEFORE the Posted Form record, don't reassign ownership, because this puts it in the wrong Business Unit. Instead, send an email to the owner of the posted form telling them the record already exists, but they can't have it reassigned.

The reason why this happens is each of our Business Units represents a different Agency in Big Brothers Big Sisters. Each is a siloed business unit in their own community across Canada, and we can;t have people snatching records from each other automatically. It's a long story.

What we can't figure out consistently is how to write the Check Condition logic to compare the two Created On date fields. WE have <=, >=, On or Before, Not on, On or After, and more... The crux to this is if those comparisons are just look at Created On as a Date field. or if it is looking at it as a Date/Time field. That makes a big difference between whether we have a whole day to work with, or if records are being compared to the second.

------------------------------
Peter Gulka
National Director, Information Solutions & Data Analytics
Big Brothers Big Sisters of Canada
Burlington ON
------------------------------

I would recommend Jason Lattimer's date workflow utilities kit. There are so many great custom wf activities in here you could use:

https://github.com/jlattimer/CRM-DateTime-Workflow-Utilities

------------------------------
Ty
------------------------------

Original Message:
Sent: May 14, 2019 03:54 PM
From: Peter Gulka
Subject: How to compare the earlier of two date/time fields in a Workflow?

I searched for this extensively with no luck for our particular issue.

We are trying to build a workflow to deal with some peculiarities around how ClickDimensions handles Posted Form data for Lead or Contact records that it creates versus ones that already exist in the database.

The problem right now is we need a workflow to compare the "Created On" date of the Posted Form record to the "Created On" date of the linked Lead/Contact.

1. If the Lead/Contact are new and were created AFTER the Posted Form record was created, reassign the Lead/Contact to the owner of the Posted Form data (in a different business unit)
2. If the Lead/Contact already existed in the system, and were created DEFORE the Posted Form record, don't reassign ownership, because this puts it in the wrong Business Unit. Instead, send an email to the owner of the posted form telling them the record already exists, but they can't have it reassigned.

The reason why this happens is each of our Business Units represents a different Agency in Big Brothers Big Sisters. Each is a siloed business unit in their own community across Canada, and we can;t have people snatching records from each other automatically. It's a long story.

What we can't figure out consistently is how to write the Check Condition logic to compare the two Created On date fields. WE have <=, >=, On or Before, Not on, On or After, and more... The crux to this is if those comparisons are just look at Created On as a Date field. or if it is looking at it as a Date/Time field. That makes a big difference between whether we have a whole day to work with, or if records are being compared to the second.

------------------------------
Peter Gulka
National Director, Information Solutions & Data Analytics
Big Brothers Big Sisters of Canada
Burlington ON
------------------------------