7 Replies Latest reply on Mar 30, 2010 8:33 AM by philmodjunk

# Cross reference? Beginner question

### Title

Cross reference? Beginner question

### Post

Filemaker v10

OSX 10.6

3 tables:

- A table of accounts

- A table of currencies

- A table of transactions. Each transaction has an amount which is associated with an account and denominated in a currency.

I would like to be able to select an account and see a portal of all the currencies that this account has transactions in and the total amount of the transactions in each currency for this account.

• ###### 1. Re: Cross reference? Beginner question

Can you post a more detailed description? I'm not sure I follow the relationship between currencies and transactions.

An example might help.

• ###### 2. Re: Cross reference? Beginner question

Hi Phil,

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.

• ###### 3. Re: Cross reference? Beginner question

Accounts::AccountNumber = Transactions::TransAcc

Transactions::TransCurr = Currency::CurrencyName

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.

• ###### 4. Re: Cross reference? Beginner question

Hi Phil,

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?

• ###### 5. Re: Cross reference? Beginner question

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

• ###### 6. Re: Cross reference? Beginner question

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.

• ###### 7. Re: Cross reference? Beginner question

If you are using Filemaker 10 or newer, you can edit records while they are pulled up in a summary report.