populate records in a join table?

[Tim Falconer]

Hi folks,

This question seems too simple, and I can figure out a number of ways to do it, but I want to know what's <good> and <right>...

I've got a main "orders" table, that is a typical orders-with-line-items kinda thing. Happens to be called "Job".

I've got an "address book" table, also very typical. Happens to be called "Contacts".

I have a join table between these, because a Job can have many Contacts - "sold to", "ship to" "carrier", etc. And obviously a Contact can have many jobs, and many roles - sometimes "sold to" is the same as "ship to", sometimes it's not.

User Interface:

Job records are initially created in the Quote layout: a button called "New Quote" creates a new Primary Key for a new Job.

Then, there's a button called "look up contact" for the quote. This will always create a relationship for the "sold to" contact. It takes you to the "Contact Details" layout (straight out of the Starter Solution).

There's a button on the Contact Details layout, "Save this contact to quote".

...

Somehow, we have to end up with a record in the Contacts Join table that has:

the foreign key for the Job

the foreign key for the Contact

the "contact type" field with the contents "ship to".

It seems that the Job and Contact Type are, together, a composite key with respect to the Job, which is to say, they must, as a composite, be unique. That is, if one has a job open and seeks to change the contact on the quote, it should over-write the Contacts foreign key in the join table record for this Job's quote, and not create a new record.

Maybe this is a totally hare-brained scheme to begin with? Do I even want a Contacts Join table, or do I just want a Foreign Key field in Jobs for each of the distinct types of contacts, i.e.:

KF_SOLDTO

KF_SHIPTO

KF_CARRIER

KF_AP

KF_AR

...etc? It's this possible proliferation of KF's in the JOB table that gave me pause, but maybe that's really a good way to do it...? I've been going back and forth.

On the other hand, even if that's the case, I really want to know how to do this in general: I have two tables that need to be joined up by a join table, how do you create the record with both KF's? Do you write variables with the keys, create a join table record, and paste the two KF's in there? How do you know if that join table record already exists? You don't want to create duplicates, do you?

(If this sounds similar to what I was asking a couple of weeks ago, it is... but it's only similar: this is a different problem... I tried to apply the solution to the other one, and had to come back to ask here... after googling for hours...)

Thanks again,

tzf

[bcooney]

Jobs may have many "Contacts," but of each type, it only has one. I would have a kF for each type of ContactID in the Job record just like you suggested: kf_SoldTo, kF_ShipTo, etc.

As far as creating a join record, typically, you do not know both foreign keys. For example, on an Invoice the Line Items table is the join btw the Invoice and Parts table. Usually, the InvLI::_kF_InvoiceID is populated (via a portal with allow create, or scripted), and then a separate process asked the user for the InvLI::_kF_PartID (popup selector, popup menu, wizard-like Find).

[Tim Falconer]

Awesome. Thanks. :)