How to bulk change one value to another in a single field?

  • 1
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I have a field in a table where I need to bulk change a specific value to be a different value.  The field is populated through a Multi-select Text and we have updated one of the values in the list of possible values, so it has left some invalid values.

The old value = "Customer Services"
The new value = "Customer Service"

The field does hold other values due to it being Multi-select text, so some records may have something else like "Staff", "Customer Services", "Finance" - it's not always the only value in the field.

What do I need to do to bulk change the values in these records?
Photo of Mike

Mike

  • 402 Points 250 badge 2x thumb

Posted 1 year ago

  • 1
  • 1
Photo of dwhawe

dwhawe, Champion

  • 662 Points 500 badge 2x thumb
If you want to do all of them, run your "List All" report.  Then at the upper right, below the table ribbon will be the word More with a caret to its right.  Click on the caret and one of the options should be "Search & replace in this report. 

Choose the field then the from an to fields and click Next.  Then it will tell you how maby records will change and when you click Ok it will change them
Photo of Mike

Mike

  • 402 Points 250 badge 2x thumb
Thank you for your reply.  I actually found that option after I posted my question, however the field I want to update is not given as an option in the drop-down list of fields.  Is this because it's a Multi-Select?
Photo of dwhawe

dwhawe, Champion

  • 662 Points 500 badge 2x thumb
You may be right that it is because of multi select.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Try this.  But I have not tested.

Export a dump to excel of the record ID and the multiselect field.  It should come out as a semi colon delimited list, actually a semicolon and a space.

Use Excel's Find and Replace  to find and replace.

Then upload to your table

BUT!  I have not tested this, so on the first upload, just upload 1 record to see that it works!  Then if all is good import the rest.
Photo of Mike

Mike

  • 402 Points 250 badge 2x thumb
That worked beautifully, thank you!  Quickbase even gave me a couple of warnings telling me that existing records may be updated.

Something to note: 
Export a dump to excel of the record ID and the multiselect field.  It should come out as a semi colon delimited list, actually a semicolon and a space.
This was actually just a semicolon, no space:
Staff;Customer Service;Finance
But Excel Find & Replace didn't care either way and worked well.

Thanks again!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Great, now I will know that the technique works for the future.  That find a replace in Excel is super powerful as it replaces within strings.