Forum Discussion

HowardFu's avatar
HowardFu
Qrew Cadet
7 years ago

multiple auto numbering systems

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.

4 Replies

Sort By
  • SuryaExpert's avatar
    SuryaExpert
    Qrew Assistant Captain
    7 years ago
    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's avatar
      HowardFu
      Qrew Cadet
      7 years ago
      Hi Surya,

      Any suggestions based on my comment below?
    • HowardFu's avatar
      HowardFu
      Qrew Cadet
      7 years ago
      Hi Surya,

      Do you think you can spell out the steps a bit more?
  • HowardFu's avatar
    HowardFu
    Qrew Cadet
    7 years ago
    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