Several comments come to mind:
- 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.
- 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).
- 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.
- 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.
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.
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.