4 Replies Latest reply on Nov 4, 2015 8:32 AM by gcardin

    Hide object when a value is found in another table

    gcardin

      I want to hide a button when a certain value is found in a related table (firm_project).

       

      My calculation looks like this : firm_project::_fk_type_firm_id = 2

       

      A PROJECT can have multiple "fk_type_firm" related to it. I don't know what function to use to search through "firm_project" table. I manage to make it work since I used the value of the first "_fk_type_firm_id" available (3) in the "FIRM_PROJECT table but I don't understand why it seems to only look at the first value of that table and not go through it.

        • 1. Re: Hide object when a value is found in another table
          mikebeargie

          when you evaluate related data, it will only ever return to you the first value of the related data, unless you are using a function that is designed to evaluate all of the related data.

           

          Functions like that include Sum(), which will summarize a number field from related data; Count(), which will count the number of related records; List() which will return a return-delimited list of data from the related records; and more.

           

          What you need is a function that can search for a value within a list.

           

          You can return your list with List( firm_project::_fk_type_firm_id )

           

          Filemaker has a handy function called FilterValues as well, so if you use:

          FilterValues ( List( firm_project::_fk_type_firm_id ) ; "2" )

          it will return a filtered list of the matching values of "2" in your list.

           

          Lastly, you can count the number of values to see if the condition evaluates to true:

          If ( ValueCount( FilterValues( List(firm_project::_fk_type_firm_id) ; "2") ) > 0 ; "1" ; "0" )

          • 2. Re: Hide object when a value is found in another table
            gcardin

            Thanks Mike, it's working!

             

            I thought it would be a much simpler formula than that!

             

            Thanks again

            • 3. Re: Hide object when a value is found in another table
              mikebeargie

              You could also use this, but I figured explaining the basics of related data would be better:


               

              if ( ExecuteSQL("SELECT COUNT(*) FROM firm_project WHERE \"_fk_type_firm_id\" = ?";"";"";"2") > 0 ; "1" ; "0" )


              also, pointing out the quotes in the above statement, you may want to NOT use leading underscores in your field names. I always use a_ instead. Leading underscores are not compatible in a number of ways.

              • 4. Re: Hide object when a value is found in another table
                gcardin

                Ok, thanks for the information!