Expand my Community achievements bar.

Is there a custom field that can generate a unique number. We have a use case where we need a unique number generated but leveraging reference numbers on tasks, issues, projects, etc. may not be a sustainable solution.

Avatar

Level 4
 
Topics

Topics help categorize Community content and increase your ability to discover relevant content.

13 Replies

Avatar

Community Advisor

I've been asking for this for years! I'm sure it's in the idea exchange somewhere.

It would be perfect for job numbers.

Avatar

Level 10

@Lila Whitney‚, a common ask, but no.

They used to long ago, but changed it so that all objects have unique numbers across objects. It's a really common ask, and there is an Idea Exchange entry going back a couple years:

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

I even provided an outline of how it might accommodate advanced features.

Avatar

Level 7

It would be a great custom field option! Relying on the reference numbers is NOT sustainable. I was really disappointed when they decided to not make sequential numbers an option. I wish they would look into it again. It is such a COMMON ask.

Avatar

Level 10

Hi 

Finding difficulties to open this link, is link moved elsewhere?

BR

Kundan.

Avatar

Community Advisor

Do you just need the number to be unique, or do you also need it to be sequential? Everyone in the thread is talking about sequential numbers (which is a common request and is possible with Fusion); but if you only need the number to be unique, there are several potential methods and no Fusion required. Happy to share a couple methods that we use if "unique" is the primary requirement.

If you like my content, please take a moment to view and vote on my Idea Requests: https://tinyurl.com/4rbpr7hf

Avatar

Level 10

Unique, sequential, and without Fusion. Such as sequential project numbers.

Avatar

Level 4

I agree with what Kevin stated (unique, sequential, and without fusion). Our customers use the unique number to interact with us so there would also need to be a way to provide this to them in email communications and their submission tracking. With the large volume of work that we do (approx 700 projects a month), we needed a short term solution to get by. We built a custom form field that gets copied from the request (submitted by a customer) to the project. It is based on the request reference number because that is what is sent to the customer. We have no way to control our email messages which also causes issues. Any solution for a unique number would also need to include some control over how that is presented to the customer. The other issue we see with our current solution is that the numbers are growing exponentially and we are worried our archiving system will not be able to accommodate the extremely long numbers at some point. It is creating a great deal of angst for our users and owners of other systems.

Avatar

Community Advisor

Following this one folks; for years, in fact.

 

So: having mulled it over a lonnnnng time, this morning, I worked out a solution. It is crude, but effective, and will allow anyone to enter a unique, sequential number without Fusion (i.e. for free). Accordingly, it will come with no support, several cautions, and no apologies...but those for whom it will suffice are welcome to use it As Is.

 

I will submit it at my earliest opportunity [below]:

 

Regards,

Doug

 

With thanks to my colleague John Mitchell for the nudge...

 

Here is the sequential numbering technique I mentioned quite some time ago. In this example, imagining a numeric custom parameter called "Project Number" exists on a custom form that is procedurally (and technically) attached to all Projects, and that currently has a maximum value of 10581:

 

  • Create a new Project Report called "Max Project Number"
  • Set the view (e.g. Project Number, Project Name, Owner, etc.)
  • Click the Project Number column in the column settings set Sort By This Column = First and sort it Descending and Summarize this column by Maximum
  • Set the grouping to Entry Date with Group Dates by set to Year and check Collapse this grouping by default
  • Set the filter as desired (e.g. Project Number is Not Null) and consider adding a performance "booster" (e.g. Entry Date greater than $$NOW-7d ago, assuming new projects arrive and get numbered every day, so the most recent will always be within the last 7 days)
  • Skip the chart tab
  • Click the top right Report Settings and set When the Report loads, show the: to Details Tab then click Done
  • Save the report 

When this example report then runs, it finds all of the Projects entered within the last 7 days, sorts them by Project Number descending, Groups them by year (i.e. usually just one grouping, except at the end of December), and collapses the group(s) to then display the maximum Project Number on that grouping line, which would be 10581.

 

Sharing such a report to users can then be used as a quick reference: when they are adding a new Project, then can then enter the next number 10582 (manually).

 

This is a relatively simple solution and does have MANY shortcomings (e.g. data staleness, requires manual page refreshes, lacks data protection, assumes simple sequential numbering, assumes all users have full sharing rights to see all numbers, and many others), so I am neither recommending nor supporting it.

 

That said, those who need a sequential number but do not have our UberCalc solution (or Fusion) are welcome try it out, in case it is sufficient for your requirements. 

 

Regards,

Doug

 

P.S. When I made my previous post above, I'd used an API "report" (vs "search") to pull the maximum Project Number back as JSON (without needing the Entry Date trick the report uses) and then exposed it in a dashboard using an External Page...but having thought it over today, the above struck me as a far more pleasant experience for end users

 

cc: note to self @Doug_Den_Hoed__AtAppStore 

Hi Doug!

 

Awesome, working as intended after testing. Question regarding the max project number of 10581. I tested using 200,000 as a starting point and iterated up to 200001 with the next entry. The report works just as described!

Is your note above a reference to a max # of total entries to the field? (e.g. using project #200000, only leaves 10580 more possible sequential projects before this field "breaks" making the max possible project number generated in my example #210,580?)

 

Are we talking a custom-field system rage limit I'm not aware of here? 

 

Expectations met as far as I am concerned, thank you so much for giving a solution to this pesky problem.

 

-John 

Avatar

Community Advisor

 

My pleasure @JMitchell44,

 

I'm glad that Max Project Number reporting technique (vs my initial API kludge) did the trick, and no, to confirm, nothing magical about my selected sample data of 10581 and (whoops -- caught and corrected a typo above) the subsequent 10582 next number, and no system limitations: it's just a number field.

 

I have several tangential ideas on how you could stretch this even further, but let's let it breathe a bit first and see how it is received and which gotchas make it to the top of the pile.

 

Regards,

Doug

 

 

 

 

Avatar

Level 3

Piggybacking onto this thread – does anyone know if there's a better way to reference an expense on a project than the crazy long expense ID? It would be great for a PM to be able to reference or check up on the status of an expense using this unique ID.