Compating terms in two fields

[Oldsneekers]

Is there a calculation that will find a common term in two fields? I have a TO for a "DrugLibrary" that contains three fields: Name, Synonyms, Reactors. In a another TO "MedList" I have also have fields for Name, Synonyms, Reactors. (in adition to others). when a drug is added to a patient's MedList from the DrugLibrary I want to check the for drug interactions by comparing MedList::Name and MedList::Synonyms with DrugLibrar::Reactors to see ir the MedList::Name is contained in the field DrugLibrar::Reactors (easy using pattern count as the name is one term) but also (and here is the hard part) to see if any term in MedList::Synonyms (which can contain any number of terms but usually less than 10) is contained in DrugLibrar::Reactors (which can contain also any number of terms but usually less than 10).

If there is a one calculation that can do this great, otherwise I have to turn the single fields Reactors and Synonyms into new TO's and use them as portal fields with each record containing only one term and using a loop script inside a loop scrip to compare row buy row. A lengthy process.

Thanks (as always)

"Mind is mind because of things as things are things because of mind. These two are merely relative and both, at source are emptiness."

[Oldsneekers]

After a quiet drive through the woods, I came up with a Let calculation. I use the let function to ID each term in one field:

Let(

A=middleword(reactors;1;1) ;

B= middleword(reactors;2;1);

C= middleword(reactors;3;1) ...all the way to

J = middleword(reactors;10;1)

Comparer= my other field];

Case(

PatternCount(A; Comparer)>0; 1;

PatternCount(B; Comparer)>0; 1;

PatternCount(C; Comparer)>0; 1; (...)

PatternCount(J; Comparer)>0; 1; 0) )

Thus if there is a common term in the two fields this calc will have the value of "1" otherwise the value will be 0. Alternately, I can make the end value of this calc the common term found by substituting the "1" with the field entry for the calc.

This has the limitation that I can compare up to predefined limit of terms but I can make the let function so long that it will include all the realistic possibilities.

I hope this is of help to others in my predicament but if there is another calc, I'm all ears.

Edited January 26, 2006 by Guest

[comment]

I have a feeling that FilterValues() is the function you want, but I am not entirely clear concerning your structure and what exactly is required here - for example, is this check needed when a new medication is added to a patient, or is this a constant reminder. A sample of your file would be useful here, I think.