Complicated relationships, main record to phonebook file, advice needed
We have a phonebook file which is related to the main record in several ways. Each main record has several fields which are specific to outside service providers. There are items in the phonebook which are related to the main record as simple businesses, hospitals, charitable donations, newspapers, florists and so on. So the main record has quite a few relationships to the phonebook each of which is determined by the business type.
The selection of a related businesses is done by business name, through a field with a pop up list in the main file which is specific to each business type. Example, Main::newspaper, Main::hospital etc. each is related to Phonebook::businessname. We have a problem in that the names of some of the organizations we deal with change. Any name change requires the manual change of the names in the main file and then the name change in the phonebook to keep the relationships. Users tend to change the Phonebook::businessname which leads to broken connections.
I am planning to shift to a serial number based relationship main to phonebook rather than a business name relationship.
First thought is that this will require doubling the number of relationships for each business type:
first step is selection of business name giving the name > name relationship
when selected this triggers a lookup of the serial number, then allowing the serial number > serial number relationship which provides the rest of the related data such as address, phone and other details from the phonebook.
I need advice on accomplishing: select by name but relate by serial number, if possible, without creating another 15 or so relationships in an already very busy set of files.
Thank you for looking at this.