3 Replies Latest reply on May 21, 2014 6:23 AM by philmodjunk

    How to retrieve the right price from product table?

    MarieGuyon

      Title

      How to retrieve the right price from product table?

      Post

           Hi there,

           This is my first post. I am currently building an database to manage inventory. I have a a customer table and one of the field is customer category : retail or wholesale.

           In my product table, I have a retail selling price and a wholesale selling price.

           I am now creating a customer order. It is pulling data from both the customer and products tables. How can I get the right selling price based on the customer category (retail or wholesale)?

           I would like to use a lookup function so that if my prices change later on, my prior customer orders data won't change.

           Thanks for your help
           Lili

        • 1. Re: How to retrieve the right price from product table?
          philmodjunk

               The following answers assume that you have these tables and relationships. You'll need to substitute your table and field names for mine:

               Customer-----<Invoices----<LineItems>-------Products

               Option 1:

               Have both wholesale and retail unit price fields in the LineItems table. Give each "Looked up value" auto-enter options and set up your cost calculation field with an If function that computes with either unit price based on the value of the Customer::Category field:

               If ( Customer::Category = "Wholesale" ; Qty * WholeSaleUP ; Qty * RetailUP )

               Option 2:

               Define your cost field as a number field with an auto-entered calculation:

               If ( Customer::Category = "Wholesale" ; Qty * Products::WholeSaleUP ; Qty * ::Products::RetailUP )

          • 2. Re: How to retrieve the right price from product table?
            MarieGuyon

                 Thanks! Option 1 worked a treat and allows me to keep the historical price data. So even if the prices change in a few weeks, prior orders will keep reflecting the old price (which option 2 couldn't do or I probably implemented it incorrectly).

                 Thanks a lot for your help, greatly appreciated.

            • 3. Re: How to retrieve the right price from product table?
              philmodjunk

                    Option 2 uses an auto-enter calculation. The computed value will not change for existing invoices when you change prices in the future.

                   Auto-enter calculations do not update when you change the value of  a field from a related table. (But do make sure to clear the "do not replace existing value" option so that if you modify the Qty field, the cost will update.)