12 Replies Latest reply on Mar 1, 2017 1:31 AM by crmorris2

    Filtered lists from related records

    crmorris2

      Hi all,

       

      I am quite new to filemaker but I am getting the hang of it, slowly.

       

      I have 2 tables and they are related by a user id. What I want to do is get a list of records with a mix of fields from both tables.

       

      I have no problem with getting the list, what I am having trouble with is filtering this list. I have tried creating a portal but it doesnt seem to work and only shows 1 record on the list but I can actually click through the records.

       

      I have attached a few screenshots and hope it makes sense.

       

      Help, please!

       

       

      My tables: (ODBC Source with many more tables but these are the only ones I need data from)

      tables.png

       

      My list which I open with a script to only show records from a certain course (not finished the formatting of the layout as you can see)

      list.png

       

      Testing a portal on another layout. As you can see it has the records but only gives me one record in the list.

      Portal test.png

       

       

      Thanks in advance!

       

      Craig

        • 1. Re: Filtered lists from related records
          philmodjunk

          Your relationship attempts to link an uninfexed, probably global user id field to an unindeced user id field. That won't work.

          • 2. Re: Filtered lists from related records
            crmorris2

            Hi, thanks for your reply.

             

            Do you know what my best option would be for filtering the records without a portal?

             

            I need to be able to filter gender/coutrny/completed date etc

             

            Thanks

            • 3. Re: Filtered lists from related records
              philmodjunk

              Forget my last post. I missed the detail that these are ODBC sourced tables.

               

              How is your layout set up?

               

              is it based on the left hand table occurrence? On which of the other two occurrences is the portal based?

               

              Is the relationship from layout table occurrence to portal table occurrence one to one or one to many?

              What you describe would be correct if there is only one related portal record.

              • 4. Re: Filtered lists from related records
                crmorris2

                My layout is based on the left hand table (users) and the fields are a mixed from both tables.

                 

                From the looks of the entire relationship table on the ODBC source, I would say it is one to many, it is extremely complicated!

                 

                There are lots of other tables available but these are the only 2 that I need data from (ignore the vouchers table, I was using it for testing). Thats why I have just added these 2 and created a relationship using userid

                • 5. Re: Filtered lists from related records
                  philmodjunk

                  You haven't answered my questions about your portal.

                   

                  Assuming that it is based on the second occurrence, a single record could be a correct result. You'd need to go to a layout based on that table and do a find for records of the first table's user id to check.

                   

                  One thing to check with on a portal that always shows only one record is to make sure that all field objects are part of the portal row. Enter layout mode and move the portal without selecting any objects in the portal row. The objects should move with the portal. If they don't, they aren't part of the portal and that could be the cause of your trouble.

                  • 6. Re: Filtered lists from related records
                    crmorris2

                    Apologies. The portal is based on the second table (users_join_courses).

                     

                    Just had a thought. To get a list of users from the users_join_courses table i would have to do the layout based on that and then the portal based on the user table, as many users join 1 course but users can have many courses, if that makes sense?

                     

                    I will also try your last option just to make sure it isnt that.

                    • 7. Re: Filtered lists from related records
                      philmodjunk

                      "Just had a thought. To get a list of users from the users_join_courses table i would have to do the layout based on that and then the portal based on the user table, as many users join 1 course but users can have many courses, if that makes sense?"

                       

                      Um, no that doesn't. You have just described a "many to many" relationship. And that is normally set up like this:

                       

                      Users---<user_course>---courses

                      Users::userID=user_course::userID

                      courses::courseID=user_course::courseID

                       

                      With that set up, you can place a portal to either user_course or courses on your users layout to list all the assigned courses for a given user.

                       

                      By name, it appears that your second table is what I just called user_course and that should then be the basis for your portal and your layout should be based on users.

                       

                      I now wonder if a portal is needed at all here. You may need to describe what you want to see on the layout given your "mixed together " comments.

                      • 8. Re: Filtered lists from related records
                        crmorris2

                        I have got the tables you suggested in place.....

                         

                        2017-02-27_11h21_11.png

                        I have tried to add a portal as a test using a layout on the users table. As a test I used the user_id, email, First_name and Last_name fields within the portal and filtered it with a course_id of 97 (which I know exists and has users registered to that course but I get a blank portal.

                         

                        What I am aiming to get is a simple list of user details on different courses that will allow me to filter it by completion date, gender, and other attributes.

                         

                        Thank you for your help on this.

                        • 9. Re: Filtered lists from related records
                          philmodjunk

                          I don't see a use for having a portal.

                           

                          Start with a layout based based on the Join table and set it up as list view or table view. Add the fields you want from all three fields to this layout.

                           

                          To filter the list, perform a find.

                          • 10. Re: Filtered lists from related records
                            crmorris2

                            I have already created a layout and the list works fine. My issue is not being able to FILTER the list. I know I can search the list but unfortunately it is difficult to filter. For example....

                             

                            I only want the records for people on course 60 to show up. I achieved this by loading the list using a script which does a find for people on course 60, this works perfect, BUT, once the search is done and the records are loaded, there is nowhere to continue the filtering such as gender, completion date etc etc without doing an omit record search, plus I will be hiding the top bar from view so will need to be able to build the filter function on the layout. Ideally I need to isolate the records for course 60 and then i can find/sort/filter everything, but there is no way of isolating those records. This is why I need a portal because of its filter feature.

                             

                            Thanks

                            • 11. Re: Filtered lists from related records
                              philmodjunk

                              There are many ways to perform a find. One way is to set up a row of global fields in your header, one for each field you want to filter on. The global fields can be set up with value lists or just be edit boxes. Script triggers (or a "filter" button) can be used to perform a script that finds all records matching your criteria.

                               

                              For examples of scripts that use data in global fields as find criteria, see these examples:

                               

                              Scripted Find Examples

                              • 12. Re: Filtered lists from related records
                                crmorris2

                                Thank you for your help, I will look at the link you sent and hopefully will be able to sort something out that works. I appreciate the time you have taken to help.

                                 

                                Cheers

                                Craig