Incorrect math Alpha5v7 to V8

[Dave Mac Callum]

Hi there,

I assumed that this problem was a bug. It turns out that I do not understand what happens in embedded browsing. Can anyone suggest where I can go for further reading so I can understand what's going on here ?

I am including correspondence regarding this "bug"

Dave Mac



Operating System: Microsoft Windows XP Professional Service Pack 2

Bug Description:

I have a Form named Debtors Enquiries. I attach the db under which it runs. I alseo attach a correct V7 screen jpeg and an incorrect V8 screen jpeg.

This has been developed in Alphav7. Works reasonably well. When I run it under V8 the arithmetic (math) behaves differently.

To get to the problem :

Using V8

1. Load db (Focus_Universe). Main Menu displays.
2. Select "Debtors Ledger - 1". Select "Enquire Accounts - 6".
3. Dialog box Search (by name) displays. Key 24 . 24 CARROTS MARKETING displays.
4. Select Embedded Browse Last-Item button. Running Balance of R16,053,40 displays. (we use , and not . in South Africa) This value is correct.
5. Select Prev button. The transaction amount R11,938,08 has now got focus. The Running Balance of R27,991,48 displays. This is correct.
6. Select the transaction directly preceding this transaction by clicking the button directly below the Prev button. The Running Balance of R39,929,26 displays. This is incorrect.

7. Do the same excercise using V7 and when you get to step 6. the correct amount of The Running Balance of R27,991,48 displays. This is correct.

The math is done in Embedded Browse OnFetch Event.

Do I have to run my V7 program through some process before using V8 ?


Dave Mac

[Dave Mac Callum]

Re: Incorrect math Alpha5v7 to V8

This is the response from Alpha



Dave

I looked at your onFetch event in the embedded browse and I see that you are getting a pointer to the SAME table that the embedded browse uses.

This is a problem because the embedded Browse 'owns' this table pointer and it controls it - you don't. Therefore code that relies on you being able to control the record pointer is not going to work reliably.

I would approach the problem differently.

1. I would add a new field to the 'trade transactions' table called 'runningBalance' - numeric 13,2

2. I would remove the any code from the embedded Browse's onFetch event.

3. In the form's onFetch event (which executes every time the PARENT record changes), I would add a script that dynamically computes the values in the 'runningBalance' column for the current set of child records. Here is a sample script that you could put on the form's OnFetch Event:


t = table.current()
'this gives you the current value of the linking key value
acc_id = t.acc_id
dim t2 as p
'open a NEW instance of the child table - you (and not the embedded browse) 'own' this instance
t2 = table.open("trade transactions")
dim i2 as p
'set the filter and order on this new instance so that it matches the order shown in the embedded browse
i2 = t2.order("ACC_ID+CDATE(DATE)+TRANS_TYPE","acc_id = " + s_quote(acc_id))
dim records as n
records = i2.records_get()

'i did not know which field you were computing the running total of, so I just picked the 'new_amnt' field.
dim cummTotal as n = 0
if records > 0 then
dim i as n
for i = 1 to records
cummTotal = cummTotal + t2.Net_amnt
t2.change_begin()
t2.Runningtotal = cummTotal
t2.change_end(.t.)
t2.fetch_next()
next i
end if
t2.close()


Now that the 'runningTotal' field has been populated, you can simply display it on the form.

[Dave Mac Callum]

Re: Incorrect math Alpha5v7 to V8

I then replied as follows


Hi Selwyn,

Thanks for the input.

I implemented your suggestion and it works fine. Am attaching the amended db. (I retained the Embedded Browse OnFetch code for reference - refer below)

The reason I needed to go to XBasic is speed of display when displaying an account.

On Find Account, if you enter Account name BASTIONS you will see what I mean by speed (or the lack thereof).

I think I understand about the table pointer belonging to Embedded Browse. I thought I might try including a new instance of the child table in the Embedded Browse OnFetch so as to not interfere with the Embedded Browse pointer. Is this feasible ? The result indicates that I haven't got it right.

Can you advise

Dave Mac

[Tom Cone Jr]

Re: Incorrect math Alpha5v7 to V8

Dave, this will take some time. I think there's a solution here using a pointer to the child table, provided you don't try to set a new index primary. Your form is based on a one to many set. The browse is displaying child records. The records displayed in the browse object represent the filtered subset of all the records in the child table that are linked to the current parent table record. The browse does this by automatically creating a filtered view of the child table records. A query, if you will. When your script gets a pointer to the child table and then sets a permanent index "primary" for that table, you're asking Alpha to abandon the internal query that's already been run to limit the display of records in the browse object to the correct child table records. This you can't (or at least shouldn't) do. The permanent index is not filtered for the current primary table record. A fetch first would fetch a record regardless of whether or not that record was related to the current parent table record.

