Sequence is now not sequential due to deleted (testing) records.

Hi Blanca,

Deleted records are one of the occurrences that can throw off a serial number of sequential number that is being generated off of Record ID# or through a serial number set up like the one you are referencing. What we most often recommend as this where possible deletions be avoided as they can throw either of the two processes off on a count. The alternative method you mention of using a snapshot and a relationship does so by using a summary field which counts up a number of related records. The issue with this is that when you delete records that summary field will also decrease meaning if you have 70 records and then you delete record 23, the next record you create will be #70 again since the number of summarized records is still 70. One way to avoid this is to restrict all deletions on this table and instead have a field added to make a record as inactive and then it will be hidden on your reporting. If you are curious about testing the other method we do have a Knowledge Base article that goes over an example of setting up a serial number using a relationship and a snapshot field which can be found here:

https://community.quickbase.com/quickbase/topics/how-can-i-set-up-a-numbering-system-to-track-quotes...

One thing to note is that the sequential serial number field noted here will begin counting when it is created, it won't be able to count correctly on pre existing records as when it is created it will use any existing records as its basis unless the relationship is created and populated at that time.

There is also the possibility of using a Quick Base Action to capture any deleted records and have them counted on a hidden table that would help to keep your counts correct even when an older record is deleted. This set up can get a little more complicated as it involves an action, a relationship, and a table that is hidden for auditing purposes. I hope this information is helpful Blanca. 

If the true sequence, without gaps, is needed you will need to create a numeric field.

You will want to export/import to make the update as needed.

>Create a report with two columns, [Record ID#] and your [Formula RFI # Field]
>Sort by the Formula field.
>Export to Excel
>Re-number from 1 (or whatever starting point you want)

>Import the new list with the Record ID# and the new sequence to a new numeric field.  

Now you will know your 'max' number and max record ID#.  You can then adjust your formula to automatically pick up where you left off.

Then you can combine the formula and the numeric field that you just imported to.

So it will either use the imported value, or use the formula to make a new value.

If(IsNull([Imported Sequence]), [Record ID#]+1000, [Imported Sequence])

Then you wont have to update any of your other reports/formulas.