3 Replies Latest reply on Sep 6, 2012 12:26 PM by Stephen Huston

    Dealing with price change relationship


      In the past price changes have been pretty easy; new order new price... change in order new price based on the pricing table. I have been asked to create a method that will allow for older orders to be re-priced with the old logic while new orders could be done with new or older pricing. Since the price increase only changes a few items at a time I am thinking of building the pictured type of relationship to avoid having to make unnecessary records. The sort will be by versionID in descending order. My thought is this will ensure the item table is always able to find the most recent price for an object depending on the version of the pricing they intend to use. Is this sort costly at the relationship level? Have others faced a similar request? If so how did you deal with it? The itemPrice table only has around 3000 records in it at the moment.


        • 1. Re: Dealing with price change relationship
          Stephen Huston

          Several comments come to mind:

          1. Your image shows the sort on the ID rather than the versionID, as you specified, but that may just have been an oversight when doing the image capture.
          2. Sorted relationships do slow performance a bit on resolving a value, but that should not have a major impact if the value is being used for a lookup (as opposed to something like looping set-field processes).
          3. The larger you price lookup table becomes (more records), the more the sorted relationship may slow resolving the value. However, this is more likely to be significant if the number of versionID records for a given itemID grows dramatically rather than just based on the number of records in the tables.
          4. It's still not clear to me how the user will select which option to use for old v new prices, but I will assume you have that in hand.

          I have rarely had to offer users a choice of pricelists to use, but have, at times allowed them to apply discounts, but only if the discount does not price the item below some floor/minimum price, which is stored in the pricing table for each item. That option can be done with an auto-enter based on a calculation/case test to determine if their discounted price or the minimum price gets stored in the field. Auto-enter via calculation can give you more flxibility that a simple lookup value, but the related value can be part of the test evaluated in the calculated test/result.

          • 2. Re: Dealing with price change relationship

            Thank you for the thoughtful reply. Good to know that it will be doing the sort on its related set instead of the whole table. I had this vision of the whole table being sorted in my head before it got to the related set since you don't actually pick the point in the relationship to start the sort. I was basically thinking of a script that ran the sort before the find. As far as the user end it is something that will be set at the order level and then auto-entered as the items are created.

            • 3. Re: Dealing with price change relationship
              Stephen Huston

              The relationship and sorting is also done on the server side now, so there isn't really any load on the network traffic for this stuff until the server resolves and sorts it. Thank you FileMaker for that change in the newer file versions.