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?
Question
How to renumber a set of records?
+6This topic has been closed for replies.
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.
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.
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.
+6Kevin: 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?
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.
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.
+6Thanks. 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"?
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
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
Sign up
Already have an account? Login
Login to the community
Login with Quickbase US – quickbase.com Login with Quickbase EU – quickbase.eu
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.