Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Updating un-related records

  • 1.  Updating un-related records

    Posted Dec 02, 2019 11:56 AM
    ​Hi Everyone -

    Scenario: the client have handed over a list of customer records to bulk update (e.g. set Relationship Manager to 'John Smith'). The records are all un-related - i.e. there is no advanced find / query that would bring back the set (or even a sensible superset).

    Of course one can update each record individually, but is there a way to bulk update ? Any faster / smarter approach?

    Hope someone can point me in a better direction!

    Thanks,

    Tamim.

    ------------------------------
    Tamim Sadikali
    ------------------------------
    Conference-CRMUG_200x200


  • 2.  RE: Updating un-related records

    TOP CONTRIBUTOR
    Posted Dec 02, 2019 12:40 PM
    Do you mean the list of customer records is in a printed report, or are they in a spreadsheet?
    If printed, then you are out of luck. I will assume for the moment that it is in a spreadsheet and contains account name, address and a "Relationship Manager" lookup to theUsers entity.

    Are they all the same type (such as account)? I am assuming they are all accounts

    Is there a unique (or nearly unique) value on each record? For example: I have a client that has 16000 accounts, and the zip code almost perfectly unique...the combination of the zip+first couple characters of the Street 1 field are 100% unique - this is called an alternate key. Assuming that is also the case for you...

    On the account entity,
    • create a new alternate key field, and
    • export all your accounts to Excel,
    • use a function to populate the alternate key field and
    • re-import it to update your accounts
    Now create an alternate key column for the customer's spreadsheet using the same formula.

    Next, I would create a new temp entity with a lookup to the customer (account) and the Relationship Manager (user), then create a workflow that will run when those fields are filled in to update the related account record. Then when you import the customer spreadsheet, use the new alternate key you created in the lookup for the account. As the records are imported, your workflow will update the accounts with the relationship manager.

    Does that help?

    ------------------------------
    If this answered your question, please click on the arrow button next to Reply Inline and choose 'Make Best Answer.'
    Thanks.
    Nelson Johnson, Solution Architect
    BroadPoint, Inc., Bethesda MD
    Link with me! https://www.linkedin.com/in/nelsonjohnson/
    ------------------------------

    Conference-CRMUG_200x200


  • 3.  RE: Updating un-related records

    TOP CONTRIBUTOR
    Posted Dec 03, 2019 07:14 AM
    Tamim

    You could use the XrmToolbox Bulk Update tool and manually enter the guids of the records that you want to update using the in operator.


    Cheers!


    ------------------------------
    Rex Kenley Tan, MCSA, MCSD
    Tallmadge OH
    https://www.youracclaim.com/users/rex-kenley-tan

    *Always be CURRENT with JavaScript & C#, NEVER be obsolete.

    DISCLAIMER: All views expressed on this site are my own and DO NOT represent the opinions of ANY entity whatsoever with which I have been, am now, or will be affiliated.
    ------------------------------

    Conference-CRMUG_200x200


  • 4.  RE: Updating un-related records

    TOP CONTRIBUTOR
    Posted Dec 03, 2019 10:28 AM
    Here are the Steps

    1. If you export your entire list of active Accounts yoiu can then set up a Unique Key Column such as:
      1. =[ZipCode]&left([Name],15)&left([Address1],10)
      2. Name has to be pretty long to get by Companies that begin with the same word such as "American"
    2. Rename the Column and call it "UniqueKey"
    3. Do the same thing with your Update File making sure the Unique Key is the far left Column and Sort by the Unique Key
    4. Copy and paste the Update File to the right of your Exported Table,
      1. Leaving 3 empty Columns
      2. Name the Range something simple like "A"
    5. in the Column to the right of the exported Data  and the UniqueKey, enter this Formula:
      1. =VLOOKUP([UniqueKey],A,1,false)     <-- False is very important to use since it requires an Exact match
      2. It will create a Column called "Column1"
    6. Sort by Column1 and hopefully you will end up with a 1 to 1 match of UniqueKeys
      1. If you do not have a Unique Match, then you will either need to tweak the Unique Key or make decisions on which Duplicate Record you need to keep
      2. You may also find that some Keys are not found at all by the VLOOKUP.
    7. You can use the Formula "=[Column1]=[UniqueKey]" in one of the 2 remaining blank Columns to discover where the Tables change from TRUE to FALSE.
    8. Once you have everything matched then:
      1. Delete all unmatched Rows.  Make sure you delete the entire Row and not just the Data in the Cells
      2. Copy all of the changes from the Update File Table into your Exported Table
    9. Delete all of the temporary Columns
      1. The Unique Key Column, Column1, and the temporary Table with the changes
      2. Make sure you delete the entire Column and not just the Data in the Cells
    10. Import the Table back in to CRM using Workplace/Imports
      1. If the Import File is not in the correct Format go back in to the Excel File and delete Empty Rows and Columns again.
      2. If you do it right you won't have this problem
    I know this is pretty technical.  Some day when I have time perhaps I will create a Video to demonstrate how this works.

    ------------------------------
    Gerry Yurko
    CRM Developer
    Lightower Fiber Networks
    Boxborough MA
    ------------------------------

    Conference-CRMUG_200x200


  • 5.  RE: Updating un-related records

    Posted Dec 04, 2019 09:12 AM
    Nelson, Rex, Gerry - sincere thanks for your ideas.

    Nelson - the idea of customising an entity with an alternate key field does not appeal (plus is impractical in an enterprise-sized project where change is highly managed).

    Gerry - I like this idea, i.e. 'merging' two sets on an alternate key *within Excel*, allowing one to use nothing more than the OOTB data import tool.

    Rex - this was my favourite...and what I actually ended up doing. There were a few gotchas with using the Bulk Data Updater (can elaborate if anyone is interested) but it felt like the 'cleanest' way forward.

    My thanks again to all of you for helping me out here.

    Tamim.

    ------------------------------
    Tamim Sadikali
    ------------------------------

    Conference-CRMUG_200x200


  • 6.  RE: Updating un-related records

    MICROSOFT MVP
    Posted Dec 05, 2019 01:49 AM
    Hi @Tamim Sadikali !

    As the author of the Bulk Data Updater I would love some feedback!
    If there are any features you want or bugs to fix, please submit them here: https://github.com/rappen/BulkDataUpdater/issues
    If you have questions etc, I'll gladly try to answer them here.

    Thanks!
    Jonas


    ------------------------------
    Jonas Rapp
    MVP
    Sweden
    ------------------------------

    Conference-CRMUG_200x200


  • 7.  RE: Updating un-related records

    Posted 22 days ago
    Edited by Tamim Sadikali 22 days ago
    Hi @Jonas Rapp - my apologies for the delay on replying.

    Firstly, I want to re-iterate that the BulkDataUpdater worked a treat. Moreover, given the complexity / number of steps in the other options proposed, this for me was the clear 1st-class approach.

    ------------------

    Gotchas (thought to write up here to complete the thread...) -



    1. The column-set of the returned records from the query did not align with the FetchXml. In particular, some of the columns, e.g. systemuserid, did not get returned at all. I could not work out why.
    2. Attribute to update - I wanted to set 'Manager' on a list of users, and my only option (as far as I could see) was to provide the guid for the manager (user) in question. I couldn't for example provide a fullname (and be informed if non-unique) or give another more friendly key. And then when I opened up FetchXmlBuilder from inside BulkUpdater, I still could not retrieve my required guid. I needed to open up FetchXmlBuilder outside / as a separate process, to achieve this.

    Hope that helps. I stress again though that these gotchas aside, my UX was very positive!

    Best,

    Tamim.

    ------------------------------
    Tamim Sadikali
    ------------------------------

    Conference-CRMUG_200x200


  • 8.  RE: Updating un-related records

    TOP CONTRIBUTOR
    Posted 22 days ago
    Glad it worked for you, @Tamim Sadikali.

    As an update to my approach, I was given 5 Spreadsheets containing 140,000 Accounts with the thought of adding them to our existing 80,000 Accounts.  The idea would be that Account Execs would not need to manually create Accounts for prospective Customers and in fact we would turn off the ability for Account Execs from creating Accounts and give the capability to an internal team instead.

    My co-worker identified 5,500 matches to our current Table.  I ran my test and identified an additional 3,500 Accounts that matched.  In addition, I ran a test combining the 5-digit zip Code with the Website and identified an additional 1,100 matches.  My co-worker was obviously surprised at this.

    We have our own Import Utility that was built in-house.  Data is loaded into a SQL Table and the Utility reads the SQL Table and tries to create the Record.  If it fails it writes back to the Record in the SQL Table of the exact error and continues to the next record.

    Supplying Guid's to Lookups is much faster than doing Name Lookups so we do supply Guids to the Table. Currenty I am inporting 130,000 Accounts in a Dev Server before importing to Production this weekend to resolve any errors and to see how long it will take to upload.  Currently we have the following results:

    Importstatus   Num
    201                   225
    101                   42464
    1                       88270

    A status of 101 is a successful Import,  A status of 1 is a record waiting to be processed
    The 201 Status is an error with the following Error-Message:

    Status Error_Message
    201 Principal team (Id=0c4c788b-4a2b-ea11-9100-999999999999, type=9) is missing prvReadAccount privilege (Id=886b280c-6396-4d56-a0a3-999999999999)
    201 Principal team (Id=0c4c788b-4a2b-ea11-9100-999999999999, type=9) is missing prvReadAccount privilege (Id=886b280c-6396-4d56-a0a3-999999999999)
    201 Principal team (Id=0c4c788b-4a2b-ea11-9100-999999999999, type=9) is missing prvReadAccount privilege (Id=886b280c-6396-4d56-a0a3-999999999999)
    201 Principal team (Id=9c73c27a-4a2b-ea11-9100-999999999999, type=9) is missing prvReadAccount privilege (Id=886b280c-6396-4d56-a0a3-999999999999)
    201 Principal team (Id=31b3bab3-4a2b-ea11-9100-999999999999, type=9) is missing prvReadAccount privilege (Id=886b280c-6396-4d56-a0a3-999999999999)
    201 Principal team (Id=31b3bab3-4a2b-ea11-9100-999999999999, type=9) is missing prvReadAccount privilege (Id=886b280c-6396-4d56-a0a3-999999999999)

    What that told me was that several Teams did not have Security Roles and therefore could not own an Account.  I corrected them mid-stream and now am not producing any more failed records.

    I am estimating that it will take about 5 hours in our Dev Server to create 130,000 Accounts​

    ------------------------------
    Gerry Yurko
    CRM Developer
    Lightower Fiber Networks
    Boxborough MA
    ------------------------------

    Conference-CRMUG_200x200


  • 9.  RE: Updating un-related records

    MICROSOFT MVP
    Posted 22 days ago
    Hi Tamim, and thanks for the feedback.
    Regarding not being able to change the returned set of attributes, I cannot understand why you were not able to use it the way you needed from BDU.

    Regarding setting Manager, yes unfortunately Lookup attributes can only be set using the guid, so far.
    But I just created issues #34, #35 and #36 to improve functionality around lookup attributes :) ​​​

    ------------------------------
    Jonas Rapp
    MVP
    Sweden
    ------------------------------

    Conference-CRMUG_200x200


If you've found this thread useful, dive deeper into User Group community content by role