Skip to main content

How to automatic create WBS (Work Breakdown Structure) [aka: hierarchical or outline numbering]

NLOIA
Forum|alt.badge.img+8

Hi!

     In my project plan I need to use hierarchical (or outline) numbering such:
1  ~ 1.1 ~ 1.2 ~ 1.2.1 ~ 1.3 ~ 1.4 ~ 1.4.1 ~ etc. (print 1)
     I've be doing this manually, but it would be great to have it being created automatically.
     I got the formula below from the internet, but I stuck in the error showed in the print 2.
     I also pasted, lower, the instructions I received.
     Please, can someone drive me to a easy way to have such WBS numbering (Work Breakdown Structure)?
     I thank you all in advance.

The formula I'm using: "IF(LEN({Parent}) = 0, {Autonum} & ".", {Parent.Autonum} & "." & {Autonum})"

Print 1: (the need)

Print 2: (The error)

Below the instructions I received:
"

  1. Check your table structure:
  • Ensure you have a linked record field (e.g., named {Parent}) that connects each subtask to its parent task.
  • Verify the existence of an autonumber field (e.g., named {Autonum}) in both the parent and subtask tables.
  1. Create a new formula field:
  • In your subtask table, go to the "Add field" menu and choose "Formula."
  • Name the new field something descriptive, like "Hierarchical Number" or "Task Number."
  1. Paste the following formula into the formula field editor:

"IF(LEN({Parent}) = 0, {Autonum} & ".", {Parent.Autonum} & "." & {Autonum})"

  1. Replace field names if necessary:
  • If your linked record field name is different than {Parent}, replace it accordingly in the formula.
  • Do the same for the autonumber field if its name is different than {Autonum}.
  1. Save the formula:
  • Click "Save" to apply the formula to your subtask table.

Testing and adjustments:

  • Add some records to your tables, including both parent tasks and subtasks.
  • Check if the "Hierarchical Number" field is automatically populated with the correct hierarchical numbering.
  • If there are any errors or inconsistencies, double-check your formula and field names.
  • You can further customize the formula to add leading zeros or change the separator between numbers.

"

for reference, here's the same feature in excel:  https://www.youtube.com/watch?v=qpczx4YA1Vg 

Best regards.

6 replies

Databaser
Forum|alt.badge.img+19
  • Databaser
  • Inspiring
  • 866 replies
  • December 8, 2023

Is {Parent.Autonum} an existing field in your table?

Jan - Databaser
NLOIA
Forum|alt.badge.img+8
  • NLOIA
  • Author
  • Inspiring
  • 21 replies
  • December 8, 2023

Hi!

Nope, there's no {Parent.Autonum} field in my base.

Till now I was thinking it is the junction of the "Parent" with the "Autonum" through a point: "." 
Now I realize that I'll need to create such field. Am I right?
If yes, in which field I will use the formula?

Thank you !!

Databaser
Forum|alt.badge.img+19
  • Databaser
  • Inspiring
  • 866 replies
  • December 11, 2023

I think {Parent.Autonum} is meant to be a lookup field, looking at the autonumber of the parent. You could adjust the formula to:

IF({Parent}, {Parent.Autonum} & "." & {Autonum}, {Autonum})

If a task has a parent linked to it (= field {Parent} is not empty), it will look at the parent autonumber (lookupfield) and combine it with the autonumber of the task. Otherwise, it will just give you the autonumber of the task. 

Jan - Databaser
NLOIA
1 person likes this
  • NLOIA
    NLOIA
NLOIA
Forum|alt.badge.img+8
  • NLOIA
  • Author
  • Inspiring
  • 21 replies
  • December 11, 2023

Hi!

Thank you for helping @Databaser !

Sorry, but this formula still do not recognizes the field "(Parent.Autonum)".

I highlight that  now I have the WBS number separated from the string of the "Task Name" field and it will be a good idea to bring the previous "Extracted WBS" register to the actual desired "Parent" field

I success returned one register for one of the "Parent" field using the formula below:


IF(
  {AutoNum} = 1, "",
  IF(
    FIND(
      {Extracted WBS},
      {Extracted WBS},
      {AutoNum} - 1
    ) > 0,
    {Extracted WBS}
  )
)

But it only worked for the register #2, the subsequent lines was not filled. I'm trying to discover how to fix it.

Remembering: My objective is to automatically fulfill the "Parent" field of the actual one, normally it will be its previous register. So (in my mind) it is only use a formula that returns the content of the "Extracted WBS" to the actual "Parent" register in the column "Parent".
The print below shows the actual result.

Best regards.

NLOIA
Forum|alt.badge.img+8
  • NLOIA
  • Author
  • Inspiring
  • 21 replies
  • December 11, 2023

Hi!

Another approach:   

Now I have a Field called "WBS" (single line text type) who haves in it the outline numbering I entered manually.

Is it possible to have an AirTable Formula that create a new one "WBS" register or change the actual number in the cell, based in the "WBS" number that exists in its previous line.

Example: I have the fifteen registers below:

01 - 1 ONBOARDING
02 - 1. Merchant onboarding (API)
03 - 1.1 CREATE LEGAL ENTITY (Controller/Account Controller)
04 - 1.1.1 API call to Create Legal Entity (Controller, Acct_Controller)
05 - 1.1.2 Consume Create Legal Entity Response
06 - 1.1.3 Perform testing and validation - Testlan
07 - 1.1.4 Certification - Legal Entity (API)
08 - 1.1.5 Perform testing and validation - Stage
09 - 1.2 UPDATE LEGAL ENTITY (Controller/Account Controller) (API)
10 - 1.2.1 API call to Update Legal Entity
11 - 1.2.2 Consume Update Legal Entity Response
12 - 1.2.3 Perform testing and validation - Testlan
13 - 1.2.4 Perform testing and validation - Stage
14 - 1.2.5 Certification -Update Legal Entity (API)
15 - 1.3 CREATE ACCOUNT (API)

Cases:
A - If I create the 16th line, I want that AirTable fill-up the "WBS" cell in the 16th line with an '1.3.1' number, following its predecessor, the '1.3'

B - If I insert a new blank line, after the the 4th line, that has the "WBS" number '1.1.1", I want that AirTable fillup the "WBS" cell in the 5th line with an '1.1.2' number, following its predecessor, the '1.1.1' from the 4th line.

C - If I move the the 9th line to the position of the 8th line, I want that AirTable automatically updates the "WBS" number of this cell to '1.1.6', based in the number of the 8th cell, that is '1.1.5'

Is it possible to obtain such formula?
If not through formula, is there another method?

I thank you in advance.

 

Databaser
Forum|alt.badge.img+19
  • Databaser
  • Inspiring
  • 866 replies
  • December 12, 2023

I wish I could help you further, but somebody with more (scripting I guess) experience will have to jump in. 

Jan - Databaser
NLOIA
1 person likes this
  • NLOIA
    NLOIA

Reply


Most helpful members this week

Terms & Conditions