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.