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 24-0001. This appointment may have a follow-up that should be a child of this row, so the job should be 24-0001A. The next appoitment would receive the 24-0002 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 25-0001.
In that case, I may have something like this:
24-0001
24-0002
24-0002A
24-0003
25-0001
24-0004
25-0002
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 auto-generate a JOB ID in Smartsheet, incorporating the year of an event, auto-numbering for primary entries, and sequential alphabetical characters for follow-up 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 first-level 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 like24-0001A
,24-0001B
, etc., for children of the parent24-0001
.
Example in Practice
Consider a sheet with the following entries:
- A parent entry on January 2, 2024, would get a JOB ID of
24-0001
. - A child entry of the above parent, if it is the first child, would get a JOB ID of
24-0001A
. - Another parent entry in the same year would follow as
24-0002
. - A second child for the first parent would be
24-0001B
.
This system ensures each entry within a year starts at
0001
and increases sequentially, while follow-ups for a specific entry are alphabetically ordered starting fromA
.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!