Would/could there be more than one "genetic_ID" per family, or is it just one or none?
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!
Is genetic_ID an ID that refers to a Disorder table, or just a string?
We have a drop-down value list, that contains custom values and I think they are all strings - "None", "Disorder A", "Disorder B" etc.
Well, you could try the following:
• create a calculation field, type text, unstored, in the Family table
then use either
ExecuteSQL ( "
SELECT DISTINCT ( disorder )
Lower ( disorder ) <> ? AND
id_family = ?
" ; "" ; ", " ; "none" ; id
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 ¶
"Typical Family" ;
Substitute ( resultList ; [ "Disorder " ; "" ] ; [ ¶ ; ", " ] ) // first sub gets rid of generic specifier; unnecessary for proper names
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!