Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Updating un-related records

  • 1.  Updating un-related records

    Posted 13 days ago
    ​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 13 days ago
    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 12 days ago
    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 12 days ago
    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 11 days ago
    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 11 days ago
    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


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