Retain last used number after moving rows out with Auto Generate Document number w levels

Question

Hello folks,

I sure could use some help.

I have a working PRF Document numbering system for where Parent row and Children are a family of numbers.  (see example below)  (*many thanks to the folks who posted this solution!)

PROBLEM:  When I move rows of completed PRF's from above rows off the sheet (because it gets quite long for the user), the formula to determine the Suffix renumbers my PRF's and then I no longer have a continuum of unique numbers being used.   For example:

(If I delete all rows for the first 3 PRF's  PRF......-2021-01, ...2021-02, ....2021-03  then all PRF's renumber.  What was once PRF...47 becomes PRF...44

PRF...48 becomes PRF...45

PRF...49 becomes PRF...46

Sighs.   What I need is to retain the last number used and never use it again so that document numbers remain uniquely generated for all time.  Renumbering is a big problem.   Can anyone help with a solution please?  Here is what I have.  Key formulas posted below.

Prefix  =PARENT() + IF([Parent ID]@row = "TOP", "", PARENT(Suffix@row) + ".")

Suffix =MATCH(UniqueID@row, COLLECT(UniqueID:UniqueID, [Parent ID]:[Parent ID], [Parent ID]@row), 0)

Parent ID=IF(COUNT(ANCESTORS()) = 0, "TOP", "R" + PARENT(AutoNum@row))

UniqueID=IFERROR([Vendor / Supplier Name]@row + AutoNum@row, "")

Significant Autonum Family =IF(MAX([Family ID]:[Family ID]) = [Family ID]@row, 1, 0)  (marks last row so it can not be moved)

Nojolu Christine · Answer

Thank you Toufong Vang !  I missed that.

I am going to try it as soon as I am back in office.  thank you!

TVang · Answer

1- Is there no way to write the value of a cell (generated with formula) into another cell which retains only the value - so that I can retain the next available PRF Document number?

Unfortunately, this is not available in Smartsheet.

https://community.smartsheet.com/discussion/74588/is-there-a-way-to-convert-the-content-of-cells-obtained-through-formulas-to-static-content

2- OR am I missing something with AutoNum---can we relegate Autonum to run only on Parent Rows or within a formula with a helper "level" column?

Auto-numbers are generated for every row.

TVang · Answer

Unfortunately, archived rows do not always come off the main sheet in ordered sequence, so when rows from the middle are removed, a renumbering would unfortunately occur...

This is where the auto-number comes in. The criterion, [AutoNum]:[AutoNum], @cell<AutoNum@row ,  ensures that all older rows (rows with lower auto-numbers) are counted. The newest row will be assigned the next number in the sequence. As long as you're counting related records in both the current and the archived sheets, and you're not counting parent rows, [Prefix]:[Prefix], @cell<>"" ,  the approach will work.