Updating Numeric Fields in same table

[MikeC]

"Hello World". And that is about the level of proficiency I feel I am at many times! I have several problems that have eluded me for answers for the past few weeks but first here is the one that once figured out will keep me busy once again for a time. Any direction / examples will be appreciated. A sample database is attached for those inclined.

I have read that posting operations can be a bit picky but what I want at the moment may not even be that at all. Just a "simple" field update procedure is needed I think. I have 3 forms all based on the same table called "prod". The beginning form "Prod_Form_Tab" has an embedded browse listing all the products and associated information such as prices, quantities,etc.. By pushing the "change" button (with any product highlighted) it brings up the next form called "Product_Record_Change" which is used to change most of the information on the first form(Prod_Form_Tab). It is on this form and the next one that the help is needed.

On the form "Product_Record_Change" there are the following numeric fields that I wish to be updated by changing values in the the next form accessed via the "Change Inventory" button: Stock Total, Last Unit Cost, Average Unit Cost, and Inventory Value. By pushing the Inventory Change button the form "P_Inv_Adj" is brought up. It has 3 user-entry
fields in which I want to use for changing the values in the former 2 forms(which of course is actually changing the values in the prod table itself).

I have tried many ways to accomplish this to update the Stock Total field of the form "Product_Record_Change" form with the closest success at where I am at now---but when I change the value, say by 100, the Stock Total will first change by 200 when saving(closing the form P_Inv_Adj)....then if I go back into the P_Inv_Adj by pushing the Inventory Change button and close it again without changing any value it will add an additional 100. So in all it adds 3 times whatever I first enter in the P_Inv_Adj field. As far as I can tell I have not told Alpha to do this but obviously I have somewhere.
I used a calculated field expression in the prod table's field rules to make the Stock Total field update with the changing values. Basically it is---Stock_Total + Qty_Chg(quantity changed).

If the field rule is not what I should be using I just want to say that I have also tried substituting a calculated field for the Stock Total field with no success as well as attempting to write some script to no avail(not saying either of which may be just what I need but I couldn't even get close using them!).

If anyone could just point me toward a direction to accomplish this at least I would know I wasn't wasting my time on something that shouldn't be considered.

Mike

[Tom Cone Jr]

Mike, could not download your attachment. Will try again later. -- tom

[MikeC]

Here it is again.

[Tom Cone Jr]

Mike, I don't have time to unravel this today, or even tomorrow. I did notice a couple of things that may be contributing to the problem.

1) several of your scripts use the action script "clear all fields" in forms based on your table. For example, the script that opens your called form does this as a matter of course. This is hardly ever wise. Since each field in your called form is bound to the underlying table, when the script "clears" any given field it's actually emptying the corresponding field in the record buffer for your table. This places the table in change mode. And empties the fields in the current record buffer that are displayed on form. It's possible you need this, but to me because this happens automatically and without warning to the user, it can be very misleading. The "empty" fields in your form will present to the user the same interface they would see if entering a "new" record, when in fact, the current record is being changed. Consider what would happen if you clear the fields, and the user enters nothing in the called form, but then chooses OK to save and close the form. I'd be very worried this would empty the table of meaningful data.

2) some of your close form scripts are set to automatically save changes to disk if any are pending when the form is closed. It may help you unscramble the complete sequence if you arrange to force the user to save or cancel pending edits before closing any of your forms.

-- tom

[Tom Cone Jr]

Mike,

your Inv_value field in the Prod table is set to a calculated data entry style in field rules, but the calc field expression is blank. Don't know if this is contributing to your problems, but thought I'd mention it. This is wrong as it currently sits. This will affect other fields, since since field is used in other calc expressions. -- tom

[Tom Cone Jr]

Mike,

I've looked at the database several times today, and still cannot figure how the pieces fit together or what it is you're trying to accomplish. Sorry.

If you want to produce a simpler example that illustrates the problem, I'll be glad to look again. Otherwise, it's going to take considerable time to figure out how your forms and field rules work (or should work) together. If you want to put together a complete, detailed, description telling us what the fields mean, how they're used, what the field rules are supposed to do, and what the forms and their buttons are supposed to do, I'll take another look. Otherwise, any specific recommendation I might make could have drastic repercussions elsewhere in the app.

