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" )
Thanks Mike, it's working!
I thought it would be a much simpler formula than that!
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.
Ok, thanks for the information!