4 Replies Latest reply on Jul 29, 2017 12:35 PM by philmodjunk

    Filtering Drop Down List

    user28271

      I am constantly struggling with getting drop down list to only display certain values.  Everything I think I got it I hit a wall.

       

      First off if there a way to get a list via an Execute SQL?  If so I would love to learn how.

       

      But Here is the best I can do to explain the problem.

       

      Rate Card to Clients

      Clients to Job

      Job to Bill

      Labor to Job by Job ID.

       

      In the portal of all the related labor in billing I need a drop down list of only that clients rate card.

       

      Rate card is 3 hops from the labor.

       

      Can anyone help me map this drop down list?

       

      Screen Shot 2017-07-28 at 4.57.27 PM.png

        • 1. Re: Filtering Drop Down List
          user28271

          BTW I just figured it out... and I am not sure if I should be mad at myself or FileMaker.  I'll explain so you can tell me why I am an idiot.

           

          My Labor to Bill (by Job Number) had three keys to the relationship:

           

          Job ID = Job ID

          Not Billable Boolean ≠ 1 (so they can ignore labor they will never bill)

          Billed Boolean ≠ 1 (so labor already billed doesn't appear)

           

          It appears that if I have TWO not-equal links the drop down didn't work.

           

          Once I removed one of them, either one, it worked.  So I wrote a quick case statement so I would only have one field to deal with.

           

          So I ask, is my solution a fluke or is this a bug in FileMaker?

          • 2. Re: Filtering Drop Down List
            philmodjunk

            Impossible to say from the info provided. The relationship you describe in your second post doesn't match your diagram in your first--which appears to have an incorrect relationship matching client ID to job ID in one spot.

             

            A screen shot of the actual relationships would be better.

             

            In general, a good way to debug a relationship based conditional value list is to temporarily put a portal to the table occurrence from which you want to list values for your value list. If the portal lists the values you want to see, then your relationships and data are correct for your CVL.

             

            You asked if ExecuteSQL could be used and this is possible, but not in the way you would expect if you are familiar with how this works in other systems. If you use the Magnifying Glass tool to search this forum for "Adventures in FileMaking #1" you'll find download links for a teaching file with many different working examples of Conditional Value Lists along with detailed info on how they work. Several of the examples use ExecuteSQL.

            • 3. Re: Filtering Drop Down List
              beverly

              philmodjunk says:

              In general, a good way to debug a relationship based conditional value list is to temporarily put a portal to the table occurrence from which you want to list values for your value list. If the portal lists the values you want to see, then your relationships and data are correct for your CVL.

               

               

              That is exactly how I test the conditional value lists prior to making "live". The portal can be stuck to the side while you tweak the conditions. Hide thereafter except for Layout mode, if desired and there is a possibility that you need to check it later.

              Beverly

              • 4. Re: Filtering Drop Down List
                philmodjunk

                And sometimes you look at that portal and decide to use it instead of the value list as it's very easy to take that portal and make it a selection portal where clicking a portal row selects a value and enters it into the field.