multiple auto numbering systems

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • In Progress
I have seen similar posts but the situation is either different or I couldn't follow the formulas that were posted so I apologize if this is redundant.

I have a [Project Number] field that is a formula numeric field.
I have a [Project Type] field with Type A, Type B, Type C

I would like [Project Number] to start at 50000 and number sequentially for Type A, start at 60000 for Type B, and start at 70000 for Type C. The tricky part is to get it to number sequentially for each type, not just based on the next [Record ID]. I don't really understand how to do a "snapshot" so I was hoping it could be explained using my situation.
Photo of Howard Fu

Howard Fu

  • 294 Points 250 badge 2x thumb

Posted 4 months ago

  • 0
  • 1
This is how I have done something similar before. I would have a separate table for the Types and then have a parent child relationship between Types and the Projects table. Types is the parent and Projects is the Child. Then have a summary field on that relationship for # of Projects-A and make sure this is a count of the Projects where the related type field = "A". Similarly for B, C and so forth. Then pull these fields to the Projects table as look fields from the Types table. Then write a formula field for Project Number such that, if it is A, then it would be 50000 + that max value for A (which you brought as lookup) and take the Right most 5 characters. If it is B, 60000 + that max for B and so on. Hope this helps,
Photo of Howard Fu

Howard Fu

  • 294 Points 250 badge 2x thumb
Hi Surya,

Any suggestions based on my comment below?
Photo of Howard Fu

Howard Fu

  • 294 Points 250 badge 2x thumb
Hi Surya,

Do you think you can spell out the steps a bit more?
Photo of Howard Fu

Howard Fu

  • 294 Points 250 badge 2x thumb
Hi Surya,

I'm following you up to the part where I pull look up fields of # of Projects for each type back to the Projects Table. However, what is the reason I need the max value for A? The max function takes the max of a set of numbers but what numbers is it picking from other than the number of each project type? There's only 1 option now so the function doesn't work. Also, what is the purpose of taking the Right 5 characters? Please help clarify.

Thank you