Looking at a great many different data migrations, created by a great many different people you start to see trends in the approaches taken. One common data migration tactic involves migrating OptionSet/Picklist/Dropdown data into Dynamics CRM using Scribe.
How is this done?
One of the most common situations involves the source data for the OptionSet being stored as text values. A common solution for this problem is to actually store a map between the text values and the associated integer value in the ScribeInternal database (Specifically in the KeyCrossreferenceTable; it’s what it wanted to be when it grew up). An important consideration with this approach; make sure the KeyCrossReference table is updated with the latest OptionSet from CRM, or else values could be left off during your integration job.
Is there an alternative?
Why yes, I’m glad you asked. An alternate approach I can suggest gets around the use of having a CrossReference table. It leverages one of my most favorite Scribe functions…drumroll… “DBLookup”. There are several different flavors of it available now in Scribe. Scribe describes this little gem of a function as “Looks up and substitutes one field for another. Useful to substitute full values for codes or vice versa…” If you are an Excel geek, you could think of this as Scribe’s very own version of “Vlookup”.
Here is how it works
To get the OptionSet text to be an OptionSet value/int, we need to use the function “DBLookup2”. This will let us use not one but TWO lookup fields instead of just one. Double the lookups, double the fun!
Here is a breakdown of the different variables used in the function:
Seems simple enough!
Now to get this to swap in OptionSet values instead of text values. Take the following example:
EX. DBLOOKUP(S7, “new_state”, “QACRM”, “stringmap”, “value”, “attributename”, “attributevalue”)
In this example, you are taking your text value, referenced in the source as “S7”, and trying to compare that value against the stringmap entity.
The stringmap entity has a list of every OptionSet and related data. The important columns here are as follows:
- text value / label (column name: “value”)
- integer value(column name: “attributevalue”)
- name of the attribute (column name: “attributename”)
Looking back at the example above, the only values you should have to modify to get this to work for you are the first 3 values. Update as follows:
DBLOOKUP( [column number; usually S#], [schema name of the target OptionSet; example new_state], [name of the CRM adapter], “value”, “attributename”, “attributevalue”)
Voila! There you have it, an easily reusable function that accomplishes this same task with much less work!
If you are unsure about the name of your CRM adapter, it should be located at the top right of your Scribe Workbench window:
What are some of the Pros and Cons of this approach?
- Don’t need to maintain a list of OptionSet text values and integer values
- Don’t need a second job to maintain the OptionSet crossreference table
- Less code
- Easy to manage
- Slower than using a SQL table / (CrossReference table example)
- The reason for this is because you are making an api call as oppose to a SQL call (api is slower than database)
If you have further questions about this tutorial, or other questions related to Scribe and/or Dynamics CRM, reach out to us. Email us at firstname.lastname@example.org.