Invoice

[Tom Cone Jr]

RE: Invoice


Craig,

Maybe I'm just a 'Nervous Nellie', but I'm wary of the Grp parameter in the context of Total() on a set based form. Perhaps it works fine when the Grp in question 'happens' to match the key value used to link the child table records to the current parent table record. This would be understandable, even though I don't see it documented anywhere.

Are you saying that you could use Total() on other 'groups' within the child table? i.e. for some but not all of the child records linked to the current parent table record? (an example might be totalling only those transactions records for transactions occurring between two dates?)

In your experience will Total() on a form work with both single field links, and multi-field links between parent and child table?

I guess what I'm asking is this. Are there situations where Total() is not going to work when working with child table records in a set based form?

-- tom

[James Peterson]

RE: Invoice

We have only used this calculated expression in this one application, so can't verify it's reliability. So far so good.

[CraigSchumacker]

RE: Invoice

Tom:

I must be worse than I thought at expressing my opinions on paper!

As I see it, TOTAL() is a form/report level function for working with tables within a current table or set. TOTAL() can only calculate based on the grouping within the structure of the set.

DBSUM() can be used to go beyond the capabilities of TOTAL(), but adding DBSUM() to a form adds overhead to that form, and slows the fanning of records by pressing the page up and page down buttons.

I cannot think of a single instance where I had to reflect a total on a form that went outside the structure of the (well structured) set, although it is conceivable that one might.

At least twice in the past three or so years, I have used DBSUM() on a form to tally child records in an embedded browse, and at least twice, I was forced to revert back to TOTAL() because the overhead of DBSUM() slowed the fanning from record to record down to a crawl.

Now, on to the heart of your question...

You said:

"I guess what I'm asking is this. Are there situations where Total() is not going to work when working with child table records in a set based form?"

To the best of my knowledge, total works at every level of the set, when used within the context of the set. DBSUM() is required if you require totals outside the structure of the set.

Now, let me ask a question or two:

Why the distaste for TOTAL(). Isn't this exactly the same concept (and level) as grouping, and totaling, within a report based on a set? Do you use DBSUM() within reports to tally child records? Don't you use group breaks on a report?

Are you under the impression that TOTAL() does not work for the purpose for which it was designed? Do you have confirmed information on a bug (Other than the undisputed refresh() issue)?

Tom, you are a seasoned (O.K. VERY seasoned) user of A5, so the 'Nervous Nellie' hunch leaves me scratching my head. It all boils down to this:

1) Does the TOTAL() function work.

2) Does TOTAL() produce faster results, under the appropriate circumstances, than DBSUM().

From my standpoint the answer to both of the above questions are yes.

If you believe TOTAL() is flawed, we should take steps to have it repaired by A.S.

I'll do a few further tests on this end... a working example is probably in order at this point...

Craig

[Tom Cone Jr]

RE: Invoice


Craig, thanks. I agree, a working example would be helpful.

Here's where I'm uncomfortable. On a regularly recurring basis we get questions here from folks who're having trouble making total() work within the context of a form. In some of these cases, if I recall correctly, the function was not behaving as expected. Perhaps these were all display synchronizing issues, and we missed it, but I don't think so.

The Xbasic Reference Manual's discussion of Total() does not mention forms at all. The entire discussion, at least to my way of reading, seems to imply that it's intended for reports. Maybe I'm inferring too much because I readily admit the text does not say that Total() is intended solely for reports, either.

It's the discussion of the Group and Sub-Group names that worries me. I think I could define what 'Group' and 'Sub-Group' mean in the context of a report. I have never seen them defined in the context of a form. To me that creates an ambiguity. People may be making some assumptions about what these terms mean. It seems so to me, which is why I framed the questions posed earlier.

-- tom

[CraigSchumacker]

RE: Invoice

Tom:

I, too, rarely look at it, and wouldn't go so far as to say it's the perfect example, but please look at the forms in the INVOICE sample that shipped with A5...

You'll notice TOTAL() is used on all the forms...

Craig

[CraigSchumacker]

RE: Invoice

Tom:

Reading from the book, here is the only reference to the word report:

"The Group and Sub-Group names can be one of the following: GRAND, the name of the current table, the name of the current table in the current set, OR the name of a grouping level that is defined in a report layout."

The key word is the 'OR'... I believe the writer is identifying groups, and one of those that can be used is the grouping level within a report. The writer does not specify reports, solely, at least not as I read it.

Craig

[Michael Humby]

RE: Invoice

I hope Craig is right because I have always used child totals based on a parent grouping, never used dbsum. Though must admit I have always used the linking field for the group level.

Michael

[Tom Cone Jr]

RE: Invoice

Craig,

Quick review of recent message traffic turns up two areas where folks seem to be having trouble with total() when used to summarize field values in a child table on a set based form. (Wow, that's a mouthful!)

1) Efforts to embed a filter inside the expression usually lead to trouble. All working examples I've found this afternoon simply use a field in the child table, and total values found within that field. No filters or subsets of linked child records are present in the examples I've studied.

