× Discuss on Template programming, jBASE programming, Enquiries, No-File enquiry, Enquiry routines, Version, Version routines, Menus, Abbriviations, Creating local reference fields, Fast path enquiries, Creating charts and graphs, Generating Reports, Deal slips, Straight through processing, Multi Company and Multi Book setup, Tabbed screens, Composite Screens, T24 API, etc...

TOTALS IN ENQUIRY

  • AMMAR.FAIDI
  • Topic Author
  • Offline
  • Elite Member
  • Elite Member
More
11 years 7 months ago - 11 years 7 months ago #12279 by AMMAR.FAIDI
TOTALS IN ENQUIRY was created by AMMAR.FAIDI
dear All

how can i get the totals for sum of accounts per customer , e.g assume that i have 10 customers and for each one i have 3 accounts , how can i get the balance total for each of the customers in an enquiry

thnx
Last edit: 11 years 7 months ago by AMMAR.FAIDI. Reason: missing words

Please Log in or Create an account to join the conversation.

  • jpb
  • jpb's Avatar
  • Offline
  • Moderator
  • Moderator
  • TAFj-R20 - 'unix'
More
11 years 7 months ago #12281 by jpb
Replied by jpb on topic TOTALS IN ENQUIRY
What should the enquiry look like?

one line per customer showing only the total
or
list all accounts with its balance and a total underneath

total in local currency ?

Please Log in or Create an account to join the conversation.

  • AMMAR.FAIDI
  • Topic Author
  • Offline
  • Elite Member
  • Elite Member
More
11 years 7 months ago #12287 by AMMAR.FAIDI
Replied by AMMAR.FAIDI on topic TOTALS IN ENQUIRY
one line per customer

Please Log in or Create an account to join the conversation.

  • jpb
  • jpb's Avatar
  • Offline
  • Moderator
  • Moderator
  • TAFj-R20 - 'unix'
More
11 years 7 months ago - 11 years 7 months ago #12291 by jpb
Replied by jpb on topic TOTALS IN ENQUIRY
This should show what you expect . . . change the LCY accordingly ;-)
     ENQUIRY........... ACC.SUM.BY.CUS
 -------------------------------------
   1 PAGE.SIZE ........ 4,19
   2 FILE.NAME......... ACCOUNT
   3. 1 FIXED.SELECTION CUSTOMER NE ""
   4. 1 FIXED.SORT..... CUSTOMER
  14. 1 FIELD.NAME..... CUSTOMER
  15. 1. 1 OPERATION... CUSTOMER
  17. 1 LENGTH.MASK.... 10R
  35. 1 SINGLE.MULTI... S
  14. 2 FIELD.NAME..... LCY
  15. 2. 1 OPERATION... "USD"
  14. 3 FIELD.NAME..... BREAK.CUS
  15. 3. 1 OPERATION... B C CUSTOMER
  14. 4 FIELD.NAME..... DISP.CUS
  15. 4. 1 OPERATION... F CUSTOMER
  16. 4 COLUMN......... 3
  17. 4 LENGTH.MASK.... 10R
  21. 4 DISPLAY.BREAK.. BREAK.CUS                                               
  14. 5 FIELD.NAME..... CUS.NAME                                                
  15. 5. 1 OPERATION... F CUSTOMER                                              
  16. 5 COLUMN......... 12                                                      
  17. 5 LENGTH.MASK.... 35L                                                     
  18. 5. 1 CONVERSION.. L CUSTOMER,SHORT.NAME                                   
  20. 5 TYPE........... L                                                       
  21. 5 DISPLAY.BREAK.. BREAK.CUS                                               
  14. 6 FIELD.NAME..... CURRENCY                                                
  15. 6. 1 OPERATION... CURRENCY                                                
  17. 6 LENGTH.MASK.... 3L                                                      
  35. 6 SINGLE.MULTI... S                                                       
  14. 7 FIELD.NAME..... ONL.ACT.BAL.CALC                                        
  15. 7. 1 OPERATION... ONLINE.ACTUAL.BAL                                       
  17. 7 LENGTH.MASK.... 19R                                                     
  35. 7 SINGLE.MULTI... S                                                       
  14. 8 FIELD.NAME..... ONL.ACT.BAL                                             
  15. 8. 1 OPERATION... F ONL.ACT.BAL.CALC                                      
  17. 8 LENGTH.MASK.... 19R                                                     
  20. 8 TYPE........... CCY CURRENCY                                            
  35. 8 SINGLE.MULTI... S                                                       
  14. 9 FIELD.NAME..... CCY.BAL                                                 
  15. 9. 1 OPERATION... CALC CURRENCY : ONL.ACT.BAL.CALC                        
  14.10 FIELD.NAME..... ONL.BAL.LCY                                             
  15.10. 1 OPERATION... F CCY.BAL                                               
  17.10 LENGTH.MASK.... 19R                                                     
  18.10. 1 CONVERSION.. @ E.EQUIV.LCCY                                     
  20.10 TYPE........... CCY LCY                                                 
  14.11 FIELD.NAME..... SUM.BAL.LCY                                             
  15.11. 1 OPERATION... TOTAL ONL.BAL.LCY                                       
  16.11 COLUMN......... 41                                                      
  17.11 LENGTH.MASK.... 19R                                                     
  20.11 TYPE........... CCY LCY                                                 
  21.11 DISPLAY.BREAK.. BREAK.CUS                                               



    SUBROUTINE E.EQUIV.LCCY
