5 Replies Latest reply on May 30, 2012 4:52 PM by philmodjunk

    discount script

    ChrisDurkee

      Title

      discount script

      Post

      I have a simple portal invoice line item set up. Each line is an item that then has details for each item. So there could be several lines that are the same item, but different details. 

      I am looking for a way for file maker to recognize multiple items and then discount them accordingly, for example if there are five lines with the same item (different details) then it will discount each of the items a dollar off. Then when there are 10, the 10th is free?

      Thanks for any help as I am pretty new to Filemaker and scripting.

        • 1. Re: discount script
          philmodjunk

          Much depends on the "rules" you define for discounting and whether you want to record the discount as only a total amount at the bottom of your invoice or by deducting it from the unit price of the individual items in your lineItems portal. I'll stick with just a discount total for the entire invoice for now as it's a little simpler to set up.

          I would assume you have these tables:

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

          You may be able to use fields in Products to document each type of applicable discount or you may want to establish a separate related table of discounts. The fields and data for documenting your example discounts might look like this:

          ProductID   MinQty    MaxQty Dtype Damount
          1234             5            9         1      1.00
          1234             10         9999     2

          And to get a count of total products for each ID for just the current invoice, make a new occurrence of LineItems and link it like this:

          LineItems::ProductID = LineItemsSameProductInvoice::ProductID AND
          LineItems::InvoiceID = LineItemsSameProductInvoice::InvoiceID

          You can define a calculation field, cInvoiceQty, as:

          Sum (LineItemsSameProductInvoice::Qty )

          To compute the total of each type of item ordered.

          You'd link your discounts table to LineItems like this

          LineItemsSameProductInvoice::ProductID = Discounts::ProductID AND
          LineItemsSameProductInvoice::cInvoiceQty < Discounts::MaxQty AND
          LineITemsSameProductINvoice::cInvoiceQty > Discounts::MinQty

          Then, once the line items are all selected in your portal to line items on your invoice layout, you can run a script that loops through the line items records and checks first for a related record in Discounts and then uses the Dtype and Damount fields to determine how to apply that invoice.

          • 2. Re: discount script
            ChrisDurkee

            Thanks. I think I understand most of it, but I'm pretty green, so i'll try and see what I understand.

            What is Dtype? and is the LineItemsSameProductInvoice the new occurence of LineItems or a new table all together?

            • 3. Re: discount script
              philmodjunk

              Dtype is just a field where you specify what kind of discount. Your example showed two types of discount one for a dollar off each item and one that deducted the cost of the 10th item. Thus, I gave it values of 1 and 2 as a way to distinguish what "rules" would need to be followed in order to apply the discount.

              LineItemsSameProductInvoice is indeed a new occurrence of LineItems.

              • 4. Re: discount script
                ChrisDurkee

                I'm not sure I understand where it gets a quantity for this line Sum (LineItemsSameProductInvoice::Qty )?


                • 5. Re: discount script
                  philmodjunk

                  This is the Quantity field defined in line items so that if someone purchases 3 identical socket wrenches, you can make a single entry in your line items portal with 3 in the quantity field. Since LineItemsSameProductInvoice is an occurrence of LineItems, it has every field defined in LineItems. By referring to LineItemsSameProductInvoice, the relationship matches to all line item entries for the same invoice and same product ID and the sum function totals up all the qty fields for those records. This way, whether you have 3 line items for "socket wrenches" with 1 in each qty field, a single line item with 3 in the qty field or 1 with 1 and the qty and a second with 2 in the qty field, you get the same total qty, 3 so that you can use it to determine what discount might apply to them.

                  Keep in mind that not all details of the looping script have been discussed yet. One issue is to keep from applying the same discount more than once should there be multiple line items in the invoice with the same productID.