field is text and I need it to auto populate a unique number but keep the previously entered numbers already there.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have a text field that the company has been using to enter record numbers into. This has become a hassle to find what number you are on as we add more records. I would like to make the field auto-populate a unique number when the record is being created but I need to keep the previously entered record numbers that have been entered already (these are not unique for every record as there are many jobs and each one had record numbers entered starting at 001. This is not necessary but I can't change previously entered data for record purposes). I would like to keep it in the same field if possible without creating a new field (for example I could use the record ID number field but I need the previously entered numbers to stay as their report numbers). 

Photo of Meme

Meme

  • 10 Points

Posted 3 years ago

  • 0
  • 1
no problemo,

Use this as a new formula-text field

IF(Trim([my old field])<>"", [my old field],ToTex([Record ID#])
Photo of Meme

Meme

  • 10 Points
There is one more issue i thought of. Some information is preloaded into this field but I cannot assign it a record number until later. I only want it to pick up the record ID number if another multiple choice text field "Item Status" is selected as
CP SCHEDULED
CFR SUBMITTED
CLOSED/ANSWERED
If anything else is selected under item Status I want it to be left blank or keep it a plain text field.
Is this possible?
What do you want the formula to do if the number was entered manually but there was no selection made in that multiple-choice field?
Photo of Meme

Meme

  • 10 Points
Leave it blank until one of the 3 fields mentioned above are selected
Ok try this version.  It uses a formula variable, just to make the formula as simple as possible.



var text JobNumber = IF(Trim([my old field])<>"", [my old field],ToTex([Record ID#]);

IF(


[Item Status] = "CP SCHEDULED"


OR

[Item Status] ="CFR SUBMITTED"

OR

[Item Status] = "CLOSED/ANSWERED", $JobNumber)
Photo of Meme

Meme

  • 10 Points
I am getting a syntax error with this formula and do not see where the issue is.
The first line was incorrect.  Should be

var text JobNumber = IF(Trim([my old field])<>"", [my old field],ToText([Record ID#]));
Photo of Meme

Meme

  • 10 Points
I am getting the error "A formula may not contain a reference to itself directly or indirectly through another formula." when I enter the formula into my existing Report No. field. I tried to also enter it into a new field and it gave me the report numbers but deleted my existing report numbers.
If you have changed the field type of your existing job number field, which has been entered manually,  to a formula field, then you will have destroyed your existing job numbers.  That would explain why the formula field his not showing them if they no longer exists.

If that is the case, then you will need to contact QuickBase Support to ask for an excel restore of that field and import it back into your application.

Contact me off line via the information in my Profile if  that is what happened and your require consulting assistance.

If you still do have the data for the Job Number in the application, then we can sort out why the formula is not working.
Photo of Meme

Meme

  • 10 Points
I am making changes using a copy of the table so there is no need for a restore. I just need to know how to get the formula to work. What if i create a new formula text field? Can I pull that existing data from the old field and do what I am trying to do and keep the existing report numbers?
Yes, create a new formula text field.
Photo of Meme

Meme

  • 10 Points
I was looking at this incorrect. It works as a new field. i appreciate all of the help you have given, it is appreciated so much!
What do you mean by that comment?  If you create a brand new field of type formula-text, then there is no way that creating a brand new field can delete data in an existing field.

It's like adding a formula in column B in an excel sheet.  There is no way that formula in column B will delete data in column A.
Photo of Meme

Meme

  • 10 Points
This is working great. i have been asked to make the numbers reflect a zero to start until it goes above the thousandth, For example; my report numbers are 300, 301, 302 but going forward need to be 0303, 0304, 0305 format. Do you know if this can be accomplished. i have tried but have had no luck in my searches. Thanks.
try this
IF(Trim([my old field])<>"", [my old field],Right("000", & ToText([Record ID#]),4)
Photo of Meme

Meme

  • 10 Points
I am getting syntax error. This is what my formula is, maybe this will help?     

var text JobNumber = If(Trim([Report No.])<>"", [Report No.],ToText([Record ID]-3987));
If(
[Item Status] = "CP SCHEDULED"
or
[Item Status] ="CFR SUBMITTED"
or
[Item Status] = "CLOSED/ANSWERED", $JobNumber)
try this


var text JobNumber = If(Trim([Report No.])<>"", [Report No.],Right("000" & ToText([Record ID]-3987),4));
If(
[Item Status] = "CP SCHEDULED"
or
[Item Status] ="CFR SUBMITTED"
or
[Item Status] = "CLOSED/ANSWERED", $JobNumber)
Photo of Meme

Meme

  • 10 Points
Beautiful!, yes it works great! Thanks so much!
Photo of Meme

Meme

  • 10 Points
For some reason new report numbers that are generated are not coming out correctly. Now they are only generating 000. Nothing else.
Is this in Add mode or after the Save.  The Record ID# does not exist until the record is saved.
Photo of Meme

Meme

  • 10 Points
Thank you, I did not notice that yesterday. Thanks again!