locked
Upcoming Birthday Query RRS feed

  • Question

  • I’m looking to create a saved query/advanced find which shows birthdays within the next month. I know this isn’t possible with the default attributes of the relationship because the advanced find can’t break dates down into day/month (excluding year). I have seen solutions online which separate the birthday into day/month attributes on save using plugins (which is completely acceptable) however I’m having troubles getting the query to do what I’m looking for. There’s no default way to retrieve the current month number then compare it to the relationship’s birth month number + 1.

    Only solution I can begin to think of is to create a “Current Date” entity with day month year attributes that is updated daily with the current values… That seems quite roundabout and overly complicated (but not terribly surprising).


    Monday, February 1, 2010 10:55 PM

Answers

  • This might be a little complicated but I think this will be the most complete/best solution:

    Create a custom attribute on the contact entity: new_upcomingbirthday
    Create on create/update workflow which calls a custom activity to ensure that new_upcomingbirthday is set correctly (http://blogs.msdn.com/crm/archive/2008/01/11/part-2-happy-birthday-via-workflow-using-custom-workflow-activity.aspx)

    Then create a windows service which fetches all relationships with a new_upcomingbirthday < today and run the above workflow to ensure that they are kept up to date.

    The reasoning for this approach (in my mind) is mostly for resource usage. We will be setting up with multi-tenancy and each tenant has the possibility of having several hundred users with hundreds of contacts each. So rather then having thousands of wait conditions sitting we can just go through nightly and process all contacts which birthdays have just passed.

    If anyone has additional arguments/information related to the way I'm thinking then please let me know. Its quite possible I'm completely out to lunch on this one ;)

    Thanks.

    This is the solution we ended up putting together and it seems to work pretty well so far.
    Tuesday, February 2, 2010 8:14 PM

All replies

  • If you create an attribute with the next anniversary date you could then use this field. The coders in the forum will know how.

    Leon Tribe

    Want to hear me talk about all things CRM? Check out my blog

    http://leontribe.blogspot.com/

    or hear me tweet @leontribe
    Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe
    • Proposed as answer by Leon TribeMVP Tuesday, February 2, 2010 10:12 AM
    Tuesday, February 2, 2010 10:12 AM
  • This is a good idea. Not sure why it didn't occur to me earlier.

    Is there a built in way to do nightly jobs within the CRM? or would it have to be a windows system job that runs?
    Tuesday, February 2, 2010 4:55 PM
  • You'd have to set up a windows service I think.

    Leon Tribe

    Want to hear me talk about all things CRM? Check out my blog

    http://leontribe.blogspot.com/

    or hear me tweet @leontribe
    Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe
    • Proposed as answer by Leon TribeMVP Tuesday, February 2, 2010 8:00 PM
    Tuesday, February 2, 2010 8:00 PM
  • Paulo, this is the best I've seen for this kind of thing:  http://inogic.blogspot.com/search/label/Recurring%20Activities
    Tuesday, February 2, 2010 8:00 PM
  • This might be a little complicated but I think this will be the most complete/best solution:

    Create a custom attribute on the contact entity: new_upcomingbirthday
    Create on create/update workflow which calls a custom activity to ensure that new_upcomingbirthday is set correctly (http://blogs.msdn.com/crm/archive/2008/01/11/part-2-happy-birthday-via-workflow-using-custom-workflow-activity.aspx)

    Then create a windows service which fetches all relationships with a new_upcomingbirthday < today and run the above workflow to ensure that they are kept up to date.

    The reasoning for this approach (in my mind) is mostly for resource usage. We will be setting up with multi-tenancy and each tenant has the possibility of having several hundred users with hundreds of contacts each. So rather then having thousands of wait conditions sitting we can just go through nightly and process all contacts which birthdays have just passed.

    If anyone has additional arguments/information related to the way I'm thinking then please let me know. Its quite possible I'm completely out to lunch on this one ;)

    Thanks.

    This is the solution we ended up putting together and it seems to work pretty well so far.
    Tuesday, February 2, 2010 8:14 PM