7 Replies Latest reply on Jul 31, 2011 9:32 PM by LaRetta_1

    Need for one to one? can't find info...



      Need for one to one? can't find info...


      For my specific need, I am trying to create a db to track student financial account information (for a single class not an entire school). My initial thought was to set this up like I've done with another invoicing solution that I've built.  ONE student will have MANY invoices that might contain MANY line items.

      I've now shifted my thinking that for easier tracking purposes and my layout needs, I like for ONE student to have ONE invoice (where all activities for the year will be added) with MANY line items.  I've searched online and read sections from books but cannot find where anyone justifies a true ONE to ONE via the Relationship settings.

      My current setup for MANY INVOICES:

      Students::student_ID ----<Invoices::student_ID

      Invoice::invoice_ID ----<Line Items::invoice_ID

      Items::item_ID ----<Line Items::item_ID

      What would my setup look like for ONE student with ONE invoice?

      Any thoughts/comments/advice/how-to's would be greatly appreciated.

        • 1. Re: Need for one to one? can't find info...

          You make a button which calls a script with a scriptparameter (student_id).

          With the script you go to a layout with all the invoices.

          I made this script in Dutch, I don't know all the translations. 

          • 2. Re: Need for one to one? can't find info...

            A one to one relationship would be implemented just like a one to many relationship in terms of setting up layouts, buttons etc. to work with your data it's just that you'll only have one matching child record for any given parent record.

            A true one to one relationship can be replaced with a single table used in place of two related tables, but this is not a change to make lightly as a one to one relationship can be converted in to a one to many relationship much more easily than dividing a single table into two related tables. Sometimes other design considerations argue for using a one to one relationship in place of a single table. One such example is when you want to set up different access permissions for some of the fields, than the others. With a one to one relationship, you can set those different permissions on each of the two related tables.

            • 3. Re: Need for one to one? can't find info...

              One-to-one (1:1) can also be used effectively 1) if you have fields with substantial text, such as notes or 2) if you have fields which logically can be grouped separately.  One such example ... a government form with individual questions not found in the regular data.  One CPMS form (for Title XIX state payments) has 65 demographic questions. 

              It is then logical to place these 65 fields in a related 1:1 table simply to reduce the size of field definitions (since we don't have folders for fields).  There is nothing wrong with 1:1.  ALL data from ALL fields in a table must be loaded when accessed.  So for speed purposes, unused data and large text fields needn't be loaded every time because they are rarely accessed.

              It is also easy to split the tables ... since the table holds the StudentID, import the table (selecting option of creating new table at the bottom of the target-side import).  Delete the redundant fields from original table that you want in the second table and delete fields from the second table that you want in the first table; leaving the StudentID in both.  Connect on StudentID.

              • 4. Re: Need for one to one? can't find info...

                Another use for 1:1 relationships are to create a pseudo FieldLevelPrivilege.  Since you cannot parse privileges to the field level, you can move all of the fields needing restriction to another table in a 1:1 relationship and then control the privileges of the new table.

                Example: data should not be altered if approvals are already applied, but shipping info can be changed even after data is approved.  Data is in one table with restrictions applied, shipping info is on a 1:1 related table without restrictions applied.  A long way around to FLA, but it's the only one we've got for now.

                • 5. Re: Need for one to one? can't find info...

                  Thanks for all the responses.  My novice will truly show here but I must be missing something very basic in the relationship tab in managing my database.  I would like to keep my existing structure but replace my ONE to MANY with a ONE to ONE (following PhilModJunk's suggestion).

                  What I have:

                  Students::student_ID ----<Invoices::student_ID

                  What I want:


                  Where do I set that up?  Is this a field change? Edit relationship change? If so, what option would I select in the = menu to allow this?

                  OR, do I simply connect from student_ID----invoice_ID?

                  Sorry for the confusion.

                  • 6. Re: Need for one to one? can't find info...

                    No change needed.

                    In fileMaker, a one to one relationship is the same as a one to many relationship. You just have one matching record on your other side of the relationship. FileMaker attempts to interpret relationships in Manage | Database | Relationships by looking at the field options of the fields used as keys in the relationships. If a key field has a "unique values" validation or an auto-entered serial number, then FileMaker uses a single line. If it's not restricted to unique values or a serial number, file maker shows crows feet. You can apply a unique values validation rule to the Invoices::Student_ID field if you want. That will produce the single line you are expecting, but you may find there's no real need to do so.

                    • 7. Re: Need for one to one? can't find info...

                      Unique validation on StudentID might be important if you are ever importing into this table or recovering data from backup to eliminate possibility of duplicate student invoices.  But with 1:1 relationships, you can place the other table's fields directly onto your Students layout (turn on allow creation on the Invoices side).  If there is no invoice, typing into these fields (from the Student layout) will create a new invoice.  If there is already an invoice, the data will display on the student layout and can be modified directly.

                      By placing the Invoice fields directly on Student layout you can only ever have one invoice.  You can still place a portal of the LineItems on the Student layout as well. :^)