# 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?

• ✭✭✭✭✭✭

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
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 from`ID_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`.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!