2 Replies Latest reply on Dec 3, 2015 7:44 AM by stevaroni

    Stumped

    stevaroni

      I have set up the following arrangement:

       

      Facility---<Pricing>---Price Categories---<Item

       

      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?

        • 1. Re: Stumped
          coherentkris

          seems to me that unit price is an attribute of item. Is this a purchasing system? an inventory control system?

          Whats the relationship between facility and item? what is a price category? I dont think their is enough information about what your trying to accomplish.

          • 2. Re: Stumped
            stevaroni

            Sorry for the inadequate description. This part of the database keeps track of rental items rented to each facility. The capability I am trying to add is for the database to also keep track of rental prices for each item which can be customized for each facility. So I created the "Price Categories" table to keep a list of each price category and its associated rental price and price unit (hourly, daily, weekly, monthly). Sales Reps have the option however to deviate from normal pricing. For instance, they can take something that is normally billed at $20 per week, and instead bill for $90 per month. When they set up pricing for a new facility, I don't want them to have to enter prices for each rental item. I want them them however to be able to override the normal. I figured I could do that with lookups.

             

            In terms of how to set up the records for each new facility, I could set up a script that copies all of the "Price Category" table primary keys into new records in a foreign key field. Then a lookup could copy over standard price and price unit info. However this will get messy when changes are made to the "Price Category" table which needs to be dynamic. I cannot figure out how to set this all up so changes made in the "Price Category" table automatically make changes to the "Pricing" table. When this is all set up, the result will be that when a new record is made in the rental table, the pricing information is pulled out of the "Pricing'" table due to the relationships of the "Item" table and the "Facility" table.