tablesum() in a separate single row

[echase]

Sample database data (many Client_IDs):

Client_ID=4:
-PmtAmount: $1,000.00
-PmtAmount: $500.00

I have used tablesum() to sum the total amount of payments keyed off the Client_ID. This is in a Web Application -> Component -> Tabular Grid.

I want only one row/cell showing the tablesum() result to appear at the bottom of the group of rows that shows all of the payment amounts for each client, however it appears that I can only include that result as a column, with the same (correct) result appearing on each row.

i.e. IS:

------------------------------
| PmtAmount  |  Total Amount |
------------------------------
| $1,000.00  | $1,500.00     |
------------------------------
| $500.00    | $1,500.00     |
------------------------------

SHOULD BE:

-----------------
| PmtAmount     |
-----------------
| $1,000.00     |
-----------------
| $500.00       |
-----------------
| Total Amount  |
-----------------
| $1,500.00     |
-----------------

I am unable to edit the HTML for that Component (of course) to do that.

Any tips? Thanks.

[Al Buchholz]

Re: tablesum() in a separate single row

Is this what you want?

[echase]

Re: tablesum() in a separate single row

That would be ideal, however the field is a calculated field (to format values as dollar amounts), and my Summary Values only display Maximum, Minimum, First and Last ... no Total. What would I need to do to get Total into that list?

My current workaround is to create another component containing just the total values, limited to a single row and linked to the PmtAmount record id. It does the job, but elegant it ain't. :)

[Al Buchholz]

Re: tablesum() in a separate single row

Eric

You calc field is resulting in a character expression instead of a numeric expression. So you only get summary functions that make sense for a character field...

Instead of formatting with a calc field, why not using the formatting option for the numeric field?

[echase]

Re: tablesum() in a separate single row

That was my first choice, however there is some issue. The data was imported from an MSAccess97 database table, and when I originally tried to use the formatting option I received an error (follows) that made me look closely at the field type. After importing, the original field type was "ExponentNumeric". I changed it to "Numeric", but I still get the same error when trying to use the default formatting tool:

Script: UI_DLG_BOX: Pre-defined Formats line: 3
        currentDisplay = a_format[indx].display
        variable type mismatch

No pre-defined formatting options appear after acknowledging the error, which is why I ended up using calculated values to get my dollar formatting. (BTW, changing the field type to Numeric dropped any characters in the decimal places, i.e. 6000.15 ended up as 6000, so I'm not sure that's a good option for me, anyway.)

Thank you very much for your continuing help, Mr. Buchholz.

[Al Buchholz]

Re: tablesum() in a separate single row

Sounds like we should get that problem resolved...

I've sometimes changed the ExponentNumeric and then had a numeric with a length of 20. Max for dbase is 19 - so I lowered to 19 and the other problems went away.

I think the numerics are still there, the formatting just needs to be set.

Maybe your calc field should just be set equal to the access numeric field and then see if the formatting works.... might be less changing of structure....

[echase]

Re: tablesum() in a separate single row

I'll keep experimenting with the field types, but I'm guessing it's more of a problem with the way Access hands out the data than with how Alpha5 interprets it. Thank you, again.

<edit>
It's interesting that the Total Paid calculated data used in the image below is gathered from the raw Amount field, not the calculated value, and in the display I did use the default formatting tool to format it:

TotalPmt = tablesum("payments.dbf","Client_id="+Client_id,"Amount")

I'm trying your suggestion re: setting the calc field = the raw amount field.
Update: Nope. Same deal.

Interesting because tablesum() seems to have performed some modification of the field data type that a 'normal' calulation did not. I also tried: Amount1 = val(substr(Amount,1)) and a few variations on that with no luck. While the resulting field offers the Summary Total option, the values are all 0, so I can't use it.
</edit>

Here's what I ended up with (inelegance, and all):

[echase]

Re: tablesum() in a separate single row

To bring some out-of-thread info in here for everyone else:

The original MS Access97 field data type is defined as "Currency", which I am guessing is what is causing the difficulty. Still guessing, Alpha5 may not be able to interpret that type of field as a number and is falling back to a default string interpretation, from whence the issues come.

I imported the data into Alpha5 using the ADO Import Genie, which I believe is my only option for getting table data from an Access db, and which does not offer the option of modifying the data type on a per field basis.

[echase]

Re: tablesum() in a separate single row

It's definitely related to MS Access97's field formatting, not necessarily the data type.

I copied the original Access97 database (still in use, so I couldn't work on the live copy), and modified the field in question as a test. I first modified it to be a Number type, but included additional instructions to use Currency-type formatting. (Field data type = Number but with special display characteristics.) Same issues. I then modified it to be simply a Number type with two decimal places and voila ... Alpha5 treats it like a Number.

So, when we finally finish with the Alpha5 design and transition over to that system, away from the Access97 database, I will make that table modification and use the more simple formatting tool and Summary Total tool to do the job.

Thanks, again!

[echase]

Re: tablesum() in a separate single row

Sorry ... attachment idea didn't work out. :) Never mind ...

[echase]

Re: tablesum() in a separate single row

I've been doing some further experimenting.
This is way too much info, so I begin with an Executive Summary:

Success!
1) Import raw data
2) Convert field type => Numeric, 15.2
3) Convert again => Character, 15.0
4) CalcFld: Amount1 = val(substr(Amount,1))
5) Amount1 = money format, Summary Total ok

On to the too much info:

These are my results using the following system configs:
- Alpha5 v7 Build 4130, System Addins Build 3033 Trial
- Original DB: Access97 => WinNT Server 4.0 (fully patched)(Access97)
- Alpha5v7-1: Win98SE (fully patched)(Access97)
- Alpha5v7-2: WinServer2003 R2 (fully patched)(No Access installed)
- Importing direct from Original DB (no ODBC specified)

Looking at the Field Rules for the Amount field (post-import, pre-conversion) shows a Type of "T??: 8" where ? = gobbledeygook characters. Note that many of the data actually in the field are longer than 8 chars, but this seems to have no affect re: the A5 imported definition. I presume it is not reading the definition correctly both in it's Type and Length values.

I smacked my head and changed the Type to Character, and was then able to calculate a field using val(substr(Amount,1)) that offered both the default money formatting and the Summary Total options!

Unfortunately, I couldn't retain the decimal place data with this type of conversion. (I didn't guess that would happen, as I figured converting to Character should have retained everything in each cell.)

Using the Numeric Type with 2 decimal places did retain the decimal place data, and seemed to stabilize the Type as I viewed it both in Design view and Field Types view, however the Web Component interface continues to treat it as text, and using the calculation (above) resulted in an "Error 1 - Invalid field in field list".

So I started again, imported, converted to Numeric with 2 decimal places, and then converted the Field Type to Character! I then used the val(substr(...)) calculation to get a proper field that (a) had the right decimal place data, (b) was a valid field and (c) resulted in a field I could use the default money formatting calculation and Summary Total with.

It seems that the Alpha5 Enterprise Edition is able to handle the Type conversion during the import process, without all of the rigamarole I performed, so that's likely a cleaner solution.

Thanks to Al Buchholz for help above and beyond the call!