Help with parent/Child row autonumber considering the year
I have a sheet in which I am trying to create an automatic JOBID. The jobID should be the year of the client's appointment + Parent auto number; for example, in an appointment on 01/02/2024, the jobID would be 240001. This appointment may have a followup that should be a child of this row, so the job should be 240001A. The next appoitment would receive the 240002 and so on. I have figure out how to do this with this formula:
=IF(Ancestors@row = 0, "" + IFERROR(MATCH([Row Number]@row, COLLECT([Row Number]:[Row Number], Ancestors:Ancestors, 0), 0), ""), "")
Although, now I have a something that I have not figure out. If the client fills the form for a date on 2025, the count should start again from 0001. For example, if it is on 02/02/2025, the jobID should be 250001.
In that case, I may have something like this:
240001
240002
240002A
240003
250001
240004
250002
I Have found a way to do this creating a count field and using the formula:
=COUNT(COLLECT([year_app_]:[year_app_], [year_app_]:[year_app_], [year_app_]@row, [Row Number]:[Row Number], <=[Row Number]@row))
But I am not sure how to integrate these two formulas.
Here is a screenshot of my sheet.
Any thought?
Answers

The following formulas create a structured system to autogenerate a JOB ID in Smartsheet, incorporating the year of an event, autonumbering for primary entries, and sequential alphabetical characters for followup entries (children). Here's an explanation of how each part of the formula contributes to this process:
year_app =VALUE(RIGHT(YEAR(Date@row), 2))
ID_A0 =IF(Ancestors@row = 0, RIGHT("0000" + MATCH([Row Number]@row, COLLECT([Row Number]:[Row Number], Ancestors:Ancestors, Ancestors@row, [year_app]:[year_app], [year_app]@row)), 4))
Parent =IF(Ancestors@row > 0, PARENT([Primary Column]@row))
ID_A1 =IF(Ancestors@row = 1, CHAR(64 + MATCH([Row Number]@row, COLLECT([Row Number]:[Row Number], Parent:Parent, Parent@row))))
JOB ID =IF(Ancestors@row = 0, [ID_A0]@row, PARENT([ID_A0]@row) + [ID_A1]@row)Column Definitions
year_app
:=VALUE(RIGHT(YEAR(Date@row), 2))
 This formula extracts the last two digits of the year from a date column
Date
and converts it to a numerical value. It is used to define the year part of the JOB ID. For example, if the date is in 2024, this formula results in24
.
ID_A0
:=IF(Ancestors@row = 0, RIGHT("0000" + MATCH([Row Number]@row, COLLECT([Row Number]:[Row Number], Ancestors:Ancestors, Ancestors@row, [year_app]:[year_app], [year_app]@row)), 4))
 For rows that are parent rows (i.e., have no ancestors), this formula generates a unique numerical identifier. It does this by finding the row's rank within the context of its year and padding it to ensure it has four digits. For example, the first entry of 2024 becomes
0001
.
Parent
:=IF(Ancestors@row > 0, PARENT([Primary Column]@row))
 This column is used to reference the parent row’s primary column value for child rows. It helps to identify and link children to their parent row.
ID_A1
:=IF(Ancestors@row = 1, CHAR(64 + MATCH([Row Number]@row, COLLECT([Row Number]:[Row Number], Parent:Parent, Parent@row))))
 For firstlevel child rows, this formula assigns a letter based on the child's position relative to its siblings. The formula uses
MATCH
to determine the order among siblings andCHAR
to convert this order to a corresponding alphabetical character (A, B, C, etc.).
JOB ID
:=IF(Ancestors@row = 0, [ID_A0]@row, PARENT([ID_A0]@row) + [ID_A1]@row)
 This is the final JOB ID assignment. For parent rows, it simply uses the numeric ID generated in
ID_A0
. For child rows, it concatenates the parent row’s JOB ID with the alphabetical character fromID_A1
, effectively creating IDs like240001A
,240001B
, etc., for children of the parent240001
.
Example in Practice
Consider a sheet with the following entries:
 A parent entry on January 2, 2024, would get a JOB ID of
240001
.  A child entry of the above parent, if it is the first child, would get a JOB ID of
240001A
.  Another parent entry in the same year would follow as
240002
.  A second child for the first parent would be
240001B
.
This system ensures each entry within a year starts at
0001
and increases sequentially, while followups for a specific entry are alphabetically ordered starting fromA
.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!