Rolling dates as Smart Fields

Querying data based on rolling dates is not available across the board in CRM. The data warehouse (DWH) provides the ability to do this, but there are some issues with this:

  • it's not a part of the standard product, so you need to pay to get rolling dates
  • not all date fields exist in the DWH - e.g. Recurring Gift next transaction date
  • the DWH is no where near as comprehensive as the Source Views
  • DWH query results are static, so you need to add refresh processes each time you want to run a query

One way rolling dates might be achieved is to extend the scope of Smart Fields. 

At the moment they are applied to Constituents, but if we extended them to effectively act as a global variable, then they could produce dynamic dates that could be used in Query. You have a new smart field of type Date, and then use the same rolling date functionality as built into the DWH (i.e. 7 days ago, in the next 28 days, in the last 4 months, etc). You could have as many as you want. They would all be whole dates, and refreshed once daily.

I suggest a global variable approach rather than using the constituent smart fields, because it would be inefficient to roll out and maintain (the same) dates across the constituent records. You could do it that way and it would work, it just wouldn't be efficient or elegant.

Once you have the rolling date in a smart field you can then put it on your field selection list, and then use it in query criteria.

(Note that we also need Smart Fields on Interactions... I assume someone must have already raised that one).

  • David Bourne
  • Dec 3 2016
  • Reviewed: Voting Open
Organization Name (Please enter full organization name) The Wilderness Society
Reported Version 4.0