10 Replies Latest reply on May 21, 2014 3:56 PM by deathrobot

    Filtered value list question

    deathrobot

      Title

      Filtered value list question

      Post

           I have a question about filtering a value list. I have the following tables:

           PEOPLE ---< DESCRIPTION >--- CATEGORY

           There are several categories, one of which is "musician"

           I am now working in a layout where the tables are:

           AFM ---< RECORDED >--- PEOPLE

           There is a field on the AFM layout with a value list showing records from PEOPLE. I would like to filter that list to only show people whose category is "musician." I know I have to somehow filter through the DESCRIPTION table, but I've tried MANY combinations, and can't seem to get this to work. Any help would be greatly appreciated.

           Michael

           EDIT: I also tried creating a global field in AFM with the id for "musician", and a new relationship like this:

           AFM ---< DESCRIPTION >--- PEOPLE

           Where AFM::Global = DESCRIPTION::id_Category

           I thought using a value list based on this relationship should limit the results, but…it doesn't…

        • 1. Re: Filtered value list question
          philmodjunk

               It would appear from the many to many relationship that a given record in People can be linked to more than one record in Category with one record in Category being "musician".

               If you are using FileMaker Pro 12 or newer, this would appear to be a good candidate for an ExecuteSQL controlled conditional value list:  Using ExecuteSQL to Produce a Conditional Value List

               Another approach would be to use this chain of related Tutorial: What are Table Occurrences?:

               AFM------Category|Musician-----<Description|Musician>-----People|Musician

               The match fields from AFM to Category|Musician would be:

               AFM::constMusician = Category|Musician::Category

               The other match fields would be the same as your first set of table occurrences and relationships that you show in your post.

               constMusician would be a calculation field that returns a constant value: "Musician" or whatever value in the Category field of the Category table identifies a musician.

               Then your conditional value list would be based on fields from People|Musician with the "include only related values starting from [AFM]" option selected.

               But this assumes that your "field on the AFM layout" is actually a field in the AFM table. But since you have a many to many relationshp with PEOPLE here, it is possible that this is really a field from the Recorded table. If so, then in each place where I have Identified the AFM table, use Recorded instead.

          • 2. Re: Filtered value list question
            deathrobot

                 Hi Phil,

                 The SQL seems to work great. One thing I noticed is that you were able to have your calculation stored, but I cannot. It works fine, but I'm wondering if I'm doing something wrong.

                 I have these two relationships:

                 AFM ----< RECORDED >---- PEOPLE

                 and

                 AFM ----< PEOPLE_SQLAFM
                 (AFM::unstored_Musicians = PEOPLE_SQLAFM::id)

                 My SQL cal is called "unstored_Musicians" and is in the AFM table:

                 Let ( [ ~sql =
                 "
                 SELECT
                 ~idField
                  
                 FROM
                 ~table1
                  
                 WHERE
                 ~matchFieldA LIKE '%~valueA%'
                  
                 "
                 ;
                  
                 $sqlQuery = Substitute ( ~sql ;
                 [ "~idField" ; SQLFieldName ( People::id) ];
                 [ "~table1" ; SQLTableName ( People::id ) ];
                 [ "~matchFieldA" ; SQLFieldName ( People::category ) ];
                 [ "~valueA" ; "1-2-063510196273-0000000-01312-075696725372376" ]
                 );
                  
                 $sqlResult = ExecuteSQL ( $sqlQuery ; "" ; ¶)
                 ];
                  
                 $sqlResult
                  
                 )

                 Anything wrong with the above?

                 Thank you!!

                 Michael

            • 3. Re: Filtered value list question
              philmodjunk

                   I'm surprised that you need to use such a cryptic expression to identify which values represent a musician, but I see no issues with the SQL.

                   And the field does not need to be stored to work as the match field in this specific relationship so the fact that you need it to be unstored should not be a problem here.

              • 4. Re: Filtered value list question
                deathrobot

                     Thanks for the followup, Phil.

                     BTW, I use this longish calc for SQL because it doesn't break if a field or table gets renamed. As I'm still in development with this, it's always possible things might get altered.

                     Michael

                • 5. Re: Filtered value list question
                  deathrobot

                       One more question about this.

                       I have my value list defined as:

                       **********

                       Use values from first field
                       PEOPLE_SQLAFM
                       id

                       Also display values from second field
                       PEOPLE_SQLAFM
                       name

                       Include only related values starting from PEOPLE

                       Show values only from second field is checked

                       **********

                       In the first portal (the one filtered to only show LEADER records), when I select the field with the drop-down value list, everything works as expected. However, when I select that same field in one of the other 4 portals (all of which use the same relationship; just different filters), I get "<no values defined>." If I uncheck "Include only related values…" in the value list dialog, all the drop-downs work, but I obviously lose the filtered version of the list; it just shows everyone.

                       Any idea why this is?

                       Michael

                  • 6. Re: Filtered value list question
                    philmodjunk
                         

                              BTW, I use this longish calc for SQL

                         I didn't find that cryptic and it's a very good practice to keep on using even when no longer actively developing the file.

                         What I found surprising was that you had to use the Like keyword and 1-2-063510196273-0000000-01312-075696725372376 to tell who was or was not a musician.

                         

                              Any idea why this is?

                         I know that you've posted that they all "use the same relationships" but from the results you report, this does not seem to actually be the case. Are you sure that these other portals don't refer to a different table occurrence? OR that your field in the portal row isn't from a different table occurrence?

                         Are all of these portals on the same layout?

                    • 7. Re: Filtered value list question
                      deathrobot
                           

                      What I found surprising was that you had to use the Like keyword and 1-2-063510196273-0000000-01312-075696725372376 to tell who was or was not a musician.

                           Yes, this is pretty obtuse. It's the id (a custom UUID) for that record in my category table. I probably could have skipped using an id at all since there are only 5 categories…

                           

                      I know that you've posted that they all "use the same relationships" but from the results you report, this does not seem to actually be the case. Are you sure that these other portals don't refer to a different table occurrence? OR that your field in the portal row isn't from a different table occurrence?

                           There is only one TO that they can point to. In fact, I just option dragged the first portal to make the other 4. When I double click on them, they show they are using the same TO ("RECORDED"). Same for the fields themselves.

                           

                      Are all of these portals on the same layout?

                           Yes.

                      • 8. Re: Filtered value list question
                        philmodjunk

                        OR that your field in the portal row isn't from a different table occurrence?

                        • 9. Re: Filtered value list question
                          deathrobot

                               They are identical. I double checked, but they were in the portal before I option-dragged.

                          • 10. Re: Filtered value list question
                            deathrobot

                                 Okay, problem solved. I had the wrong end of the chain selected in the value list dialog in the "Include only related values…" dropdown.

                                 Thanks for sticking with this thread so long, Phil.

                                 Michaek