Can you create a column formula for autonumber generation that will recognize ancestor relationship
I have a sheet that I would like to custom create an autonumber formula so a record ID is given when a new record is submitted, but I also also want it to be able to recognize descendant rows and give them the same Record ID as the ancestor row (instead of generating a new record ID for every new row). Below is an example of the result I'm aiming for.
Answers

Hey @D Gray!
It's sounding like you will need a couple of helper columns that you will need to hide. I tend to set these to the left of the Primary column so that they are a bit less intrusive with the, for a lack of better words, "Hidden Column Indicator," but that is purely preference!
I accomplished what you were looking for by using 4 helper columns with column formulas.
Hier  This column tells you the level in the hierarchy. The formula is:
=IF(PARENT(Hier@row) = "", 1, PARENT(Hier@row) + 1)
Row ID  This is just the autonumber column. Don't mistake this for a good Row ID column as it is generated as rows are added and do not change if the row is moved. i.e. Row ID in row 6 is 6 then is moved to row 7, Row ID will still be 6.
Row_ID  This column will correctly number the row. The formula is:
=MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
Number  This column will number your projects as well as the tasks underneath them. The formula is:
=IFERROR(RANKEQ([Row_ID]@row, COLLECT([Row_ID]:[Row_ID], Hier:Hier, 1), 1), [Row_ID]@row 
PARENT([Row_ID]@row))
Once you have those set up, you can create your Record ID by using this formula:
=IF(Hier@row = 1, "Record ID  " + Number@row, PARENT([Record ID]@row))
If you want to also automate the "Project/Task" Primary column, you can use this formula:
=IF(Hier@row = 1, "Project " + Number@row, "Task " + Number@row)
That is if you're willing to forego the Project A/B for Project 1/2.
Let me know if this works out for you and you can of course change the column names to whatever you'd like!
Help Article Resources
Categories
Check out the Formula Handbook template!