1 Reply Latest reply on Oct 26, 2014 9:29 AM by philmodjunk

    Manage banding pricelist



      Manage banding pricelist



      I'am a very novice in Filemaker (and my English is also poor).

      Can anyone help me, what is the right way to do the following:

      I would like to manage a pricelist in Filemaker which is "banding". It means for example
      1 piece of supersticker costs 10USD/pc,
      10 pieces of supersticker costs 9USD/pc,
      20 pieces of  supersticker costs 8USD/pc etc.

      Which calculation formula is able to find the right related price in related fields?

      I think I make a product table with fields: Product_ID and Product_name
      Than I make a table for prices with fields: product_id and quantity and price
      Than I connect (relate) the Product_ID with a product_id as a one to many relationship
      Than I would like to make a layout with a calculation, where I fill the product (drop down list) field and type in the quantity in the quantity field, than the right unit price automatically appear in the unit price field. 

      But I don't know how to make a layout, where the calculation (product quantity multiply with product price) where the correct price will appear automatically for the quantity.

      Thank you for your kind help.

      Best regards: Csaba

        • 1. Re: Manage banding pricelist

          Presumably, this is to record customer orders to purchase product from you or vendor purchase orders in order for you to purchase product from a vendor. Either way, you need more tables and relationships in order to document the fact that different orders will specify different quantities and thus look up different prices based on the Quantity.

          The standard data model (tables/relationships) when you don't have "banded prices", would look like this:


          Orders::__pkOrderID = OrderLineItems::_fkOrderID
          Products::__pkProductID = OrderLineItems::_fkProductID

          This allows you to put a portal to LineItems on an Orders layout where you select Products by ID in the _fkProductID field and info about that Product (including but not limited to pricing) is looked up (copied over) from Products.

          To support quantity based pricing, you can link in your Prices table like this


          OrderLineItems::_fkProductID = Pricing::_fkProductID AND
          OrderLineITems::QtyOrdered > Pricing::Qty

          In addition to specifying those match fields, you'd double click the relationship line and use the Relatiionship Details dialog to specfiy that your Pricing records would be sorted by Qty in descending order. That way, the price will be looked up from the pricing record with matching ID that has the Largest Qty that is still less than or equal to the amount ordered.

          If your orders are never for more than a single product at at time, you don't need the orders table, but you still need a table to fill the role of OrderLineItems.

          For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          Caulkins Consulting, Home of Adventures In FileMaking