Key field question

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.

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.

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..

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.

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.

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#].

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 #.

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.

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

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.

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::