Discussions

Expand all | Collapse all

How to renumber a set of records?

  • 1.  How to renumber a set of records?

    Posted 02-07-2018 00:43
    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?


  • 2.  RE: How to renumber a set of records?

    Top
    Contributor
    Posted 02-07-2018 01:24
    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.



  • 3.  RE: How to renumber a set of records?

    Posted 02-07-2018 01:25
    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.


  • 4.  RE: How to renumber a set of records?

    Posted 02-07-2018 17:07
    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?


  • 5.  RE: How to renumber a set of records?

    Posted 02-07-2018 17:28
    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.


  • 6.  RE: How to renumber a set of records?

    Posted 02-07-2018 17:33
    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"?


  • 7.  RE: How to renumber a set of records?

    Posted 02-07-2018 21:58
    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