Since there's no such thing as cascade update of primary keys...
In reading previous posts I've discovered that I set up my database sloppily, and I'm trying to rectify that. In my Products table I set ProductID (the id that is used internally and on the website for our products) as the "primary key". I've built relationships in other tables based on this ProductID, but am encountering the problem that if a correction needs to be made to that field, I lose my relationships.
The problem I'm having in trying to fix this is that I don't want the user to ever see the auto-generated serial number "primary key." But I need them to be able to select the ProductID from a drop-down and have their selection relate the records in the Products table and the current table. I know I can set my dropdown to show values from a second field (in this case the ProductID) while actually using the serial number ID. When I do that though, the field displays the serial number (makes sense).
So, how can I "hide" the auto-generated serial numbers from the user so they only see data that makes sense to them (i.e. the productID)? I know there's a solution, I'm just not seeing it.
I have two options: if you are using FMP 13, you can hide the primary key field even as you use it as a drop down list. Or you can set up a drop down list of just the public ID but use this value to look up the primary key.
Both methods can be found in "Adventures in FileMaking #2 - Enhanced Value Selection"