Date and Time operators in CRM Explained

When you add conditions to Advanced Find in CRM to filter records based on a DateTime field, some operators such as “Today” are quite clear, while others such as “Next X Weeks” are trickier (e.g. When does the week start?). This post explains in detail what these operators mean and how to combine them to create more powerful filters.

Day Operators

Next X Days: Starts now and includes the next X days (regardless of time). E.g. If it is 3:30PM, “Next 1 Days” includes today after 3:30 PM and goes until the end of the day tomorrow (11:59PM). Note that this is not the same as “Next 24 hours”. When the field is only Date (no Time particle) then usually the time is set to 12AM (hidden) so this operator will not include today.

Last X Days: Includes all values that are before now and on/after X days ago. E.g. If it is 3:30PM, “Last 1 Days” includes today (before 3:30 PM) as well as yesterday the entire day (12AM-11:59PM). Note that this is not the same as “Last 24 hours”. Also note that when you have DateTime fields without a time component, the time will usually be se to 12AM (hidden) so this operator would include today in that case.

Next 7 Days: Behaves the same as “Next X Days” when X == 7.

Hour Operators

Next X Hours: Behaves like “Next 60*X Minutes”. It starts now and it includes X hours ahead. E.g. If it is 3:30PM then “Next 1 Hours” includes from 3:30PM – 4:30PM.

Last X Hours: Includes all values in the current hour before now, as well as all values in the past X hours. (NOT the same as “Last 60*X Minutes). E.g. If it is 3:30PM then “Last 1 Hours” includes from 2:00PM to 3:29PM).

Week Operators

Next Week: This is NOT the same as “Next X Weeks” where X == 1. It includes all values starting on the first day of next week and includes the 6 days after that one. The first day of next week is configurable in the System Settings section. For example, the week can start next Sunday (Default for U.S.) or next Monday (Default for U.K.).



This operator uses a “calendar week”. As an example, if it is Friday and the first day of the week is Sunday then “Next Week” means all values from next Sunday until next Saturday.

Last Week: This is NOT the same as “Last X Weeks” where X == 1. This operator includes all values before the current calendar week. The calendar week depends on the “Frist Day of Week” system setting. For example, if today is Saturday and the “First Day of Week” is Sunday then “Last Week” would include all values starting 2 Sundays ago and until last Saturday.

This Week: Includes all values of the current calendar week.

Next X Weeks: Same behaviour as “Next X*7 Days”. (Not the same as “Next Week” when X == 1). E.g. “Next 2 Weeks” is the same as “Next 14 Days”.

Last X Weeks: Same behaviour as “Last X*7 Days”. (Not the same as “Last Week” when X == 1). E.g. “Last 2 Weeks” is the same as “Last 14 Days”.

Month Operators

Next Month: Includes all values of the next calendar month. If we’re in August then it includes all values in September (same year).

Last Month: Includes all values of the last calendar month (before the current month). If we’re in August then it includes all values in July (same year).

This Month: Includes all values of the current calendar month. If we’re in August then it includes all values in August (same year).

Last X Months: This operator does not use the calendar year. It includes all values before now and on/after the same calendar day on the previous month. E.g. if it is 3:30PM on August 3rd then “Last 1 Months” includes all values between July 3rd all day (since 12AM) and today at 3:29PM. When X == 1, this is NOT the same as “Last Month”. Also note that when you have DateTime fields without a time component, the time will usually be se to 12AM (hidden) so this operator would include today in that case.

Next X Months: This operator does not use the calendar year. It includes all values starting now and until the same calendar day on the next month (inclusive). E.g. if it is 3:30PM on August 3rd then “Next 1 Months” includes all values between now (3:30PM) and September 3rd (until 11:59PM). When X == 1, this is NOT the same as “Next Month”. Also note that when you have DateTime fields without a time component, the time will usually be se to 12AM (hidden) so this operator would not include today in that case.

Older than X Months: This operator does not use the calendar year. It includes all values before the current calendar day of the previous month. E.g. if it is 3:30PM on August 3rd then “Older than 1 Months” operator would include all values on or before July 2nd at 11:59PM.

Year Operators

Next Year: Includes all values of the next calendar year.

Last Year: Includes all values of the last calendar year.

This Year: Includes all values of the current calendar year.

