Portal/field/layout relationship issues

[bcolburn]

I have a table called Patients that stores a variety of information, including a list of medications being taken and a checkbox field indicating all drug classes represented by the medications listed. Each record in this table represents a patient.

Per another user’s advice when I first attempted to solve this problem, I have created a new table MedLibrary in which each record represents a different medication and contains two fields: DrugName and DrugClass. I wanted to be able to search all records in that table to match existing medication name/class entries, so I watched this video and created a second TO (MedLibrary2) with the following relationship:

[MedLibrary2::SearchTerm = MedLibrary::DrugName]

When I set the search layout to display records from MedLibrary and use a portal to MedLibrary 2 records that displays DrugName and DrugClass, everything works GREAT. However, I ultimately want to apply an OnObjectEnter script trigger to fields in the portal so when I select a particular record in the portal, it inserts DrugName and DrugClass into the list of medications being taken in a particular patient’s record (i.e. a field in the table Patients).

I am not sure how to go about this. I have tried several things, but I’d rather just ask for suggestions than explain all my failed attempts so far. The only other important piece of information is that each record in Patients has a unique identifier that is currently used to link data from multiple tables within each record. I have a hunch I may ultimately need to create a relationship with this field, but I’m not sure how to do it without screwing up the search/portal functionality I’ve created.

What I want to know is:

What relationships need to exist in my diagram (and between which fields)?

What table should I be displaying records from in the search layout?

Will my OnObjectEnter script trigger idea work as planned?

Thank you in advance for your help! You guys are lifesavers!

[comment]

when I select a particular record in the portal, it inserts DrugName and DrugClass into the list of medications being taken in a particular patient’s record (i.e. a field in the table Patients).

No, no, no - that is NOT what you want. What you need is to create a new record in the Prescriptions table, containing the PatientID of the currently viewed patient record and the MedicationID of the clicked medication record.

The other thing is that you want to be on a layout of Patients when this happens - therefore the "search" portal (the one you want to select from) must be based on a relationship between Patients and (another TO of) the Medications table.

I suggest you study these two examples:

http://fmforums.com/...358#entry339358

http://fmforums.com/...726#entry291726

[doughemi]

Whenever you have two tables which need to relate many entities to many other entities ( Patients and MedLibrary, where many patients may take any drug, and any patient may take many drugs), you need a Join table Prescriptions. Each record in that table is an instance of one patient taking one drug. It would have a patient id, a drug id, perhaps dosage info, and other information regarding that instance of that patient taking that drug. It is related to Patients via PatientID, and to MedLibrary via DrugID. Once you have that, you can describe relationships that will tell you which patients take a particular drug, or which drugs a particular patient takes, or how many patients take a drug, or a myriad of other reports.

[bcolburn]

Thank you both. Consultant, you are indeed correct that studying other databases is the best way to learn. Sorry for snapping on my other thread. And Doughemi, thanks for translating while also being clear and compassionate.

[doughemi]

If you have other relationships between Patients and MedLibrary, (including relationships via other tables), then you should probably start out by making a new Table Occurrence of each of the tables involved in order to prevent circular paths. Then add the relationships, and make sure that the layouts you create for exploiting these relationships are based on the correct Table Occurrence. This makes following the logic much, much simpler down the line (no need to ask me how I know that!), and TOs are cheap in terms of file size and memory usage.

[bcolburn]

Out of curiosity, since I already have all three fields of interest lumped in the MedLIbrary table (DrugName, DrugClass, and QTRiskLevel), is it possible to just create a calculation field that synthesizes this information from all three fields and then link that single field to the Prescription linking table? If not, why? I'm not sure I understand why all the drug details have to belong to their own unique tables yet.

[bcolburn]

Ah, I think I answered my own question. If I do what I just proposed, I only get a single line of text, which I think would make it more difficult to search the database for patients taking a certain drug or patients taking a certain class of drug.

[doughemi]

Yes, that is exactly what you want to do. Never duplicate information! The information in the Prescriptions table has to do with that specific Rx -- dosage, start date, end date, perhaps side effects noted in that patient. The info in the MedLibrary table has to do with the drug itself -- class, proprietary brand name, generic name, class, etc.

[doughemi]

Ah, I think I answered my own question. If I do what I just proposed, I only get a single line of text, which I think would make it more difficult to search the database for patients taking a certain drug or patients taking a certain class of drug.

You got it!

[bcolburn]

Isn't that exactly what Consultant is doing in his examples? It looks like he is creating a duplicate field for, say, Drug Class, that he can put into the DrugName table and use to link the two. But is the value in DrugName calculated from the value in the DrugClass table or just text and the fields are linked? In his sample database, it's just text. I guess I'm confused about the fields in the linked tables. I understand that having the links allows them to communicate, but since things like the drug name/class, etc never change, why not just use a copied TO of the MedLibrary and link all three fields (Name, Class, RiskFactors) to single set of duplicates in the Prescriptions Table? Why is it necessary to create a different table for each attribute if the attributes never change? And how can the tables still communicate and store the specific info for each drug if a particular table has fewer records to match with? If there are 200 drug name records and only 4 drug class records, how will it know which links up to which unless each drug has a single record with all of that data present? I only ever have to search by drug name (not attribute, necessarily), so I'm not sure if I'm making extra work to do this or if I'm just misunderstanding the purpose of this design,

