I have set up the following arrangement:
What I want to be able to do, is for each facility, set up a price list for each price category which allows for a default standard price and price unit, and the ability to enter another price and price unit when the default is not wanted. The only way I can currently see to do this is to run a script that copies all of of the primary keys from the Price Categories table and enters them into new records in the Pricing table. Then the person runs through the various items and decides whether to accept the standard price, or change to a special price (same for price units). This doesn't allow however for modifications to take place in the Price Categories table which flow into the Pricing table. I am at a loss on how to do this. Can anyone suggest the proper way to go about this?