Can you post a more detailed description? I'm not sure I follow the relationship between currencies and transactions.
An example might help.
Accounts table has a field called AccountNumber
Transactions has 4 fields:
- TransAcc which holds the account number that is linked to AccountNumber in the accounts table
- Amount, which holds the amount of the transaction
- TotalAmount (Summary field that calculates the total of the amount)
- TransCurr which holds the name of the currency in which the transaction is deneminated
Currency table has a field called CurrencyName which is linked to TransCurr in the Transactions table.
I would like to have a layout for the Accounts table, on which there is a portal that displays all the currencies in which there is a transaction and the total amount of all the transaction in that currency.
Your relationships appear to be:
Accounts::AccountNumber = Transactions::TransAcc
Transactions::TransCurr = Currency::CurrencyName
Try this instead:
Accounts::AccountNumber = Currency::AccountNumber
Curency::AccountNumber = Transactions::TransAcc AND
Currency::CurrencyName = Transactions::TransCurr
Define a calculation field in Currencty, TotalTrans as Sum(Transactions::Amount)
Place a portal to Currency on your layout that includes TotalTrans to show the total amounts of each currency for the given account.
Note: You may need to create new table occurrences for this if you need your current relationships for data entry or other purposes.
You may need to create a new table to use in place of currency in the above example if you use this currency table in ways that make adding an account number field to it impossible. A summary report based on your Transaction table could also report this information without a portal or the new relationships.
Thanks for that.
Yes those were the relationships that I had in mind.
I agree that your solution works and would give me the result I want. The trouble is if I have 20 currencies and 10 accounts then it seems to me that there has to be 200 entries in the currencies table. Obviously I don't want to do that by hand, and I also don't want to have to add another 20 lines every time I add a new account in the accounts table. Is there a way of automatically generating the extra lines in the currency by "multiplying" it in a cartesian sense with the accounts table?
The relationships I described were for reporting purposes. You'll likely need a separate set for data-entry. I tried several options before I came up with this approach.
Define additional table occurrences of Currencies and Transactions like this:
Accounts::AccountNumber = Transactions 2::AccountNumber
Transactions 2::TransAcc = Currencies 2::AccountNumber AND
Transactions 2::TransCurr = Currencies 2::CurrencyName
From accounts to transactions 2, allow creation of records via this relationship for Transactions 2.
From Transactions 2 to Currencies 2, allow creation of records via this relationship for currencies.
Place a portal to Transactions 2 and attach an OnObjectSave script with a single script step: Set Field [Currencies 2::CurrencyName ; Transactions 2::TransCurr] to Transactions 2::TransCurr.
This script trigger will automatically generate your currency records as you make entries into the transactions 2 based portal.
A better approach would probably be to simply construct a summary report based on transactions that groups your records by Account ID and currency names to produce the same data with summary fields.
Here's a link to a simple tutorial on setting up summary reports that you may find useful:
Creating Filemaker Pro summary reports--Tutorial
Thank you very much for that Phil, I'll give it a shot.
The reason I don't just want a summary report is because I actually need to handle the balances of the accounts in each currency, "viewing" them through a summary is not enough.
If you are using Filemaker 10 or newer, you can edit records while they are pulled up in a summary report.