[comment]

Almost without exceptions, you should use a meaningless ID as the "link" between tables. This way, you can change any attribute (e.g. the name of a drug) and your relationships will keep working.

If there are 200 drug name records and only 4 drug class records, how will it know which links up to which unless each drug has a single record with all of that data present?

In your example, each drug record would have a ClassID value - one of 4 possible - linking it to its parent class record.

[bcolburn]

OK. Is there a way to enter the serial number after records have already been created?

Or even better, since I already have a value list for all Classes and all RiskClasses and all PathologiesTreated, is there a way to transform each value in the value list into a record in the right table?

[comment]

Yes. For the parent table, define the ID field to auto-enter a serial number. Then show all records, click into the field, and select Records > Replace Field Contents… > Replace with serial numbers (+ Update…). Once you've done that, you'd probably want to deny entry to the field and/or make it non-modifiable.

If you already have child records in another table, replace the new matchfield's value with the parent's new ID, using your existing relationship - then change the relationship to use the new matchfields.

[bcolburn]

I thought about this over the weekend and I wanted to clarify something. What fields actually go in the portal? On each drug record, am I using the DrugName field from that table, the DrugClass field from its own table, and the DrugRisk field from that table? Or am I creating a DrugClass and DrugRisk field in the MedLibrary table and then the relationships described above take care of linking the tables? I understand why my proposed suggestion of a single text field that merges these attributes won't work, but I think I'm still just a little unclear on why how the relationship model you guys both described functions. Can one of you explain what I'm actually doing when I link the ID fields and how that affects which fields I put into the portal?

[doughemi]

Your MedLibrary should have information about each drug. If there is any chance that characteristics of a drug will ( a ) change, or ( b ) be expanded or reduced, those characteristics should be in their own lookup table, so that adding or deleting those new characteristics does not mean rebuilding your entire solution. For example, if Drug Z is discovered to have a new risk factor, you don't have to tear apart your MedLibrary table to add the risk to Drug Z's record; you simply have to add a new record to the DrugRisk table relating Drug Z's ID to the ID of the new risk (Risks should be another separate table--DrugRisks is a Join table which will have separate records each containing one DrugID and one RiskID).

When you link ID fields, a parent record has access to all the information in all child records which have the same ID. Example: Let's look at Patient ID 1. The patient ID field is linked to a PatientID field in Prescriptions. Also in Prescriptions is a link to MedLibrary MedID. If patient 1 takes drugs 11, 12, and 13, then there are 3 records created in Prescriptions, one for each drug. The MedLibrary table has records with everything about each drug, including DrugName. Drug 13 is named "Wonderjuice".

A portal on a Patient layout might show records from Prescriptions. Each of those records has a MedID (which does not necessarily need to be displayed in the portal), which allows the Prescription record to access the related record in MedLibrary. You can then put the MedLibrary::DrugName field in the portal, and FM will display Wonderjuice in the portal on the Patient layout.

You would then create other relationships with different Table Occurrences to display other portals.

[bcolburn]

OK great. That's what I thought and I think I actually don't need as much complexity as I now know how to create to solve this particular issue. But this is great to know. Thanks for your help and patience!

[bcolburn]

I have a related question on this now. I am trying to create my search field in a way that it can search my portal recordsbased on both brand name and generic name. I currently have both names listed in a single field as "Brand (generic)" and was hoping to use the PatternCount() function to run the search. Is there a way to search fields in a portal record this way?

[comment]

IIUC, this is not really about searching, but rather about establishing a relationship. Say you have a relationship such as:

SomeTable::gSearchPhrase = Data::Name

In order for a record in the Data table to become related (i.e. show in the portal based on this relationship), at least one of the values in Data::Name must match one of the values in the gSearchPhrase field. The term "value" here means an item in a return-separated list.

For example, if gSearchPhrase contains "Cat", the following records will be related:

"Cat"

"Dog¶Cat¶Mouse"

but these will be NOT:

"Cats"

"Dog, Cat or Mouse"

Keep in mind that you can also use portal filtering; a bit slower, but offering more options.

[bcolburn]

Gotcha. This is the problem. If I need to match the phrase directly, I was thinking maybe it's worth breaking the drug name into two separate fields for brand name and generic name. If I do that, is there a way to have a single g.SearchPhrase field search both of them at the same time? Or do I need to have two separate search fields that work using the relationship you just described?

[bcolburn]

Oh wait. Can I just turn every name from "Brand (generic)" into "Brand¶generic" so I don't have to do as much work?

[doughemi]

That'll make it harder to update later when brand name drugs have new generic equivalents available, and you might not be involved in the project.

[comment]

I was thinking maybe it's worth breaking the drug name into two separate fields for brand name and generic name.

That's always a good idea (one fact per field). Once you do that, you can define a calculation field =

List ( BrandName ; GenericName )

and use it as the matchfield for the relationship.