Delete Parent FileMaker Record Deletes Related Child Records?

From Dwayne Wright PMP - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

A READER ASKS
Two tables connected with the auto delete related records setup, what happens if the parent table is deleted from the graph entirely? Are the records that were related to the parent table deleted by the action of the table being removed. What about if just the table occurrence itself is removed by not the table?

-------
DWAYNE RESPONDS
When you define a relationship, you can define an option to delete related records when the parent record is deleted. This is a great way to cleanup orphaned related records when the parent record is deleted. It is a nice feature but I do ask that you consider the consequences of this feature about before implementing.

I haven’t run across a delete parent table with the associate related delete option enabled in the “real world” but your question is an excellent example of some questions that you should consider before implementing this setting. In my research of FileMaker blogs and books, I didn’t find this particular question addressed. So I decided to build an example file, set it up with the exact settings you described and give it a go. In the movie linked below, you will see that my tests seem to indicate that related records ARE NOT deleted if the parent table is deleted.

---

There is a related movie on this topic! CLICK HERE!

---

ABOUT CASCADING DELETES
Cascading Deletes is a term that is associated with the method of deleting all related records when a parent record is deleted in the parent table.

In the bottom third of the relationship setup dialog, you can see options to handle the relationship for the tables on either side of it. You can set it up so that you can add related records on the fly through the relationship. You can also automatically delete related records when a parent record is deleted. This is helpful because it helps eliminate orphan related records. However, all automatic deleting options should be carefully thought out before you implementation. You can even sort the related records for portals or which related record is shown for related fields on a layout.

CAUTION: It is possible to have an out of control cascading delete in this situation. It is possible that one deleted record can trigger multiple related record deletions until you have a snow ball effect. Be sure this setting is well planned out and tested before implementation.

Also your security account privilege setting can cause the auto related delete setting to quit working. If you do not allow a user to delete records in the related table occurrence, the cascading delete will not work. So you can have an option of allowing the user to delete records in that file and perhaps take away the menu option that makes the Delete Record option available. Another option is to build a maintenance script that cleans up orphaned records attached to another script or an after business hours timed script.

More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2009 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

A READER ASKS: Security And The Relationship Graph

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

CHAPTER 09: The Separation Model

A READER ASKS
We have multiple files in our FileMaker solution. We have instances where a user has full access in one file and does not have full user access in the second file. In regards to our mixed access users, looking at the relationship graph for the file they have full access to ... what does the relationship graph look like?

-------
DWAYNE RESPONDS
I have to admit that I don’t have a lot of solutions that use this type of configuration. I decided to try a “real world” test on this and my results seem to indicate ...

the user doesn’t seem to have any relationship graph restrictions in a mixed setting mentioned above. Check out the movie below and feel free to share any experiences you may have.

There is a related movie on this topic! CLICK HERE!

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2009 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

ADVERTISEMENT ==================
Help support this blog by considering a donating to its ongoing growth. For more details, please visit http://www.dwaynewright.com/donate.html

FILEMAKER: The Separation Model And Run Script With Full Access

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

CHAPTER 09: The Separation Model

This posting was inspired by a series of posts on the FileMaker Tech Talk forum. If you are serious in honing your FileMaker development skills, please consider joining the FileMaker Technical Network and enjoy a wide array of support options like the Tech Talk forum, member only web site access, free software, exclusive learning materials and more. Membership runs just $99.00 a year and I couldn't recommend it enough.

--------
In a separation module situation, the user interface layouts reside in one file, the actual data resides in another file and external data sources allow the two files to work in unison (with some exceptions). One exception is the ability to run a script with the full access setting due to its dependent nature on where the script resides.

QUICK LINKS TO RELATED ARTICLES
The Separation Model
External Data Source
Manage External Data Sources
Get(PrivilegeSetName) And Scripts With Full Access
The Get(PrivilegeSetName) Function
Substantive Privileges Explored
How Scripts Are Called
FileMaker 9 Server Only Runs Web Compatible Scripts
Robots

To recap, the "Run script with full access privileges" checkbox on a script is dependent upon actions within the file it resides. Executing a script to delete records within the interface file with "Run script with full access privileges" activated will not override the lack of deletion privileges within the data file for that user.

OPTION ONE
Run the delete script within the data file and have its "Run script with full access privileges" checkbox selected. The script in the interface file calls upon the data file script and then returns. Depending on the number of records affected (shown within the interface file), the user might see what is going on. Somewhat akin to when Toto pulls back the curtain on the Wizard Of Oz.

OPTION TWO
Add the ability for the user to delete records in the data file but add conditions that can only occur within a scripted process. This is done by linking the ability to delete to a TRUE calculated result. (add link where I discuss this). The most elegant way to do this is to link the calculation to a global value set by the script, either a global field that can be seen in the data file or setting a global variable within the data file.

REMEMBER: Global variables ... like the "Run script with full access privileges" setting ... are file dependent.