Last X Years: This operator does not use the calendar year. It includes all values before now and on/after the same calendar day on the previous year. E.g. if it is 3:30PM on August 3rd 2012 then “Last 1 Years” includes all values between August 3rd 2011 all day (since 12AM) and today at 3:29PM. When X == 1, this is NOT the same as “Last Year”. Also note that when you have DateTime fields without a time component, the time will usually be se to 12AM (hidden) so this operator would include today in that case.

Next X Years: This operator does not use the calendar year. It includes all values starting now and until the same calendar day on the next year (inclusive). E.g. if it is 3:30PM on August 3rd 2012 then “Next 1 Year” includes all values between now (3:30PM) and August 3rd 2013 (until 11:59PM). When X == 1, this is NOT the same as “Next Year”. Also note that when you have DateTime fields without a time component, the time will usually be se to 12AM (hidden) so this operator would not include today in that case.

Other Operators

Any Time: Same as “Contains Data”. It will return all values which are not blank.

Composite (logical) Operators

Given the operators in Advanced Find, you can combine them and group them to achieve more complex operators. These are some typical examples:

Older Than X Years: Simply multiple X by 12. E.g. “Older than 2 years” is the same as “Older than 24 Months” which is a supported operator.

On Year 2008: This is equivalent to combining “On Or After” 2008-01-01 and “On Or Before” 2008-12-31. You can use the same logic for filters such as “In January 2010”

In the Past: You can combine “Last 1 Months” and “Older than 1 Months” to include all the values in the past.

Next 3 Calendar Weeks/Months: Unfortunately you cannot build such a query from DateTime fields. You would have to either store the week/month in a separate numeric field or otherwise build a report that applies additional Datetime expressions to filter the results. CRM only supports filtering by calendar week/month for last, this and next week/month. (same applies to year).



Note: Most of these operators depend on the user’s time zone. For example, a DateTime field value can be “Today” for users in Italy, but “Tomorrow” for users in Canada.

Triggering plugins / workflows when associating entities (N:N)

A question I get asked often is “What is the best way to trigger some custom logic when entities are associated in CRM”? While there is no “good” answer, I will provide a set of different approaches in this post which can be helpful to select the best strategy to implement your custom logic.

When you think of N:N relationships, there are multiple ways to implement them in CRM:

1. Native N:N relationships: These are the relationships you can easily create between 2 entities by customizing the entity relationships.

2. Custom “Intersect” entities: These are custom entities that act as the “glue” between 2 entities. For example if you have multiple students and multiple teachers, you might create a custom entity “Student Teacher Association” which contains a lookup to the student, a lookup to the teacher and then additional relationship metadata such as the course name and classroom. Your custom entity would act as an “intersect” entity representing an N:N relationship between teachers and students.

3. Connections or Relationships: Connections can also be used to relate any 2 entities and assign a “connection role” to the relationship (such as “partner” or “friend”).

I won’t go into details and the pros and cons of the above 3 alternatives since Richard has already done a great job at that in this post. The point I want to make is that depending on how you model your N:N relationships, then the answer around how to implement custom logic when entities are associated changes.

Native N:N Relationships

Consider for example, that you want to send an email every time a user gets assigned a new security role. There as a system N:N relationship between users and system roles called “systemuserroles_association”. If you wanted to intercept these events, you would have to register a plugin on the “Associate” message.

However, because the Associate message does not allow you to specify the primary or secondary entities then you’d need to register it as a “global plugin” (no primary entity). Therefore, your plugin will be triggered every time any 2 (or more) entities get associated by any native N:N relationship. For this reason, you must make your plugin smart enough to be able to identify if the current association is the one you are interested in or not. For the user / security roles example, you would need to add the following code to your plugin:

IExecutionContext context = (IExecutionContext)serviceProvider.GetService(typeof(IExecutionContext));
string useRoleRelationshipName = "systemuserroles_association";
            
if (context.InputParameters.Contains("Relationship") &&
    useRoleRelationshipName.Equals((context.InputParameters["Relationship"] as string), StringComparison.InvariantCultureIgnoreCase))
{
    // Assigning new role(s) to a user
    // Send a notification email
}
else
{
    // Another association, not interested, exit
    return;
}

You will need to filter in your plugin to only execute when the “Associate” is called for the relationship(s) in question.

The problem with native N:N relationships is that while you can register plugins, it is not possible to trigger workflows on these kind of events. The only workaround would be that you register a plugin which will then start a specific workflow on-demand, but the workflow engine will not be able to automatically start a workflow when you associate 2 entities with a native N:N relationship.

Custom Intersect Entities

