3 Replies Latest reply on Dec 1, 2013 7:38 PM by philmodjunk

    Match Records from two different tables.

    KylePrince

      Title

      Match Records from two different tables.

      Post

           I'm unsure exactly how to phrase what I want to do briefly so I'll apologize if the title is vague. Essentially what I'm trying to do is  this. I have three tables as such

           Table 1                        Table 2                      Table 3

           Some fields                 Some Fields            Some Fields

           CurrentMonth             Month                       Description

           Category Name          Category Name      Category Name

                                                 Description

            

           I want to create a layout (ideally based off table 1) that shows me the matching records from table 2 (for both month and category) but only if those records have a description matching one of the descriptions in table 3. Currently I have the database related as follows:  

            

            

           Table 1                            Table 2                             Table 3

           Some fields                      Some Fields                    Some Fields

           CurrentMonth    --- = ---  Month                       

           Category Name --- = --- Category Name --- = --- Category Name

                                                      Description         --- = --- Description

            

           This works partially...A layout based table 1 will show me all the records from table two with matching months and category names (in a portal) and a layout based on table 3 will show me all the records from table two with matching category names and descriptions but I haven't yet been able to get it to worth with both - surely there should be a way to do this kind of specific find? I also realize that in my particular case I might be able to get away with moving currentMonth into Table 2 and using a self-join relationship (what I'm about to try next) but for my own reference I want to know if there is a way to do what I'm trying to do. Obviously you could enter find mode - but the point of this is to have a layout that always has this information readily available - rather than having to perform a find or run a script every time you want it.

           Thanks for any guidance you can give.

            

        • 1. Re: Match Records from two different tables.
          davidanders

               Table 1 (Students Many) >-- StudentClass (StudentInClass ONE) --< Table 2 (Classes Many)
               A layout based on the Join Table "StudentInClass" can display fields from Table 1 and Table 2
               A layout based on Table 1 can display fields from the Join Table.
               A layout based on Table 2 can display fields from the Join Table.

               Adding Table Occurances can | will change this.

               Relational Design advice in the first 5 links here
          http://forums.filemaker.com/posts/f6ed4be796?commentId=222931#222931

                

          • 2. Re: Match Records from two different tables.
            KylePrince

                 Thanks for the links but I don't think I'm trying to create a many to many relationship. Let's give the tables names. Table 1 is categories, 2 is transfers, and 3 is descriptions. Each category can have multiple transfers but each transfer only ever has one category. Similarly there are many possible descriptions but each transfer only ever has one description. I'm trying to have a list of categories showing all transfers from the current month that have a preexisting description. 

                 even if this is considered a many to many relationship I'm unsure of how the links provided could be repurposed for this case. Sorry, this is just one of those times I just don't get it. 

            • 3. Re: Match Records from two different tables.
              philmodjunk

                   Set up a portal to table 2 but include this portal filter to filter out Table 2 records that don't match to a related record in table 3:

                   Not IsEmpty ( Table3::Description )