8 Replies Latest reply on Mar 15, 2012 10:38 AM by philmodjunk

    Protecting Child / Parent Dependencies

    Vinny

      Title

      Protecting Child / Parent Dependencies

      Post

      I want to prevent the user from deleting a child record if a parent record exist.  What is the best way?

      For example, I have:

      Parts Table

      Part Types Table

       

      On my parts layout, I have a pop up which allows the user to select / define a Part Type (value list from the Part Types Table).

      On my Part Types table, I have "add" and "delete" buttons.  I don't want the user to be able to delete the Part Type if there is a Part entry that is using that Part Type.

      What is the best way to check (in script) if that Part Type has a parent and therefore stop the deletion?

      Also, I noticed that if I did delete the Part Type, the popup in the corresponding Parts entry just shows the old ID number from the deleted child record (I'm using ID / auto serial numbers for the relationship).

      Thanks in advance for your help.

        • 1. Re: Protecting Child / Parent Dependencies
          philmodjunk

          I noticed that if I did delete the Part Type, the popup in the corresponding Parts entry just shows the old ID number from the deleted child

          A PopUp menu formatted field using a value list that specifies hiding the first field will display the second field value from the related record. Once that record has been deleted, there's no name it can display and it then reverts to the actual data stored in the field, the ID number.

          From the Part Types layout, this if test will check for the presence of a parent record before permitting the delete:

          If [ IsEmpty ( Part::PartID ) ]
              Delete Record
          Else
             Show Custom Dialog ["At least one Part Record uses this part type record."]
          End If

          • 2. Re: Protecting Child / Parent Dependencies
            Vinny

            Yes, this would work in a simple direct relationship as in my example above, BUT if I am using join tables, how would I check if a parent exists?

             

            Parts Table

            Part ID

            Part Name

             

            Part Types Table

            Type ID

            Type Name

             

            Join Table

            FK_Part ID

            FK_Type ID

             

            This is one example, but I have other instances where there are join tables to other tables so that the (in this case Part types) table can be used in multiple places.  I need to develop a script that will check all dependencies to make sure it is not being used and if the delete is ok.

            An example would be a contacts table.  The contacts table may be linked via a join table to a quotes table or a sales order table or a meetings history table.  I wouldn't want the user to be able to delete the contact until we first check that the contact was not linked to any quotes, sales orders, or meetings.

            I don't mind having to check multiple tables via a script, but I'm looking for an efficient way to do it so I can make a generic script for checking dependencies.

            Thanks!

            • 3. Re: Protecting Child / Parent Dependencies
              philmodjunk

              You will need to analyze such "chains" of multiple table occurrence relationships, but the same test should work even with the join table. FileMaker can "tunnel" through the intervening tables tracing the relationship from current record in the layouts table to the related record (if it exists) in the specified table. There are two details that you have to watch out for:

              1) there must be an intact "chain" of related records as the expression traces through the table occurrences to the referenced table.

              2) if there is more than one related record, your calculation will refer to the "first" related record. In unsorted relationships, this will be the first record created. In sorted relationships, this will be the related record that sorts to the beginning of the set of related records.

              • 4. Re: Protecting Child / Parent Dependencies
                Vinny

                I agree, unless you have multiple table occurences.

                For example, if I am looking at a layout based on the Part Types table, but the relationship between Parts and Part Types is actually based on "Part Types 2" table occurence, I don't think it will work.

                I would have to be in a layout that's based on the table occurence which has the "tunnel" or relationship, right?  How else would I explicitly spell out the relationship to check with the "IsEmpty" test?

                • 5. Re: Protecting Child / Parent Dependencies
                  Vinny

                  I have one way of doing it, but I'm not sure it's the best.  In the Part Types Table, I have a calc field that counts the number of times that the Parts table has it's ID number in the foreign key field (FK_Type ID).

                  I think the key here is that I can evaluate it from the context of the other table occurence / relationship.

                  In the script, I supply it with a script parameter that tells the script the name of this field to check.  If this field is 0, then it is not being used.

                  Also, if there is more than 1 dependency, I can make more calculation fields, and also a calc field for the sum of them.  That way I can check one field to test all the scenarios.

                  My only concern is that I have little experience with calculation fields, and I'm hesitant to use them unless I have to.

                  • 6. Re: Protecting Child / Parent Dependencies
                    philmodjunk

                    For example, if I am looking at a layout...

                    Then you need to add an additional table occurrence and relationship so that it does work. This is pretty easy to do.

                    If you maintain proper referential integrity, you can also just use the same test but refer to the ID field in the join table. To insuer referential integrity, you can enable the delete options for the join table in the relationship so that the join record is automatically deleted each time a related part or part type record is deleted.

                    • 7. Re: Protecting Child / Parent Dependencies
                      Vinny

                      Ah...so I could add another parts table occurence....I like that Idea.  My relationships chart is beginning to look like a 1000 pc puzzle that has been abandoned....but I like avoiding the use of calculation fields.

                       

                      Thanks!

                      • 8. Re: Protecting Child / Parent Dependencies
                        philmodjunk

                        Please note that you can check the join table for the existance of a join record without using a calculation field to do so.

                        You might find this article helpful in orgainizing your relationships graph: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/