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:

Labels:

Comments

Post a Comment

Popular posts from this blog

Validating User Input In CRM Portals With JavaScript

When we are setting up CRM Portals to allow customers to update their information, open cases, fill out an applications, etc. We want to make sure that we are validating their input before it is committed to CRM.  This way we ensure that our data is clean and meaningful to us and the customer. CRM Portals already has a lot validation checks built into it. But, on occasion we need to add our own.  To do this we will use JavaScript to run the validation and also to output a message to the user to tell them there is an issue they need to fix. Before we can do any JavaScript, we need to check and see if we are using JavaScript on an Entity Form or Web Page.  This is because the JavaScript, while similar, will be different.  First, we will go over the JavaScript for Entity Forms.  Then, we will go over the JavaScript for Web Pages.  Finally, we will look at the notification JavaScript. Entity Form: if (window.jQuery) { (function ($) { if ...
22 comments
Read more

Dynamics Set IFrame URL - D365 v8 vs. D365 v9

While doing client work, I came across a problem with setting an IFrame URL dynamically.  The underlying issue was that the sandbox instance is on v8 of Dynamics 365 and production is on v9 of Dynamics 365.  The reason for this was because this client was setup around the time that Microsoft rolled out v9.  Anyways, JavaScript that I wrote to dynamically set the URL of the IFrame wasn't working in the v9 instance.  This was because of changes that Microsoft made to how IFrames are loaded on the form and also changes to JavaScript. Here is my v8 setup: JavaScript runs OnLoad of contact form.  This works because of how IFrames are loaded in v8.  You can also run it on either a tab change (hide / show) or OnReadyStateComplete event of the IFrame.  Depending on your setup you will need to choose which is best for you.  For me in this case it was the OnLoad event. Here is the JavaScript: function OnLoad() { //Get memberid var...
26 comments
Read more

Navigating Microsoft Dynamics 365 Customization: Plugins vs. Azure Functions

Embarking on the Microsoft Dynamics 365 customization journey offers numerous opportunities to enhance your business processes. However, deciding between the available options, such as Plugins and Azure Functions, can be challenging. This engaging post will serve as your trusty guide, helping you choose the best option for your Dynamics 365 customization needs! The Two Customization Pathfinders: Plugin and Azure Function The Agile Plugin 🏃‍♂️ Reference: Microsoft Docs - Write a plug-in Plugins are like the swift trail runners of the Dynamics 365 customization world. They're the go-to choice for quick, real-time (synchronous), or background (asynchronous) operations that occur within the platform. They can intercept events and modify data before it's saved or displayed to the user. Choose Plugins when: You need real-time processing (synchronous) or background processing (asynchronous). You want to ensure data integrity. You need tight integration with Dynamics 365. Plugins mig...
31 comments
Read more