FileMaker has a cascade delete option, but where is the cascade update? I would be nice if changing the primary key value could update key values in related tables.
That would require a script trigger on every layout in the database that has the key field placed on it. It would also require a pretty intricate script to go to a layout for every related table and an update loop to update every record with that key. This seems like a lot of work to maintain. I may be wrong since I haven't used it in some time, but I do believe this was a feature of Microsoft Access 10 years ago.
not to throw water on this, but I'm pretty much into NOT allowing cascade anything (including delete). Script it all. I have had to "update" key fields (when importing/syncing, for example), but then I use a tempKey field to hold the relationship, Go To Related (passing new key via script) and setting the new key with the Script Parameter. for "Delete" I try to set a field ("markToDelete") and change relationships, if needed, rather than actually delete. If an admin needs to "purge" those "markToDelete" records (any file), there's a script for that and likely just an "archive" rather than delete.
Agree Beverly. Every record has a 'deleted' key field that gets set to 1 if a user 'deletes' that record, it is never deleted - just never shows up in searches and never used in any reports.
I have forgotten how many times I have had to 'undelete' a record because a user ignored the warning and cliked the 'Delete' button rather than the default 'VCancel' button!
psuchad wrote:I would be nice if changing the primary key value could update key values in related tables.
I would be nice if changing the primary key value could update key values in related tables.
I think it's pretty safe to say that if you need this, then something is wrong, with the possible exception of data migration - but migrations are rare and you wouldn't want regular features to be based upon migration requirements.
Yup, scary what could happen, and easily implemented if required
What is it you are trying to do? Changing the primary key for a record or the foreign key in a related record has the potential of breaking the relationship.
Ok that stated, I had a database that needed to import template record sets into a main set of tables. The relationship was essentially a hierarchy so all of the new records had to point to the correct parent record. In this setup, the records were exported and then imported. Primary and foreign keys were not included in the export. When the parent records were imported/created, the primary key was put into a variable. Then when the related records were imported the Foreign key was setup as an auto enter/do not replace field with the value being the variable holding the parent record foreign key. End result is the new record set had the correct keys and were correctly related.
This could be done using a script with variables, finds and replace field contents. As a script trigger it would probably have to be on the parent/top level record only.
We have a very large client services system that tracks across various service types and agencies. The database consists of about 15 files, all related to the main client master file. The client master file alone contains 155 related tables (granted they are not all related via the client ID). The issue that we have is when a person comes into the intake process they are not always initially found in the system due to various name spellings and nicknames used. If they are not found they are added as a new client. Weeks, months, sometimes years later we find that we have a duplicate person in the system. The current process is to manually merge the client ID across all related tables. This is more and more complex the further down the wormhole the client goes in the interconnected agencies and services.
I understand the underlying risk of overwriting keys and it is not something that is taken lightly. But in my opinion, I would rather have something that is setup on the relationship level rather than rely on a scripted solution to be maintained across the hundreds of tables.
You have a difficult setup. If possible, I would see if there is a way to do a more thourough search up front. I know you have already tried and they still sneak through...:(
In thinking about this, the best method I can come up with is a scripted appraoch. In psudo code:
Update Primary record, store both Old ID and New ID in variables.
Go to each table
Find all records with old key
Replace with new key
I would include a number of Do you really, really really want to do this steps.
Retrieving data ...