How to renumber a set of records?

  • 0
  • 1
  • Question
  • Updated 9 months ago
  • Answered
I have an existing set of about 700 records.  Each record has a manually assigned number.  Turns out there are some duplicates in (e.g., two "501"'s).  There were also some records numbered to fit "between", so a 419.1 to slot between 419 and 420.  

I now need to renumber all of the records correctly.  The order will be by a date field.  

How? 

I'm thinking I could export the records to Excel, sort the list by date, and quickly renumber there.  Then create a new table with this list. Lookup the "NewNumber" from the list by matching a record ID value.  Then change the the field type back to number, and delete the lookup function and table. 

Is there an easier way?
Photo of ROBERT SACHS

ROBERT SACHS

  • 390 Points 250 badge 2x thumb

Posted 9 months ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,482 Points 20k badge 2x thumb
Here is a console script that will do the job:

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=623

Just substitute your application dbid, table dbid, apptoken, fidDate and fidOrder into the top of the script and paste the modified into the console. See screenshot of me applying this and displaying the resulting report.

Photo of Kevin O'Boyle

Kevin O'Boyle

  • 602 Points 500 badge 2x thumb
You don’t need to create a new table. When you export your table, be sure to export your record ID# or primary key field (if you changed the primary key from record ID#). Renumber in excel with your new sort #, then re-import back into the same table and create a new field with your new sort # ‘newnumber’. Quickbase will recognize the record id# on the import and you will update the existing records instead of making new records.
Photo of ROBERT SACHS

ROBERT SACHS

  • 390 Points 250 badge 2x thumb
Kevin:  For some reason there is no recordID# field in this table?  Perhaps I accidentally deleted it? (This application was created over 10 years ago by me and I don't remember everything I did (or did wrong)).  How do I expose QB's internal recordID value again?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
Robert

The Record ID field is not deleted. :). Quick Base would not allow that. But it might have been renamed. If you list your fields and use the Advanced Options link at the top right of the field list you can see the field ID numbers. It will be field ID # 3.
Photo of ROBERT SACHS

ROBERT SACHS

  • 390 Points 250 badge 2x thumb
Thanks. It got renamed.  Once the "newnumber" is in, then I assume that I swap name (name "number" to "oldnumber", "newnumber" to "number" and delete "oldnumber"?
Photo of Kevin O'Boyle

Kevin O'Boyle

  • 602 Points 500 badge 2x thumb
you'll want to insert new number into all the reports and forms where 'oldnumber' was.  go to the field properties on 'oldnumber' and view the usage tab, replace oldnumber everywhere it exists,  then delete oldnumber.

A lazier option that I often use is to convert old number into a field type formula - numeric.  Set old number = newnumber.  then convert oldnumber back to a regular numeric field.  this saves time and effort if you have alot of reports and forms to update