In this case, triggering custom logic is easy because you only need to register a plugin on Create of your custom intersect entity. You will also be able to register workflows in this case because workflows can trigger on Create of any entity. Workflows will also have the ability to access fields from both sides of the relationship since each side is considered a “parent” entity of the intersect entity and you can always access parent entities and their fields from the workflow designer. This approach would also allow you to easily build queries and reports of your relationships.

This approach gives you in general much more flexibility to implement your relationships and the custom logic behind it. One disadvantage that I find with this approach is that you lose some of the neat OOB functionality to relate records. For example, if you want to associate 150 students with one teacher, you will have to create 150 records of these intersect entities one-by-one, while native N:N relationships allow you to select multiple at once and associate them all with one click. You might also miss the “Add Existing” button that you get with native N:N relationships. There are also more limitations in this approach when you want to show a related view because you would have to show a view of the intersect entity and not a view of the related record type (so things like sorting by fields of the related record type is not possible).

Connections (or Relationships)

These are similar to the custom intersect entities and you can define plugins and workflows on create of connection records. However there are a few subtle differences:

1. Connection Roles are solution aware so you can package them in your solution.
2. The connection entity is used for all connections for all entity types. For custom intersect entities you would need one custom entity per association type (which can be good and bad depending on your scenario).

3. You cannot access the fields of the entities getting connected from the workflow designer (you can do this with custom intersect entities).

4. If you register a workflow on Create of Connection, it will trigger every time any 2 records are connected and you would need some conditions in your workflow to filter out the connections you are not interested in.

5. You can customize the Connection entity but it would be strange to add fields to capture metadata about a specific relationship because the fields will be there for all relationships. In that case you should probably consider customizing your own custom intersect entity.

Note that I would not recommend using the CRM “Relationships” as these have been replaced with a more powerful model (Connections) in CRM 2011.

Conclusion
While it is possible to trigger custom logic on association (or disassociation) of entities, you should consider how you model your N:N relationships in CRM and how that would affect your automation possibilities in terms of plugins and workflows.

Note: If you are interested in triggering custom logic for 1:N or N:1 relationships, this is quite simple: You only need to register a plugin (or a workflow) on the “Update” event of the child entity, so whenever the “parent” field of the child is updated then your plugin / workflow will trigger.

Bulk Activate / Deactivate Records in CRM 2011

We all love bulk delete because it allows bulk deletion to run asynchronously in the background, it allows recurring bulk delete jobs and it lets you enter an Advanced Find query to specify the exact criteria to select which records to delete. But what about bulk de-activation? Luckily there are tools to allow you to extend the bulk delete functionality to bulk (de)activation of records.

If you follow my blog you might be familiar with the CRM 2011 Workflow Utilities project I have been maintaining. Recently, I have extended it to include a custom workflow step for setting the state of multiple records, similar to bulk delete:

1. Runs asynchronously in the background (workflow)

2. Allows you enter a query to specify exactly which records you want to bulk (de)activate (Uses Advanced Find to specify the query)

 

Consider this scenario: Your sales manager needs to be able to manually deactivate multiple accounts when they get old. You might need to deactivate thousands of accounts and cannot expect the sales manager to go page by page clicking the “Deactivate” button. You can certainly not expect the sales manager to write an SDK application to do so ;-)

So once you install the CRM 2011 Workflow Utilities solution available here, you can create a workflow and insert a “Bulk Activate / Deactive” step:

The first parameter is used to define the query that defines which records you want to activate/deactivate. You can click on the lookup icon and then click “New”. In this example, I will create a query to deactivate all active accounts older than 1 year:

The second parameter (Target State) correspond to the state code to which I want to set my records. In this case I want the accounts to become inactive, so the corresponding statecode is 1.Most records in CRM have statecode 0 (for Active) and 1 (for Inactive). If you wanted to bulk activate (instead of deactivate) you would select 0 as your target state.

The third parameter corresponds to the target status. The default status is -1 which corresponds to the default status code for your selected statecode. If you want a different status than the default you can always browse the entity metadata to find the correct value. In this example, I want the status to become “Archived” so I look at the account “statuscode” field to find the corresponding value (100000000):

After configuring my custom step, it will look like this:

Note: There are special entities which you cannot activate/deactivate. For example, cases cannot simply be deactivated. They need to be resolved or cancelled, these special operations are not supported by this tool. Additionally, note that the workflow primary entity is irrelevant for this step since the records to de-activate are specified by the query and it can point to a different entity type.