Find and Replace help
I have a two tables, Vendor and Product, linked to a join table named VendorJoinProduct. Both tables use a unique serial number ID rather than a vendor or product name as their unique identifier (that way, if a vendor changes their name or a product name, I can change the name and the link remains valid). In my VendorJoinProduct table, I am linked to VendorID in the Vendor table and ProductID in the Product table . Multple Products can be linked to a Vendor and the same Product may come from different Vendors. Also my population of Vendors and Products may be more than what may end up being linked in the join table; ie. I may have 100 Vendor names but not all of them may be included in my VendorJoinProduct table because they may not have any products linked to them yet.
I have another table, Inventory, that requires the use of a specific VendorJoinProduct ID and I want to use a list to select from the eligible VendorJoinProductIDs that represent the various Vendor and Product combinations. Since I am using serial number IDs at the Vendor and Product level, I cannot get a defined list to show the descriptive form of Vendor name and Product name. My attempted solution was to create a field in the VendorJoinProduct table that stored a calculation of [Vendor Name & ": " & ProductName] by which I can create a descriptive list in a drop down menu. My problem is if a Vendor name changes, I have to go into each instance in the VendorJoinProduct table that lists that Vendor so that the calculated field can update with the new name - a process that is a bit onerous. So I then thought the proper way to affect the Vendor name change to my calculated field was to do a find and replace script if the Vendor name was edited. However, I cannot figure out what that script should be since I will have written over an old Vendor Name and replaced it with a new Vendor name in the first instance; how do I capture the lookup of the old name to be replaced with the new name in all instances? What would that script look like?
The "better" solution, if possible, would be that in the Inventory layout, I could pull only a list of Vendors names, which have a defined VendorJoinProduct combo and then after selecting such Vendor name, a list of only the Product names linked to that Vendor in the VendorJoinProduct table. This would be a preferred solution if I could figure it out. Any ideas on the "better" solution?
Using FM 11 ProAdvanced
Many thanks for any ideas or help!