3 Replies Latest reply on Jan 18, 2014 7:59 AM by duncanbaker

    Customer specific price list

    duncanbaker

      It might just be too early on the weekend for my brain to have kicked in, so wanted to check on something. It's nothing too complex:

       

      Customers table:

      Name

      Discount (% off the standard prices)

      Surcharge (an additional cost per item, if they are located far away)

       

      Products table:

      Name

      Standard price

      Customer price (standard price less discount + surcharge)

       

      Looking at the Customer layout, I want a portal showing products with that customer's prices. I have something working utilizing a global field in the product table that is set to the customer ID, which is used in the relationship. But, I think I'll need to set this via a script all the time. Wondering if there's a smarter way of achieving this?

       

      Thanks

        • 1. Re: Customer specific price list
          Mike Duncan

          Sounds like you might consider a join table to hold the values to relate customer to products to display items. That way the value can also be stored, and not set in a global, which would also work across records for reporting and in list views.

          • 2. Re: Customer specific price list
            erolst

            duncanbaker wrote:

            Wondering if there's a smarter way of achieving this?

            Unless you have the need to see several customers simultaneously with their correct customized prices, I'd say ‘not really’; you need to let the calculation in Products know which Customer's values to apply, and since there is no inherent relationship between a customer and a product, you have to do that on an ad-hoc basis by setting your global field. Use an OnRecordLoad trigger to automate the “process” (well, it's just a Set Field [] one-liner …).

             

            You could of course create a join-table of customer-product combinations, but unless you have the need to … (see above), I consider this not really necessary. You'd also have to maintain it – for new customers, new products, change of price, change of customer settings …!).

             

            When you create line items, you insert and store the adjusted price (and you could also store the applied values for discount), so your reports will show the correct figures as of the order date. I consider that all the join table necessary …

             

            Mike –

             

            I think you misread the description: it's not the adjusted prices that are in a global, but the customer ID that sets a relationship to get the current customer's settings for the adjusted price calculation.

            • 3. Re: Customer specific price list
              duncanbaker

              Thanks for taking the time guys. I'll go with setting the global with script triggers then.