Synchronize FM record deletions with MySQL

[Crashin]

As it stands right now, I'm able to synchronize both the creation of new records and the modification of existing records within FM to the MySQL databases on our web server. Thus, when customers come to our website they will be able to view their account information. Where I'm hung up is when records are deleted from FM. I cannot come up with a method to delete the correlating record from MySQL.

I'm using PDM's SQL plugin, if that helps.

One thought I had was to create a table in MySQL that would hold only the p-keys for the FM customer records. I would empty that table nightly, and then re-populate with the existing p-keys. I could then compare the existing customer records in the MySQL customer table with those in the MySQL p-key table, and delete those out of the customer table that didn't have a correlating ID in the p-key table. This solution, however, is very cumbersome and time consuming. Our customer table in FM has nearly 23,000 records, so looping through them every night to update the p-key table would take a lot of time. Not to mention, we have other databases we will need to use this same solution on.

So, a way to delete the MySQL record at the same time as the FM deletion would be best. Any ideas? Thanks!

[_ian]

If you only allow the deletion of Filemaker records through a script you should then be able to issue the SQL command to delete from MySQL through the SQL plug-in at that time.

Ian

[Crashin]

Hi Ian...thanks for the reply.

I've considered that, and I think it would be a great solution. Do you happen to know of a way to restrict the deletion of records to using a script? I don't know of a way to prevent users from using keyboard shortcuts or menus, short of having to buy another plug-in (we've already bought two for this project).

[Anatoli]

Another way can be not deleting the records at all.

Just flag them in one field with entry "Deleted".

Then you will get that into MySQL as well.

All web or local searches will have not "deleted" parameter.

Then you can use your web engine (Lasso or php) to delete records from MySQL and simple find script in FM to find "Deleted" records and Erase them in block.

HTH

[Crashin]

That's another great idea! But, I'm still concerned about how to keep the end user from deleting records. Is there a way to restrict the DB so that records cannot be deleted?

[_ian]

You can edit the access privileges for the file to alter what menu commands are available to the users. It sounds as though users currently have all menus available. If you select Editing Only or None from the Menu Commands drop down list in Access Privileges:Passwords you will restrict the available commands. You can then either delete or flag the records through a script. Users will not be able to delete records through the normal FileMaker commands.

hth

Ian

[Crashin]

Thanks, Ian! I think that will do the trick!