5 Replies Latest reply on Feb 27, 2012 3:06 PM by AlexXander

    FilterValues Function



      FilterValues Function


      OK I've been using the FilterValues Function as 

      Field E = FilterValues (Field A; ValueListItems ( Get ( FileName ) ; "List Field D") )

      And this worked great up untill I was no longer able to keep Field D = List(List(Test 2::Field B);List(Test 2::Field C)) -That made up the Value List "List Field D"- As an indexed field.  

      Now I figured I would just do this:

      FilterValues ( Field A; Field D) but that returns all empty record so I tried FilterValues ( Field A; Field B) and FilterValues ( Field A; Field C) and it was the same result, just a list of empty cells when there are clear matches beween the fields. 

      Does anyone know how to get this to work.


        • 1. Re: FilterValues Function

          Please explain what results you want for this. What list of values do you want to produce in order to compare the value of field A to it to deterimine if that value is in the list?

          Just guessing here, but if you want the value of Field A returned if that value is in Field B of any record in the table or in Field C of any record in the table, you could do this:

          Define a value list of all values in Field B. Define a value list of all values in Field C.

          Use this expression:

          Filtervalues ( List ( ValueListItems ( get ( FileName ) ; "RieldBValueList" ) ; ValueListItems ( Get ( FileName ) ; "FieldCValueList" ) ) ; Field A )

          • 2. Re: FilterValues Function

            ValueListItems is what I was using before but in the main database that i am creating what represents Field B and Field C are calculation fields that can not be indexed and When I try to make them into a value list, Filemaker tells me that it will not work. So, using Value List is not an option.

            So I want to know is there a way to make the expression 


            FilterValue (Field A; Field B) and FilterValue (Field A; Field C) work, or simply use the list Field C not as a value list for FilterValue (Field A; Field C) (Note the first image for Field C is wrong as a list it only shows 3's ) Because when I try these calculations all it returns is empty cells.

            However, I do know of an alternative as I can reverse the formula and make Field A into the list as it is unchanging and can be indexed but I believe that that may make things even more complex for my table. So if there is another way please let me know.

            • 3. Re: FilterValues Function

              I help a lot of people at the same time so while I sort of remember your earlier posts it leaves me with an imperfect understanding of what you have set up ith Fields B and C. Without knowing how the values in B and C are derived, I can't really suggest anything.

              • 4. Re: FilterValues Function

                Hmmm, on second thought.

                If it is a list of all values in the table for these two fields, you could define a new calculationf field, BC as List ( B ; C ).

                Then a self join:

                Table::anyfield X Table 2::anyfield

                Where table and table 2 are occurrences of the same table with X specified as the cartesian join operator.

                Then you can use List ( Table 2::BC ) to produce a list of all values in B and C in a single list for use with filtervalues.

                • 5. Re: FilterValues Function

                  That works great, and I dont have to index the list. Thanks