3 Replies Latest reply on Nov 3, 2009 10:43 AM by martinpaulrice

    Confused by table relation

    martinpaulrice

      Title

      Confused by table relation

      Post

      Hi, I'm a new FM user. I'm working on an Intel iMac with OSX 10.6.1

       

      In a budget and expense tracking app I'm trying to put together I have two tables: Budget and Transactions.

       

      In the Budget table I have the following fields:

       

      Account Name; Account Type; Budgeted Amount; Current Balance; Difference; Budget Month

       

      In the Transactions table I have the following fields:

       

      Date; Description; Comment; Check Number; Amount; From Account; To Account

       

      The From and To Accounts will contain account names from the Account Name field in the Budget table. It didn't seem to make much sense to duplicate those account names every time I entered a transaction. So I created a relationship from Budget to relate Account Name to the From and To Accounts in Transactions.

       

      Then, I (tried to) set up the From and To Accounts in transactions to be drop downs that displayed the Account Names from Budget.

       

      I seem to have two problems:

       

      Sirst, in the transactions table view, which is where I prefer to do my entries, I don't have From Account and To Account fields. There just a field titled Budget::Account Name. And even there, the drop down doesn't drop down and I can't get the cursor to go into the field.

       

      Second, in the form view, I can see both the From and To Account fields, but nothing drops down and I can't get the cursor in those fields either.

       

      It seems clear that I have these set up wrong and I don't understand where the error is.

       

      I hope someone can figure out from this description where I've gone wrong and give me a clue for getting it right.

       

      Thanks.

       

      Martin

      Signal Mountain, TN 

        • 1. Re: Confused by table relation
          martinpaulrice
            

          Well, it's clear that I misunderstood what a relationship is and finally realized that I didn't have a relationship to make. What I did was simply make two value lists (FromAccount and ToAccount) for the Transactions table with the values from the Account Name field in the Budget table. All works fine now.

           

          I added two fields to the Transactions table: FromAccountType and ToAccountType. I could get these by setting up two more value lists as I did for the Account Names, but then I'd have to pick them from the drop down menus every time. It seems to me that because each record in the Budget table has an Account Type along with the Account Name, I ought to be able to get the FromAccountType and ToAccountType to fill in automatically as soon as I enter the From Account Name and the To Account Name. But . . . I just don't have any idea how to do this.

           

          Any hints would be greatly appreciated.

           

          I forgot to mention in my first posting that I'm working with FM Pro 10

           

          Thanks,

          Martin 

          • 2. Re: Confused by table relation
            philmodjunk
              

            Every data field has a list of possible Auto-enter options you can select for that field. Go To Manage | Database | Fields, find the field and double-click it to pop up the field options dialog. On the Auto-enter tab, there's a "Looked Up Value" option that can copy data from a field in a matching related record into your data field--such as account type.

             

            This assumes that you've used Manage | Database | Relationships to create valid relationships that link your two tables.

             

            Transactions::AccountFrom = Budget::AccountName

             

            Transactions::AccountTo = Budget 2::AccountName

             

            To create the second relationship, select Budget and click the button with two green plus signs in the bottom left corner of the Manage | Database | Relationships dialog.

             

            Using the above examples, you'd refer to Budget to set up a look up for AccountTypeFrom and Budget 2 to set up a look up for AccountTypeTo.

             

            An alternative approach is simply to create the above relationships and then place Budget::AccountType and Budget 2::AccountType on your transactions layout.

            • 3. Re: Confused by table relation
              martinpaulrice
                

              Phil, thank you so very much for this. I followed your instructions for building the relationship and then making the fields auto lookups and it worked perfectly. Not only did it solve the immediate problem, but it also gave me a start at building an understanding of relationships. (That should make my wife happy 

               

              I really appreciate the help.