10 Replies Latest reply on Oct 23, 2015 8:52 AM by dnechytailo

# Very simple subtotals

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

Thank you!

• ###### 1. Re: Very simple subtotals

Good morning.

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

Don't apologize. We all start somewhere and we all need advice. FileMaker is meant to be easy to pick up and start using, but it requires some guidance to become an expert. People are on here to guide you on your path into FileMaker.

What you need can be handled in a few ways. The function Sum ( ) can find the sum of a field in a related table based on the relationship. With your current relationship from Companies to Loans, if you tried to do Sum  (LOANS::Amount) you'd get the sum of EVERY RECORD because that relationship sees every loan that is related to the company.

So you'd need to create new relationships from Companies to Loans and use each relationship to 'see' only one currency.

1. I'd create another Table Occurrence of the Loans table and call it "Loans_EUR".
2. Then in my companies table I'd create a calculated text field called "EUR_c", set it to text with the result of "EUR".
3. Then relate Companies to Loans_EUR with Companies::Comp_ID = Loans::Comp_ID   and Companies::EUR_c = Loans::Currency.
4. Create a calculated field called Companies::Sum_EUR and set its result to Sum (Loans_EUR::Amount).

Because of that 2nd value in the relationship, that sum will only sum those with the "EUR" currency.

• ###### 2. Re: Very simple subtotals

Dear Jeremy,

thank you for your kind attitude and guidance.

I'll try this approach and let you know how it worked.

Yours,

Dmytro

• ###### 3. Re: Very simple subtotals

good answer from Jeremy (as always!) I have another option that you might not have considered: Filtered portals. I will use the standard Summary Field (from records) in a filtered portal to also show these "groupings". You'd need 3 filtered portals based on the Currency. No additional relationships need be created!

See this article will help explain how I use 1 row filtered portal to show summary fields:

http://filemakerhacks.com/2012/09/13/aggregates-in-filtered-portals/

beverly

• ###### 4. Re: Very simple subtotals

subsummaries is the best thing.

They also make you independent of currency.

See attached.

• ###### 5. Re: Very simple subtotals

see no attachment...

• ###### 6. Re: Very simple subtotals

Great technique. I always hesitate just a bit before replying with my own thoughts. I've learned that no matter the technique, there's always another one out there. It never ends, huh?

• ###### 7. Re: Very simple subtotals

Jeremy's solution helped! Thanks a lot!

The only minus is multiple occurrences of same table in relationship graph, which you need to create for every currency in your database, as well as multiple technical fields in the table...

I'll try Beverly's option as well and see how it works. Will let you know then.

Yours,

DN

• ###### 8. Re: Very simple subtotals

It't there... here once again:

• ###### 9. Re: Very simple subtotals

Dear siplus,

maybe I'm doing something wrong, but I see no attachment in this thread...

• ###### 10. Re: Very simple subtotals

Here how it worked in both Jeremy's and Beverly's solutions (Jeremy's on the right, Beverly's - on the left):