Project Number creation using Unique ID

  • 0
  • 2
  • Question
  • Updated 2 years ago
  • Answered
Hi there- I am creating unique project numbers that incorporate a variety of information from other fields. This all works well except for one thing-currently I create the project number in a field called "Project Number Creation" that equals this 

[Dept] & ToText([Acct. Code]) & ToText([Proj. Year]) & ToText(0) & ToText([Record ID#])


Then I have a conditional rule that updates the Project Number field to the Project Number Creation field upon save. HOWEVER, currently the Record ID doesn't populate when you create a record, only after you save. So my first save populates the first part of the number and I have to save again to incorporate the Record ID. Is there a way to display the Record ID (or any sequential number) prior to save so the Project number will update correctly?
Photo of Sarah

Sarah

  • 10 Points

Posted 2 years ago

  • 0
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
Is this field the Key field to your record?  is that why you feel that you cannot just show the formula field as the Project Number Creation field?
Photo of Sarah

Sarah

  • 10 Points
Yes, the Project Number is the key field. All accounting, project information links to it.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
My only suggestion would be to have an ADD form which is very minimal and forces the user to save the record.  It will save with a bad Project Creation field.  Then they will have  to edit the record to complete their data entry and the Project code will get updated.  But the problem can be that this needs to be a unique number so unless it initially gets created with a date / time built into its value, then it may not be be able to save, as its the record ID# that makes it unique.

I have to run now, but I think that you need to have in your formula to use the record ID if its >0 else use totext(now()) as a placeholder in place of the record ID# to be sure that he record will save.
Photo of Ricardo Tsai

Ricardo Tsai

  • 40 Points
Hello Sarah,
I faced something similar last year.
What I did is:
When user is creating the record, have a non unique field for them to enter named "Temp ID".
[Dept] & ToText([Acct. Code]) & ToText([Proj. Year]) & ToText(0)
Created another formula field named: "Project ID". What Project ID does is:
[Dept] & ToText([Acct. Code]) & ToText([Proj. Year]) & ToText(0) & ToText([Record ID#]).
So, when user is creating, they only enter the initial part, once saved, the Project ID kicks in. Instructed everyone to use/report and search on "Project ID".
"Temp ID" is not a unique field because when user is creating the record, he/she will first enter the:
" [Dept] & ToText([Acct. Code]) & ToText([Proj. Year]) & ToText(0)".
That part can be duplicated and the system will tell you that it is not unique. But once saved the Record ID# is created, "Project ID" itself becomes an unique value so be sure to also set "Project ID" as a not unique field, or you will have duplication problems during creation (before saving the first time).