Automating project codes

warrior604 · ‎Apr 07, 2022

Hello! Here is what I am dealing with:

I have a table of clients and each has an acronym (KET for example) I also have a table of projects, with multiple projects linked to a client. I want to have a project code for each project. This is made up of the client acronym, year of the project, and a project number. The issue I am trying to solve is generating the project number automatically for each project.

For example:

KET-2021-01 is the first project from KET in 2021, KET-2021-02 would be the second

The 01, and 02 are unique with regard to the client, but not overall. We can have another client with 01, 02, etc.

I have not been successful in using auto-numbering as I want to be able to have repeat project numbers, i.e. TFN-2021-01 uses 01 again but for another client.

Can I do this via a formula? Would this require an automation? I am new to that, so help is appreciated.

If this has been answered already and you can point me in the right direction I would appreciate that too! New to the forum.

Ben_Young1 · ‎Apr 07, 2022

Hey @warrior604! Welcome in!

I apologize for the rushed reply, I don’t have much time!
But your post caught my attention and I wanted to give it a shot.

I created your two tables.
Clients & Projects.

Each client gets a unique code for their specific account.

Super simple.

Next is the projects table.

Here’s a sneak peek at the finished product.

As you can see, there are multiple codes with a -dddd-1 or -dddd-2 string, etc.

Here’s how I did it:

Using your linked field in the client table, create a rollup field that counts the number of linked projects.

Here are my field configuration settings:

In your projects table, create four fields.
- A number field (Mine is just named Project Number).
- A lookup field to the client code field (Mine = Code)
- A rollup field (Mine = Client Project Count → There is a screenshot below)
- A date field for the project start date. Or really whatever date you would like to reference. The year value in your formula will come from this.

All my fields end up looking like this:

Next, you’ll want to create an automation.

When you create a new project, this automation will skim the number of projects that the client has assigned, and write it to the number field.
This will allow the formula to look at that hard-coded number.

It’ll make more sense here in a second.

Here’s how I set it up:

Here’s the update record step:

If you have the ability to, I recommend changing the permissions on the number field to allow nobody to modify the value.
Be sure to allow automation to change the value.

Next is the formula field.

Here’s the formula I used in my example:

IF(
    AND(
        {Client Code},
        {Account},
        {Project Number}
    ),
    {Client Code}
)
& 
IF(
    {Start Date},
    "-" & 
    DATETIME_FORMAT(
        {Start Date},
        "YYYY"
    )
)
& 
IF(
    {Project Number},
    "-" & {Project Number}
)

There is a cleaner way to do it, but I’m rushing.
I’ll clean it up when I get back!

Functionally, this formula will build the project code based on what you give it in the field.
You need to fill in the fields in order for the full formula to generate the code.

Another side note about the formula.
If you intend to copy and paste it, please be sure to confirm that your field names match yours, or else it will fire you an error in the formula.

After you do that, you can go ahead and hide the rollup, the number, and the lookup field!

Again, I’m rushing, but please don’t hesitate to fire a question at me about this and I will be sure to reply when I can!
There are also some amazing people that live on this forum who can also chime in and probably refine my solution above.