Get a word from a field

[tlevon]

Hello,

I'm trying to find a calculation in the If (test;result1; result2) format that can find a specific word in a field. The field I'm talking about contains two or three words without knowing which is where (left, middle right) .I want the test part of this calculation to search for a word in this field and return the result I will set it to.

Help appreciated

[comment]

The field I'm talking about contains two or three words

Please provide more details: How are these words separated? Any punctuation marks in the field? What is the reason you need to do this - perhaps there's a better way to arrange the data.

[tlevon]

Please provide more details: How are these words separated? Any punctuation marks in the field? What is the reason you need to do this - perhaps there's a better way to arrange the data.

My database (a medical one) has two tables which are related to each other with a one to many relationship. The parent table has a field called diagnoses which includes separate diagnoses from the child table for each patient (each patient has more than one diagnosis). The values in the diagnoses field (parent table) are separated just with blanc spaces and the diagnoses field itself is a calculation of the type GetNthRecord (childtable::diagnosis;1)& " " & GetNthRecord (childtable::diagnosis;2) etc. Now, the parent table has also a layout which is a kind of summary with checkboxes. For each possible diagnosis there is a separate calculation (container type) which adds a checkmark beside each diagnosis in the summary layout. I want each calculation to look inside the diagnoses field (parent table) and search if there is the specific diagnosis. The calculation I want to use is If (test; checkmark; emptycheckbox). So the test in this calculation has to look inside the diagnoses field (parent table) and if it finds the specific diagnosis word to return the checkmark in the report field. My question is how can I isolate a specific word (diagnosis) from a field that has multiple words in it (more than diagnoses)separated from each other with blanc spaces. If you believe that the structure of my database as presented is inefficient I would appreciate suggestions. Furthermore, if you need more details about tables, fields etc please specify so I can explain.

Thank you in advance

Levon

[comment]

For each possible diagnosis there is a separate calculation

Why is this necessary? To show patient's diagnoses as checkboxes, you only need a calculation field =

List ( childtable::diagnosis )

Format the field as checkboxes, using a value list of all diagnoses. This BTW also allows a single diagnosis to contain multiple words.

[tlevon]

Why is this necessary? To show patient's diagnoses as checkboxes, you only need a calculation field =

List ( childtable::diagnosis )

Format the field as checkboxes, using a value list of all diagnoses. This BTW also allows a single diagnosis to contain multiple words.

Right, the point is that each diagnosis in the child table has its own field and I have not used a value list for this purpose. It would be much more convenient for me at this point to find a function that isolates the specific word from the parenttable::diagnoses field.

[comment]

It would be much more convenient for me at this point to find a function that isolates the specific word from the parenttable::diagnoses field.

The point is that parenttable::diagnoses is a calculation, and it is much more convenient to look at the original data, while it is still return-separated.

For example, to test whether parent's diagnoses include "diabetes", you can use =

not IsEmpty ( FilterValues ( "diabetes" ; List ( child::diagnosis ) ) )

But why would you need a field for each diagnosis - when you already have a child record for it?







BTW, this calculation:

the diagnoses field itself is a calculation of the type GetNthRecord (childtable::diagnosis;1)& " " & GetNthRecord (childtable::diagnosis;2) etc.

can be simplified to:

Substitute ( List ( child::diagnosis ) ; ¶ ; " " )

[tlevon]

The point is that parenttable::diagnoses is a calculation, and it is much more convenient to look at the original data, while it is still return-separated.

For example, to test whether parent's diagnoses include "diabetes", you can use =

not IsEmpty ( FilterValues ( "diabetes" ; List ( child::diagnosis ) ) )

But why would you need a field for each diagnosis - when you already have a child record for it?







BTW, this calculation:







can be simplified to:

Substitute ( List ( child::diagnosis ) ; ¶ ; " " )

Thank you very much, the first calculation that you provided me works just fine for my purpose. It is true that for economical reasons I should in the first place use value lists instead of separate fields for each diagnosis. However, since Im relatively new to filemaker I ll try to incorporate your suggestion in one of my future solutions. Again thanks for your time and the information you provided me.