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 ) ]
Show Custom Dialog ["At least one Part Record uses this part type record."]
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?
Part Types Table
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.
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.
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?
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.
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.
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.
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/