Calculated Form Fields, values persist

[MikeC]

Hi,

Believe me, I really do try and not abuse the generousity of everyone on this forum. Without it I, and many others, would surely be spending countless more hours trying to find answers. Let me assure all that when I ask a question it is only after many hours(sometimes literally days worth of time) of searching and trying as much as possible. For me I expect this as part of the learning curve.

But Am stumped once more I guess.

The two pertinent forms are :
Transaction - based on trans table (holds all transaction records)
tapAdd_Trans - based on trans==>allocation_tbl-->prod set (details of each transaction)

I have three fields on the form tapAdd_Trans which reflect a transaction
process(numerical).

Total Allocation (TTotal_Amt)--the total amount of transaction which is user entered.

Allocation total amount(c_allo_total)--the sum of the allocations for a single

transaction--a calculated field in the form's field properties.

Unallocated (cUnalloc)--the amount of dollars remaining that has not been allocated yet--a calculated field in the forms field properties.

These fields do exactly what they are supposed to do....but there is a problem.

When a transaction has been entered in the transaction form (insert button) and allocations have been entered for this transaction in the tapAdd_Trans form (insert button) and then saved and closed , the very next transaction entry will retain the prior "Allocation total amount" which is the c_all_total calculated field. The last allocation product will many times appear too. Sometimes the prior c_allo_total just shows up on the next transaction temporarily when another transaction is made as when new allocations are entered into the tapAdd_Trans form the calculations will be made but they are not always permanent---once the tapAdd_Trans form is closed and reopened many times all is as it should be...but not always. I cannot make it consistantly do much of anything except mess things up.

example:

First transaction: A total amount of $1000.00 is entered into form tapAdd_Trans.
An allocation of $250.00 is made for some product.

Ttotal_Amt shows $1000.00
C_allo_total shows $250.00
cUnalloc shows $750.00

Which is good.

Second transaction:
Now when the Insert button on the Transaction form is used to bring up the tapAdd_Trans form in order to enter a new transaction the c_allo_total still shows $250.00 and as a result the cUnalloc shows ($250.00)<--Negative. The Ttotal_Amt shows $0.00 . (The negative number in the cUnalloc tells me that it is not the problem). Sometimes, but not always, the last allocation will show up as well.

Enter in a total amount of $100.00 . cUnalloc now shows ($150.00). The C_allo_total still shows $250.00 . And a prior allocation may be present.

Close tapAdd_Trans before any additional allocation is made to this transaction.
Reopen the transaction just made and the calculated fields show what they are supposed to. Although when a prior allocation existed it will persist and messes things up as it should not be there of course.

Of course after reading all that I wrote I realize that the calculated fields most likely are not at fault but the prior allocations.....

As usual, I have attached a sample database of this for anyone who has the time, inclination, or knowledge to help me with this.

I added a red button to the Transaction form that only opens tapAdd_Trans for a new record--figured this might help in eliminating possible problems with the script that is on the Insert button that normally would be used. The problem exists when using either button although less with the new red one. If the red one is used additional info has to be entered when adding a transaction such as account type, company(vendor), besides the total dollar amount--the normal Insert button fills a few things in as default.
To easily get the problem to occur in a consistant manner, open the Transaction form, doubleclick(or highlight and press the Change button) on the one record that is entered--and then close it with either the Cancel or Close button. Then enter a new transaction by using the Insert or Red colored button.

If no one has the time to actually look at the database, then perhaps a bit a direction would be enough (of course this assumes that someone can actually understand what I wrote).

Thanks,

Mike

[MoGrace]

Hi Mike,
I believe it was Ira or perhaps Stan who mentioned a "trick" to use to make the calculations refresh on fetch is to invoke a change event. What I did to make this happen is put a logical field on the form and hide it then toggle its value on each fetch and save the record. Seems clumsy but it works.

[MikeC]

Thanks Robin for the suggestion--I may be able to use it for a later time but it doesn't do anything for the current problem I'm having.

It seems to me at the moment that the calculations are actually doing what they are supposed to...and correctly for the record they are referring to. Herein lies the problem I think----the calculations are showing their values of a previous allocation and shouldn't be as a New Record has been called up. For some reason when the new record script is used it clears all the fields except for the calculated ones.

I have tried clearing them with script and have tried various recalculation scripts I have found on this site with obviously no luck.

Looking at the problem in this light do you have any other suggestions as to what I should try next?

Mike

[MoGrace]

One thing I did in v4.5 for displaying a browse that could potentially have hundreds of records - which calculated fields greatly slowed down the form; was to put variables on the form and then script the calculations into the variables when I was ready to see them. The variables can be set to initiate as 0. OnFetch can update the variables with the calcs. You can even offer a button to "Recalc". I will take a look at your tables and see if anything else comes to mind.

[MoGrace]

Mike,
I cannot tell what you are doing by looking at the tables you zipped. It looks like maybe a vending machine business? Calling a form that is just as complicated to do a new record entry seems it could be troublesome. On the trans form I got a new record when I tried the edit button (I think that was what I did). Perhaps someone else will take a look and be able to offer more help. I like the way you use little buttons with arrows to pop up the lookup forms. You might want to add bubble help to them though since one of them changed a record unexpectedly. I noticed too that you are using multiple browses of the same table in tabbed forms to give different sorts of the table. Wouldn't that be just as easily accomplished with a button to quick sort the browse? I know you are not looking for form design solutions - but I am better at that... ;-) Whatever it is you are working on sure looks interesting! Sorry I couldn't be more help.

