13 Replies Latest reply on Nov 2, 2015 3:46 PM by disabled_morkus

    How to filter a value list?

      I know this question has been asked and answered many times, but I think my situation is a little different since I'm not trying to filter successive values lists, for example

       

      ---

       

      I have a value list I'm using (a list of categories) that will, when each category is checked, will populate a M:M resolver table for VENDORS:CATEGORIES. This all works well (thanks to that poster for the suggestions).

       

      However, I now realize that due to all the categories after data entry of those, I would like the category value list to only show the categories that have not been picked yet. 

       

      However, after a few hours of trying to create a FM relationship and mucking around with the value list configuration, I can only come up with two ways FM wants to display the value lists: (1) all values regardless of which I've picked (what works now), or (2) just the values I've already picked.

       

      I've tried creating a "<>" join using a TO called CATEGORY_NOT_CHOSEN which is joined to CATEGORY on the PK. Then, I tried using that TO in the value list. Tried lots of things like this including the value list option of only using related values, etc.

       

      But I can't seem to figure out how to get the values in the value list I haven't yet picked.

       

      In SQL this is a 10 second query. To be able to build a Value List using a SQL statement would warrant an immediate upgrade to that FM version!

       

      (select cat_item from category where cat_item.PK not in (select cat_pk from ....)

       

      ----

       

      So, I have three tables:

       

      VENDOR

      CATEGORY

      VENDOR_CATEGORY (M:M resolver table

       

      ----

       

      I'm not sure why this is so difficult. It's actually probably simple and I'm missing it.

       

      Would appreciate any suggestions.

       

      Thanks,

        • 1. Re: How to filter a value list?
          TomHays

          Perhaps the "Dwindling Value Lists" example (.zip) in Kevin Frank's demos will be helpful.

           

          -Tom

          • 2. Re: How to filter a value list?
            jbrown

            Morning.

             

            Here's my set up of it. Based on what we talked about last night in setting up the script to handle entering multiple categories at a time.

             

            I think you're on the right track: A relationship to categories of categories not already picked. Not sure where you put the relationship. I set mine up from Vendor to Category after creating a field in Vendors of categories picked.

             

            This field holds the IDs of the categories already picked in a return-delimited list. When a new record is created the field gets a 0 filled in so the relationship has something to compare to. My script now sets this field with its previous contents as well as the category IDs that were picked in the check box.

            • 3. Re: How to filter a value list?
              bigtom

              Use ExecuteSQL for this. If you do not have at least FM12 you should upgrade.

              • 5. Re: How to filter a value list?

                My FMP issues, even after several years using FMP, being a "java guy", are I'm not always sure where some features of FMP leave off and where I need to fill in the gaps with scripts and such. Since SQL is not thoroughly supported in the product, I tend to look at relationships via TOs as roughly the equivalent to SQL joins. Yet, this doesn't really appear to be the case. That is, i seem to be expecting too much from TOs.

                 

                My posting above was really, as with the first posting, a sanity check trying to verify that I needed to write a script.

                 

                ---

                 

                OK, the way I implemented this "don't show previously chosen categories" was the following below. I'm still having an issue (due to a CHANGE LAYOUT in the script) to figure out which trigger to use to run the script automatically:

                 

                In Script...

                --------------

                1. Create CSV list of PK, Category Item via ExecuteSQL

                2. Parse CSV returned to extract PK and Description

                3. Create a new record in a special table just for this script's output

                4. Update the Value List to use this table for its check box field list

                5. Go back to the main layout (Vendors, in this case).

                 

                This script works fine.  (though I had to write/debug the SQL in a remote Java IDE, with JDBC connection to FMP database, so I could quickly write and modify SQL, and see actually debugging messages, and not just "?".)

                 

                -----

                 

                Yet, if I try to automate the script, say, using an OnRecordLoad action on the layout, then I'll have a recursive loop due to the layout change in the script .... so that doesn't work.

                 

                Since I'm using a popover button, there doesn't seem to be a script trigger that will work with a pop-over button. I tried onObjectEnter to run the script, but that didn't work.

                 

                I could, of course, just stick a button on the layout to (manually) click that runs the script, but I was hoping for some automated way.

                 

                onRecordLoad would be the obvious choice, I think, but I don't see how to get around the issue that the script changes the layout and will thus, in an infinite loop, trigger the OnRecordLoad.

                 

                Suggestions on this final issue?

                 

                Thanks for all the input and ideas!!!

                 

                - m

                • 6. Re: How to filter a value list?
                  Mike_Mitchell

                  morkus wrote:

                   

                  My posting above was really, as with the first posting, a sanity check trying to verify that I needed to write a script.

                   

                   

                  You really don't, if you use the link I posted below. The Magic Value List technique uses calculation fields to achieve the same result.

                   

                  morkus wrote:

                   

                   

                  OK, the way I implemented this "don't show previously chosen categories" was the following below. I'm still having an issue (due to a CHANGE LAYOUT in the script) to figure out which trigger to use to run the script automatically:

                   

                  In Script...

                  --------------

                  1. Create CSV list of PK, Category Item via ExecuteSQL

                  2. Parse CSV returned to extract PK and Description

                  3. Create a new record in a special table just for this script's output

                  4. Update the Value List to use this table for its check box field list

                  5. Go back to the main layout (Vendors, in this case).

                   

                  This script works fine.  (though I had to write/debug the SQL in a remote Java IDE, with JDBC connection to FMP database, so I could quickly write and modify SQL, and see actually debugging messages, and not just "?".)

                   

                   

                  You can somewhat alleviate this issue by using this Custom Function. It will evaluate in the Data Viewer and give you the error associated with the SQL query if FileMaker can't parse it.

                   

                  If (

                   

                  //the sql call results in an error, return empty so the error will be returned

                  _executeSQL = "?" ; "" ;

                   

                  //the sql call is executed correctly, just return the result

                  _executeSQL

                  )

                   

                  // ===================================

                  /*

                   

                      This function is published on FileMaker Custom Functions

                      to check for updates and provide feedback and bug reports

                      please visit http://www.fmfunctions.com/fid/335

                   

                      Prototype: sql.debug( _executeSQL )

                      Function Author: Andries Heylen (http://www.fmfunctions.com/mid/57)

                      Last updated: 28 July 2012

                      Version: 2.2

                   

                  */

                  // ===================================

                  • 7. Re: How to filter a value list?

                    I really appreciate your reply.

                     

                    But, there's nothing like just being able to write SQL in a nice big window with real-time errors showing problems with the query. And, being able to see lots of rows in grow-able windows with modifyable fonts. The FMP data viewer is so primitive and limiting (by comparison), I can barely use it at all and avoid it when possible.

                     

                    ---

                     

                    Back to the pick list issue...if FMP let me create a SQL to populate the pop-over (or any value list, for that matter), problem solved. If FMP let me create a layout from a SQL statement, then life would be much simpler.

                     

                    As I guess it is with most environments some things are easy in an environment and some things are difficult.

                     

                    Thanks again.

                     

                    - m

                    • 8. Re: How to filter a value list?
                      bigtom

                      You can trigger a script with a Popover but you need to add the trigger to the popover panel and not the button itself. Right-click or Ctrl-click on the panel to set it.

                      • 9. Re: How to filter a value list?

                        That's cool and sounds like exactly what I needed.

                         

                        But, after I added the script to the OnObjectEnter of the pop up, the popup itself no longer displays. If I remove the script trigger, the pop-up then displays.

                         

                        The idea is to refresh the table the pop-up reads so it doesn't display already-picked categories.

                         

                        Suggestions?

                         

                        Thanks,

                         

                        - m

                        • 10. Re: How to filter a value list?
                          bigtom

                          It seems that you may be confusing a pop up with a popover?

                          • 11. Re: How to filter a value list?

                            You're right, I called it a pop up when I meant to write pop over.

                             

                            I still have the same issue: The script doesn't allow the pop over to open after I run it. Remove the script and the pop over works.

                             

                            It's a moot point, however, since this method doesn't actually work. If I set the value list to point to the CATEGORIES table and set a field equal to a Value list from this table, it always displays all the fields.

                             

                            Due to the M:M relationship, it's a bit more complicated that some examples shown.

                             

                            However, it seems that due to no SQL->Value List (the simple, quick solution), I'm forced into creating a field and joining that to the CATEGORIES table.

                             

                            ======

                             

                            AS A TEST.... If I create a field in the VENDORS table and populate it with a sample PK from the CATEGORY table, then JOIN that field to the CATEGORY table (same field), I don't see how to limit what is shown. I created a layout and made that layout the "Category 2" table (the TO created after this join), but, again, all records appear. I went in and changed the relationship to "<>", but all the records still appear.

                             

                            Sigh.

                             

                            I really am missing something about how FM works.

                             

                            Appreciated your reply.

                             

                            - m

                            • 12. Re: How to filter a value list?
                              Mike_Mitchell

                              Not to belabor the point, but the magic key value list is designed especially for this conundrum. Have you looked at it?

                              • 13. Re: How to filter a value list?

                                I took a look at that FMP project, but it didn't seem to really address what I'm trying to do. I guess I Need to start with a simpler example not a magicValueList project...

                                 

                                (The ERD subset above could actually populate/create a FMP database with a JDBC connection.)

                                 

                                Thanks,

                                 

                                - m

                                 

                                P.S. I was in a hurry and made a mistake or two on the ERD sample above. The categories table has two fields: a PK and an ITEM (the text of the category.)