In my experience you can use a pointer to the child table but you can't set a different index or query filter primary for it. To do so breaks / conflicts with the set definition used to display the records on the form.

Try this.

Get a pointer to the child table in your script, as you've done.
Then use that pointer to fetch the first record and write a field value from that record to the trace window.
Then use that pointer to fetch the last record and write a field value from that record to the trace window.
Study the trace window results.
You'll see that the fetch_first and fetch_last methods did NOT jump to the first or last record in the table. They jumped to the first and last records in the filtered subset being displayed in the browse. Pretty nifty, huh?

You should be able to step through the rows of the browse by fetching records using the pointer to the child table. <tbl>.fetch_eof() will return true if the next fetch would push beyond the last child table record that has a matching link field value. So, use the pointer to the child table, but do so without breaking the linkage needed by the set and form. This means don't try to do anything in the script that you couldn't do through the form, itself. Since you can step through the records using the form, you can do the same thing with a pointer to the child table. Since you can't abandon the set filtered view of the child table through the form you shouldn't try to do so with your script.

This means you can probably modify your script to get the running balance you need without having to open another instance of the child table, and without having to put the child table in the set structure twice.

[Tom Cone Jr]

Re: Incorrect math Alpha5v7 to V8

Dave,

I've been rummaging through your onFetch script for the embedded browse in the Debtors Enquiries form.

If I display the value of the s_prev_os variable on the form I am perplexed why you subtract it from s_run_bal at line 91 ?

When I follow the exact sequence you outline in the first post of this thread, after I press the PREV button s_prev_os has a value of -11,938.08. When line 91 subtracts this from s_run_bal the effect of course is to ADD 11,938.08 to s_run_bal and this leads to the incorrect value you are seeing. Alpha Five is correctly subtracting a negative number. There is no math error, so far as I can see.

I'm now wondering if maybe there's an error in the logic of your script, rather than a problem with the pointer to the child table.

-- tom

[Dave Mac Callum]

Re: Incorrect math Alpha5v7 to V8

Dave,

When I follow the exact sequence you outline in the first post of this thread, after I press the PREV button s_prev_os has a value of -11,938.08. When line 91 subtracts this from s_run_bal the effect of course is to ADD 11,938.08 to s_run_bal and this leads to the incorrect value you are seeing. Alpha Five is correctly subtracting a negative number. There is no math error, so far as I can see.

-- tom

Thanks for your interest in my problem.

I think that the maths is correct. The value of -11,938.08 needs to be added back because the result is the value owing before the value of -11,938.08 was deducted.

I am happy with the result of the program when it runs under V7. It's in V8 where I get the problem.

I am a lot clearer on what happens after absorbing your explanation. I took out an index that I had included, and it does not seem to make any difference. I also returned the child record number to its original after establishing first and last record numbers just in case this may make a difference. Now I don't think I am doing anything that I cannot do on a normal form operation.

I included some trace.writeln and I notice that under V7 it makes sense to me that s_prev_os value is -11938.08 at rec number 582. However, V8 makes no sense to me that s_prev_os value is -11938.08 at rec number 583 as well as rec number 582 !!. It the location in the code (line # 140) where I save s_prev_os, rec number 583 does not know this -11938.08 yet. If you compare the two different traces you will see the difference.

Interesting thing is that if you go through the prev transactions one at a time V8 works ok.

I am attaching the code as it is now with the traces.

Would appreciate any observations on this

Dave Mac

[Tom Cone Jr]

Re: Incorrect math Alpha5v7 to V8

It seems to me that the PREV button script is causing the problem.

If you fetch the last item and then press the PREV button item 583 is selected. The current OS for it is -11,938.08 BUT your form is showing the value of the s_prev_OS as the same value, -11,938.08. It should be zero because the outstanding amount on the previous record is zero.

As you note if, instead of using the PREV button, you fetch the last record, and then fetch back up the list one item at a time, when you get to item 583 the previous OS is zero, as it should be.

I'm guessing the problem you're seeing is related to the work Alpha has done to improve the performance and functionality of the embedded browse object between vers 7 and vers 8, though this is only a guess and certainly not an explanation.

The onFetch event script in the browse object is interrogating the child table beneath the form directly. Your browse's navigation buttons are talking to the same table but through the form. I suspect the problem you're seeing is because they're not precisely in synch when values are assigned to your variables.

The key question is why does the PREV button produce the wrong value for the prev OS?

Later on...

Dave, in terms of alternatives I'd look for a solution that did not use the onFetch event of the browse object. I'd give serious consideration to moving all the processing to the navigation buttons instead. What worries me about your present approach is that your script runs each time the onFetch for the browse object fires. Yet your PREV and NEXT buttons force the browse to fetch a series of records in a real tight loop, one right after the other. On each fetch your entire script must be processed. I should think there's a better way.

Still later...

As I thought through the implementation quagmire off loading the processing would entail I began to wonder if there was some way to slow down that tight loop in the PREV button's onPush script. It turns out there is. Open the form in design mode then edit the PREV button's action script as follows:
a) leave the first action alone
b) convert the second action to xbasic, and then add the following line at its end:

xbasic_wait_for_idle()

c) delete the remaining actions
d) copy the revised second action and paste it in 8 times.
e) save the script and then run the form.

I see a bit of a ripple in the browse object as fetches the 9 previous records, but the math is right when all is said and done.

Hope this helps.

-- tom

[Tom Cone Jr]

Re: Incorrect math Alpha5v7 to V8

Dave, here's a replacement script for the PREV button on your Debtor_Enquiries form.

'Date Created: 06-Dec-2007 09:34:04 PM
'Last Updated: 06-Dec-2007 09:50:46 PM
'Created By  : Tom Cone Jr
'Updated By  : Tom Cone Jr

'Set the variable "l_ftch_nxt"  False.
l_ftch_nxt = .f.

'Go to previous record in Form 'Debtors Enquiries' at Debtors_xsacts level.
'Repeat 9 times to scroll the browse object on the form.

DIM object_name as C
object_name = ":Debtors_Enquiries:Debtors_xsacts"

DIM varP_Object as p

'Get a pointer to the browse object being scrolled
varP_Object = obj(object_name)

varP_Object.fetch_prev()		
ui_yield()

varP_Object.fetch_prev()		
ui_yield()

varP_Object.fetch_prev()		
ui_yield()

varP_Object.fetch_prev()		
ui_yield()

varP_Object.fetch_prev()		
ui_yield()

varP_Object.fetch_prev()		
ui_yield()

varP_Object.fetch_prev()		
ui_yield()

varP_Object.fetch_prev()		
ui_yield()

varP_Object.fetch_prev()		
ui_yield()

This your action script converted to xbasic with ui_yield() function calls after each fetch, but I've shortened it by removing unnecessary boilerplate included by the action scripting code generator. In testing here with Ira's CSDA utility I discovered that ui_yield() performs noticeably faster than xbasic_wait_for_idle(). So, in addition to simplifying and streamlining the action scripting xbasic, I chose to use ui_yield() instead of xbasic_wait_for_idle().

Hope this helps.

-- tom

[Dave Mac Callum]

Re: Incorrect math Alpha5v7 to V8

Hi Tom,

Really got to thank you for all your effort to help.

Implemented as you advised and it works great.

Was a little surprised that V8 actually runs slower than V7. I would have expected V8 to be quicker.

Just thinking about this case generally, and it occurred to me that many applications must have the same problems. Large tables How do other designers implement a solution for similar applications ?

I started off duplicating Alphasports embedded browse item number count which uses a Calc counter. I found that on large files it was just too slow as I pointed out to Alpha in my bug report. Selwyn gave me a suggestion which was probably the way to go but had the same problem of speed (taking more than a minute to display an Account while the child table was being populated).

Once again many thanks for your support.

Dave

PS. Incidentally, When I push "Debtors Enquiry - 6" button to invoke the enquiry program, I need to initialize variables. I do so by having a Form On Activate event which runs a script. That On Activate never runs (I have put a temp ui_beep to sense when it runs). Surely the On Activate must fire when the form gets focus ?

Any ideas why it does not ?

[Tom Cone Jr]

Re: Incorrect math Alpha5v7 to V8

Was a little surprised that V8 actually runs slower than V7.

On its face your statement is a broad generalization. It doesn't square with what I see here in most areas of the application. In fact, the opposite is more often true.

As mentioned earlier a lot of work has been done on the embedded browse since vers 7. Check the release notes for vers 8 to see what I'm talking about.

