3 Replies Latest reply on Oct 27, 2009 10:07 AM by CG325Girl

    Two files, Three Tables

    CG325Girl

      Title

      Two files, Three Tables & One Layout - Is This Possible?

      Post

      Filemaker Pro 10.3 and 9.3 on Windows XP

       

      Is there a way to get fields from 3 different tables located in two different files to appear on one layout?

       

      I have tried placing two portals, one from the 1st and 2nd table, into a layout of the 3rd table.

      The 1st portal shows up,since it's table is located in the same file, but the 2nd portal, who's table is located in a different file, does not.

       

      So, maybe portals to tables located in a different file are not possible?

       

      Thanks for any help on this one ...

        • 1. Re: Two files, Three Tables & One Layout - Is This Possible?
          CG325Girl
            

          Okay, I figured out what my problem was.

           

          I found out I was a Missing Index Error.

           

          You cannot create a relationship between 2 tables with calculated fields, since they cannot be stored or indexed and references unstored calculations, summary fields, etc. Ugh.

           

          My work-around is to create a sub-script that automatically copies the calculation field into a static field (every time new data is imported). In the meantime, I copied the calc field into my newly created field, and now my portal to another table located in another file works.

           

          A second work-around would be to create a join table, but would that be better or less stable than 2 portals? I really don't like my solution ...

          • 2. Re: Two files, Three Tables & One Layout - Is This Possible?
            philmodjunk
              

            A minor picky quibble:

            "You cannot create a relationship between 2 tables with calculated fields..." Actually you CAN create a relationship with stored calculations. This problem occurs when the calculation isn't/can't be stored. You can even use unstored calculations in relationships if it is the key in the Parent table not the portal table. (The relationip becomes "one way" it works going from Parent to child but not child to parent.)

             

            Join tables are generally good database design, but can't say if it's a good idea here without knowing more about your tables and the values you need to use to link them in relationships.

             

            • 3. Re: Two files, Three Tables & One Layout - Is This Possible?
              CG325Girl
                

              Great - thanks for the heads-up. This is good info. The scenerio you described was exactly what I had set up: the child table's key field was an unstored calculation, and it wasn't working.

              I'm going to create that Join table and see if I like it better.

               

              Thanks for your help