Very simple subtotals

[Dmytro]

Hello everyone.

 

I am new to FM Pro, so I bring my apologies if my question turns out simple and stupid... 

 

Table 1: Companies:

 

Comp_Code

...

 

Table 2: Loans:

 

Comp_Code

Loan_ID

Amount

Currency

...

 

Table 3: Currencies

 

Curr_Code

...

 

I have a portal showing all loans per company based on Loans table (related to Companies through Comp_Code field):

 

Comp_Code          Loan_ID          Amount          Currency

AAA                       1                     100000           USD

AAA                       2                     50000             USD

AAA                       3                     200000           EUR

AAA                       4                     60000             EUR

AAA                       5                     300000           GBP

AAA                       6                     70000             GBP

 

All I want is to show subtotals of loans per Company by currencies in the bottom of the same layout (portal):

 

Total loans USD: 150000

Total loans EUR: 260000

Total loans GBP: 370000

 

I've played with calc fields and summary fields in multiple ways, but didn't get any good result.

 

I know it must be simple, but I am old Access user, which does not know all FM tricks so far  

 

Rely on your help...

 

Thank you!

[siroos12]

On "Loans" Table, make calculation fields for each currency, the calculation would be something like this:

 

For USD:

if(Loans::Currency="USD";Loans::Amount;0)

 

 

For EUR:

if(Loans::Currency="EUR";Loans::Amount;0)

 

and so forth, this way you can add a summary field for each of these calculation fields to show up that specific currency subtotals.

 

Hope this make sense.

[LaRetta]

You might wish to also check this demo file by Comment:

I did want to mention also, that it would be better to use a sub-summary report.  You could stay on the Company form view record and open a smaller window which would:

Go To Related Record ( from Loans, match current record only ; using sub-summary report layout - open new window ).  And then sort by break field which would be your Currency.

In your Loans table, add a summary field which is: Total Of Currency Amount.  In your report, add a leading part based upon that break field ( Currency ) and place your new summary field inside the leading part.  After the User is done viewing, close the window.  You can use Modal Window which keeps it locked on top until you dismiss it.  Be sure to attach a button (close Window) to the top of the Loans layout first so you can have a way out of that modal window.

This method would be the fastest and Users are comfortable with popup windows.  Comment's method is perfect if one really needs to use a portal for display.  :-)

Edited October 24, 2015 by LaRetta

[LaRetta]

I neglected to mention ,,, to produce only the totals ( notice I also corrected it to Total of Amount ) delete the body of the report, place the Currency and Amount summary field in the leading part so only the totals per Currency display.