Formula to add 1

Detrie Zacharias

I am trying to figure out a formula that will add 1 to the Parent [DMR #]

The below will Place a "1" at the end but I need to add count by one

Like this

DMR5575-1

DMR5575-2

DMR5575-3

=IF(Level1 = 0, [DMR #]1, PARENT([DMR #]1) + "-" + 1)

Andrée Starå

Hi Stavros,

I think we need one or two so-called helper columns to make it work.

What is your starting number? Is it DMR5575 or DMR5575-1?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Detrie Zacharias

Hi Andree.. I think that's the part I was missing. I'm not opposed to adding helper columns

DMR5575 is the starting number it is an autonumber

Andrée Starå

Can you share a screenshot of the structure?

Detrie Zacharias

DMR# is an AutoNumber

Work Numbers is the field Im trying to populate (shown as I would like it calculate)

Primary is the primary

ANCESTORS is a Formula field =COUNT(ANCESTORS())

Andrée Starå

Try something like this.

Add a so-called helper column, Name it RowID

Add the following formula in that column:

=COUNT($Ancestors$1:$Ancestors@row)

Change the formula in the column Work Number to:

=IF(Ancestors@row = 0, [DMR #]@row, (IF(Ancestors@row = 0, 
COUNTIF(Ancestors$1:Ancestors@row, 1), "" + PARENT() + "" + 
COUNTIFS(Ancestors$1:Ancestors@row, Ancestors@row, RowID$1:RowID@row, >(PARENT(RowID@row))))))

Did it work as expected?

Detrie Zacharias

Hi Andrée

This works EXACTLY as expected. I'm trying to understand the formula.

Where in the formula does the RowID convert from the sequential number to "1" when a new DMR # is created?

Jeana

@Andrée Starå Hi Andrée, I found this discussion and it's right along the lines of something I'm trying to do. I wondered if you could take a look at this similar scenario and shed some light on the formula?

Here's a screen shot. The DESIRED column is what I'm after, basically if Ancestors = 1 I want the Asset # to increment by one (add 1). This number should carry down to CHILDREN but GRANDCHILDREN (If Ancestors = 3) should display the Asset # - 1 or Asset # - 2, as shown.

Appreciate any help you can offer. I also don't have to use an Automated Number field. It could be all in the formula.

Here is your formula in the column Andrées formula - slightly modified:

=IF(Ancestors@row = 0, [Auto Asset #]@row, (IF(Ancestors@row = 0, COUNTIF(Ancestors$1:Ancestors@row, 1), "" + PARENT() + "" + "-" + COUNTIFS(Ancestors$1:Ancestors@row, Ancestors@row, [Row ID]$1:[Row ID]@row, >(PARENT([Row ID]@row))))))

Thanks!!

Andrée Starå

Hi @Jeana

Apologies, I missed your post.

Do you still need help, or did you solve it?

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!