Help with Parent / Child row auto numbering

Amanda Fulbright

Hello!

I have a sheet that is being used to track decisions. We have a parent row (blue) and then child rows (white) for each group that needs to make a decision. We want the parent row Decision IDs to be sequential numbers "EHR###". The problem I'm running into is that each child row gets an AutoNum as well so as new decisions are added we're actually skipping a lot of numbers at the parent level. Below the parent EHR74 has children that were assigned EHR92 - 95. The formulas from the WBS posted on this forum correct the numbering in our Decision ID column but we're wasting a lot of numbers.

Does anyone know how I can get the parent row to be the only one actually using our desired "EHR###" auto number but also still get the WBS to function so child rows inherit the parent auto number + .#?

This is a problem because we're storing related documents on Google Drive and have an automated process to create folders by the Decision ID. Skipping numbers means we have a ton of empty folders that will never be used and just junk of the drive.

These are the formulas I'm using for reference.

Thanks in advance!

Amanda Fulbright

I figured out a work around to solve the parent / child numbering issue. I created a new helper sheet, "Decision Intake Sheet", with an AutoNum column configured to number my parent rows as "EHR###". This sheet has only the fields I gather via my intake form and has an automation to move newly added rows to my "In Process Decision" sheet.

On the "In Process Decision" sheet I updated my AutoNum column to be only numerical, without the EHR number. When rows are moved from the intake sheet to the In Process sheet the value in the AutoNum column is placed in the source sheet AutoNum column. Therefore each parent row now has an autonum of "EHR###" but child rows are created with numerical autonums. This allows the WBS formulas to continue to work as designed.

Julio S.

Hi @Amanda Fulbright,

As you know, there isn't currently a way for Auto-Number Columns to discriminate between parent and children rows when performing calculations. When you have a moment, please let our Product team know about your feedback by filling in this form, here. Thank you!

To avoid possible accidental undesired behaviors, I would recommend testing the following in a copy of the sheet instead of the original. The formula that I used below, assumes that there is an "Ancestors" text/number column with the following Column formula =COUNT(ANCESTORS([Decision Description]@row)) and the Auto-Number column (named Row Number) only adds a numeric value based on the order that was added to the sheet. Note that even if there are some jumps on the row numbers after some tasks in-between were deleted, the formula still assigns the correct number to the "Only parent numbered".

With this in mind, the following formula should achieve what you intend: =IF(Ancestors@row = 0, "EHR" + IFERROR(MATCH([Row Number]@row, COLLECT([Row Number]:[Row Number], Ancestors:Ancestors, 0), 0), ""), "")

I hope that this can be of help.

Cheers!

Julio

Amanda Fulbright

Hi Julio!

Thank you for helping me with this. I did log an enhancement and hope it gets prioritized. Working with parent/child rows is so much harder than it should be!

I tested your formula and got it to work but there's one problem I failed to mention. When a parent decision is complete I am moving the parent and all child rows to a "Finalized Decision" tracker to archive it. When I tested this I found that I can end up with the same decision id being used over and over since it's based on the range on the "In Process Decision" sheet. I need to find a way to ensure no number is ever used again once it's been assigned on the sheet. Thoughts?

Amanda Fulbright

@Julio S. The more I think about this the more I wonder if what I need is a helper sheet that tracks the Decision ID and feeds it back to the "In Process Decisions" sheet. Perhaps there's a way to link the two so that only parent rows get assigned the auto generated number on the helper sheet and then I can use that number to number my child tasks. I'm going to experiment with this today and see if I can figure it out.

Amanda Fulbright

I figured out a work around to solve the parent / child numbering issue. I created a new helper sheet, "Decision Intake Sheet", with an AutoNum column configured to number my parent rows as "EHR###". This sheet has only the fields I gather via my intake form and has an automation to move newly added rows to my "In Process Decision" sheet.

On the "In Process Decision" sheet I updated my AutoNum column to be only numerical, without the EHR number. When rows are moved from the intake sheet to the In Process sheet the value in the AutoNum column is placed in the source sheet AutoNum column. Therefore each parent row now has an autonum of "EHR###" but child rows are created with numerical autonums. This allows the WBS formulas to continue to work as designed.

Pestomania

Hello all,

I was following your answers above and want to thank you very very much! This has been very helpful. I was able to play with it and get all of this to work in one formula:

IF(Ancestors@row = 0, "AIL-" + IFERROR(MATCH([Row Number]@row, COLLECT([Row Number]:[Row Number], Ancestors:Ancestors, 0), 0), ""), IF(Ancestors@row = 1, PARENT([Task Number]@row) + "." + IFERROR(MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], Ancestors:Ancestors, 1), 0), ""), ""))

In my case Task Number is the actual line numbering sequence and it does not cause a circular reference.

Nanamann

Hello, this has been very helpful, thank you. How would you modify the Formula (IF(Ancestors@row = 0, "AIL-" + IFERROR(MATCH([Row Number]@row, COLLECT([Row Number]:[Row Number], Ancestors:Ancestors, 0), 0), ""), IF(Ancestors@row = 1, PARENT([Task Number]@row) + "." + IFERROR(MATCH([Row Number]@row, COLLECT([Row Number]:[Row Number], Ancestors:Ancestors, 1), 0), ""), ""))

to include a "Task Number" when "Ancestors" = 2?