RSMUS.comWe recently had a client in state and local government industry that required records to be activated and deactivated depending on dates in the record. Some examples of where this may be useful is expiring quotes and contracts automatically as well as activating and deactivating memberships or subscriptions for other not-for-profit organizations.
Our client requested to be able to automatically control how long providers and other members of an assigned team are associated with an individual with a health condition. The benefit of date-sensitive functionality is that it makes the process of manually deactivating connections to a user (80,000 individuals times at least 10 connections) obsolete.
Our solution used a daily Scribe job and a SQL view to activate connections when the current date falls inside the start and end dates and deactivate connections when the current date is outside the start and end dates. This functionality is useable with other entities in CRM that have start and end date fields such as Contracts and Opportunities. Here are some details on how to accomplish Date Sensitive Functionality on any entity (system or custom) in CRM:
Prerequisites:
- Scribe Insight
- Access/Permissions to CRM database (Sql Server)
Step 1: Create the SQL View
Step 2: Connect the source of a new Scribe package to the SQL View
Step 3: Connect the target to CRM
Step 4: Create 2 independent update steps called Activate <entityname> and Deactivate <entityname>
Step 5: For the 1st step have a pre-operation step: IF(S2 = ‘Activate’, TRUE(), GOTONEXTSTEP()) this will check the source to see whether we are going to the activate or deactivate step for this record.
Step 6: For step 1 set up data formulas for statecode and statuscode to make the entity active.
Step 7: For step 2 set up data formulas for statecode and statuscode to make the entity inactive
Step 8: Save and test your data job
Step 9: Use the Scribe Console to make a daily integration using the created Scribe package.
As a result of implementing this solution you will have a CRM system that dynamically sets deactivated records to active when they are within their specified time range and deactivate any active records that are not within their specified time range. If you have any questions on how to apply this to your specific business needs reach out to me and you will Experience the Power of Being Understood.
By: Brendon Colburn – Maryland Microsoft Dynamics CRM partner
