13 Replies Latest reply on Aug 31, 2014 4:24 AM by JosephMauriello

    filter value list on value in separate related table

    JosephMauriello

      Title

      filter value list on value in separate related table

      Post

           I'm struggling to figure out how to do something that seems relatively simple.  I want a value filtered value list but the aspect that I want to filter on is in a third table. 

           the three tables I'm working with are:
      Participants, Groups, and Programs

      Programs is the a parent to Groups. There's a fourth, many-to-many table that links Participants to Groups. (see attached image)

           I want users to be able to add Participants to Groups via a Participants layout. I want this to happen using a drop down menu that uses a value list populated with Groups. The thing is, I want to filter the value list so that it only shows Groups that are children to active Programs. So, the flag that I'm attempting filtering by is in the Programs table.

           This must be possible, right? I've read a number of forum posts, examined sample files and watched several videos trying to figure out what I might do to get the desired result.

           Any advice?

           EDIT: 25 views with no answer leads me to think that something is unclear in my question or my approach? If that's the case, let me know and I'll fix it. Thanks!

      Manage_Database_for_%E2%80%9CTesting_Concepts%E2%80%9D.png

        • 1. Re: filter value list on value in separate related table
          JosephMauriello

               bump - maybe posting on a saturday evening is not the best way to get a response to a seemingly difficult question?

          • 2. Re: filter value list on value in separate related table
            AppGuy

                 Joseph-

                 You are almost there... You need to have a active filter in your relationship as well...  currently it seems you are connecting via pk... which is good... just add an active filter between the relationships and you should be good... 

                 ie... active -- active and fk -->pk as well...    you could have active =1 in both..  If you put someone as In Active just put a 0

                 JP

            • 3. Re: filter value list on value in separate related table
              philmodjunk

                   I took the week end off to finish a database project. It happens to be a tutorial file on the subject of conditional value lists--including "hardwired" conditional value lists that only list values from records meeting specified criteria. And if the field that identifies a record for an active group is an unidexed calculation field, you might use it with ExecuteSQL to produce the conditional value list. These are specific topics demonstrated in this file with extremely thorough documentation on how the examples were set up.

                   See my FaceBook Page for a download link (the file is free):

              Caulkins Consulting, Home of Adventures In FileMaking

              • 4. Re: filter value list on value in separate related table
                JosephMauriello

                     This is great Phil!

                     I was in the middle of working on a detailed response to JP's suggestion but my session timed out and I lost the post frown

                     I'll study your examples and reply here with success or questions. 

                     thanks!

                • 5. Re: filter value list on value in separate related table
                  philmodjunk

                       Due to a noxious forum bug, please protect yourself with a "Select-All, copy to your clipboard" action just before submitting a private message or comment to this forum. The bug can lose your comment and log you out of the forum--forcing you to sign back in and re-enter the comment or message. By copying to the clipboard before posting, you can re-enter your message by pasting from the clipboard instead of having to retype it all over again.

                  • 6. Re: filter value list on value in separate related table
                    FentonJones

                          

                         I'm going to attempt to try this, using the logic of a Value List; and hope it works :-/
                         "VL" is "Value List"
                         "TO" is "table occurrence", on the Relationship Graph
                          
                         The VL is 'from' "j_ParticipantGroup" (TO). (Though it is seen in a portal on a "Participant" (TO) layout.)
                          
                         Field (with value): ParticipantID
                         (Will be created automatically when you click into the portal, or via a "new" script)
                          
                         Field (with VL): GroupID
                         (With the VL, with drop-down list of groups with an active program)
                          
                         Field (new): z_c1, calculation, 1, (number)
                         (Just a 1, for all records, can be Indexed or Unindexed)
                          
                         Relationships (new):
                         Programs_active
                         Groups_active
                          
                         Relationships:
                          
                         j_ParticipantGroup::z_1 = Programs_active::Active
                         (Targets only the "active" programs)
                          
                         Program_active::Groups_active::GroupID
                         (Sees only the groups with an active program
                          
                          
                         Value List:
                         Field: Groups_active::GroupID
                         From (TO): j_ParticipantGroup
                          
                          
                         I also did not name the new fields in the way I would, as I use full names (so not to confuse with other "original" TOs); and sometimes at "_VL" at the end, if the TO was created just for a VL.
                         (Which could be a good reason to use SQL instead, as Phil showed; but I know little.)
                          
                         P.S. I hope this is mostly correct, as I cannot read very well, and I imagine it doesn't sound so good either.
                    • 7. Re: filter value list on value in separate related table
                      philmodjunk

                           You've got a problem here. Data in Programs determines whether or not a Group is Active. This mirrors the issue addressed on the ExecuteSQL controlled Value Lists example. A calculation field in Groups can show that the group is active but since it uses data from Programs to do so, the result in an unstored calculation field. This field cannot be indexed and thus cannot be used as the needed match field to a constant in your j_ParticipantGroup table.

                           The ExecuteSQL method avoids this issue by using a Query to pull up a list of Primary keys from the table serving as a value source for the conditional value list. That field is then used as a match field in the relationship specified to make the conditional value list work.

                      • 8. Re: filter value list on value in separate related table
                        FentonJones

                             The Value List is going from ParticipantGroup (TO) to Program (TO) first, from a 1 into Active. This will hit ONLY the Programs "active" found set. Then it just goes to Group via its ID.

                              

                        • 9. Re: filter value list on value in separate related table
                          FentonJones

                               This is the result, showing the Value List, with this data:

                                

                          • 10. Re: filter value list on value in separate related table
                            FentonJones

                             

                            First, one thing I should have done, in my file, and its picture of the Value List result, was to show the 2nd field in the VL; especially because I named the ID fields as just "ID"; hence my "look, it shows 2 and 3, but not 1" should have shown "Group 2" and "Group 3", not "Group 1" (which has no Program "active"). I've changed my VL to that, and yes, it now visually works.
                             
                            [P.S. Removed a not so good 2nd try. The VL only worked if you already HAD an entry of any Group ID in the table; which is funky (at best). So back to the post before.]
                            • 11. Re: filter value list on value in separate related table
                              JosephMauriello

                              [wonderful - didn't learn my lesson the first time this will be my second time writing this post!]

                                   First off, thanks to both of you! 

                                   Sorry for the slow reply, I only really have time to work on this from Friday to Monday, I took this project on as a favor to my research team because I have some data driven web app experience, working weekends is the reward for being nice - hey, I like learning new things :)

                                   Fenton, I might try your solution if all else fails, but I've already put some work into the executeSQL example and I want to see if I can get that working - I've run into some trouble.

                              Here's what I've got so far:

                                   I put the ExecuteSQL command in a field on the j_PraticipantGroup table called cActiveFilter. It reads as follows:
                                    
                                   ExecuteSQL ( "
                                   SELECT \"_KpGroupID\" FROM Group 
                                   WHERE cActive_programs == 'Yes' " ; "" ; ¶  )
                                    
                                   I haven't even tried to make the relationship yet, for the purpose of testing I'm displaying the value from the executeSQL calculation in a layout based on the j_PraticipantGroup. Instead of getting the expected ID number I'm getting "?"s 
                                    
                              What am I doing wrong here?
                                    
                                   Two quick notes for clarity:
                                     
                              1.           I've got a calculated field in my Groups table that copies the value from active over. Perhaps this is overkill? I already had it set up so I thought I'd use it, otherwise I think I'd need to use a sql join?
                              2.      
                              3.           less important, but mentioned for the sake of pride, I'm using  "Yes" and "No" for my active flag because I'm a total FMP noob and didn't bother figuring out how to have "Yes/No" radio buttons display while storing the values as "0/1".
                                    
                              • 12. Re: filter value list on value in separate related table
                                JosephMauriello

                                Update:
                                I've been messing around and doing some research. This article was very helpful: 
                                "FileMaker SQL Nightmares: Tips for Debugging SQL Queries"
                                http://www.mightydata.com/blog/filemaker-sql-nightmares-tips-for-debugging-sql-queries/

                                As was this:
                                https://www.youtube.com/watch?v=y-j-msi3ij0
                                It shows you how to set up a "SQL" table where you can input SQL commands via a layout and see the results - very helpful.

                                I've changed the query around a bit (ever so subtly):

                                ExecuteSQL ( 
                                "
                                SELECT \"_KfGroupID\" FROM Groups 
                                WHERE NameActiveList = 'Yes'

                                ; "" 
                                ; ¶  
                                )

                                A few notes:

                                       
                                1. notice what was Group is now called Groups - "Group" is a SQL reserve word. 
                                2.      
                                3. Also, notice that I'm using _KfGroupID - This is actually really confusing me - I'm just not sure what Phil's getting at with the escape characters. 
                                4.      
                                5. In this iteration It's set up to take the record's foreign group ID (_KfGroupID) - I'm not sure if this is right, I'm still getting '?'. 
                                6.      
                                7. I've also tried it as it's set up in the example, using Kp_GroupID (changed because you may not use a field that starts with an underscore (or at least that's what the above link states). That solution always returns "1" in every record - that can't be right?
                                8.      
                                9. Finally, I replaced the double equal (==) with a single equals sign (=).

                                When I run this in the SQL layout it works - I think... it returns a list of values and seems to match the description in Phil's example doc. But when I plunk it in the j_ParticipantGroup field and give it a spin in my j_ParticipantGroup test layout and I get the '?' or the '1' depending on the set up. 

                                Any idea what I'm doing wrong?

                                • 13. Re: filter value list on value in separate related table
                                  JosephMauriello

                                  Okay - I got the value list working.

                                  I skimmed over this line a few too many times in Phil's example file (which is awesome, btw):

                                  When a field holds a list of values separated by returns it will match to any record in the related table where any one of the listed values matches to the value in the related table's match field.

                                  The value list is empty when there's no record for the j_Participant table for some reason... but when a record is there - the drop down list works. I can add an ID and the drop down list will appear indecisionfrowncrying angry (stages of filemaker grieving)

                                  This seems like a separate issue - I'll try to figure it out if not I'll make a new post.

                                  Thanks guys