Total of a Browse Column

F

fdumlao

New Member
Hi all, this is my first post, however I have been using this site as a resource for almost a month now.

I am an experienced programmer, but am new to Progress, and have run into an interesting challenge.

We are running Progress 9.1d on Windows. Our application is UNIFI if anyone here is familiar, however I don't think that makes a difference here.

I have a browse that pulls data from a database, and one of the fields it pulls is a dollar amount. The browse updates based on criteria entered in other widgets in the frame, such as date ranges and branch numbers. I need to get a total of the amount column, and it needs to update whenever the browse is re-filtered. I can't quite figure out how to do it. I have figured out how to get from the query the number of results returned (NUM-RESULTS("query-name")), but I dont see a way to iterate through the query results or the browse rows to pull the values I need.

Here is my code for the browse:

Code: 
/* Define Queries                                                  */
DEFINE QUERY brow-lt-master FOR tt-lt-query.
/* Browse Definitions                                              */
DEFINE BROWSE brow-lt-master
    QUERY brow-lt-master NO-LOCK DISPLAY
        tt-lt-query.borr-name
        tt-lt-query.stage
        tt-lt-query.branch
        tt-lt-query.lock-status
        tt-lt-query.purpose
        tt-lt-query.closing
        tt-lt-query.product
        tt-lt-query.lt-acnt
        tt-lt-query.amount
        (tt-lt-query.amount + 
    ENABLE tt-lt-query.borr-name
    WITH NO-ROW-MARKERS SEPARATORS SIZE 106 BY 9.52.
RUN pop-browse.
 
PROCEDURE pop-browse:
    RUN  build-query.
    QUERY brow-lt-master:QUERY-PREPARE(browse-query).
    QUERY brow-lt-master:QUERY-OPEN().
    
END.
PROCEDURE build-query:
    browse-query = 'FOR EACH tt-lt-query NO-LOCK WHERE '.
    IF lv-branch <> 0 THEN
        browse-query = browse-query + 'branch = ' + STRING(lv-branch) + ' AND '.
    browse-query =  browse-query + ' stage >= ' + STRING(lv-from-stage) + ' ' +
        'AND stage <= ' + STRING(lv-to-stage) + ' '.
    IF lv-from-date <> "" THEN
        browse-query = browse-query + 'AND stage-date >= "' + lv-from-date + '" '.
    IF lv-to-date <> "" THEN
        browse-query = browse-query + 'AND stage-date <= "' + lv-to-date + '" '.
    
    IF lv-sort-by = "" THEN
        browse-query = browse-query + 'USE-INDEX lt-acnt BY borr-name'.
    ELSE
        browse-query = browse-query + 'USE-INDEX lt-acnt BY ' + lv-sort-by .
END.
 
I think I came up with a solution, tell me what you think. It appears to be working...

Code: 
DEFINE VAR iRow AS INTEGER NO-UNDO.
fiTotalDollars = 0.00.
    DO iRow = 1 TO brow-lt-master:NUM-ITERATIONS:
        brow-lt-master:SELECT-ROW(iRow).
        fiTotalDollars = fiTotalDollars + tt-lt-query.amount.
    END.
    DISPLAY fiTotalDollars WITH FRAME f1.
 
I'd do it differently ...

But I'd also like to know if that's a temp-table you're querying ?

And if it was filled in the proc prior to the query ?
 
It is a temp table that I am querying. The table is filled with all records "belonging" to a user. Initially, all records are displayed in the browse. The user has a couple of combo boxes and some fill ins that they can use to filter the data, so for example the user can see all of the loans that they put in that closed in december. So the browse data is changing alot when the user is using this screen.
 
Ok. My idea does NOT work. NUM-ITERATIONS only returns the value of the rows that are currently visible in the browse, and since my browse has a couple hundred records displayed, but only 9 visible at any time, this is WAY off.

I tried using the NUM-RESULTS('query') function, but it also does not work. see below:

Code: 
 [LEFT]DEFINE VAR iRow AS INTEGER NO-UNDO.
fiTotalDollars = 0.00.
    DO iRow = 1 TO NUM-RESULTS("brow-lt-master"):
        brow-lt-master:SELECT-ROW(iRow).
        fiTotalDollars = fiTotalDollars + tt-lt-query.amount.
    END.
    DISPLAY fiTotalDollars WITH FRAME f1.[/LEFT]

This generates an error informing me that the row selected is not in the browse.​
 
You must log in or register to reply here.
Back
Top