Skip to main content

Creating Custom Autonumber System

  • August 28, 2018
  • 4 replies
  • 92 views
W

I have a request that seems simple but I can’t figure out how to set up.

My company makes YouTube videos, so we have a database with a master table of all our videos. These videos are categorized into channels, which currently there are only two of — so some videos belong to Channel A, while others belong to Channel B. Simple enough.

The function I want to create is a numbering field that automatically assigns a number to each new video. The catch is, I want to customize these numbers so that videos from Channel A count up A-101, A-102, A-103, etc., while videos from Channel B count up with an independent sequence B-101, B-102, B-103, etc.

The Channel gets assigned upon creation, because I keep the database grouped by channel (so that users are forced to click either Channel A or B to create a new record).

It seems like it should be easy enough to either count the matching records, or lookup the value from the previous record and add 1 — but I just can’t figure it out.

Sample database with a manual field for “Video #” — that’s the one I want to automate.

Please help!

    This topic has been closed for replies.

    4 replies

    J
    Forum|alt.badge.img+18

    Hi @Woody_Donahue - there is not a simple way to do this since, as you mentioned, it is not a simple matter to “lookup the value from the previous record”. The records don’t have a spatial relationship to each other, like they would in a spreadsheet, so you can’t do something like a VLOOKUP() or INDEX(MATCH(MATCH())) on the assumption that records are ordered chronologically.

    It may be possible to do this if all of your Video records had a unique “Name” in the Primary field, and if that “Name” was the same length(number of characters) for every record (you’d have to move the actual ‘name’ of the video into a non-primary field). If you did that, you could use a FIND() formula in your Channels table to get the position of a video’s name in the list of all videos for that channel, and then count characters before/after against the total characters, divide by the length of a “Name” (supposing they are of uniform length), and be able to produce a ‘position number’ for a video name in the list.

    It’s a pretty hairy way to do it, and you may still run into circular reference errors — but it might get you what you’re looking for.

      W
      • Woody_Donahue
      • Author
      • New Participant
      • August 29, 2018

      Hi @Woody_Donahue - there is not a simple way to do this since, as you mentioned, it is not a simple matter to “lookup the value from the previous record”. The records don’t have a spatial relationship to each other, like they would in a spreadsheet, so you can’t do something like a VLOOKUP() or INDEX(MATCH(MATCH())) on the assumption that records are ordered chronologically.

      It may be possible to do this if all of your Video records had a unique “Name” in the Primary field, and if that “Name” was the same length(number of characters) for every record (you’d have to move the actual ‘name’ of the video into a non-primary field). If you did that, you could use a FIND() formula in your Channels table to get the position of a video’s name in the list of all videos for that channel, and then count characters before/after against the total characters, divide by the length of a “Name” (supposing they are of uniform length), and be able to produce a ‘position number’ for a video name in the list.

      It’s a pretty hairy way to do it, and you may still run into circular reference errors — but it might get you what you’re looking for.


      Thank you! I got it working using a RECORDID formula in the primary field and doing what you suggested.

        J
        Forum|alt.badge.img+18

        Thank you! I got it working using a RECORDID formula in the primary field and doing what you suggested.


        Awesome! Glad it worked — I’ve run into a lot of “circular reference” errors trying to make stuff like that work before, so I’m glad you were able to make something of it.

        M

        See copied thread below for a possible solution.

        Copied response from: @Kasra_Kyanzadeh

        Thanks for the feedback! We’ll think about it.

        Auto Number, as it exists today, was designed to let you assign unique identifiers to records, without ever reusing the same identifier.

        There are two workarounds that can get you close to what you want:

        1. To start at an arbitrary number, create a formula field that adds the starting number to the auto number field. e.g. to start at 100, the formula would be: 99 + {Name of Auto Number Field} . You can hide the auto number field and just look at the formula field.
        2. To get rid of gaps, convert the auto number field to a regular number field, then back to auto number. But be careful with this! Since it renumbers the records, you’ll lose the old numbers.
          Terms & ConditionsAccessibility statement