4 Replies Latest reply on Aug 29, 2012 9:50 AM by willrollo

    Discounted price calculation

      Title

      Discounted price calculation

      Post

      Hi

      I have set up my invoicing database fine. My products table has a trade discount field, which has the data "1" if that product record can have a discount applied to it. Each customer has a different percentage discount which originates on the cutomer table - customers::discount. I have a look up field on the invoice table for this invoicedetails::discount. And for the products line tabel, invoice items, I have the field 'discountable' which uses a look up to the trade discount field mentioned earlier.

      How do I apply the discounts to the prices? I had done the calculation using an

       

       Case(
        Discountable = 1 ;

       invoicedetails_InvoiceItem_PRODUCTS::Sales_Price - ( invoicedetails_InvoiceItem_PRODUCTS::Sales_Price

       * INVOICE DETAILS::Discount Percentage);

        invoicedetails_InvoiceItem_PRODUCTS::Sales_Price)

       

      This is supposed to calculate the product price as the usual price unless it is discounted in which case it applies the customer's specific discount to it.

      But I am unsure it this is the best way about it. I also want to show the what the total discount is on an order which would mean having seperate fields for discounted and normal price totals So I can subtract one from the other..?

       

      on the actual order/invoice page layout, there is the field showing the looked up field, INVOICE DETAILS::Discount Percentage. If this is adjusted, I want the lines in the portal below to reflect this - is this possible or do i have to delete and re-enter the portal line records (invoice items)

       

      Many thanks

        • 1. Re: Discounted price calculation
          philmodjunk

          How does: INVOICE DETAILS::Discount Percentage get a value?

          • 2. Re: Discounted price calculation

            It is an auto enter calc  of

            invoicedetails_CUSTOMERS__billing::Discount

            • 3. Re: Discounted price calculation
              philmodjunk

              Nevermind, I think I am confusing table occurrences here.

              What do you call the table where you list your invoice items? Is it LineItems?

              If so, LineItems should have a "Sales Price" or "unit Price" that looks up (copies) the current price from Products. This is so a future price change doesn't cause older invoices to recalculate, but instead still shows the dollar amounts for the price that was in place when the invoice was first processed.

              Then you can set up a "DiscountAmount" field in LineItems like this:

              If ( Discountable ; SalesPrice * Qty * INVOICE DETAILS::Discount Percentage )

              This field will be empty if the line items is not discountable and 0 if the discount percentage field is zero or empty.

              Sum ( LineItems::DsicountAmount )

              will show the total discounted value for a given invoice if defined in Invoice Details.

              And your lineItemcost calculation would be:

              SalesPrice * Qty - DiscountAmount

              • 4. Re: Discounted price calculation

                The "lines' join table is called invoice items. It does look up the prices from the products table fine as I may want to change these mid order and not alrter all future orders..

                I see how your calc works..

                So I can just have my normal unit price field showing the price if discounted or the original price if not. And also the new discount amount which will be used to work out the totals or the invoice by returning a 0 if no discount etc etc...

                Think you have answered my questions...

                Thank you