[MikeC]

Hi Robin; and good morning! I just missed you last night I guess--called it quits a couple of hours early last night.

And you guessed correctly about it being a vending program. I am currently a vendor and am modeling this developement after a program I am currently usng but it (and any other vending program) has many shortfalls that I want to eliminate. By trying to do as my software does, it initially it gives me a way to do things and will most likely modify it after completed once I am more knowledgeable about Alpha.

Bubble Help is exactly what I will be adding but have left most of it out in order to continue advancing the project...along with most of the finishing touches that will be necessary to clean things up and such.

I would think that the second form (allocation) would be needed in order to give the details of each transaction(first form). Information has to be entered somewhere for each transaction and am simply following the existing software I am using--seems to make sense to me and cannot think of any other way at the moment to do this.

The multiple tabs are again what my current software has in order to sort - no other way. I considered the fact that with Alpha you can use a sort button to sort anything whenever you want but left the sort tabs just for user friendliness---they would be the most likely sorts people would want to use and is one less step to do so...and will add the sort button you mentioned once I learn more I am sure. Ideally I would be able to click on each column heading and it would sort that way--if that were possible I may get rid of the tab sort. Anyway, getting a bit off topic.

I will try and see if I can use a variable for my calculations as you suggested--if they can be set to 0 whenever the form is fetched it should eliminate my problem. I have tried several ways to set the current calcs to 0 but none have worked. I will work on this after work today and post back.

Thanks for you time Robin; it's appreciated.

Mike

[MikeC]

Not quite there yet

I have tried using a variable for the calculation and can set the initial values of the fields Unallocated and Allocation Total to 0....but there still remains a prior allocation amount which is still present and have no clue as to where or how it is being called up(many times it is not even present in the allocation table which does NOT make sense!) . Considering that most of the same problem exists as before I will most likely revert back to what I had before as I think figuring out why a previous allocation is coming up in my New Record is the key. Still working on it though---just wanted to add an update.

Mike

[MoGrace]

Sorting on the column heading of a browse is possible if you right click on the title and do a quick sort. Another more complicated alternative is to show the browse without column titles and create buttons instead which select an index. My personal favorite is to create a radio control based on a variable and let each of the choices change the index. You can also add another variable and let the user type in what he wants to find based on the radio selection.

Ex: radio control choices are Company (or Vendor), Product ID, Product Desc. User selects Company and types in ABC in the other box then the Find Button does topparent.find(var) on the value that the user typed in, based on the index the radio button set.

Have you tried defining the calculation for the variable in the OnInit and/or OnFetch event for the form instead of pointing the variable to the calculated field? I am thinking the calculation will not occur until the event calls it - which would then point to the current record.

[MikeC]

For the moment as far as the sorting goes I am thinking to leave the sort tabs as they are--I find them very convienent in my current software. I will be allowing the ability to sort any column too, but haven't decided on which method to use as of yet....I appreciate your suggestions.

I have defined the variable on the OnFetch event of the tapAdd_Trans form with the following script derived form an action script but changed it from character C to numeral N--I think the AS was faulty in this respect:
'Update value in existing variable
'vcT_curr_Allo_Total' to '=total(Allocation_Tbl->Tallo_Amt)

vcT_curr_Allo_Total = convert_type(a5_eval_expression("=total(Allocation_Tbl->Tallo_Amt)"),"N")

vcT_curr_UnAllo = convert_type(a5_eval_expression("=Ttotal_Amt - total(Allocation_Tbl->Tallo_Amt)"),"N")

I also have the same script on each OnChange event of the fields:
UnAllocated, tTotal_Amt, and Allo_Total.

To get it to work I also placed the Set Field Value AS after these scripts which sets the values of the same fields...I couldn't see where else to do this.

To set the initial values of the two fields UnAllocated and Allo_Total to 0, I placed the script on the Insert button of the first form, Transaction and seems to do its job.


After pressing the Insert button on the Transaction form all looks good, even after selecting a Vendor company. But just as soon as I activate the browse it will show an allocation from "somewhere"--seems as if it is a prior one but as I have said before there have been times when I cannot find this transgressor anywhere in the database!

Do you think I should zip up another sample with the variable method that I have concocted so far??

As the software is only doing as I tell it to I just have to figure out where I am telling it to put this allocation in with my New Record call up. I have considered also that maybe it is how I have based my forms on the sets and such--have tried modifying them a little with no success.

:-) I Will keep working on this. Normally I just go onto another aspect of the program and many times learn enough to figure things out once I go back to a prior problem--but in this case many of the next steps follow this so have to figure this out before I can go on.


Thanks again for your suggestions Robin,

Mike

*Well it was almost working! Could not compact database--deleted some temp files and form would not load--had to do restore from a backup and now when redoing what I had done it no longer is even to the point I was at. LOL ...couple more hours and then tomorrows another day with a fresh outlook.

[MikeC]

Almost

I'm close I think. I went back to having calculated fields as I originally had and I took all the scripts off the OnChange events of the 3 fields--leaving just the field property expressions.

The only problem I have now is to get the c_Allo_Total (sum of all the allocations) to refresh. No allocations show up in the embedded browse that has the data but the ghost of the previous value(s) still show up as a total in the c_Allo_Total field. The only way so far I can get it to refresh is by closing the form and then reopening it. There has to be a better way!

I have been searching the forum but most issues are regarding calculated field rules and not calculated fields placed on a form. I wish there was an A5_recalc for forms!

Mike