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!

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!

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

Hi @Jeff_Hayes,

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

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!

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 = r]








//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!

@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!

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.

@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.