Jump to content

Elementary Relationship question


This topic is 6109 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Have 2 tables in a database

Artists and Paintings with a OM relationship between Artists and Paintings.

In a layout based on Paintings, I have a field for artist Name derived from the Artist's table. If I use the OM relationship to fill Artist Name from Artist table using a value list generated from the Artist table, I see that I dont save much in terms of file size. Because In the table view I still see that the Artist name field in the Paintings table is filled with Same name in multiple records.

Whether I enter artist name into paintings table manually or using a value list via the relationship, my database filesize remains the same !

Wouln't this be redundant data?

Am I doing things wrong here?

Wouldn't I be better off creating a value list for artist names and check the option to enter othervalues than keeping a separate table for artist Names and generate a value list from it to fill Artist Names in the paintings table?

Link to comment
Share on other sites


Just display the Artist from the Artist table through the relationship. eg. Artist::Name

The relationship stores the match fields for the tables, to know which Artist Name to display.

Two tables makes adding artists easier, however you could just use a value list if you prefer.

There is almost never only one right way to solve a problem. Each solution holds pros and cons, lending it's use to the desires of the designer.



Link to comment
Share on other sites

If the artist name is a field in Paintings, then you might as well display that on the layout, not the related field. But if you are concerned with file size, you should create an auto-enter serial id field for Artist, and use that as the relationship key. That is, no artist name field in Paintings, just the Artist ID. Then you can do as Tim says, just display the artist name in Paintings as a related field. That is the most space-optimized way.

It is also possible in later versions of FileMaker to create a value list of all Artist IDs, [x] Also show the name (should be a Last, First calculation),* and [x] Show only values from 2nd field. That way only the names drop down, in either Browse or Find mode, but the field is actually the Artist ID.

To polish it off, you put the related artist name field on top of the Artist ID field in Paintings, but change its Field Behavior to no entry in Browse or Find. It then looks like the name field and acts (much) like the name field, but is the ID field.

*There is one caveat to this technique. You cannot have 2 artists with the same first and last names; because only the first one's ID will ever get chosen, the other will never be in the value list. But that would be pretty rare, and can be handled by including a middle initial field in the calculation, and validating the fields to not have a duplicate.

Link to comment
Share on other sites

This topic is 6109 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.