Autonumber Projects Based on Customer and Project Type

[Jeff_Hayes]

I’m looking to use Airtable as a full project management solution for my company. But one of the requirements is that projects must adhere to a certain project numbering convention:

XXXX-0000-X

XXXX is a 4-letter code, specific to each of our clients (1000+)

0000 is a 4-digit number, which auto-increments starting at 0001 for each client (not globally, each client counts up)

X is one letter, based on the project type

Our current system generates this number automatically upon entering a new project. Let’s say that my client’s name is ABC Tool Company. They already have 96 projects in our system. I’m going to enter a graphic design job for them.

The client’s unique client code is ABCT. The next project number would be 0097. The suffix for a graphic design project is G. All I have to enter on my project request form is the client’s name and the project type, and the system would automatically generate this code:

ABCT-0097-G

I’ve searched everywhere to figure out how to get Airtable to do this automatically, but short of creating a table for all 1,000+ clients which each has an autonumber field, I’m stumped!

Our industry requires this format, and it’s essential that I don’t screw it up by creating a duplicate code–that’s why having the system auto-generate it is crucial. Not to mention, I have 20,000+ archived projects which all have this structure.

Thanks for your help and advice…

[andywingrave]

Hey! Have you considered doing this with Zapier? It would be a relatively simple update to a record.

As I started typing I actually realised you could do this in so. many. ways…So I will narrow my answer down to how I might approach the problem.

Have a clients table, store their current project number in a field. When a new field gets added, look for the number, increment by one, update the client and create the project with the new number.

Hope that helps!

[Jeff_Hayes]

Thanks @andywingrave. I’m not super-familiar with all the Zapier can do. I’m also not great with formulas.

Is there another app I need to use with Zapier, or can it do the calculation for me?

And would you mind briefly showing me what the formula(s) might look like? I really have no idea where to start!

[andywingrave]

Hey Zapier can do all of this for you, automatically. If you need some help with Zapier, feel free to email me at andy@saastronomical.com

To do this in Airtable alone might be tricky, and I can’t see a way that would not be very hacky - But please let me know if anyone else has any ideas

[ScottWorld · Accepted solution]

Hi @Jeff_Hayes,

@Justin_Barrett posted a solution to this a few months ago… check out this thread:

Automatic Sequential Numbering of Non-Sequential Items (READ JUN 9 2021 FOLLOW-UP) Show and tell

Summary This setup allows Airtable to automatically number collections of items that may not be in sequential order. Item length can be either fixed or variable, with only minor changes needed to the core system to accommodate the latter. Example To borrow from the thread that inspired this endeavor, say you’re building product codes that have a variant sub-code, and want that sub-code to automatically increment each time an item of a given product code is added in a new record. However, it n…

[andywingrave]

Thanks @ScottWorld - This is a really cool Airtable only solution. If I were to solve this, I definitely would use Zapier or Integromat, because the end solution is cleaner.

But this is very cool, nonetheless!

[Sam_Cederwall]

Hello,

I think this could possibly be solved by the scripting block. I recently accomplished something very similar with numbering invoices and figured that my script might be helpful if you are willing to go that route. I had tried to use similar methods within Airtable to those suggested above but just wasn’t very satisfied even though they are brilliantly thought out. I never tried Zapier, so that could be a good solution as well!

I just have a few caveats. One, I’m still fairly new to scripting and am not a developer by any means. And two, because the scripting block does not have the ability to run automatically, someone would still have to run the script when assigning the project numbers.

Here’s an example of the script I made (I changed the table names, variable names, and outputs to more closely resemble what I thought your base might look like):

//Pad numbers function
function pad(num, size){
var s = "00000" + num;
return s.substring(s.length-size)
}

//Declare tables
var clientTbl = base.getTable("Clients");
var projectTbl = base.getTable("Projects");

//Declare queries
var projectQry = await projectTbl.selectRecordsAsync();
var clientQry = await clientTbl.selectRecordsAsync();

//Empty array
var idArray = []


//Loop through clients table
for(let client of clientQry.records){
    let cliCode = client.getCellValueAsString("Client Code");

//Loop through projects table
    for(let project of projectQry.records){
        let projectClient = project.getCellValueAsString("Client Code");
        let projectType = project.getCellValueAsString("Project Type")
    
        //Check for matches
        if(cliCode == projectClient){
            idArray.push(project)

            let newID = cliCode + "-" + pad(idArray.length,4) + "-" + projectType

            await projectTbl.updateRecordAsync(project, {
                "Project Number" : newID
         })
      }
    }    
  }

What this script does is look to a clients table, finds projects for the client, and incrementally assigns a number to each project as it’s counting the number of projects it finds associated with that client.

Hope this helps and that I was able to capture what you might need! Good luck!

[Jeff_Hayes]

@ScottWorld thank you for sharing this. I began to modify the sample base, and I can see it working except for a couple of issues:

1. some of my product codes are 2 digits, some are 3, others are 4. In some brief testing with your base, I can see that it causes issues. What kind of workarounds are possible?

2. we have 20,000+ projects to import, and we add thousands more each year. How well will the base perform if that truncated field ends up being 80,000 characters long?! (20,000 items x 4 characters each)

Thanks!

[ScottWorld]

Jeff_Hayes:

@ScottWorld thank you for sharing this. I began to modify the sample base, and I can see it working except for a couple of issues:

I’m not sure, because it’s not my base nor my solution. I have never actually played with it. So I would post your questions in that other thread to ask the people there about it.

[Justin_Barrett]

@Jeff_Hayes The post that @ScottWorld pointed you to, and the base linked from that post, are both mine, and I’m happy to answer your questions.

1. Both the post and the base cover an alternate version of the system that works with items of varying length. In the base, this example is in the {Variable Length} field.
2. With that many items to deal with, performance might become an issue. I’m fully aware that the solution I came up with is a workaround/hack (whichever term you prefer), and it’s not going to be an ideal option for all situations. In your case, some form of automation would be preferable.
   2.1. The Scripting block example that @Sam_Cederwall posted is one option, though as he points out, it still must be triggered manually.
   2.2. Another option is to use an integration service like Zapier or Integromat. For example, you could trigger an Integromat scenario for any record that doesn’t yet have its unique ID created. The scenario would search the base for all records matching that particular item, and create an ID that’s one larger than that count. So if you add the 10th instance of a given item, the next one would be #11. This is an oversimplified example, but the concept should work, and wouldn’t bloat the base with nearly as much “dead weight” data as my workaround creates.