Skip to main content
Question

multiple auto numbering systems

  • February 2, 2019
  • 0 replies
  • 57 views
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.
      This topic has been closed for replies.

      • SuryaExpert
      • Registered
      • February 2, 2019
      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,
      • HowardFu
      • Author
      • Registered
      • February 5, 2019
      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
      Terms & ConditionsAccessibility statement