4 Replies Latest reply on Jul 27, 2011 9:52 AM by philmodjunk

    Trying to search related fields during field calculation...



      Trying to search related fields during field calculation...


      This one has me stumped! If you can help, please let me know!

      Basically what I have is a table of "Clients" and a related table of "Cases" for each client. Each 'case' has a status like;






      So a client could have 3 or 4 'cases' all with different statuses.

      What I'm trying to do is create a calculation field in the "Client" table called 'Relationship Status' which will look at all of these statuses and produce a result based on what it finds.

      For example: If all of the cases showed 'COMPLETE' then the result would be "All Finished"

      If most of the cases were 'COMPLETE' but one of them was 'FIRST' then the result would be "Open Cases"

      I've tried using If ( PatternCount ( Claims::Claim Status ; "First" ) ≥ 1 ; "Open Claims" ; ...and further nested IF's... but it seems to only look at the first related record. What I want to do is look at a specific field in all of the related records..

      Any ideas??

        • 1. Re: Trying to search related fields during field calculation...

          It's always a good idea to include what version of FileMaker you are using.

          If your version is new enough to support the list function, you can use the list function to pull up the values from all the related records instead of just the first related record. Try this calculation for identifying when all related records are "complete":

          Patterncount ( List ( claims::Claim status ) ; "complete" ) = Count ( Claims::Claim Status ) and Count ( Claims::Claim status )

          The second Count makes sure that this is true only if at least one related claims record exists for the current client record.

          "most cases complete" is a fuzzy criterion here. I'll interpret that "most" literally as "more than half", but you can adjust the following expression as needed to fit your interpretation of the term:

           Let ( L = List ( claims::claim status ) ;
                  ( Patterncount ( L ; "complete " ) ≥ Count ( Claims::Claim Status ) / 2 and Count ( Claims::Claim status ) ) ) and
                  ( Patterncount ( L ; "first" ) ≥ 1 )

          You can then use these expressions in a Case statement like this:

          Let ( [ L = List ( Claims::claim status ) ; C = Count ( Claims::claim status ) ] ;
                 Case ( C = 0 ; "" ;
                           Patterncount ( L ; "complete" ) = C ; "All Finished" ;
                           ( Patterncount ( L ; "complete " ) ≥ C / 2 ) and ( Patterncount ( L ; "first" ) ≥ 1 ) ; "Open Cases" ;
                           "Put what result you want when none of the above is true here"
                          ) // case
                ) // Let

          • 3. Re: Trying to search related fields during field calculation...

            Looks promising, currently experimenting with Patterncount ( List ( claims::Claim status ) ; "complete" ) =... 

            Don't get caught up in my vagueness.. when I say most are complete and one is "First", the important thing is that one of the is still at the "First" stage. Now the way I was doing it, unless that record was first in the list then it wouldn't be considered by the calculation.

            I'm liking the list count, now I can say IF the Patterncount ( List ( claims::Claim status ) ; "First" ) > 1, then there must be "Open Cases"

            Can I do this: Patterncount ( List ( claims::Claim status ) ; "First" OR "Second" ) ...?

            • 4. Re: Trying to search related fields during field calculation...

              You can do: Patterncount ( List ( claims::Claim status ) ; "First" ) Or Patterncount ( List ( claims::Claim status ) ; "Second" ) )

              This evaluates as True if at least one related record is "first" or "second".

              You can also use:  ValueCount ( FilterValues ( List ( Claims::claim status ) ; List ( "First" ; "Second ) ) )

              The key difference is that a list element in the two lists has to exactly match before it is included in the list of values returned by FilterValues where Patterncount is a "contains this text" type of function. There should be no difference between using patterncount and filtervalues with your list of values, but this can be a key difference with other possible lists.

              If you have a list like this:


              Patterncount ( TheList ; "Apple" ) returns 2


              Valuecount ( FilterValues ( TheList ; "Apple" ) ) returns 1.