I'm extending an existing system that previously stored address fields in a supplier record, so it was simple to create a purchase order table with lookup fields that looked up the directly related supplier table.
There was a request to allow multiple addresses per supplier/company so I created a company table separate to the supplier (and client) tables. Additionally I created an address table and join table between company and address.
I've resolved most of the issues but can't work out how to recreate the lookup functionality from the Purchase order table to the distantly related address table.
Do I have to make the purchase order address fields calculated fields rather than lookup as I can no longer directly match to the address fields in the supplier table.
Thanks for any suggestions/clarifications.