5 Replies Latest reply on May 27, 2016 3:45 PM by erolst

    Calculating a Category Variable for Groups of Records

    jjgarner

      Hello everybody,

       

      I am trying to improve a database so that FileMaker identifies "Family Type" for each person in each family in our database.

       

      The database looks like this:

      person_IDfamily_IDgenetic_ID
      11None
      21Genetic Disorder A
      31None
      42None
      52Genetic Disorder B
      63None
      73None
      83Genetic Disorder C
      93None
      104None

       

      In English, I want my FileMaker calculation to basically do:

      "For each group of records with the same family_ID value, output the Genetic Disorder type (A,B, C, etc) if there is one; otherwise, output "Typical Family"

       

      I was thinking of doing a "for loop" strategy but I wanted to see if anyone in the community had a better idea or any tips - I am not sure how to code this!

       

      Thanks in advance!

        • 1. Re: Calculating a Category Variable for Groups of Records
          beverly

          Would/could there be more than one "genetic_ID" per family, or is it just one or none?

          beverly

          • 2. Re: Calculating a Category Variable for Groups of Records
            jjgarner

            There can be more than one genetic_ID per family - I have seen cases of parents adopting multiple kids, one with disorder A, one with disorder B, etc.


            Therefore I guess I would want the "Family_Type" output to be "Condition A + B" or something.

             

            Or, There could be multiple True or False fields for each genetic condition:  "Does this Family have a member with Condition A?"  "Does this Family have a member with Condition B?" and so on.

             

            Thanks for your help Beverly!

            • 3. Re: Calculating a Category Variable for Groups of Records
              erolst

              Is genetic_ID an ID that refers to a Disorder table, or just a string?

              • 4. Re: Calculating a Category Variable for Groups of Records
                jjgarner

                We have a drop-down value list, that contains custom values and I think they are all strings - "None", "Disorder A", "Disorder B" etc.

                • 5. Re: Calculating a Category Variable for Groups of Records
                  erolst

                  Well, you could try the following:

                   

                  • create a calculation field, type text, unstored, in the Family table

                   

                  then use either

                   

                  • ExecuteSQL():

                   

                  Let (

                    disorderList =

                      ExecuteSQL ( "

                        SELECT DISTINCT ( disorder )

                        FROM Person

                        WHERE

                          Lower ( disorder ) <> ? AND

                          id_family = ?

                        " ; "" ; ", " ; "none" ; id

                      ) ;

                    Case (

                      IsEmpty ( disorderList ) ;

                      "Typical Family" ;

                      Substitute ( disorderList ; "Disorder " ; "" )

                    )

                  )

                   

                  • or a “native” method – which due to the potential for performance problems would be preferable, except that you need some calisthenics to get rid of duplicate values:

                   

                  Let ( [

                    disorderList = List ( Person::disorder ) ;

                    disorderChoices = ValueListItems ( "" ; "Disorders" ) ; // name of your value list

                    disorderChoicesWithoutNone = Substitute ( disorderChoices ; [ "none¶" ; "" ] ; [ "¶none" ; "" ] ) ; // case-sensitive!

                    isTypical = ValueCount ( FilterValues ( disorderList ; "none" ) ) = ValueCount ( disorderList ) ; // all "none"

                    resultList = FilterValues ( disorderChoicesWithoutNone ; disorderList ) ; // de-duped

                    resultList = Case ( Right ( resultList ; 1 ) = ¶ ; Left ( resultList ; Length ( resultList ) - 1 ) ; resultList ) ; // remove any trailing ¶

                    result =

                    Case (

                      isTypical ;

                      "Typical Family" ;

                      Substitute ( resultList ; [ "Disorder " ; "" ] ; [ ¶ ; ", " ] ) // first sub gets rid of generic specifier; unnecessary for proper names

                    )

                    ] ;

                    result

                  )

                   

                  If you have a scripted mechanism for adding and removing persons to/from families, you could add the (simpler) SQL to the script to write the current status into a non-calc field – better performance and simpler calculation!