Jump to content
Sign in to follow this  
wedgeman

Multiple tables viewed in a portal

Recommended Posts

wedgeman    0
Posted (edited)

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 by wedgeman
edited for clarity and communication

Share this post


Link to post
Share on other sites
comment    1,371

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?

 

Share this post


Link to post
Share on other sites
wedgeman    0
Posted (edited)
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 by wedgeman

Share this post


Link to post
Share on other sites
comment    1,371
Posted (edited)

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 by comment

Share this post


Link to post
Share on other sites
wedgeman    0
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?

Share this post


Link to post
Share on other sites
comment    1,371
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?

Share this post


Link to post
Share on other sites
wedgeman    0
Posted (edited)

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:

image.thumb.png.5fbe4c017621e436c7953591209bfa93.png

Here's how it actually plays out..

image.thumb.png.618dac825a78f8363221a6fe0ec7296b.png

 

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

Edited by wedgeman
graphis issue

Share this post


Link to post
Share on other sites
Posted (edited)
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 by Steve Martino
  • Like 1

Share this post


Link to post
Share on other sites
wedgeman    0
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!!!!!

Share this post


Link to post
Share on other sites
comment    1,371
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.

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

  • Similar Content

    • By 123
      Hey,
      I have the following problem. I'm working on a solution for the owner of several restaurants. Each restaurant has meetings once or twice a month. How would I structure the tables so that I could have a layout on which I select a restaurant and then inside a portal I get a list of all employees. On the portal for each row I want to have a button or checkbox to mark if an employee is present or not.
      I already have the following tables:
      Restaurants, Employees and Meetings
      So far I have a relationship between the Restaurants and Employees table which I use to assign employees to a restaurant, and a relationship between the Meeting and Restaurant table, which allows me to show all employees that work at a selected restaurant using a portal on the meetings layout. How do I proceed to solve my problem? Do I create another table MeetingAttendees or something similar that I use to keep track of people attending a meeting? How would I create a relationship then to allow me to mark certain employees as present and absent?
      Thanks in advance,
      Mike
    • By docasar
      Hi,
      I have just downloaded the 360works Email plugin Sample and I am doing some tests to check whether this would solve my problem. I would like to be able to download all emails from my email account into "messages" table and then on each customer record show those emails which are related to that customer via a portal based on a relationship between, but what I can not figure out how to do it is that the portal would show but types of emails those marked as "from" emails and those marketd as "to" emails. So far I have created a relationship between Customers::email  ------- Messages::From and the portal would show all "from" messages. If I would set the relationship as Customers:email ------ Messages:To, the portal shows all "to" messages, but what I am trying to make is a join portal that would show by date both "from" and "to" messages, then I plan to make a Conditional Formating saying that if Customers:email = Messages::From then all text in blue, and all text in green when it is TO.
      Is this something that can be achieved?
      Thanks a lot for any tip,
      Luis
       
    • By TJ53
      I guess people are using a similar technique for creating and editing portal records after the new FM16 card window feature, any posts or links about this?
      In any case, this is how I'm doing it (sample attached) ... any feedback appreciated!
      Create or edit portal records.fmp12
    • By wedgeman
      So i'm dealing with two tables.
       
      1.  VisitTable.
      2. GraphicsTable
       
      in a VisitTable layout, i have one or two portals related to the GraphicsTable, each related by a different factor.  One portal would be related by a Date field, the other would be a ClientName field..
      My desire is to click on a thumbnail in either one of these portals, and have that selected image be displayed in a separate/larger image window (which could be a container field in the current table, Or another portal?  I don't care how - i just need to have it show up) 
      I don't know the best method for doing this (ie., click thumbnail to have image fillin a separate location).  In this particular instance, I don't want this to be a separate window, but to display in a container field view on the current window/layout. 
       
      how do I accomplish this?
      thx
       
       

    • By Rachel
      This is my first time posting, and I apologize if this is the wrong place. I've only been filemakering for a few months, so again, apologies if this is a stupid problem.
      I could use some advice.   I'm working on a solution that must create monthly reports in the following form:   http://i.imgur.com/Cf4Fjic.png   I've given up trying to bend the summary fields to my will, and began using the following approach:   Make two new tables - Queries and Reports.   In queries table, I have fields for M1, M2, M3, Region, Category, Subcategory and four result fields (M1, M2, M3 and YTD.) I also have foreign key into the reports table.   Then in reports table, I portal into the Queries table.   When report is run, a bunch of queries in queries table are created. The Results are populated via ExecuteSQL expressions.   My question is mainly this: Is this too backwards? I feel like maybe this is too weird of an approach and I"m just missing something simple about reporting and creating summaries?

      Thanks!  
×

Important Information

By using this site, you agree to our Terms of Use.