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

    Cross reference? Beginner question



      Cross reference? Beginner question


      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

              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.

              • 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.