4 Replies Latest reply on Mar 23, 2011 9:19 AM by philmodjunk

    Cant decide tables and relationships



      Cant decide tables and relationships


      Hello,I have to create database relations for the start but I cannot decide how to do that because i have so much variable field. Can anybody help me for the decide?

      Tables: Banks, Accounts, Transactions

      I have 7 different bank accounts and also 3 different exchanges (USD, EURO and birr) 

      I have to organise transactions for daily and report them as well.. 

      I did:

      Banks: Bank ID(autonumber for relations), Bank Name

      Accounts: Account ID (autonumber for relations), Main account, sub account

      Transactions: ID (autonumber), Date, Description, Document No, debit USD, credit USD, debit Euro, credit Euro, debit BIRR, credit BIRR, Bank ID (for relations) and Accounts ID (for relations)

      So now I have some variable as bank names, main and sub accounts, dates.. I couldnt decide how to create relations for portal tool..

      It must be like account system.. I have to write dates on form and below portal part must show transactions with related date but also i have to use that portal for records change add etc.. in that portal bank names, main account and sub accounts are variables ( like popup menu or drop down list)...

      Any idea? What must be common field? is that date? Please help to me with suggestions.


        • 1. Re: Cant decide tables and relationships

          Banks::BankID (auto-entered serial no) = Transactions::BankID (number field)
          Banks::BankID = Accounts::BankID
          Accounts::AccountsID = Transactions::AccountsID  (Remove BankID from transactions and put it in Accounts instead)

          In the previous relationship, enable "allow creation of records via this relationship" for Transactions.

          Accounts::Sub AccountID = Sub Account::AccountsID  (Sub Account is a table occurrence of Accounts)

          That's the basic structure to your tables. Table occurrences are what we call the "boxes" used to define relationships between tables in Manage | Database | Relationships. To make a new table occurrence of an existing table, click it's table occurrrence box to selecte it, then click the button with two green plus signs to make a copy of of it. Then you can double click it to rename it as needed.

          Now for your portal:

          We need a relationship where you can control what transaction records are visible in the portal. We could use the pop ups to directly control what records appear in your portal, but let's try a simpler approach to start:

          Go to the Accounts Layout and add a portal to Transactions on it. This portal, as currently set up, will show all transactions for that account for all dates. If you use a date to filter this portal, you can limit the list of specific records to a specific date or range of dates. To select a different account, you can simply perform a find for the appropriate account record which enables you to select the account or sub account for which you need to edit transactions.

          Exactly how you filter the portal records by date will depend on your needs here. Do you want to see all transactions on or after a specified date? All transactions for one specific day? All from date1 to date2?


          • 2. Re: Cant decide tables and relationships

            Dear Phil,

            Thanks for your reply and sorry for my late reply, I was abroad..

            I will try your way and let you know the result and also your questions..

            • 3. Re: Cant decide tables and relationships

              Dear Phil,

              I have to see all transactions in specific date and also it must allow me to change, add records...

              Here is my table occurrence :

              • 4. Re: Cant decide tables and relationships

                If you have FileMaker 11, you can use a portal filter to limit the transactions shown to a given date. Define a date field in Accounts, selectedDate and then you can use this filter expression:

                Accounts::SelectedDate = Transactions::Date

                To limit the transactions shown to just the specified date. You can set an auto-enter calculation on Transactions::Date to enter the date from SelectedDate so that new entries in the portal automatically get the same date as that specified in the portal filter.

                If you use this approach, put an OnObjectExit script trigger on SelectedDate and set it to run this script to force the portal filter to update each time you select a different date:

                RefreshWindow [Flush Cached Join Results]