t Creating a related record?

[Bill C.]

Newbie question:

I've got a layout Contact displaying a record in table Contact. I want a button that creates a related record in table Client. The relationship is such that for a record in Contact there can be either 0 or 1 record in Client.

So, I need to first check to make sure the related Client doesn't already exist and disable the button if so. (See related posting.)

Next I need to open a Client layout, create a new Client record, set the primary key field of Client from the corresponding field of Contact, finally commit the record. I *think* this is straightforward; e.g.,

Copy [ Contact::ContactID ][ Select ]

Go to Layout [ "Client" (Client) ]

New Record/Request

Paste [ Client::ClientID ][ Select ]

Commit Records/Requests

Is this reasonable script to do the job? Any pointers or suggestions?

Thanks in advance.

[LaRetta]

Since Client is the 'one' side, ie, you'll never have more than one then you can use the relationship itself. In your relationship dialog box, check 'Allow Creation of Related Records' on the Client side.

Place the Client name (or something else which always exists when a Client record exists) on the layout. When the field is blank, there is no Client record. If User types into any Client field that you've put on the Contact record (except for the ContactID), a new Client record will automatically be created and the ContactID will be inserted (assuming you've based your relationship between the two on the ContactID).

In fact, all Client fields can be placed on the Contact layout if you wish. Using Allow Creation to populate the 'one' side of a relationship works quite well.

LaRetta

Corrected: I had specified ClientID but meant ContactID. This is similar but reversed from MY solution and I typed ClientID by accident. Above bold has been corrected.

Edited August 27, 2005 by Guest

[comment]

The relationship is such that for a record in Contact there can be either 0 or 1 record in Client.

I may be missing something, but it seems to me that a flag field in Contact, indicating that this contact is also a client, would be sufficient?

[Bill C.]

LaRetta

Thanks for the response. Actually they're both "on the one side". It's actually an implementation of subclasses using multiple tables and there is slightly more to it than I described. There is a Contact table, a Client table, a Vendor table, and an Employee table. Client, Vendor, Employee are all "subclasses" of Contact; i.e., each has all the fields of a Contact as well as some subclass specific fields. The primary key for each subclass table contains the same value as the primary key for the associated Contact, so the subclass primary key is also a foreign key.

So, while I can't add all of the Client fields to the Contact layout it certainly makes sense to add all Contact fields to the Client layout and your suggestion will work well in that case. That will allow for the creation of a new Client along with the underlying Contact record "at the same time". Ditto for Vendor, Employee. The alternative would have been to make the user first create a new Contact, then click the [Create Client] button on the layout. Unfortunately I'll still need the buttons for the case where a Contact already exists when the need to create the corresponding Vendor/Client/Employee record arises.

Thanks for the great suggestion!

[Bill C.]

Veteran

A simple flag would do if Client had no fields that weren't already in Contact; however that's not the case. Also (see my response to LaRetta) there are other tables, Vendor, Employee, that are playing a role similar to Client -- so I can't just add Client fields to Contact. I'd have to add a bunch of other fields too.

Thanks for the response in any case.

[comment]

I see - in that case, I suggest you follow LaRetta's advice.

Unfortunately I'll still need the buttons for the case where a Contact already exists when the need to create the corresponding Vendor/Client/Employee record arises.

Not really - since the relationship is based on ContactID, the corresponding record/s can be created automatically, just by filling Vendor/Client/Employee-specific info.

You can run all this from the Contacts table. See the attached. For demonstration, I have condensed what should be 3 or 4 layouts into a single one. Simply type into any type-specific field, and a corresponding record will be created in that table.

MultiTypeContacts.fp7.zip

[LaRetta]

The beauty of vs. 7 is that Add Creation of Related works both ways. Example: While in LineItems, you can add a Product just by typing the Product Name in the Product::ProductName field placed in the LineItem layout (if that Allow Creation is checked). One caveat is that, since ALL Product fields won't be in LineItems, there is the potential of leaving important Product fields blank. Field level Product validation will work but any fields being validated - for not IsEmpty() etc. must also be on the layout.

Client, Vendor, Employee are all "subclasses" of Contact; i.e., each has all the fields of a Contact as well as some subclass specific fields.

Hmmmm, I question the need to have duplicate fields. The only time (than I can think of) that you need different tables is if there are 1:n, n:1 or n:n. But different tables of 1:1 (particularly containing SAME fields) seems a bit unnecessary. Usually this would be handled with fields designated as Value Lists to hold your sub-categories. However, I don't know your solution so general practice may not apply in your case.

LaRetta

[Bill C.]

Hmmmm, I question the need to have duplicate fields. The only time (than I can think of) that you need different tables is if there are 1:n, n:1 or n:n. But different tables of 1:1 (particularly containing SAME fields) seems a bit unnecessary. Usually this would be handled with fields designated as Value Lists to hold your sub-categories. However, I don't know your solution so general practice may not apply in your case.

LaRetta

My fault for not being more clear.

It's actually not 1:1 (or 1:n), but 1:<0 or 1>. IOW for every Client record there must be a corresponding Contact record, but a Contact need not have a corresponding Client record. If a Contact *does* have a corresponding Client record then there can be only one. A Client IS A Contact, but it is not the case that a Contact IS A Client. This also holds for Vendor and Employee.

The "duplicate fields" are on the layout only, but refer to the table fields of the base class, Contact. IOW the layouts for Client, Vendor, Employee will all have a LastName field, but it will be bound to the LastName field of the Contact table.

This is a "standard" schema for representing subclass relationships. Another approach is to let the base class (Contact) contain the union over the fields of all the subclasses (Client, Vendor, Employee), but that approach has it's problems too. I think you'll find both approaches in use "in the wild".

Hopefully this makes better sense now?

[LaRetta]

Sure does, Bill! I was just afraid you were having multiple duplicate detail information about a Client within both tables. Thanks for putting my mind at ease about your solution. Comment's demo should come in very handy then!! :wink2: