I challange and implore participation and help with this wild problem

[Mike Wilson]

Hi all,
I have spent a great deal of time figuring out the root of this problem, and on this example that I generated to show the problem. I would appreciate some participation and help. I posted a version of this before, and some were able to see the issue and others not. I am asking again for more folks to see if they see the issue and can help with thoughts and ideas about it.

I am 90% complete with an application and have come across a very unusual problem that I don't understand and am having a very tough time capturing a work around that doesn't introduce a significant, non-intuitive sequence.

The attached application is for a portfolio application. The main form is 4 tables in a set with 1:M links in a serial fashion:
Company:Accounts:Positions:Transactions.

The issue relates to the applications loss of focus/connection with the 4th table and loss of ability to "see" the table when there is a base form filter/order expression on the 3rd table, a form calculated field for values of the 3rd table, and an order expression on the fourth table. Open the example, press the Red positions button on the MainMenu, and read-follow the direction in the modeless dialog that details the steps and sequence to follow.

Those that can figure this one out, if you can even see it, are for sure A5 Wall of Famers... or maybe it's an A5 bug. Frankly, I see this as a significant application problem.... but that's just me. BTW, if I open this and do this in v8, at least in my hands, it crashes the form, and renders it corrupt. Thank very much guys....

[MikeC]

Re: I challange and implore participation and help with this wild problem

Even with your warning Mike of the form exploding, I think you knew I would still try it! :) Using v8 I do not see the problems you have tried to illustrate here. I will keep trying to reproduce it....any other ways you noticed to make this problem rear its ugly head??

Also, I only used my mouse for this--I did not use any keys....in case this could be part of the problem.

[Mike Wilson]

Re: I challange and implore participation and help with this wild problem

Hi Mike,

Thank you very much for looking.

I was alert that this is "visible" to some and not others. I don't know why this plays out this way. Maybe those that see this are "islands", but I know I am able to reproduce this 100% of the time after completely rebuilding the entire database (several times). The one I submitted in example being the third complete rebuild. If you followed the sequences that I described and you do not see the issue (Keith Hubet couldn't either but others did), then the issue is far, far more complex, and why I petitioned many others to "see" if this was visible to them. I am using the latest v9 patch on an XP media edition, SP2 machine, and tried this in v8 also, as I described. I have spent at least 2 weeks trying to figure the elements of this one out. It is so baffling to me, and I am struggling with a viable work around. I am still congering an alternative, but am still struggling with having come this far to encounter this "kwirk" which has left me stiffled.

[Tim Kiebert]

Re: I challange and implore participation and help with this wild problem

Mike I see the same as your notes.
I can definitely see it but have not made it to the wall of fame, sorry. :(

It is not only the sequence of values you specified either.

• Choose American Funds , any of the 4 positions. - It works ok

• Sort the Transaction browse on the date column using standard toolbar button (ascending or descending, doesn't matter)
• Now only the bottom three positions in the Position browse result in a successful report. Top one results in no info for the transaction browse.
• Now change the sort order of the position browse. Same result as the previous point. But the transaction records that belong to the position record that was on top now report correctly.
• change company focus to Scottrade, account Scottrade Trust, and any position does not work.
• change account to Scottrade IRA with positions ascending on Pos_Id_no and again only the bottom three of the 4 positions return results for the transaction records.
• Sort positions descending and all 4 positions work
• change account back to Scottrade Trust, all Positions work
• Sort Positions ascending on Pos_Id_no, none work
• Sort Positions decsending, all work.

Are you lost yet?

I am testing with v9 1836 3213.

And yes when you remove that blue calc field the problems seems to go away.

.... after another test.

I changed the calc for that field to

dbsum("positions","Acc_Id_No",Accounts->Acc_Id_No,"Curr_value")

which I think achieves the same result with out the wierd behavior.
Hopefully that allows you to continue but it does not explain the wierdness.(my new technical term of the day)

[Tom Cone Jr]

Re: I challange and implore participation and help with this wild problem

Mike_W, I see the original issue using Build 1938_3239 (and the new browse object). I wonder if this is a case where the advice in the help file should be heeded? In the calc display field expression you use the TOTAL() function. This function was designed for use in reports, and its use in form layouts is not supported.

[Tbaker]

Re: I challange and implore participation and help with this wild problem

Here is a quote from the A5V9 online help file on the Total() Function

This function is typically used in Reports to total records in a group, or in Forms to total records in a one-to-many child table.

FWIW

Tom B

[Tom Cone Jr]

Re: I challange and implore participation and help with this wild problem

Here is a quote from the local A5v9 help file on the Total() function:

Limitations

This function is a report writer function, not intended for table level field rules or other expressions. While the function may perform in some areas outside of the report writer, its use there is not supported.

It would seem the online help file documentation is different from the local help file documentation.

[Tbaker]

Re: I challange and implore participation and help with this wild problem

Tom C

I knew that was in the local help V9 and it also in the local help V8 but in the online help for both it does not show it as "not supported".

I always have gone with the local help version - however, as we all have at time, used the total() function in a form.

Mystery - which one should we believe (personally I align with the local help since it has been that way for quite some time).



Tom B

[Tom Cone Jr]

Re: I challange and implore participation and help with this wild problem

Tom, I was unaware that the documentation "online" had changed. Thanks for highlighting the differences. -- tom

[csda1]

Re: I challange and implore participation and help with this wild problem

Hi Mike, et al,

I have not looked at your problem (just not enough time in the day), but I can tell you there are a fair number of functions that are not able to be used in anything but reports (but might be acceptable in labels or letters). Some of these may be AVERAGE(), COUNT(), MAXIMUM(), MINIMUM(), PAGE_AVERAGE(), PAGE_COUNT(), PAGE_MAXIMUM(), PAGE_MINIMUM(), PAGE_TOTAL(), PAGECOUNT(), PAGENO(), TOTAL().

This is because a report has basically two passes through it, one to calculate where the page breaks are and to take non-running summary values and pre-calculate them (e.g for totals, percentages), and one to actually output the records.

There is also the concept of running values, that total up values but have no concept of the total (These are way more efficient). These can be reset at page and group breaks. The moment you have some calculation that forces Alpha into running the 1st pass to get summary values that are not running (including things like page x of y), I believe it goes into the 2 pass mode, vs the 1 pass mode. This doubles the report time, as well as the number of record accesses.

All of this have no real meaning in a form or browse, although, I believe Alpha has shoehorned them in at times to make some of the functions kind of work in specific circumstances, although I doubt it would cover all cases.

Using table and DB functions are best when processing on forms and browses. Alternatively, write your own function to summarize some part of the table and use that.

The other big issue, is that Calc fields on a form or browse are calculated multiple times for each record, causing a long operation such as TableSum() to be evaluated many times. This is probably because multiple calculated fields might refer to each other and Alpha needs to know that the value has "settled".

I typically will move many calculations that take a relatively long time to compute on a form/browse into an OnFetch event for a record and save them in a variable that I display on the form/browse. This way they are only evaluated once.

[Mike Wilson]

Re: I challange and implore participation and help with this wild problem

Thank you very much guys!

Having read the On-Line help which Tom_B pointed out, I believed the total() function had been "updated", especially since the genie stilol generates the expression using total() when building the calculated form field and the generated expression is functional. I was believing that the function had been updated, possibly to incorporate the dbsum() action, as the documentation had been updated. There again the assume devil has struck.

Tim,
Thanks for the dbsum() which works well. I am still mift at how the combination of table orders and this calculated field can generate a "break" in the set and for the table to become virtually "lost".

I typically will move many calculations that take a relatively long time to compute on a form/browse into an OnFetch event for a record and save them in a variable that I display on the form/browse. This way they are only evaluated once.

Ira,
Thanks for this tip, I will give this a shot. There is a fairly significant delay moving between records when this calculated field is added to the form.

Thanks again!

[G Gabriel]

Re: I challange and implore participation and help with this wild problem

Don't know about v9, but in v8 total() is problematic in that it does not update when the numbers change in the child table. No one should get fussy about that, the help file, or one help file, tells you it is a report function.

For example, if you use total() in a form to add the values from a child table (as in an embedded browse in a form based on a 1-M set), the amount shown by total() does not update if you change the values in the rows in the embedded browse. You could jump hoops (except for one hoop, a work-around trick) and it will not update.

Not sure if this thread is dealing with this specific issue or some other issues and not sure if total() was re-tooled in v9 to work in a form and update properly, but regardless, there is a trick to get total() to work in a form reliably, if that is the question of this thread.

So, what is the question?
Is it:
a-How to get total() to update? or
b-Some other embedded browse(s) misbehavior?

And.. while we are at it, does it update properly in v9?
I doubt it, but it might.

[Lance Gurd]

Re: I challange and implore participation and help with this wild problem

Build 1939-3239 with new browse: I can select any row in any of the top 3 browses and the transactions browse filters correctly, and select any row in company or accounts browses and the gain total figure is correct for those records.

Cannot see the problem here

[Mike Wilson]

Re: I challange and implore participation and help with this wild problem

So, what is the question?
Is it:
a-How to get total() to update? or
b-Some other embedded browse(s) misbehavior?

And.. while we are at it, does it update properly in v9?
I doubt it, but it might.

[Mike Wilson]

Re: I challange and implore participation and help with this wild problem

So, what is the question?
Is it:
a-How to get total() to update? or
b-Some other embedded browse(s) misbehavior?

And.. while we are at it, does it update properly in v9?
I doubt it, but it might.

The questions is how can the presence of a calculated field on the form that uses total() directed to parent (Gchild) data cause the application to loose 'linkage/access' to the records in the child (GGChild) table. You would have to look at the example to to see how the "loss" to the table data is demonstrated.