2) Occasionally folks will report that the calc field doesn't keep up or lags behind. Presumably this is due to the refresh issue Finian referred to.

So, is it reasonable to conclude that:

1) in a set based form,
2) you may use the total() function
3) to generate a summary total of all child table records
linked to the present parent table record if...
a) the set link is a simple one field link; and
b) the calc field does not attempt to filter the linked child table records further

??? How about it guys, is this a fair working premise?

-- tom

[CraigSchumacker]

RE: Invoice

Tom:

You said:

"1) in a set based form,"

I respond:

This function can definitely be used at the table level.

You said:

"a) the set link is a simple one field link; and"

I respond:

I think this is incorrect, and that the tables within the set can be linked by any number of fields, and that the link can be complex or simple...

You said:

"b) the calc field does not attempt to filter the linked child table records further "

I respond:

From the book:

"The Expression parameter can be any valid expression..."

I think this would be eligible for valid filtering expressions... Perhaps problems are occuring when attempting to filter child records?

Perhaps it's a refresh issue, as you point out. I only have a couple programs in A5V1 still in use. There was never a refresh() issue back then. This is an A5V4 thing (bug).

I have a working example of my last bout with DBSUM() vs. TOTAL(), however I will need to load enough dummy data (In place of the actual records) for posting. I'll try to get to this as soon as possible. This will take some time.

I have a headache... :~)

Craig

[Tom Cone Jr]

RE: Invoice

Craig,

I'm sorry if I've contributed to the headache!

I'll standby. Am anxiously awaiting a working model that illustrates the use of total() in a set based form, where the set using a compound key consisting of more than one link field, and includes a filter expression which limits the summary to a defined subset of all child records currently linked to the parent.

-- tom

[CraigSchumacker]

RE: Invoice

Tom:

I'll see about a single record example tonight. If it can be done, as I have suggested, you will have your wish.

I don't really have a headache! But I am off to Bally's, so don't look for any responses from me til later tonight.


Craig

[Steve Dentler]

RE: Invoice

"The only reason the total function appears faster in forms and reports is that the query is already run (by the form opening event or the report run event) and the records tagged by the DB engine."

I respond:

So you do agree? Keep in mind that TOTAL() is designed to work specifically with an open table or set, within a form or report. DBSUM() was created to allow the developer to go outside the current open table. In my mind, they have two completely different specific uses, and the only reason this cross-thread exists, is because the total() function has the refresh() issue...
-------------------------------------------------------

Please don't speak for me, I do not agree. The total function has it's place but it is not a panacea for form based calculations. Lets consider an example where a number of totals are to be drawn from the child records--say ten different fields are to be totaled from child records numbering in the hundreds. In this case, I would write an Xbasic script to scan the child records, record the totals, and write to fields for display. This would allow for a single pass through the records and would probably result in a faster display than using ten separate total() functions (unless you think that the total function is intuitive enough to also total all the other total() calculations it sees on the form in one pass! My bet is that that the total function would make 10 distinct passes through the records.

How about a real world example. A customer master form also has a tab for invoice history display. This tab has all the invoices and payments for a customer for the time period chosen in the app setup files. It may display 5 invoices or hundreds of invoices. At the bottom of the browse, there are fields to display an aging of the invoice balances (current, over 30, over 60, over 90, over 120). In order not to incurr the overhead of the calculations when browsing through the records, the aging fields are not updated until a user selects a button to do so. This is in fact, exactly the way a number of middleware applications work (e.g. Mas90). Here, an Xbasic script that would pass through the records and do the calculations and the total function would be on the same footing...in fact, my money is on the Xbasic script since it can do the calcs with a single record scan. I still submit that the total() function, the DBsum function, as well as a user-script, are all on the same footing with speed, except that 1.) with the DBsum function, a developer is not guessing as to whether an index will be used, 2) an Xbasic script can make a single pass through the records and do all the calcs necessary, and 3) your evaluation of DBsum does not consider the query performed by the engine. Yes, in simple examples, the total function might beat the DBsum function on form or report based totals.....in other cases, it might not...in all cases, one can not rule out using Xbasic which very well would probably beat or at least equal both.

[CraigSchumacker]

RE: Invoice

At no time did I attempt to speak for you. Those were your words. Not mine. I merely asked for some clarification.

[Ken L Herrold]

RE: Invoice

Oh yeah you mother wears combat boots.

For God's will you all quit bickering!

[James Peterson]

RE: Invoice

And the amazing thing is that I just had a simple, or maybe not so simple, question that started all of this. Now I have a headache.

I now have a working copy of a form used for invoice entry. The total() function is used in the form to sum a calculated field. These calculated fields are then passed along to the report, using the copy and paste feature, that prints the invoice. So far it works fine, but has not been extensively tested.