Table 1 (Visits) is a clinic visit table, with numerous fields of data.

Table 2 (Prescriptions) is a related table for prescriptions given on a particular visit. Table2 is related to Table1 by fkey  - - -    T1ID.

Table 3 (Patient) is a patient table (contacts).  PatientID is the identifier for Patient and is related in Visits,Prescriptions, and Patient

I need to have (in a Visits layout) a portal (self-related by PatientID), which shows a list of previous visits to the clinic by this patient.

On this portal I need to show any (1 or many) Prescriptions (from Prescriptions table) which would've been given during that particular Visit

So it's in essence a need to show 2 foreign tables (1 being a self-view by PatientID and the other being the Prescriptions but filtered by PatientID

I have been unable to figure out a good method for this.

My only thought has been to give up on working *IN* Visits, but creating a separate table ("Combiner"), and in Combiner, hold all Visits and Prescription data.

Could I also have a Combiner table that's simply an active "calculation" table, where these two tables are 'looked up' on an as-needed basis, combined, and fed into Visits via a portal? (so it's not actually held data, but is looked up as needed)

what's the preferred method?

Edited July 6, 20178 yr by wedgeman
edited for clarity and communication

Please do not use abstract names like Table 1, T2, P3ID etc. when asking here. Give your tables and fields meaningful names e.g. Patients, Visits, PatientID, and so on.

IIUC you have the following structure: 
Patients -< Visits -< Prescriptions 

 

1 hour ago, wedgeman said:

in this portal I need to show any T2 prescriptions which would've been given during that particular visit.

How exactly do you want to show multiple prescriptions in a single portal row?

 

33 minutes ago, comment said:

Please do not use abstract names like Table 1, T2, P3ID etc. when asking here. Give your tables and fields meaningful names e.g. Patients, Visits, PatientID, and so on.

IIUC you have the following structure: 
Patients -< Visits -< Prescriptions 

How exactly do you want to show multiple prescriptions in a single portal row?

Correct...(sorry for my poor comm skills on this)

Table Patients

Table Visits

Table Prescriptions

 

You said:   " How exactly do you want to show multiple prescriptions in a single portal row?"

 

Yes... This is the challenge. I don't know exactly HOW to show multiple prescriptions in a single visit (when viewing those visits in a self-related portal as a reference).

This is a reference for the clinician to view a history of previous visits (a self-related portal, showing related by PatientID).

As it is possible (and common) for a single visit to have multiple Prescription instances (or records), I'm really unsure of the best possible method.

Should I create an intermediate table, which calculates all these prescriptions (ie, PrescriptionNameField & "," & PrescriptionNameField & "," & PrescriptionNameField ), or is there a better method? I'm realizing this component is actually more challenging than many of the other relational challenges in this marathon 5 day project!

Edited July 6, 20178 yr by wedgeman

I am afraid  I cannot answer the question for you, because I don't know the purpose of showing these prescriptions. Basically, you have two options: (1) show the prescriptions as a list inside one of the fields in the portal, or (2) use a portal to the prescriptions themselves (so that each prescription is a portal row) and group them by visit.

The first option is easy to implement: all you need is two calculation fields - one in the Prescriptions table to concatenate the details you want to show and one in the Visits table to list them (using the List() function). No "intermediate table" is required for this.

Re the second option, note that such portal will not show visits that did not result in a prescription. OTOH, the first option will not allow the user to click a specific prescription in order to see more details.

 

Edited July 6, 20178 yr by comment

1 hour ago, comment said:

I am afraid  I cannot answer the question for you, because I don't know the purpose of showing these prescriptions. Basically, you have two options: (1) show the prescriptions as a list inside one of the fields in the portal, or (2) use a portal to the prescriptions themselves (so that each prescription is a portal row) and group them by visit.

The first option is easy to implement: all you need is two calculation fields - one in the Prescriptions table to concatenate the details you want to show and one in the Visits table to list them (using the List() function). No "intermediate table" is required for this.

Re the second option, note that such portal will not show visits that did not result in a prescription. OTOH, the first option will not allow the user to click a specific prescription in order to see more details.

 

Many thanks.

the purpose of showing prescriptions is all as a reference.. Say the clinician is reviewing prrevious patient history, needs to see previous visits and refer to any medications prescribed, to make an intelligent assessment. While it would be nice to see the prescription, it's not necessary (after all, if he were looking at the visit, he'd simply click into the visit itself, which has a direct portal to its related prescripts).

so in the end, I figured out a solution

1. create fields which are as-needed calculations of the actual Prescription fields.

2. create the portal (selff-related) to show related Visits. 

 3. install chevronned text strips (i dunno what you pro's call 'em)  ->     <<Visit::PrescItemName>>,   <<Visit::PrescItemSize>>, etc....    these will look up the data as needed...

 

any problem with running this? Will it cause issues?

3 hours ago, wedgeman said:

any problem with running this?

Hard to say, because I don't fully understand what you did. How exactly are these calculation fields defined?

In Visits table, I created 4 additional fields. These are each calculation fields [  List(Prescription::ItemName) , List(Prescription::ItemSize) , List(Prescription::ItemCycles), and List(Prescription::ItemQtyTotal) ] which reach thru the same realtionship (by VisitID#) to the Prescription table..

These calc fields are not ONLY used inside the self-related Visit reference portal.  So when one flips to that layout, the portal sees a visible list of all related prescription names & sizes inside the portal... 

 

It's working fine, i think. My primary concern is whether or not running a handful of calculation fields in a portal would/could cause any sort of performance glitch - is that an issue?

here's a snapshot of the double-chevron views:

Here's how it actually plays out..

 

Side question:  what do the pros call those double-chevron text fields?  (<<table::fieldname>>)

Edited July 7, 20178 yr by wedgeman
graphis issue

1 hour ago, wedgeman said:

 

Side question:  what do the pros call those double-chevron text fields?  (<<table::fieldname>>)

Merge fields.  You can see it in the menu bar in layout mode Insert>Merge Field...

Also as a tip, you can highlight everthing between the first and last chevron and change the font to 1 point, to shrink them up in layout mode for easier viewing.  If you ever need to see which field it is, click on the field, select 12 point.  When your done, click outside the field and Ctrl +Z to undo and return it as it was.

<<LONGTABLENAME::ExtraLongFieldName>> would basically look like   <<LONGName>>    (1 pt. font not available on this forum)

Edited July 7, 20178 yr by Steve Martino

1 hour ago, Steve Martino said:

Merge fields.  You can see it in the menu bar in layout mode Insert>Merge Field...

Also as a tip, you can highlight everthing between the first and last chevron and change the font to 1 point, to shrink them up in layout mode for easier viewing.  If you ever need to see which field it is, click on the field, select 12 point.  When your done, click outside the field and Ctrl +Z to undo and return it as it was.

<<LONGTABLENAME::ExtraLongFieldName>> would basically look like   <<LONGName>>    (1 pt. font not available on this forum)

Oh my word....  things i wish I'd known years ago!!!!!

3 hours ago, wedgeman said:

My primary concern is whether or not running a handful of calculation fields in a portal would/could cause any sort of performance glitch - is that an issue?

Probably not, since you're only showing the prescriptions of a single patient at a time.

 

3 hours ago, wedgeman said:

In Visits table, I created 4 additional fields.

As I said earlier,  you could have done this with 2 calculation fields only: a field in the Prescriptions table =

ItemName & Char(9) & ItemSize & Char(9) & ItemCycles & Char(9) & ItemQtyTotal

and a field in the Visits table using the List() function to create the above line for each prescription.