3 Replies Latest reply on Apr 15, 2011 10:10 AM by chcsep

    Relationship not working properly between two tables



      Relationship not working properly between two tables


      I'm at wit's end trying to figure out what the problem is here. It used to work and something broke. I'm not the primary user of this database, which complicates things further.

      Our database has two tables, A and B, which are connected in a relationship by the "ID" field.

      Layout 1 uses data from tables A and B, and Layout 2 uses data from table B only.

      Layout 1 correctly connects the data from tables A and B, but if I change any view (i.e. search/sort/filter/go to next record) this change is not reflected in Layout 2.

      I know for a fact that the database previously connected the two. So for example, if I performed a search that brought up the records for Adam and Bill in Layout 1, Adam's and Bill's records would also show up from table B in layout 2. If I moved from Adam's record to Bill's in layout 1, I would be on Bill's record when I went over to layout 2.

      The database is used in FMP 8, but I also have access to FMP 11 on a different computer, and I get the same problem.

      Can anyone help me troubleshoot this? The database is vital to our operations and I can't figure out for the life of me what changed or how to fix it.

      Thanks in advance.

        • 1. Re: Relationship not working properly between two tables

          Have you double checked which Table each layout is showing records from?

          It sounds like this is the culprit.  it also sounds like you want both of your layouts to show records from Table B.

          • 2. Re: Relationship not working properly between two tables

            Think Table Occurrence, rather than Table. Each Table Occurrence is a box in Manage | Database | Relationships and you can have more than one Table Occurrence Box for the same table. In Layout Setup | Show Records From, you can see which table occurrence is selected for each layout. I predict that you will see different names here when you check each layout.

            If you have different table occurrences of the same table and base a layout on each, you'll find that you have independent found sets, sort orders and current records on each and this would match what you describe here. If both layouts refer to the same table occurrence, then they'll share the same found set etc.

            • 3. Re: Relationship not working properly between two tables

              Turns out it wasn't a bug but a feature. I found out they were supposed to act independently and I wasn't using them as intended. And at the last minute, too--It's my departing employee's last day! That's what I get for not knowing how my staff use their systems. Embarassed

              Going into Edit Layout > Set Layout Order I was able to view a summary of which table was associated with each layout (as suggested by the other users), and comparing with an old database I was able to find where the settings needed to be.

              Thanks for the help!