Search text for any one of multiple specific words

[bcolburn]

Hello,

I'm trying to search a large text field that contains a list of medications to see if any one of a large list of more specific drug names is listed. I have a second field with different classes of drugs and we are trying to both: 1.) get a list of specific drugs being taken and 2.) see which classes the drugs belong to.

My idea was to use patterncount() on the list of actual medications and make a calculation field using case() to check off the drug classes based on the contents of the medication list. I am having trouble figuring out quite how to do this because it seems my search string would need to contain multiple words with an "or" relationship (i.e. if the field contains Drug A OR Drug B OR ... but I am aware that Patterncount will only search the specific string order I use to define the calculation (I think).

Do you have any ideas on a good way to accomplish this? If patterncount() is the way to go, how would I structure the command to search for ANY of the words and not ALL of them? If it's not the right function, which one should I be using? Thanks for your help!

B

[comment]

I'm trying to search a large text field that contains a list of medications to see if any one of a large list of more specific drug names is listed.

If both fields contain a return-separated list of values, you can find the common values by using the FilterValues() function.

[bcolburn]

That's a helpful function to know! Can you help me adapt it to this though? I should clarify. I have one field that is an edit box in which we manually enter a list of medications. The other is a a set of checkboxes for specific classes of drugs. The idea is that if we enter a drug that is known to belong to a certain class, we also check off the drug class in the second field. We aren't MD's and make a lot of mistakes, so I'm trying to make the drug class field auto-enter with a calculation using the text string search I initially inquired about. That is to say, I want to be able to search the meds list we enter ourselves and then autopopulate the drug class field according to a script that recognizes a long list of drugs and their respective drug classes. Does that make sense?

[comment]

I can barely follow your description - and I suspect your structure may be the real issue here.

I have one field that is an edit box in which we manually enter a list of medications.

What exactly is this list? IOW, what does a record in this table represent?

[bcolburn]

So for reference, I've been using FM Pro for two months with NO background in database design or programming. I'm doing the best I can (and I'm doing pretty well so far...)

Alright, we have a table called Past Medical History. Each record in that table represents the medical history for a particular patient. There are two fields in the field that matter for this: MedsPrescribed and MedClass. MedsPrescribed is a text field in which we manually enter a list of meds retrieved from various sources. The second class is a set of checkboxes with options like "beta blocker" and "ACE-inhibitor". We are supposed to manually check these options according to the meds we manually enter. Again, the issue is that I don't have an MD and don't know 10,000 meds and their classes. So I want to create a formula that searches for text in the first field of meds listed according to name, compares it to a stored list of drugs that are classified according to class, then automatically checks drug classes when it finds a med it recognizes in one of those stored lists.

[comment]

IMHO, there should be another table of Prescriptions, where each medication prescribed to a patient would be a record. Instead of entering the prescribed medications into a text field, they should be entered into a portal placed on a layout of Patients.

Ideally, there would be another table listing all medications, and perhaps another table of their classes - arranged as:

Patients -< Prescriptions >- Medications >- MedClasses

This way you'd only need to select the medication when adding a prescription, and its class would be fetched automatically from the related record in the MedClasses table.