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:
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).
Organization Name (Please enter full organization name) | The Wilderness Society |
Reported Version | 4.0 |
We've actually come up with a different solution by introducing a Constituent Relative Dates query node... It's a better solution than squishing them into smart fields (which would clog up smart fields).
I'm happy to share the query node with anyone that's interested. We've already shared it widely within BB Pacific and those who have adopted it seem to be happy :)
Whilst this would be handy, I think a cleaner and easier option would be https://bbcrm.ideas.aha.io/ideas/CRM-I-257 / https://bbcrm.ideas.aha.io/ideas/CRM-I-2215.
That would be https://bbcrm.ideas.aha.io/ideas/CRM-I-257
without the comma in the URL!
See also https://bbcrm.ideas.aha.io/ideas/CRM-I-257, essentially a request to have rolling dates implemented within the standard query engine.