3 Replies Latest reply on Oct 14, 2013 11:20 AM by philmodjunk

    one-to-many, relationships



      one-to-many, relationships


           There are 7 options that seem to be join type. What do the options mean?

           The same question stated differently: This is in the Database > Manage > Relationships tab and when setting up a join, between the tables there is "=" and other options are <, >, x, etc. What do those symbols represent?

           A more fundamental question: how do I do a simply one-to-many join? The default seems to be a many-to-many join.

           I have tried to look around with 'join type' or 'one-to-many' and all I find in the KB is the 3 relational database entries and many-to-many entry. Nothing refers to the basics.

           I'm an access developer switching over, so I'm just trying to learn my way around the filemaker procedures to do what I've done in access (and mysql) previously. Go Mac.


        • 1. Re: one-to-many, relationships

               Welcome to FileMaker. I've used both products so I am familiar with how they are similar and how they are quite different. Be prepared to "retrain" some of your thinking as FileMaker can be quite different from Access in a number of ways. Reaching for a familiar "tool" you used in Access and finding that it doesn't work at all in FileMaker will frustrate you for a bit until you learn how to do the same thing in FileMaker.


                    how do I do a simple one-to-many join?

               Define an primary key in table 1 that you will link to a field in table 2. There's no control to click to designate a field as a primary key in FileMaker like you do in Access. You can even define multiple "primary keys" in one FileMaker table though that would be redundant. Primary keys are created by the field options you select for that field. (Click Options or double click the field definition in Manage | Database | Fields.) The simplest primary key is to take a number field and define it to auto-enter a serial number. Or you can set up an auto-enter calculation that enters Get ( UUID ) into a text field. And you can also select a unique values validation option for a field to enforce unique values.

               Click over to the Relationships tab and drag from the primary key field in table 1 to a field of the same type that you intend to use as the foreign key field in table 2. The foreign key field should not auto-enter a serial number or UUID nor should unique values be specified for it. This creates a relationship line with the default = operator and you have a simple one to many relationship.

               Note: the different "connectors" that you can get connecting a relationship line to a Table Occurrence box in the relationship line are determined by the field options specified for that match field. You'll get the expected single line connector if the field auto-enters a serial number or has a unique values validation option selected for it. Thus, it's not uncommon to see what looks like a "many to many" connection that is functionally a one to many or many to one but where scripting and/or interface design work is used to limit the possible values in one field or the other.

               The operators that you can select work just like the operators in a Join clause in SQL--something that you may have worked with in MS Access. They mean the same thing that you'd see in a Boolean expression. Table1::Field1 = Table2::Field2 means that a record in one table links to a record in the other only if the values in the two fields are equal. Change that to a different operator such as one of the inequality operators and the logic indicated by that operator applies so you can link to records where the value in the match field is less than, greater than, does not equal, etc the field in the other table's match field.

               X is a special operator that is used when you want any given record in table 1 to match to all records in table 2. It's known as the cartesian join operator. It's most frequently used to facilitate putting a portal (think Access SubForm) on a layout that then displays all records from the related table.

          • 2. Re: one-to-many, relationships

                 Sounds good. Seems clear enough. I hope this is added to the knowledge base.

            • 3. Re: one-to-many, relationships

                   As a fellow user, I have no input on what is or is not added to the knowledge base.