5 Replies Latest reply on Apr 25, 2012 2:54 PM by philmodjunk

    Basic Relationship Question

    ArtWheat

      Title

      Basic Relationship Question

      Post

      I have two tables inside one FMPro 12 file. ToDo and Projects.

      Each table has a key field: ToDoID and ProjectsID

      I made one to many relationship in the Relationships dialog box between the two ID fields (not sure how to make it many to many and if that is what I need).

      In the Projects table, I wanted to create a portal to view all the ToDo's of an individual Project yet when I add the portal, the Portal dialog box asks me to show related records from <unknown>.

       

      I've search the knowledge base as I'm sure this is a common question but have not found what I need to resolve this. Feeling like a noob, but not to proud to ask for help.

      Appreciate it if anyone can point me in the right direction.

      Thank you,

      Art

      relationship.png

        • 1. Re: Basic Relationship Question
          Sorbsbuster

          When you first draw the portal it has no idea where you want to draw the data from.  Click on the arrow to the right of 'Unknown' and select the Table Occurance that is the one you want.  In this case you would choose 'ToDo', and then fill in the fields from that relationship that you want to see.

          • 2. Re: Basic Relationship Question
            philmodjunk

            not sure how to make it many to many and if that is what I need.

            You'll need a many to many relationship, If a record in ToDo may need to be linked to more than one record in Projects in addition to having many ToDo records linked to a given project as you currently have.

            Example:

            If you have a TODO record labeled "Get Permits" and you want to link it to many different contractor projects, but several of these projects must also link to "Inspect Foundation".

            If you find that you do need such a set up, you'll need to add a join table between Projects and ToDo.

            • 3. Re: Basic Relationship Question
              ArtWheat

              First I want to thank Sorbsbuster for taking the time to reply.

              I should have mentioned I had the relationship setup and working fine before I deleted the relationship. The main problem was that each layout has a table assigned to it in the layout setup dialog box. When I deleted my relationship, each layout was no longer tied to a specific table. Correct me if I am wrong, but what I discovered is you can't assign a relationship to a field when no table has been assigned to a layout. I had to reset these for each layout and script I had created. Once I did that, things started working again and I could create the portal as Sorbsbuster described.

              Thank you for all the help. I've been using Filemaker a long time but hadn't upgraded since version 6 so I'm very rusty on FM basics while learning all the new stuff.
              Art


              • 4. Re: Basic Relationship Question
                ArtWheat

                Hey PhilModJunk, sorry I didn't see your reply before posting my last response. Thank you very much for taking the time to respond.


                I agree, making it many to many is not what I need here. I don't see where I would ever the same to do item listed under multiple projects in this case, though your example was a good one I had never considered. I do see a use for it in a situation like you described. I see you post a lot here and have helped many. Very kind of you to do that. Grateful for what I have learned from you already both in this post and the other responses of yours I've read.

                Kind regards,

                Art

                • 5. Re: Basic Relationship Question
                  philmodjunk

                  Correct me if I am wrong, but what I discovered is you can't assign a relationship to a field when no table has been assigned to a layout.

                  Mmm, not really. You have three objects that usually start out with the same name: Tables, Table occurrences and layouts. When you create a new table, FileMaker automatically creates a table occurrence and a layout of exactly the same name. They may have the same name, but they are different objects with a different reason for being part of your database.

                  The "boxes" in Manage | database | relationships are called "table occurrences". If you then go to the layout of the same name and check Layout Setup. You'll find the name of this table occurrence in the "show records from" drop down. This is the name of the table occurrence--not the table, but since we start with identical names, this is not an obvious thing. If, however, you double click a table occurrence and use the dialog that pops up to rename it, the name in show records from will update to this new name, but if you check the tables tab in manage database, you'll still see the original table name.

                  That said, you can create any number of relationships and the layouts you have present and the fields on them will have no effect on your ability to create them. It's not unheard of to create a relationship to a table occurrence that does not have any layout in your file associated with it. It may be there for scripts to refer to or to so a calculation field can refer to data stored in it.