Here's a for instance that illustrates my difficulty.

You have a "PROD" table. I don't know if it's used for transactions or is intended to represent the current status (snapshot) for each product. These are two entirely different things. In many apps the initial quantity is entered by hand, and then the qty on hand is reduced as sales occur, and increased as restockings occur. The app tracks the qty sold or restocked. The current qty on hand is computed. Your forms seem designed to permit / require the user to enter a new qty on hand figure manually. A very differrent thing. Furthermore your sample data tables are incomplete and full of empty fields and records. Makes following the trail of your design more difficult.

In short, would like to help, but can't without a lot more background information from you.

-- tom

[MikeC]

Hi Tom and thanks for checking into this. I can now see that I do need to set this up a little different as the way I have it now it wasn't designed to create any transaction and really it should. I was incorrectly thinking that the values in the table that would be continually changed made no difference to me and so could just blank them out.
I guess I should actually be using a "Enter New Record" into a transaction table which may require the form to be based upon a set--have to think on that one a while as I had created such a form once but could not get the product name to appear on the form.
As far as using the action script "close form" on the OK button using the option of saving if not done previously, I was simply modeling my application after the one I am currently using---and just checked it out and my current software does save a Non-Transaction! Not that it causes any harm but what a waste of space! I would think that a simple CanSaveRecord script could be added that would Cancel() the button push scripts which would force them to use the cancel button (as should've been done) if a adjustment was not made(with a UI telling them why of course). I would prefer this method overall as the less a user has to do in a program regarding keyboarding or mouse clicks the better. This is true especially since I will be the first user using this program I want to expedite everything as any time savings can be used to create more things! You may snicker at this because it is only "one" more action involved but the way my current software is set up it is very frustrating having to click on an extra (and unneeded) button especially when it takes my cursor away from where I was working on a form or browse to enter data in.

So, I guess the way I have it set up currently has to be changed.
Thanks again for your time on this Tom--I got to thinking on this so much that things just got a bit blurry in how it should work and just your comments about a few things has helped me see a few of my errors--so definitely not a waste of your time! It cleared up a few things for me in how I should be designing this application-even though I thought I had it clear in my head of how it should be, obviously I wasn't seeing the entire picture.

I still am uncertain in how to create my set that would include my transaction table though (have created every possible set and had issues with each one!). I will create a new thread to ask about this.

Mike

Note--I thought I gave plenty of information about the problem but your last post made it obvious that I omitted some very important information - My next question WILL be better explained! :-)

[Tom Cone Jr]

Mike, as I age I seem to become less intuitive. I don't know. Maybe it's a streak I'm going through like a "hitter's slump" in baseball.

In your redesign I'd recommend you steer clear of giving the user direct access to edit the "on hand" quantity figure for any given product. Load it initially, then force them to use a transaction table to record increases and decreases in the quantity for each item. Type transaction "types" or "categories" might be "sale", "damaged", "purchase", "adjust". I'd envision the "adjust" to require an explanation. Say for instance if a physical inventory shows 12 widgets on hand, but the table says you should have 14 you could enter a transaction record reducing the qty by 2, "adjust" as the category, and the explanation might be unexplained shrinkage. The advantage of doing it this way is you can reconstruct the transaction history for each product. You lose this if you permit direct edits to the PROD table by the user.

Food for thought. Not gospel. Hope it helps.

-- tom

[MikeC]

Thanks Tom---I have been doing quite a bit of thinking and writing since your last post and came up with pretty much what you just said. I need to start a new thread though to come up with a narrowed down plan of attack on how to set up the set(s) for the transaction, product, client tables. All I need to do is straighten out a few tables and forms now so that if someone needs to look at my database(the whole thing this time!) they can.

Mike

---as far as your intuition....I have seen many threads in which you say you don't understand and yet almost always seem to help people! Maybe you are just at the point where your concious mind doesn't even need to understand !! :-)