Help with parent/Child row autonumber considering the year

SheylaSantana

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?

jmyzk_cloudsmart_jp

Hi @SheylaSantana

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)

https://app.smartsheet.com/b/publish?EQBCT=c82b02c0698445749bc5c311a961c69e

Column Definitions

1. 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 in 24.
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))
   • 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.
3. 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.
4. 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 and CHAR to convert this order to a corresponding alphabetical character (A, B, C, etc.).
5. 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 like 24-0001A, 24-0001B, etc., for children of the parent 24-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 from A.