Creating a numbered field

Well, I probably made this more difficult than it was needed to be, but I have been playing around with some details and am trying to create a "sort column" that would allow me to put everything in a decimal to sort on for indentation purposes (since we do not have access to the gold package).

My formula is:

=IF([# of Current Ancestors]@row = 0, [Row ID]@row, PARENT([Row ID]@row) + "." + MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], [# of Current Ancestors]:[# of Current Ancestors], >=1), 0))

Currently, it is working…somewhat. if the # of ancestors needed is 0 or 1, this formula provides the correct number:

But the # of current ancestors is greater than 1, it fails and returns the row ID of it's current row, in the above case row ID 24.

So I tried adding a second column to handle greater than 1, but it only worked for current ancestor = 2.

=IF([# of Ancestors Needed]@row > 0, INDEX(COLLECT(Test:Test, [Process Identifier]:[Process Identifier], LEFT([Parent Process]@row, FIND(" - ", [Parent Process]@row) - 1)), 1) + "." + IFERROR(MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], [# of Ancestors Needed]:[# of Ancestors Needed], >=1), 0), ""), "")

I would REALLY like to get this to match up in one column to auto-populate this data.

What is the final definition of what I am looking for:

When there is no ancestor (# of current ancestors = 0 and # of Ancestors Needed = 0), return the row ID for current row.
When there is an ancestor (# of current ancestors is greater than 0), return the value in "Test" of the ancestor + a decimal point + the next number in the sequence 1, 2, 3, etc.

Example:

Parent Process ROW ID: 2
Child Process 1: 2.1
Child Process 2: 2.2
- 3rd party process 1: 2.2.1
- Critical Application 1: 2.2.2

I hope this makes sense. I did attach some sample data below.

Compiled BIA Data (2).xlsx

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Have you looked into a WBS solution? There are a number of them already posted throughout the Community including one that utilizes column formulas.

Pestomania

I had never used those but WOW!!!! I cannot use the template as it is, but I am going to try and use the formulas to make it work.

The problem is that it provides the WBS of what is currently shown rather than what the WBS should be.

This one worked awesomely: https://www.smartsheet.com/template-gallery/project-work-breakdown-schedule-wbs

Pestomania

https://community.smartsheet.com/discussion/comment/447968#Comment_447968

Alright, I played with it for a while and again, probably made it more difficult than necessary.

The columns that the WBS template provided were great to determine the WBS of "current state" while I wanted to see the WBS of the "should be state".

So, that being said I had to add a few more helper columns and I wanted to put them here for anyone who runs across it.

Helper #1: Predecessor WBS Phase IF([Parent Process]@row <> "", INDEX(WBS:WBS, MATCH([Parent Process Identifier]@row, [Identifier Modified]:[Identifier Modified], 0)), WBS@row)

Helper #2: Current Row Phase IFERROR(MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], [Parent Process Identifier]:[Parent Process Identifier], <>"", [Parent Process Identifier]:[Parent Process Identifier], =[Parent Process Identifier]@row), 0), "")

Helper #3 & 4: Parent Process Identifier and Identifier Method removes formatting from the identifiers.

Final column: WBS Phase (Sorting) IF([Current Row Phase]@row <> "", JOIN([Predecessor WBS Phase]@row:[Current Row Phase]@row, "."), IFERROR(MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], [Parent Process Identifier]:[Parent Process Identifier], <>""), 0), [Predecessor WBS Phase]@row))

There a few hiccups I will work on:

After sorting, there are issues with the repeated sorts. So not everything is exactly in line (but it is much closer). I still have to manually move 4 or 5 lines, but much better than hundreds.
Since WBS Phase (sorting) is a dynamic field, it will change the numbering sequences as you begin to indent properly. This means I needed to resort a few times (wish they would make this easier).
A lot of formulas working in the backend may get burdensome on the system with a lot of data.
It did not account for different Site Names, so I changed my WBS predecessor to include the state initials from the Site Name as a unique identifier. This requires that the parent and children share the same site name (which may not always be the case), so I will have to work on that later.

Paul Newcome

I must have misunderstood. Are you trying to essentially output a WBS without having anything indented yet?

Pestomania

@Paul Newcome, yes. I want the system to "recommend" what the WBS will be since there is no automated indentation options. This allows my team to sort on the column and indent it appropriately.

I hope this makes sense.

Paul Newcome

Are you indicating anywhere that "this row's parent row is row #"? I imagine not if you still need to sort. Does every row have some sort of unique identifier on it?

Pestomania

Hi, yes & no.

I have a field called Parent Process Identifier & Identifier Modified which removes the hyphen (since formulas do not seem to like hyphens).

Then Helper #1: Predecessor WBS Phase

IF([Parent Process]@row <> "", INDEX(WBS:WBS, MATCH([Parent Process Identifier]@row, [Identifier Modified]:[Identifier Modified], 0)), WBS@row)

This column identifies the WBS Phase of the parent process (if applicable).

It then adds the next row phase.

So i.e. if the predecessor phase is KS - 1, it recommends KS - 1.1. If the predecessor phase is KS - 1.1, it recommends KS - 1.1.1.

This highlights in the "Sorting Column" what it should be, so if that column compared to WBS does not match, it highlights red:

It also tells you how many tabs are needed.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.