Your PREV button imposes unusual stresses on the form. It runs a tight loop containing 9 fetch_prev action statements. Since each fetch is being done through the browse object that object's onFetch event fires again for each iteration. So the very script being run gets called again each time the previous record is fetched. The called script does some processing and then "refreshes" the display of a half dozen or so variables on the form itself. Each time it's run. i.e. on each iteration in the loop. I think what's happening is the "refreshes" are falling behind because the loop is running faster, not slower, in vers 8. I think this is why slowing down the loop to give the form a chance to keep up results in the display of accurate information.

Instead of navigating through the rows in the browse using the browse object itself, xbasic permits you to navigate through the records in the table supporting the form without touching the GUI. You're driving the table through the GUI. You don't have to. Instead, you can navigate "behind the scenes" so to speak, do your processing, and then refresh the display. I mentioned this earlier in the thread. This bypasses screen refresh latency issues. But there are trade offs. You lose access to the built in events of the browse object, for example, so the coding becomes more complex. Given the huge investment you've made in your present approach I'm not recommending you change, just trying to give you a glimpse of other possibilities for the next app you design.

I'm glad you liked the script changes.

-- tom

[Tom Cone Jr]

Re: Incorrect math Alpha5v7 to V8

When I push "Debtors Enquiry - 6" button to invoke the enquiry program, I need to initialize variables. I do so by having a Form On Activate event which runs a script. That On Activate never runs (I have put a temp ui_beep to sense when it runs). Surely the On Activate must fire when the form gets focus ?

That's not what I see here. OnActivate fires when the Debtors Enquiries form opens. Perhaps your test is missing it. Suggest you add an inline xbasic action to the end of the onActivate script for the form that does this:

ui_beep()
trace.writeln("Hello, onActivate script just ran")

Save the script
Save the form

Open Main Selection form , then the ledgers, then Debtors Enquries.

You should hear the beep unless your speakers are off.
Close the form and check the trace window. You should find the message waiting for you.

[Dave Mac Callum]

Re: Incorrect math Alpha5v7 to V8

Hi Tom,

Thanks for the explanation. It's actually only when one goes down a road like this that one starts to understand the idiosyncrasies of Alpha5.

ui_beep()
trace.writeln("Hello, onActivate script just ran")

Open Main Selection form , then the ledgers, then Debtors Enquries.

You should hear the beep unless your speakers are off.
Close the form and check the trace window. You should find the message waiting for you.

Yes it works with your suggestion. Thanks.

Dave Mac

[Dave Mac Callum]

Re: Incorrect math Alpha5v7 to V8

Hi Tom,

I hope that you don't mind me pestering you on this Debtors Enquiry, but I stumbled across another problem.

I need to Filter the Trade Transaction that I display in my embedded browse. I have been working with a form filter

Date >= ctod("01/09/2007")

..and it has been working fine.

I then replaced this filter with the variable filter

Date >= Var->g_start_date

..and I don't know what it is doing !! (Var->g_start_date is populated in Autoexec script.)

I have reduced the size of Trade Transaction table in order that it will fit onto this message board. With the full size of Trade Transaction table it takes even longer doing whatever it is doing.

Any idea what I need to do to get back to the performance when I use Date >= ctod("01/09/2007"), but using a variable instead ?

Looking forward

[Tom Cone Jr]

Re: Incorrect math Alpha5v7 to V8

Dave, the board works best if new questions are put into new message threads.

Use the expression builder dialog to build and test the filter expression. I recommend letting the dialog insert the table field and variable into the expression for you (from the left pane of the builder dialog). You'll see that the builder will include full prefixes for both.

Here's the filter expression that is built if you let the builder put it together for you. I've tested this in vers 8. Let us know how it does in vers 7.

Trade_Transactions->Date >= Var->g_start_date

Though not necessary for the solution, my personal practice is to avoid using fieldnames that duplicate built in Alpha Five functions. Here, I would rename "Date" so that it doesn't dupe "Date()".

[Dave Mac Callum]

Re: Incorrect math Alpha5v7 to V8

[QUOTE=Tom Cone Jr;439912]

Use the expression builder dialog to build and test the filter expression. I recommend letting the dialog insert the table field and variable into the expression for you (from the left pane of the builder dialog). You'll see that the builder will include full prefixes for both.

Trade_Transactions->Date >= Var->g_start_date

Hi Tom,

I always use the expression builder to build and test the filter expression.

When I select from the left pane of the builder dialog F2 and the Trade_Transactions dbf appears and I select Date, full prefixes are not included on my system. Do I have to set-up my design system so that they are included ?