Skip to main content

SCRIBE Labs - Variable Connector Used To Control A Loop (Dynamically)

Recently, while working on a data migration task, I had a requirement to transform data stored in a string delimited by ";" and parse it to create multiple line items in the target systems.  Here is an example as a visual:

Source:
productA, productB, productC, productD...

Target:
productA
ProductB
ProductC
ProductD
...

In the target system, each product would be a line item on an opportunity.  In the source system this was all listed in a text box as a string and delimited by ";".  Also the cell where this data was in the source could be null, have one item, ten items, fifty items, etc.  So I needed a way to loop through this.

To set this up I needed to use three connectors, source, target, and Scribe Labs - Variables.  Below is an image of the section of the mapping where I achieved what I needed to happen:



  1. If Product To Add - This is the null handler.  If the field where the products are listed in the source is null, then we skip adding any products in the target system.
  2. Lookup uom - This is target specific related record and not important for what we are working on.
  3. Upsert Counter - Here is where we declare our counter in the the Scribe Labs - Variable connector.  For this I choose NumVarible and set the name to "counter" and the val to 1.
  4. Loop - Now we enter our loop.
  5. Get Counter - Here we do a lookup to get the val of the counter.  Yes, we just created the counter, but remember we are in a loop and need this lookup, since we will be updating the value in the loop.
  6. Lookup Product - Here is where we lookup the product in the target system.  For the lookup I am doing a like match against the product id field in the target system.  To do this I need to use the parse function in Scribe. Here is how the parse is setup: CONCATENATE("%", STRIP(PARSE(Source Field, Counter_NumVariableLookup.val, ";"),"AN")).  What we will focus on in this function is the Parse() piece.  Within the Parse we have to tell Scribe the field that we want to get the data from.  Then we tell it where specifically in that string we want to look.  Then how the string is delimited.  To make this dynamic, we are using our counter variable.  This means on first run our counter variable is set to one.  So, in a string of "ProductA;ProductB;ProductC" our parse is going to pull out "ProductA" because it is the first sub-string.  When the loop runs again, the counter will be two and "ProductB" will be returned.
  7. If Product Found - This if statement is to make sure we don't create any empty line items if the product doesn't exist in the target system.
  8. Create opportunityproduct - If we do find 1 product, then we create the record in the target system.
  9. Increase Counter - Here is where we increase the val of the counter by 1.
  10. If No More to Process - Now that we increased our counter, we need to check our string ("ProductA;ProductB;ProductC") to see if we need to run the loop again.  When you use Parse with a counter like this to get a specific sub-string.  If that we reach the end of the string, in our example that would be 4.  The Parse will return null.  So for our If statement we want to use the ISNULLOREMPTY() to check and see if we reached the end of the string.  We would setup the condition to be ISNULLOREMPTY( PARSE(Source Field, Counter_NumVariableUpsert2.val, ";") ) equals True.
  11. Loop Exit - If we have reached the end of our string, then we exit our loop.
For more information check out these helpful links:

Comments

Popular posts from this blog

Azure Blob Storage For Storing Microsoft Dynamics 365 Attachments

In my Living In SCRIBE Online Blog I talked about work I did to migrate CRM 4.0 on-premise to Dynamics 365 using SCRIBE Online.  In that blog I mentioned how there are about 80 GB worth of attachments that had to be moved into the cloud.  Well this could quickly get expensive if we stored them directly in CRM.  Also, this client wasn't using SharePoint.  So what option do we have to store this data so end users can access the data?  Why not use Azure Blob Storage?

Microsoft has an app in AppSource to move attachments from CRM to Azure blob storage.  Not only does it all for this, but it also has a web resource in it so you can allow for bulk uploads.  Here is how to set it up:

1) Go To AppSource
2) Install the Attachment Management App by clicking the "Get It Now" button under the image.
3) While its installing go to Azure and setup your blob storage account.
4) For each CRM instance you will need to setup 2 containers in your blob storage.  One is for email attachments …

XrmToolBox Bulk Attachment Manager - Version 2018.2.2.7 Released!

Today I have published version 2018.2.2.7 of my XrmToolBox plugin.  The purpose of this plugin is to make it easy to download and backup attachments in CRM.  This release is a major release as it is the first built version with all pieces working.  The first version only had note downloads working.

Overview of Plugin:
Current Version: 2018.2.2.7Purpose: Download Attachments and E-Mail attachmentsGitHub Link.GitHub Wiki Link.Nuget Package Link.How To Use The Tool: Launch XRMToolBox.Connect to organization.Step 1, choose what you want to download. (Once a choice is made, Step 2 becomes usable)Notes - Download attachments from note entity.E-Mail - Download attachments from emails.Both - Downloads attachments from notes and emails entities.Step 2, choose if you want to download all attachments in the system or specific attachments.  All Attachments - This will search the chosen entity to find all records that have an attachment and download them.Click on "Browse" and choose where t…

Updated To Version 1.7 - Azure Blob Storage For Storing Microsoft Dynamics 365 Attachments

In another post I talked about using Azure Blob Storage to store CRM attachments in Azure rather then in CRM.  Today I installed the app in a clients CRM system and found that Microsoft Labs updated the App to version 1.7.  This change had a lot of updates from UI to how to setup notes attachment movement.  Also they now allow for moving of existing attachments.  Here is the updated steps:

1) Go To AppSource
2) Install the Attachment Management App by clicking the "Get It Now" button under the image.
3) While its installing go to Azure and setup your blob storage account.
4) For each CRM instance you will need to setup 2 containers in your blob storage.  One is for email attachments and the other is for notes attachments.  So if you have a production instance and sandbox instance you will need to have 4 containers (DevEmails, DevNotes, ProdEmails, ProdNotes).  If you want to separate note attachments by entity, then create one container per entity.  The DevNotes or ProdNote…