perhaps the question I should be asking first is "does Sesame (I don't think so) or will Sesame3 have finer 'field locking' capabilities ?

What confused me was:  I have played around with a testing copy of my app, where I have commented out @XResultSetLocked;  I have an inventory record form open on my screen, and then I additionally open a PO and 'place/post' that inventory item number line item in the Purchase order screen.  Then I close the still open Inventory item form/screen.  Then if I go back into that inventory record, the cost has, in fact, been updated (XResultSetValue posted the new cost data to the underlying inventory database fields).   

But if instead of me having both the inventory screen and the purchase order screen open on my computer, a different user/workstation has the inventory record open on their screen, and I duplicate the test by creating a purchase order on my workstation and 'place/post' the inventory item, in that instance XResultSetValue does NOT post the data to the Inventory underlying database fields.

Hi Rick,

We only keep about a week's worth of inventory (50 turns a years), and might sell 50 different distinct item numbers a day to 50 different customers.  So we're basically purchasing each day what we're selling each day (toner, paper).  Each product item num will have a price record for multiple clients (some item nums we might have 100 clients buying, and some item nums might have 2 or 3).  If a specific item has 50 customers buying it, there will be 50 price records (the price record is identified by a combo of the client id and the product item num) and each price record might be a different price (based upon customer factors like size, qty purchased, timeliness of payment, etc).  Example:  an item costs us $60, and prices for that item might range from $68 to $100.  If we're only selling 3 or 4 of that item a week, I'm only going to stock 4 or 5 of them a week.  So we're quite sensitive to cost changes on that item.  If in the course of a year my cost goes up incrementally from $60 to $73, then the quicker I can adjust our price records for that item, the better I can maintain/increase my profit on that item num.   And that's just one item - we face that scenario dozens of times a day.

So before Sesame (we were using Q&A) when we placed the PO to replenish a few days' quantity of that item, and the cost changed from $60 to $63, then we had to manually go into the Q&A price records and figure new pricing for those 50 different customers - based upon each customer's unique margin for that product.  Multiple that problem by a couple dozen each day.   Very tedious.  You may perhaps guess that in many many instances those price records were not being changed/updated to reflect the new cost reality, and you'd be correct.  So a year and a half later, my cost might be $69 instead of $60, but many of those price records have not be changed upwards, and thus the ever increasing downward pressure on margins.

But with Sesame, that cost increase on a PO triggers automatically an immediate update of all price records for that item num, calculated upon each price record's unique margin.  No human has to 'keep track of cost changes and then edit price records', so I get the instant price changes I need, and it doesn't cost me expensive human labor that I can't afford.  In the 2.5 years since I built this application suite (on Sesame - yea!) of Sales Order, Invoicing, Purchase Order, Quote, PricingRecords, Client, Vendor, Delivery/Shipping Logistics, Sales Analysis, we have increased our margins 7.5 full points, which is huge, and is the reason in this lousy economy why we haven't had to lay anyone off.  And Sesame has reduced our data entry keystrokes by 60% at the same time.

So, to answer your question, the costs rarely change greatly, but they change frequently, and there are so many products that the collective impact of not adapting a sale to that individual instance of a, say, 4% increase in cost is, over time, a big deal.   As to the customer mindset, if they order a particular item 8 times in a year, the price might be different by a little bit 5 of those times.  But if they're ordering 25 different products from us in that year, some percentage of those items will have a decreasing cost (and therefore a decreasing price), so it's a constant flux of prices going up and down (like the stock market).  the changes upward are usually small, and Sesame always points out to the customer when a price has gone down from 3 months ago, so the customers are happy to know that Sesame is reflecting the cost trends of the industry both up and down.

In my experience, traditional accounting systems work fine in models where you buy a 6 week supply of something, sell it off, then buy another 6 week supply of something, then sell it off, and you know for those 6 weeks what your cost is on that item.  But we're only carrying a few days (or on some items, one sale's worth of inventory; or on some items we're not carrying ANY inventory, but only ordering the product when we've got a sale for it) and we generate invoices automatically every day from the previous day's sales orders (yea!), which increases our cash flow by about 3 days worth (yea!), and accounting systems that must wait for vendor invoices, and completed delivery tickets to be turned into invoices, for the accounting system to calculate/communicate profitability are too slow afoot.

Couldn't think of how to make that a shorter explanation - apologies!   But the short of it is - Sesame has allowed us to dig holes using a backhoe instead of a shovel, so we can dig, with no increase in personnel, more holes per day that are bigger and deeper.

I'm just interested in programming technique that would allow the 'can't execute this event at this time' to be accomplished, later, programmatically instead of manually (the phrase that is in my head - though not exactly on point - is 'rollback transaction', or 'third normal'). 

Can the price updates wait one day? Maybe you shouldn't be posting changes live, but at the end of the day, after everyone else is out of Sesame.

Or, maybe you shouldn't email yourself the ItemNumber of the locked records; but instead, store those ItemNumbers in a specially purposed database (or Global Value, Text file, etc). Then, you could run through them as a batch at a later time. Again, checking for locked records, and keeping failed updates in this list for another attempt the next time you run this batch update.

Oh, that's some good food for thought - thank you.  I'll think through how we're doing stuff and what the ramifications/tradeoffs might be (time spent to redesign/re-code versus benefits anticipated).   The 'thwarted because of a locked record' is a rare occurrence, I think (I actually just recently included the 'email myself a note when a lockout occurs). 

The '55 year old business owner with lots of hats to wear' part of me thinks that for the infrequency of the occurrence, the extra programming/brain drain might not be worth the gain.     BUT, the anal-retentive (is there supposed to be a hyphen in there?) part of me lusts to program it the right way - 'the way Hammer would do it!'

"Can the price updates wait one day?"
         Well, we place most of our PO's around 4pm (though there are exceptions).  In the instances where there is a cost change, one of the things that Sesame will do is search through all sales orders created previously that day (and since 4pm the day before) to find any instances of that item number being sold.  Sesame will then calculate what the new price for that customer should be, based upon the new cost, and then email one of us that info so we can intercept and edit that printed sales order and reprint it before we deliver the product (some goodly portion of our orders that are created in the afternoon will be delivered the following day, and many times in the a.m.).  This ends up gaining us an extra few hundred$$ a month in realized profit.  If we wait a day to update pricing, some of those orders might already be delivered, and the lower pricing on that order cemented.   And several other clients might order that product after the purchase order is executed, meaning that their pricing (if the updates wait a day) won't be updated to reflect the new cost reality.  After so many years of manual updating drudgery and tardiness and missed revenues, I really love the 'live update' feature - it's a Marlboro moment for me everything I think about it (although I'm not a smoker, it is an image that communicates, eh?).

Generating a batch or a database that would accumulate these 'lockout instances' and then run them through when Sesame was idle .... that's certainly a good idea to consider.  I'm a little boggled thinking about the complexity (or at least the richness/depth of data required to be captured) in order to recreate the foiled part of the transaction posting.   But that's certainly something for me to percolate on, while I'm watching over the next few whenevers (days, weeks, months) to get a feel for how often these foiled attempts are actually happening.

I don't see a problem with the length of the reply.  A 5% change in cost over a fairly short period ($60 => 63) is certainly a legitimate reason to not use "average".

I just wanted to point out that sometimes a programming approach is most appropriate and sometimes a change in business process is better.  In many businesses our thought processes become so compartmentalized that we only think in terms of "how can I get the technology to do what this person is asking?" when it might be more effective to change the process--which might require getting some other department involved.