Creating Custom Autonumber System


#1

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!


#2

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.


#3

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


#4

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.