Vendor management and ID #

I would like to assign unique vendor IDs in a database according to vendor type (independent column) and sequential numbering. Screenshot of what I am trying to produce below.

I have found that the Auto Number column type only accepts a fixed prefix and thus cannot populate different prefixes based on Vendor Type along with its sequential numbering.

What is the best way to do this?

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @LGraf

    Have an auto number column with the number only, then have your Vendor ID # combine it with the Vendor Type:

    =[Vendor Type]@row +"-"+ [Autonumber column]@row

    Unfortunately I don't think there is a way to have a dynamic prefix in the auto number column itself.

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @LGraf

    Have an auto number column with the number only, then have your Vendor ID # combine it with the Vendor Type:

    =[Vendor Type]@row +"-"+ [Autonumber column]@row

    Unfortunately I don't think there is a way to have a dynamic prefix in the auto number column itself.

  • LGraf
    LGraf ✭✭✭✭✭

    That works just fine. Good thinking, and thanks very much.

  • LGraf
    LGraf ✭✭✭✭✭

    @Nick Korna

    A follow up thought on this: a real issue is that if a VENDOR TYPE changes then the VENDOR ID # will also change since the formula pulls that data. VENDOR TYPEs get updated regularly when, for example, a vendor starts offering a new service or stops offering an old one.

    In the screenshot from my original post, I would like to lock in the VENDOR ID # "LEM-0001" for that vendor regardless of if the VENDOR TYPE. For example, under the current setup, if the VENDOR TYPE changes from LEM to TRANS then the VENDOR ID # will change from LEM-0001 to TRANS-0001. This issue renders the setup unusable.

    Is there a way to lock in the VENDOR ID # after it has been initially created? Auto generate does lock the number, which is great.

    Thanks very much for your thoughts.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!