Calculate age in Dynamics CRM

2019-06-04 02:13发布

问题:

So there are a couple of similar questions, but all are using javascript, which isn't ideal as it requires the record to be opened / saved.

So, how can you calculate age based off birthdate. There are 200,000 records this would need to be done on and it's using CRM 2015, so can involve calculated fields as well.

It's going to be reported on in the background, so we can't use Javascript.

Workflows are a possibility, but running them on 200,000 records daily isn't exactly elegant!

Any other suggestions?

回答1:

I've come across this requirement a number of times. I've solved it by writing a Scribe or SSIS job which runs nightly and updates the Contact.Age field.

In order to not update every Contact record with the calculated age (as most ages won't have changed), I've used one of the following:

For on-premise CRM (where I have SQL access to the database), I wrote a query to return: contactid contact age contact DoB calculated age (calculated column from DoB and getdate)

The Scribe or SSIS job would only update records where Contact.Age != CalculatedAge

For hosted CRM (where I don't have SQL access to the DB): Add a field called 'Next birthday'

The Scribe/SSIS job would search for records where NextBirthday is null or prior to today. It would update the Age and NextBirthday field.

Both of these methods mean that if the nightly job doesn't run for whatever reason, then when it's next run it will catch up on any records that are now out of date.



回答2:

http://blogs.msdn.com/b/crm/archive/2009/02/27/creating-a-birthday-contact-list.aspx has an example using a pre-plugin to populate the birth month, year, and day fields. This could be adapted to instead perform the calculation to populate an age field. That being said, this would only work for new records or records that are changed.

If you wanted to do this via workflow, you'd have to have a workflow assembly to perform the calculation to populate an age field. As an alternative that doesn't require any code, you could create an Advanced Find query for All birthdays in a certain time frame, i.e. "Birthday on or before 2/17/1975" (this should limit the amount of records returned and reduce it from the total of the 200,000). Include the birthday and a new Age field created in the columns shown. I simply created an age field as a text field with a size of 5 characters since I'm intending only to store the years old someone is in it. Export the contacts to Excel marking the options, "Static worksheet with records from all pages in the current view" and "Make this data available for re-importing by including required column headings". Make sure to include the Owner column in order to prevent reassigning all these records to yourself when reimporting the records.

Then in Excel, create a formula like the following in the Age column assuming the birthdate field is in Column L, "=DATEDIF($L2,NOW(),"y")", which will update the age field with how many years old someone is as of the current date. Note that you might have to perform this calculation on a separate column and copy in just the values in order to ensure that Excel does not change the data type for the Age column or you will not be able to import that data back into Microsoft CRM. Fill that formula down so all records are updated, and save the file. Then in Microsoft CRM, import these records by pointing to the updated XML file (Excel 2003 XML format). Here your only restrictions are going to be on the size of the import file (CRM Limits this to 8 MB per file) and will be restricted to 10,000 records for the export, so this is another reason to break up the records you are exporting for reimport.

If you do update these via a workflow, you can update more than 250 at a time using a solution like the one in the CRM 2013 Bulk Workflow tool for XRM Toolbox http://www.zero2ten.com/blog/crm-2013-bulk-workflow-tool-for-xrmtoolbox/ , which allows you to select a group of records using FetchXML as the criteria for the records to apply the workflow to, noting that this may take some time to process if you are running this at the same time for all 200,000 records.

Ideally, my preference would be to have a plugin or JavaScript, but can see with your requirements that you would need to have this run either daily or on a monthly basis (although I would not run it for all 200,000 since everyone's age does not change each day). Just choose the records that have birthdays in a particular month or on a particular date to run the workflows on or to export and reimport for since that's going to be much less intensive for server processing and will be able to complete much faster than having to update all 200,000 at a time.