Discussions

Expand all | Collapse all

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

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

    Posted 06-13-2017 22:39
    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?


  • 2.  RE: How to bulk change one value to another in a single field?

    Posted 06-13-2017 22:53
    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


  • 3.  RE: How to bulk change one value to another in a single field?

    Posted 06-13-2017 23:05
    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?


  • 4.  RE: How to bulk change one value to another in a single field?

    Posted 06-13-2017 23:09
    You may be right that it is because of multi select.


  • 5.  RE: How to bulk change one value to another in a single field?

    Posted 06-13-2017 23:18
    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.


  • 6.  RE: How to bulk change one value to another in a single field?

    Posted 06-13-2017 23:31
    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!


  • 7.  RE: How to bulk change one value to another in a single field?

    Posted 06-13-2017 23:40
    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.