Key field question

  • 60
  • 1
  • Question
  • Updated 2 weeks ago
  • Answered
For each record in one of my tables I want to generate a "Job #", a unique 4-digit number starting from 1000. I want this to be the key field.

I have a formula field called [Job #] with this formula:
[Record ID#]+1000

This works fine. It's the key field part that I'm stuck on.

A key field also by default has to be a required field. The Record ID is not created until after a form is saved. So the problem is that I can't save a new record because [Job #] is required as the key field, but can't be created until after the Record ID is also created, which means saving the form first.

I hope that makes sense. Basically I'm prevented from being able to create new records. I can't figure out a way to have [Job #] to be the key field AND be a unique number for every record without using [Record ID#].
Photo of James Trory

James Trory

  • 806 Points 500 badge 2x thumb

Posted 3 weeks ago

  • 60
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
You should retain the [Record ID#] as the Key field.  You can also retain the Job # as a text formula field.

If your highest record ID# is say 57 after playing with the app for a while and now ready to go live, just carefully import from excel enough records to advance the number wheel of the record ID to 1000.  Do not go past 1000 as you cannot roil back the Record ID#.

Then just delete those records.


(Edited)
Photo of James Trory

James Trory

  • 806 Points 500 badge 2x thumb
Well the problem isn't resetting the record number, the problem is that I want to use the formula field [Job #] as the key field and not [Record ID#]. But I can't because [Job #] can't exist until AFTER a record is created.

The reason I want to do this is because I have another table that is a child to the table with [Job #] in it. I want to relate records containing [Job #] in both tables, but I can't without [Job #] being the key field in the parent table.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
I do not understand.  Are you saying that you want to enter the Parent and the children at the same time? ie be in ADD mode for the Parent while also being able to add child records in grid edit mode on the yet unsaved parent.  I believe that you can do that just natively now.  ie when the Parent is saved it will auto connect the children when they are entered in Grid edit..
Photo of James Trory

James Trory

  • 806 Points 500 badge 2x thumb
No not at the same time.

I need each record in the parent table to have a unique # that is called [Job #]. This number has to be 4 digits. I want this to be the key field that is used to associate records in the child table (ie, one [Job #] can have many records in the child table).

The issue I'm having is that [Job #] can't be the key field because technically [Job #] doesn't exist until a record has first been saved, because [Job #] is dependent upon [Record ID#].

So when creating a new record I can't save it because [Job #] - as the key field - is required but can't be created because there is no [Record ID#] yet. And I don't know what to do about that.

Sorry for not explaining it clearly. I hope this helps and I appreciate your efforts so far in trying to work out what I'm saying.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
You have not explained to me why you can’t use the record ID field like everybody else does.


I think you are overthinking this and making it more complicated than it needs to be.
(Edited)
Photo of James Trory

James Trory

  • 806 Points 500 badge 2x thumb
Yes perhaps. That's why I need help :) I'm not explaining something, which is why I'm trying to do this in the first place.

In the child table, users won't be creating one record at a time. I intend for them to import batches of new records, possibly hundreds at a time, either with an Excel sheet or just copy/paste using 'import from clipboard'.

Column A will be the [RefID] and Column B will be the associated job, [Job #].

The user will know what the Job # is because that record will have already been created in the parent table prior to them importing into the child table.

So, as part of the one-to-many relationship, in the parent table I can see how many [RefID] records are related to a single [Job #].

But in order to do this, doesn't [Job #] have to be the key field in the parent table? I have found so far that if I keep [Record ID#] as the key field then this is what needs to be in Column B when a user imports to the child table and that isn't going to work for my app.

[Job #] is going to be critical in what I ultimately want to do later, which is to use [Job #] to relate records together across multiple apps.

It's very much like [Record ID#] but it has to be 4 digits. Not my decision, it's just how it has to be. So that is what is really causing me the headache. Otherwise yes, I'd just use [Record ID#].
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
Here is your choice.

1. Use the Record ID# as the Key field and that has the advantage of being auto generated.
2. Have a different field be the Key field.  it will be a data entry field and will not auto increment.

In you original post, I assumed you said that you wanted the Job number to auto generate so that is why i focused on a solution with an automatically generated Job #.
Photo of James Trory

James Trory

  • 806 Points 500 badge 2x thumb
Yes I do need the Job # to be auto generated which you're right is why I was using the Record ID to generate it.

The issue is simply that in order to relate any other records in other tables or apps to the Job # seems to be next to impossible without it being the key field. The Record ID being the key field doesn't help me do this.
(Edited)
Photo of zvi bock

zvi bock

  • 240 Points 100 badge 2x thumb
I think this can be done using a 2 step process
1- Create a random number https://community.quickbase.com/quickbase/topics/is-there-a-way-i-can-generate-a-random-number-for-m...
 and populate the key field with the random number using a form rule 
2 - Create an action or automation that changes the key field to the value of record id # + 1000 any time a record is added
If you choose to use an action you would need to create a report link that connects the record to itself (I do this all the time). With automations you can create trigger when a record is added for the record to update itself.
Good Luck :) let me know if you need any more help on htis
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
James, when you do the batch import, how does the user know what Job # the children are supposed to be related to?  Perhaps you want to proactively create a bunch of parent records ahead of time for the people making the batches to use as they like.

I just don't know enough about your process to understand how these child records are being created.  what are these child record in real life.
Photo of James Trory

James Trory

  • 806 Points 500 badge 2x thumb
Yeah it's definitely complicated.

I'm writing an app for the production team at the photo studio I work at. Every shoot that they have to organize needs a unique 4-digit code that allows them to easily identify a shoot, which is [Job #].

[RefID] represents each SKU that they will shoot on a job, so one [Job #] can have many [RefID].

I have a table called 'Sets' where they will create a new job that includes pertinent information about that shoot, like the date, the location, what brand of clothing will be shot, etc. When they save the new job it creates [Job #] from the Record ID. There are anywhere from 4-8 jobs per day so there are a lot of jobs to keep track of.

Several days later (maybe even weeks later), in another table called 'Sent to Studio', they will add the [RefID] SKU's. There could be hundreds. These are the SKU's of the garments that will be shot on a job. Next to those SKU's I want them to also be able to add the [Job #] from the jobs they created earlier in the week so that we can see which [RefID] were on which [Job #]. I don't see a way around having to do this in bulk.

I've been trying to work out how to do this without [Job #] being the key field in 'Sets' but it doesn't work. The issue is that if [Job #] is the key field, [Job #] is now required. But [Job #] can't be created until after the Record ID has been created, which won't get created until the set is saved. This results in the user not being able to create and save a new set, because [Job #] isn't created yet but is also required for saving a new set. It's like a vicious circle.

Where it gets more complicated is that I have another app already built called Shot Tracker. It's been in use for over 2 years. This is tracking the images of the SKU's after the shoots have happened.

The jobs in this app also use a [Job #] to identify a shoot but that # is being created in another software entirely. I want to ditch that software and transition the job # generation to the new app I'm building in Quickbase. Then I can relate the new app to the current Shot Tracker app with [Job #] being the key field across multiple tables so that we can see what's happening on each of these jobs, from set to SKU to actual images.

But I think that requires me to work out how to make [Job #] the key field in several tables, possibly in more than one app. Otherwise this spider's web I'm creating won't work.

::phew::
(Edited)
Photo of James Trory

James Trory

  • 806 Points 500 badge 2x thumb
Yeah I understand what you're saying.

I have to speak with the production team about this. I think the problem for them if I create an app that used Record ID as the job # is that there would definitely be an overlap in the production teams' current invoicing and tracking records with older jobs from several years ago.

My idea was that if I can start the app off by generating Job # to continue on from the current number sequence then it would be easier for the team to transition to the new QB app. I can only do this by taking Record ID and adding whatever number the production team is on, so if they're on job # 1900 when they first start using the app I would put in a [Job #] formula, "[Record ID#]+1900" or whatever worked to continue the sequence.

It could be however that we could start from scratch, in which case this would all be a lot easier. But I'm not sure that's going to be possible.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
I really think you are making this needlessly complicated.

We are where we were.

I suggest that you should just Import (from excel) and Delete 2,000 Set records into the app and roll the number wheel ahead so it won't conflict with the past numbering system.

Then just use the [Record ID#] as the Job number  and as the as the Key field.  If you like you can rename the [Record ID#] to be called [Job #]
 
Plain vanilla solution. 
Photo of James Trory

James Trory

  • 806 Points 500 badge 2x thumb
Yeah I think you're right. This is the only way this will work.

I also just need to go and speak to the team. For all I know they'd like to start the job numbers over anyway.

Thanks for your help. Sometimes the best solutions are the ones that need to be bashed over someone else's head until they get it :)
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
OK :)  Pick a new starting point if you like at a clean # like 2000 or 5000, so everyone knows which are the old numbers and which will tie into Quick Base.

You cannot roll back the wheel so don't accidentally burn off too many numbers.
Photo of James Trory

James Trory

  • 806 Points 500 badge 2x thumb
Yes agreed, it does need to be coordinated. I will continue to build the app and at launch I can push the Record ID's forwards.