****************************************************************
    $INSERT I_EQUATE
    $INSERT I_COMMON
    $INSERT I_ENQUIRY.COMMON
****************************************************************
* ccys & amts
    CCY1=O.DATA[1,3]
    AMT1=O.DATA[4,100]
    CCY2 = LCCY
    AMT2 = ""
    RATE = ""

* convert
    IF CCY1=CCY2 THEN
        AMT2=AMT1
    END ELSE
        CALL EXCHRATE("1",CCY1,AMT1,CCY2,AMT2,"",RATE,"","",RET.CODE)
        IF ETEXT THEN AMT2 = ""
    END

* result
    O.DATA = AMT2

    RETURN
****************************************************************
END
Last edit: 11 years 7 months ago by jpb.
The following user(s) said Thank You: armin

Please Log in or Create an account to join the conversation.

  • AMMAR.FAIDI
  • Topic Author
  • Offline
  • Elite Member
  • Elite Member
More
11 years 7 months ago #12302 by AMMAR.FAIDI
Replied by AMMAR.FAIDI on topic TOTALS IN ENQUIRY
dear jpb
it worked fine but i have one issue that is the totals are accumulative how can i zeroise it after displaying each total

B.R

Please Log in or Create an account to join the conversation.

  • jpb
  • jpb's Avatar
  • Offline
  • Moderator
  • Moderator
  • TAFj-R20 - 'unix'
More
11 years 7 months ago #12304 by jpb
Replied by jpb on topic TOTALS IN ENQUIRY
Dear Ammar,

in my env. with R08 and desktop the example is working OK, the TOTAL is cleared when the customer changes (as defined in 21.11).

I haven't check with R11 and browser...

Please Log in or Create an account to join the conversation.

  • AMMAR.FAIDI
  • Topic Author
  • Offline
  • Elite Member
  • Elite Member
More
11 years 7 months ago #12305 by AMMAR.FAIDI
Replied by AMMAR.FAIDI on topic TOTALS IN ENQUIRY
ok i'll try it again
thnx

Please Log in or Create an account to join the conversation.

More
3 years 2 months ago #23234 by CryptoGuy
Replied by CryptoGuy on topic TOTALS IN ENQUIRY
Hi jbp,

I was looking for such enquiry, and I came through this old post :), I'm trying to do something similar to show total balances of categories using CAL table, so I want one line per category and show total balance. I created an enquiry and it shows total balance for each category but it displays as many lines as the number of consol keys with this category.
What am I doing wrong? How to show only one line per category?


Thanks.

Please Log in or Create an account to join the conversation.

  • silvergem
  • silvergem's Avatar
  • Offline
  • Elite Member
  • Elite Member
  • TAFC|R12/R13, TAFJ|R20
More
3 years 2 months ago - 3 years 2 months ago #23235 by silvergem
Replied by silvergem on topic TOTALS IN ENQUIRY
Did you sort by field category?
Last edit: 3 years 2 months ago by silvergem.

Please Log in or Create an account to join the conversation.

More
3 years 2 months ago #23236 by CryptoGuy
Replied by CryptoGuy on topic TOTALS IN ENQUIRY
Yes I did.

Please Log in or Create an account to join the conversation.

More
3 years 2 months ago #23237 by CryptoGuy
Replied by CryptoGuy on topic TOTALS IN ENQUIRY
Here is my enquiry fields :

