Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Any experience with &q...
Microsoft Dynamics CRM (Archived)

Any experience with "effective dated" records in Dynamics CRM?

(0) ShareShare
ReportReport
Posted on by Jeroen J 415

Hi dear reader,
I'm investigating the (im)possibilities of adding an "effective date" field to standard entities.

For instance, a customer will be moving in three months. I'd like to change the address to his new one immediately, but with an effective date over three months.
This would mean that the original record would not change, but a new one would be added with the new address with the effective date of today+3 months. When opening the account, it should show the record that's currently effective. The other ones should be searcheable in some way.

This is much like Peoplesoft handles its historical and future data and in some (custom) entities (of our on premise, Dynamics CRM 2016 installation) I'd like to start using a similar setup.

Any experiences with or thoughts about this?

Thanks for thinking along with me.
Regards,
Jeroen

*This post is locked for comments

  • Jeroen J Profile Picture
    Jeroen J 415 on at
    RE: Any experience with "effective dated" records in Dynamics CRM?

    Thank you very much for your descriptive answers. Made clear to me that I was totally thinking in the wrong direction.
    I think I'll be able to figure this out now :)

  • Suggested answer
    ashlega Profile Picture
    ashlega 34,475 on at
    RE: Any experience with "effective dated" records in Dynamics CRM?

    You might probably do it with workflows as well.. Create a workflow that will start automatically on create of that custom entity record.. make sure you have a lookup from that custom entity back to the "main" record.. so, the workflow will just need to wait till the effective date(on that custom entity), and, then, it can update the main record.

  • Suggested answer
    Aric Levin Profile Picture
    Aric Levin 30,188 on at
    RE: Any experience with "effective dated" records in Dynamics CRM?

    Hi Jeroen,

    We have done something similar where data has to be moved in place of other data on a regular basis.

    What we have is an SSIS package that runs on a daily basis (after midnight) and checks for records with an effective of that date.

    When it finds the record it backs up the data from the parent record, and puts the new effective data into the entity where the old data was.

    It is pretty straight forward, and can be done with a simple SSIS package.

    We used KingswaySoft SSIS Connector for Microsoft Dynamics CRM/365 to update the data in CRM.

    Hope this helps.

  • Suggested answer
    ScottDurow Profile Picture
    ScottDurow 50,177 on at
    RE: Any experience with "effective dated" records in Dynamics CRM?

    Hi Jeroen,

    I've had to implement similar features before and they are not as horrible as you might think provided that you have a way of running a batch job task every day that will look at a custom entity that has the effective date and 'replace' the data on the main entity with data that is coming into effect.

    So for addresses you would lock the account address fields and have a custom address entity that has effective dates - then the batch job would find custom adddress records that are in effect but not linked to the account and then update the account and change a N:1 lookup to the custom entity to point to the 'current' address.

    Hope this helps

  • Jeroen J Profile Picture
    Jeroen J 415 on at
    RE: Any experience with "effective dated" records in Dynamics CRM?

    My example was not a realistic one (specially because the way of dealing with addresses is different everywhere).

    We're currently investigating the use of an HRM module which has salary and employee entities. Both have to be effective dated, but aren't (yet).

  • Aric Levin Profile Picture
    Aric Levin 30,188 on at
    RE: Any experience with "effective dated" records in Dynamics CRM?

    What entities in particular are you looking to implement this in? System Entities or Custom entities that you created.

    Are you using the Customer Address/Internal Address entities...

  • Jeroen J Profile Picture
    Jeroen J 415 on at
    RE: Any experience with "effective dated" records in Dynamics CRM?

    Additionally, I found it hard to search/bing/google for more information about this quest due to too general keywords. Pointing me out into the right direction would be very much appreciated!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans