8 Replies Latest reply on Oct 14, 2016 5:39 PM by user28328

    Need Help with List View Filter

    user28328

      Hi,

       

      I need some assistance filtering a list view, please.

       

      I've attached a section of my relationship diagram which will show a many-to-many relationship between AMAStaff TO and Sites TO using a JoinTable SiteAMAStaffJoin.

      This relationship works fine and will store a "1" in the "Select" field of the SiteAMAStaffJoin table for those Sites I want the Staff Member to have access to.

      The UserName Field in the AMAStaff table is directly related to the file "AccountName"

       

      I now need to filter a list view for the "Sites" table based on the status of the "Select" field (ie. it must be 1)

       

      So the process (as I see it in layman's terms) would be:

      1. Get the _pkOfficersID from AMAStaff WHERE UserName == Get(AccountName) AS $StaffID
      2. Find all Sites WHERE SiteAMAStaffJoin::_fkAMAStaffID == $StaffID AND SiteAMAStaffJoin::Select == 1

       

      I have no idea how to write the correct scrip that should run OnLayoutEnter to perform the above steps though.

       

      Would appreciate any help. Cheers!

       

      Tim

        • 1. Re: Need Help with List View Filter
          philmodjunk

          Are you trying to limit the listed sites to those permitted for the current user?

           

          On which table occurrence is your layout based?

           

          What I see is not a Join table linking two tables in a many to many relationship. That would look like this:

           

          Staff---<Join>----Sites

          Staff::__pkStaffID = Join::_fkStaffID

          Sites::__pkSiteID = Join::_fkSiteID

           

          I believe that I see you trying to figure out FileMaker in terms of how other systems link forms to an SQL query. If so, this analogy may help. A FileMaker layout is based on a table occurrence, a box in the relationship graph that refers directly to a single table. The SQL equivalent would be Select * FROM TableOccurrence. To bring a "where" clause into play, you perform a "Find" which then pulls up a found set (similar to a record set) of records you can view on a given layout. OrderBy is achieved by then selecting Sort Records or performing a script for the same.

          • 2. Re: Need Help with List View Filter
            user28328

            Thanks for the Reply!

             

            The Layout in this instance is based on the "Sites" Table.

             

            That Many-toMany relationship connection was suggested in this post and was a great way for me to avoid circular references to other tables in my solution:

            http://fmforums.com/topic/86854-many-to-many-relationshipscript-to-allow-selection-of-multiple-records-to-be-added-to-a-…

             

            Any chance you could post your example of the applicable Find query in this circumstance? My efforts so far have yielded no results...

             

            Cheers.

            • 3. Re: Need Help with List View Filter
              philmodjunk

              You didn't answer this question:

              Are you trying to limit the listed sites to those permitted for the current user?

               

              • 4. Re: Need Help with List View Filter
                user28328

                Sorry, yes, that's correct

                • 5. Re: Need Help with List View Filter
                  philmodjunk

                  Then each user has their own account and you can use settings inside Manage |Security to limit which records of a given table they can view. Then, any find you or a script performs will automatically omit records that they are not permitted to see.

                   

                  For the users' privilege set, select "custom privileges" from the records menu. From there you can select a given table and choose Limite from the view drop down before specifying a calculation that, if it evaluates as True allows the user to see the record and if it evaluates as false for a given record, does not allow them to view the record.

                   

                  Here's  a script that you can run either OnFirstWindowOpen or OnLayoutEnter that finds all records, but will omit records the current user is not permitted to see:

                   

                  Enter Find Mode []

                  Set Field [YourTable::NeverEmptyfield ; "*" ]
                  Set Error Capture [on]

                  Perform Find []

                  1 of 1 people found this helpful
                  • 6. Re: Need Help with List View Filter
                    user28328

                    Thanks again!

                     

                    Looks like a good solution and now looking at the Calculation that the Security Limit needs. I'm still struggling with the logic to filter it correctly and appreciate your patience...

                     

                    I've put in:

                    AMAStaff::UserName = Get ( AccountName ) and

                    SiteAMAStaffJoin::Select = 1

                    • 7. Re: Need Help with List View Filter
                      philmodjunk

                      You might need to set one term for access control on the AMAStaff table:

                      UserName = Get ( AccountName )

                       

                      and a different expression for access control on the join table:

                      Select = 1

                      1 of 1 people found this helpful
                      • 8. Re: Need Help with List View Filter
                        user28328

                        Thanks again for your help philmodjunk, your info proved very useful.

                         

                        I ended up rethinking the structure of this a little bit to achieve it properly.

                         

                        For anyone following this thread in the future, I ended up doing the following:

                         

                        • Basing the ListView on the Join table rather than the Sites Table
                        • Using the AccountName as the primary key to join the tables, which made it easier to get related records using the Get(Account Name) function
                        • Setting the Custom View Filter in the Security Tab to filter the Join table to be UserID = Get (AccountName) and Select= 1

                         

                        Thanks again - Great learning experience!