OPTION THREE
There are a collection of options here that can get pretty messy. One is to temporarily re-login the user with a delete privilege in the data file for the duration of the script. Another option is to NOT delete the script but flag them for deletion. Later on, a user with delete privileges or a batch script (run by FileMaker Server or a robot) actually deletes the record.

ALL METHODS BREAK THE SEPARATION MODEL MYTH
Adding in of the listed options (and likely those that I didn't list) will break the myth that separation model implementations are seamless upgrades. That whatever the coding update to a solution, all you need to do is pop in a new interface file and you are good to go.

In some cases, the developer can go onsite (even virtually) and add the needed schema changes to the LIVE data file. This can be done outside of traditional business hours, after a proper backup has been done. The "bag of hurt" method can be adding the schema choices to an empty data file and the reimporting all the data records again. Both methods have some level of upgrade risk involved and those risks should be measured properly before executing.
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2009 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

ADVERTISEMENT ==================
MacUSA provides website and FileMaker Pro hosting services. Utilizing the highest performance web and FileMaker servers. We make FileMaker database hosting as simple as possible. Mac USA Technical Support has been voted the best by our customers. FileMaker Server hosting for your FileMaker web hosting needs supporting FileMaker 10 down through version 5. Hosting FileMaker Pro databases for Instant Web Publishing and Custom Web Publishing including PHP, XML, ODBC, and MySQL access. Use Promo Code dw0904TB for FREE setup (a $25 value) when signing up for your FileMaker Hosting services. Offer expires 6/30/2009. http://macusa.net

A READER ASKS: FileMaker Relationship Graph Documentation

From Dwayne Wright - Certified FileMaker 9 Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

A READER ASKS

I love your blog series I’m learning a lot from it. I know you normally charge for advice but I have one quick question I’m hoping you can help with.

I am trying to setup and anchor/buoy system and looked at your great graph with yellow headers for groups.

I know how to do all of it except get those headers. Are they simply hollow occurrences? I’m running 9adv and 10 adv.

Thanks very much for you feedback.

-------
DWAYNE RESPONDS
Not a problem, they are not table occurrences but it is funny you would say that. These are relationship graph notes and they work a lot like the sticky notes you use in an office. They are created with the comment tool and are the (rather lame) internal way to document your relationship graph.

Here you can see the tool for creating the notes.

There is a related movie on this topic! CLICK HERE!

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2009 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

ADVERTISEMENT ==================
Help support this blog by considering a donating to its ongoing growth. For more details, please visit http://www.dwaynewright.com/donate.html

EXAMPLE: Cartesian Join, Minus One

From Dwayne Wright PMP - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

There are times you may want to have a table related to itself and this is commonly called a self join relationship. Now FileMaker will not allow you to join two table occurrences together twice and that is essentially what you are doing with a self relationship. FileMaker will require you to create a new table occurrence. So a self relationship would probably look something like ... clients ( table occurrence ) linked to clients1 ( table occurrence ). Both table occurrences would reference the same table but you would see them differently any time you are viewing table occurrences for fields, layouts, scripts, value lists and the like. 

STEP 1: SETUP THE CARTESIAN JOIN
The Cartesian Product describes a relationship where any record in one table occurrence will match all the records in another table occurrence. The relationship operator that you would use for this is the X operator.

Here you can see the Cartesian join in use.

STEP 2: PREPARE FOR MULTIPLE COMPARISONS
Now you will likely want to omit the current record that you are on from the Cartesian portal you are looking at. This can be done by using a multiple predicate relationship. FileMaker 7 and higher allows you to create more than one relationship comparison layer, this is somewhat akin to the multiple available layers in the sort dialog box. The setup for a multiple layer comparison relationship starts off just like a regular FileMaker relationship. You navigate to the relationship tab in the define database dialog box. You draw a relationship between two table occurrences and then open up the relationship setup box.

Along the right side of your relationship box and below the right hand table occurrence field pick list, there is a button with the label of "Add" and sits along side a button labeled "Change." These new buttons are linked to a box below the buttons that lists the levels of valid relationship comparisons in order of first listed is compared first and then on down the line. Normally, you will only see the one comparison line but that may change as you grow to appreciate what a multiple layer comparison can do for you.

Here you can see we have it setup to add our second comparison layer.

STEP 3: ADD THE DOES NOT EQUAL TO JOIN ≠ ( not equal )
This could be used to flip the results on an equal to relationship. Before FileMaker 7, if you wanted to see a not equal relationship, you would have to create additional key fields that were the calculated opposite of the equal to relationship ( so that “yes” data strings would read as “no”). No need for those redundant fields these days, now you can use the exact same relationship setup but use the not equal operator to flip the results.

So we want to say if the name does not match the current record (or better yet the primary and foreign key fields do not match). I’ll use the name field here just for illustration purposes.

Here is what our relationship looks like in the final version.

An example file can be downloaded at ...
http://www.dwaynewright.com/blogfiles09/CartesianMinus1.zip

More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2009 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.