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

    Cross reference? Beginner question

    AlexR

      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
          philmodjunk

          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
            AlexR

            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
              philmodjunk

              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
                AlexR

                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
                  philmodjunk

                  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
                    AlexR

                    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
                      philmodjunk

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