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

Nojolu Christine

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)

Toufong Vang

One approach is to make the newest suffix a function of the count of children on this sheet and the count of related (relevant) rows on the sheet that older records are moved into. This would get you the total count of existing child rows. Then you can use that number to calculate the newest suffix.

For example:

COUNTIF( {Archive_Sheet_Parent_Column}, Parent@row) + COUNTIFS( [Prefix]:[Prefix], @cell<>"", [Parent]:[Parent], Parent@row, [AutoNum]:[AutoNum], @cell<AutoNum@row)

The reason is that... In the current sheet, you potentially have 5 columns that are automatically generated and do not depend on other columns (create date, modify date, auto-number, etc.). However, none of these 5 factor into the value of the suffix. In your formula, AutoNum helps you find the newest row, but it is not used in the calculation of the Suffix.

That said, it's likely that community members with more Smartsheet experience will have other approaches.

Cheers!

Nojolu Christine

Thanks so much for your thoughtful response. It is much appreciated (I am new to Smartsheet myself).

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, and therefore, unique association for my documents no longer is viable. The numbering would remain relative instead of fixed.

I agree with your assessment that other columns - NOT relative to the numbering - are found on my sheet and if there is a solution which utilizes these, then it has more hope for working.

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?

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?

Toufong Vang

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.

Toufong Vang

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.

Nojolu Christine

Thank you Toufong Vang ! I missed that.

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