Creating record in related file

[Rfox]

It seems like it should be easy, but it is not working.

I have a complex database with all kinds of information. The related file contains only "First Name" "Last Name" "Full Name" and a container file for a photo. "Full Name" is the matching file as it would be the same in both databases. It is simply (in the main DB) First Name & Last Name. (In the related DB it is a text file.)

In the relationship definition it is set to "Allow creation of related records" as well as allow deletions.

So when I create a record in the main database, shouldn't it create one in the related DB? It does not. However, delete does remove the record from both files.

I must be missing something. Thanks for the help.

[ESpringer]

Ah, now I understand ...

You seem to have assumed that "Allow creation of related records" was designed to generate a "buddy" record each time you make one in the main file. But FileMaker doesn't assume that you need *one* related record for each main record -- some records may need many; others may need none. This is convenient, in your case, because your photo file really should get a record *just* for each person for which you've actually got a photo.

In fact, when it's a one-to-one situation, a related file is almost never a good idea, since you could just put the data into the main file for each record. (Image files are a good exception, if you want to keep the main file "sleek" for frequent backup copies...)

Anyway, you need to put a portal (with fields from the related file), or simply a related field (like the container for the picture) onto your layout. Then, you should be able to enter data into those and find that the related record is created.

I'd recommend checking out information on related fields and portals in these forums... Feel free to ask if you're still uncertain how to set up your layouts to do these things...

[Rfox]

As you said, the reson for the separate file is to prevent the pictures from making the main DB so BIG.

My photo field in the main DB IS a container field pointing to the ralational DB.

I will work with the portal idea and see if I can make that work.

In the data Entry layout, would I make the First and Last Name a portal for the photo DB and then when I enter the new name it will create in both??

BTW, I did look through the forum (and help files) quite extensively before posting the request. I just never found anything that answers my situation.

Thanks for your help

[Rfox]

I couldn't make the portal idea work, but this is what I did that works.

However, I'd rather NOT have to run a script for data entry.

In related file I made First Name and Last Name a relational field that gets it's info from the main DB.

I made a script that enters New Record mode and pauses for data entry. On "Continue" if copies "Full Name" from Main DB to "Full Name" in relational DB. Thereby the full enry is completed.

But this forces operator to select continue at the end of data entry to accomplish the objective.

Is there a better way?

[John Caballero]

Yes. Try the portal idea again. One thing to consider, though - if your join field (I believe you called it Full Name) in the related file is a calculation, the auto-create related records option won't work. Let's assume that it's not a calculation.

OK, so you've got your relationship from your main file to your related file, and it's set up to auto-create related records. Go to a layout where you can play around, get into layout mode and use the Portal tool to create a fairly long and wide rectange. In the Show Records From drop down menu (in the Portal Setup dialogue box), select the relationship. The Show rows should default to 1, if not go ahead and make it 1. That will place the portal on your layout. Now, while still in layout mode, click and drag the Field tab (on the far left)over to your portal. When you release, you'll see a list of fields that live in your related file. Select one you can type into (First Name, is fine) as well as the field that is the join field. Enter browse mode, click in the First Name field you can type into it. You should see the join field auto-populate with the Full Name that you specified in your main file. That's it - you just created a related record.

[Rfox]

Thanks John,

The challenge is that "Full Name" *is* a calculation. It is First Name and Last Name joined (&). Maybe I could experiment with having the full name as an entry field and then Fist Name and Last Name as calculated fields from there.

The reason for using the Full Name as the match File is I can't think of anything else that is unique to each child. If I had done serial numbers differently I could use that, but I am currently extracting Filemakers Record Number for the ID number.

[Jim McKee]

Rfox said:

The reason for using the Full Name as the match File is I can't think of anything else that is unique to each child.

The problem is that names are not unique. And, people change their names. How will your users handle a situation where they enter a new person who has the same name as one already present in the database? If you read through the posts here about best practices for devising unique keys, you will find one constant recommendation: relationship keys should be essentially meaningless and therefore never need to be modified. Before you get too much deeper into building your structure, it might be a good idea to think through the long-term consequences of using names as your relationship key.

My suggestion is that you assign serial codes to each record in your main database. Then, script a procedure to assign the serial key of each main file record to its related record(s) in your photo file. This is not a difficult process, and could very well save you lots of headaches down the road.

Also, if you have only one related photo record for each main file record, a cool way to manage your photo file records would be to have the related photo container field displayed on the main record. The user clicks on this field to initiate a script. If there is no related photo record yet, one is created and the user is immediately taken to a dialog where they select the photo to be inserted into the new photo record. If a related photo record already exists, the user is prompted to confirm that they want to insert a different photo into the related record. Or, the user might simply want to copy the photo to the clipboard for use elsewhere, and this could also be handled by the script. See the attachment for some ideas about how to implement this stuff.

People.zip

[Rfox]

Thanks Jim,

I think I should impliment the idea to assign a unique Serial number to each entry. I can see that duplicate names would be a serious problem, even if they are unlikely in my situation.

The idea of a script to create a new entry in the photo BD may also work. Since it is a DB of children attending Junior Church, the user is not the one whose name is in the DB. But all entries do NOT have pictures and there is no need for a record in the photo DB if there is no picture. So, a script to enter the photo when it is available will work.

Good ideas!

[ESpringer]

You shouldn't need a script. Just go into layout mode and *PLACE* the related field onto your layout. To do that, drag the "field" thing from the status/layout area onto the layout, and when the dialogue box asks which field you want to place there, use the pop-up at the top to choose your related file name. Then, the fields from *that* database file will appear below. Choose your container file.

Now, you can paste into that field just as if it were in your main database. FileMaker will, behind the scenes, create a record in the remote database (assuming the right-hand key is not a calculation!), give it the appropriate match values given the source record you were browsing, and populate it with the picture you're pasting.

Best of luck.