4 Replies Latest reply on Mar 9, 2011 4:14 PM by billcritten

    Can I enforce referential integrity using relationships?

    billcritten

      Title

      Can I enforce referential integrity using relationships?

      Post

      I created a Master/Detail table setup and joined the tables on the ID fields where ID is primary key in Master and Foreign key in Detail.  I also checked the box to allow to create records based on this relationship in the detail table.  The idea is that the user should not be able to save a detail data record unless there is a corresponding record in the master table.  Is it possible to enforce this?  How to achieve this?  No worries about any complexities (as I am a coder).  I just need to learn the ways of FM.

      Thanks

        • 1. Re: Can I enforce referential integrity using relationships?
          philmodjunk

          You can use a validation rule that is invalid unless there is a matching parent record. This is only an issue for records created on a layout based directly on the detail table. In a portal to detail on the master layout, this will not be needed so I would only do this if my system design makes creating records on such a layout possible.

          You can also enable the "delete related records in this table..." option for the detail table so that deleting a master table also deletes all related detail tables. This option must be implemented with care, but it is necessary to help ensure that you do not have any "orphaned" detail records.

          • 2. Re: Can I enforce referential integrity using relationships?
            billcritten

            I think the terminology is getting me a little confused. 

            "In a portal to detail on the Master layout"  

            may I ask how I go about assembling/creating this portal to detail on the Master layout?  So far, I created a layout for the Master table and a layout for the detail table.  I am guessing that these layouts are independent of each other.  How would I go about creating the portal to the detail table (or is that to the detail layout) ?

            Thanks

            • 3. Re: Can I enforce referential integrity using relationships?
              philmodjunk

              It's to the detail table.

              If you have this relationship:

              Master::PrimaryKey = Detail::ForeignKey

              Then you can go to the Master layout (you can actually create many different layout sthat all refer to Master should you need to do so), enter layout mode and use the Portal Tool to draw a rectangle on your layout where you want your portal. A Portal Setup... dialog pops up and you can select Detail in the Show Records From Dialog. The other controls in this dialog let you specify different options that control the look and function of your portal. When you click OK, you will be shown a new dialog box where you can select the fields you want in portal. While you would normally select most portal fields from Detail, you can also select fields from other related table occurrences should you need to.

              When you dismiss this dialog, you'll see a portal on your layout in it's default "mini table" format. You can alter the appearence of this portal in a number of ways. you can specify a fill color and pattern, a border color and pattern. You can resize the portal rows--even rerrange things so it'smore like a miniature form instead of a table.

              If "table occurrence" is a new term, you might want to read this tutorial as a good understanding of what they are and how they are used is critical to getting the most out of your FileMaker solution: 

              Tutorial: What are Table Occurrences?

               

               

              • 4. Re: Can I enforce referential integrity using relationships?
                billcritten

                I just discovered the Portal tool.  I also re-discovered the help menu.  Got to used to using online resource - didn't even think about local resources.  I think I am getting the idea.  Again, many thanks for all the help.