Key field question

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