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.
- I'd create another Table Occurrence of the Loans table and call it "Loans_EUR".
- Then in my companies table I'd create a calculated text field called "EUR_c", set it to text with the result of "EUR".
- Then relate Companies to Loans_EUR with Companies::Comp_ID = Loans::Comp_ID and Companies::EUR_c = Loans::Currency.
- 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.
thank you for your kind attitude and guidance.
I'll try this approach and let you know how it worked.
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:
see no attachment...
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?
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.