How to create custom calculated job number field?

[tb011018]

I found a few old threads that touch on this but I haven't been able to find a complete solution. We use job numbers to track and store production files. The Reference Number in Workfront is not suitable as we have no control over starting number or how it is calculated. I created a field in a custom form but I'm manually entering this number into the field. It would be great if Workfront could calculate this when the issue is converted to a project. It would follow this logic: year + "-"+ (last job number used +1)

e.g. Project A in the year 2020 = 20-1001, Project B in the year 2020 = 20-1002 Project C in the year 2021 = 21-1003 etc.)

Does anyone have any text formulas that would work for this?

Thank you!

[CarrieRo]

I don't have an answer but definitely looking for the same/similar option for our team. If you receive a good solution please post.

[ljanz]

This problem keeps coming up for many users, and I wish Workfront would re-visit this topic. There was an Idea on the Exchange about it that got shot down a while back:

https://one.workfront.com/s/idea/0870z000000PSAoAAO/detail

We also would like something to work like that. I have pretty much resigned to the fact that we can't do what you are suggesting without manual control of the project numbers or investing in an add-on (https://store.atappstore.com/product/ubercalc/), and that is something we aren't willing to do. Time and money are finite resources for us.

We have a calculated field that incorporates the year and 2 custom form fields that include a department number and a campaign number. Then we add the project reference number, but this solution is starting to get unwieldy as the reference number is now 6-digits long.

[RandyRoberts]

I have asked for a long time for an iterative number field type to no avail. I am surprised that Workfront has not addressed a job number solution since every company I've ever known uses job numbers. After just 2 years in Workfront our reference numbers were in the 7 digit range due to go to 8 characters in the next 2 years. It would be nice if projects got their own sequential reference numbers. It is impossible to get sequential job numbers due to the way Workfront reserves numbers for objects.

[Doug_Den_Hoed__AtAppStore]

Hi Teresa (et al),

I invite you to consider our UberCalc solution, which can generate the next (well formatted / sequential) Job Number, either on schedule (eg every hour in, the background), or in real time (eg on button click from right within Workfront).

Regards,

Doug

[CalvinFold]

You can see in the link Leah posted that I griped quite vocally about this in the Idea Exchange, apparently to no avail, despite even spelling out how an advanced, flexible, customizable version of this could work. And we griped to our implementation specialist and our account rep pre-launch.

Like others, we weren't willing to invest in an add-on (sorry Doug!).

So we opted to use the "ID" fields as our project and task numbers. Not having them absolutely sequential confuses new people to our group and finance ("Where did all the missing Projects go?"), but we had little choice.

And as Randy said, these are going to escalate to alot of digits over time. Maybe by then WF will have given us what we need (or we moved on to another product…).

I have a looong list of "Why the heck is this obvious thing missing?" items, but this one is probably in my Top 5 because it seems like low-hanging fruit for WF.

[MoniqueEvans]

My teams didn't care too much that the numbers weren't sequential so I was able to create a calculated field that pulls in the last two digits of the entry date's year, the project reference number, and a 3 letter code for the project's group ID with hyphens in between each.

For example: 20-572801-GDS so that way we can easily identify the project by a number and division.

[tb011018]

Thanks Monique!

Though this solution won't fix my issue for a specific number, it may be helpful to others looking for a unique identifier. Could you please share the steps you took for creating this calculated field?

[MoniqueEvans]

Sure. I created two calculated fields

• Project Number
  • CONCAT(RIGHT(YEAR(Entry Date),2),"-",Reference Number,"-",Project Team Code)
• Project Team Code
  • IF(Group ID="GROUP1IDNUMBER","GROUP1CODE",IF(Group ID="GROUP2IDNUMBER","GROUP2CODE"))
  • This can go on for hundreds of groups
  • Be sure to include every necessary group because you'll be using the same field on all projects and the calculation needs to be the same on each

[MMcAnelly]

Hi, Monique

I'm trying to build a custom project number and came across this posting. I'm curious, is your Project Team Code tied to a different custom field you created or something created by the system? Can you please let me know?

Thanks,

Michelle

[MoniqueEvans]

Hi Michelle,

The Project Team Code is a calculated field also on the form.

IF(Group ID="GROUP1IDNUMBER","GROUP1CODE",IF(Group ID="GROUP2IDNUMBER","GROUP2CODE"))T

This uses the Project Group (Workfront system field) to add in a code.

For example if the project was created this year for the Marketing group, then the project number would be 21-1234567-MKTG where 1234567 is the project reference number

I will say that I created this back in 2015 and now know it's possible to nest my IF statement within the Project Number text mode to avoid needing a second custom field. I'm not the best at nested IF statements but there are some others within the Community that could easily help.

[AaronAr]

Hi Monique,

I have used a similar formula to create a job number field. The formula I used was 

CONCAT(RIGHT(YEAR({entryDate}),2),"-",({referenceNumber})).

However, it returns N/A on my custom form. I have ensured I amusing the project object type as well. Do you have any idea why the field still returns N/A even though there is a reference number and entry date populated?

Thanks,

Aaron

[MMcAnelly]

Thanks, Monique!