Page Size 4,19
File Name CONSOLIDATE.ASST.LIAB
Fixed Selection.1 BALANCE NE ""
Fixed Sort.1 CATEGORY
Field Name.1 CAT
Operation.1.1 CATEGORY
Column.1 1
Length Mask.1 35L
Single Multi.1 S
Field Name.2 BREAK.CAT
Operation.2.1 B C CAT
Column.2 3
Single Multi.2 S
Field Name.3 DISP.CAT
Operation.3.1 F CAT
Column.3 4
Display Break.3 BREAK.CAT
Single Multi.3 S
Field Name.4 BAL
Operation.4.1 BALANCE
Column.4 5
Length Mask.4 19R
Single Multi.4 S
Field Name.5 TOTAL.BAL
Operation.5.1 TOTAL BAL
Column.5 6
Length Mask.5 19R
Display Break.5 BREAK.CAT
Single Multi.5 S
Page Fields 2 1 4
Multi Fields 0
Break Fields 1 2
Process Breaks 2 3 5
Total Fields 1 5
Product AC Accounts
Curr No 21


and here is how the output looks :


CAT BREAK.CAT DISP.CAT BAL TOTAL.BAL
10001 '-100'
10001 '-1305'
10001 '-1200'
10001 '-2605'
1001 '-186.4'
1001 '-2800'
1001 '-30197.991'
1001 4194.5 -180
1001 '-34538295.157'
1001 '-4970'
1001 40306.207
1001 '-352'
1001 '-365'
1001 300
1001 108.888
1001 '-137'
1001 1250
1001 '-34535338.453'

Any ideas ?

Please Log in or Create an account to join the conversation.

  • silvergem
  • silvergem's Avatar
  • Offline
  • Elite Member
  • Elite Member
  • TAFC|R12/R13, TAFJ|R20
More
3 years 2 months ago #23238 by silvergem
Replied by silvergem on topic TOTALS IN ENQUIRY
I don't understand anything. It is much better to show this using ENQUIRY,INPUT command to better Visualize the fields

Please Log in or Create an account to join the conversation.

More
3 years 2 months ago #23242 by CryptoGuy
Replied by CryptoGuy on topic TOTALS IN ENQUIRY


Please see the attachment.
Attachments:

Please Log in or Create an account to join the conversation.

  • silvergem
  • silvergem's Avatar
  • Offline
  • Elite Member
  • Elite Member
  • TAFC|R12/R13, TAFJ|R20
More
3 years 2 months ago - 3 years 2 months ago #23243 by silvergem
Replied by silvergem on topic TOTALS IN ENQUIRY
It is correct that the balance appear on column "6". If you want it under BAL, you need to change to "5,+1"

COLUMN
Specifies the screen co-ordinates to display the enquiry field.
Defines the exact or relative screen position to display the field value. The following formats are accepted:

Column Action

'' Null - field will not be displayed.
CC Field will be displayed at column CC on the current line.
CC,LL Field will be displayed in column CC on line LL. (exact positioning, normally used for variable heading data).
cc,+nn Field will be displayed in column cc on the current line + nn lines (also supports -nn). Note that when a multi/sub value field is displayed. Each item will be displayed +nn lines from the last.
cc,+nnM Field will be displayed in column cc on the current line + nn lines (also supports -nn). When a multi/sub value field is displayed ONLY the first item will be displayed +nn lines, each subsequent item will be displayed on the next line.
P Used only in conjunction with break instructions - performs page 'throw' when break condition met.

Validation Rules
5 type A (alphanumeric) characters.

Last edit: 3 years 2 months ago by silvergem.

Please Log in or Create an account to join the conversation.

More
3 years 2 months ago - 3 years 2 months ago #23246 by DUBLIN
Replied by DUBLIN on topic TOTALS IN ENQUIRY
Hi CryptoGuy,

You need to remove column number in enquiry fields CAT, BREAK.CAT @ BAL i.e.

Field Name.1 CAT
Operation.1.1 CATEGORY
Column.1 1<---- make it blank
Length Mask.1 35L
Single Multi.1 S


Field Name.2 BREAK.CAT
Operation.2.1 B C CAT
Column.2 3 <---- make it blank .
Single Multi.2 S

and in here :
Field Name.4 BAL
Operation.4.1 BALANCE
Column.4 5 <---- make it blank
Length Mask.4 19R
Single Multi.4 S

It should give you a Total per Category.

But... you could have lines with the same CATEGORY but different currencies . Then your enquiry would show wrong numbers as you will sum "apples and oranges".

Field BALANCE holds value in original ccy.

May be you should use field that holds balance in LOCAL currency instead...
Last edit: 3 years 2 months ago by DUBLIN.

Please Log in or Create an account to join the conversation.

Time to create page: 0.158 seconds