Viewing "Grandchildren" from context of "Grandparent"

[GarthT]

Hi

I am trying to set up a database to review some surgical cases of mine. 

The basic structure seems to work fine.

I have attached an abridged section of the database with four tables:

Patient --< ProcedureEvent --< PrimaryProcedureEvent >-- PrimaryProcedure

What I would like to do is:

When looking at the Patient layout (Grandparent) be able to see a summary of all of the Primary Procedure Events (Grandchildren).

The attached file has some text dummy data on the Patient layout to show what I am trying to achieve.

I have tried using portals bu I can only get the first "Grandchild" to show

Thanks for any advice and help

Garth

 

Parent_Child_Grandchildren.fmp12

Edited January 16, 2018 by GarthT
Minor change to relationship diagram

[Steve Martino]

Your structure seems on the right track, but you have the fields in the wrong table.  If you think of what you are doing like an invoice database you will be better off.  Of course I'm not a doctor so I may be guessing incorrectly.

Customer---<Invoices-------<LineItems>-------Products

                       equals

Patient --< ProcedureEvent --< PrimaryProcedureEvent >-- PrimaryProcedure

A primary procedure should just be a list of possible procedures anyone can get (like a product)

Primary Procedure Event will be a list of line items (possible procedures)

Procedure Event is like an invoice.  It only needs a date and fields related to 'invoices' in general-like start and end times and results.  Everything else should be in line items.

All the fields specific to a patient should be in patient field

Edit:

To add, then when you want to view a report about a patient and all their procedures you base your report on Invoices, for general info (Events), or LineItems, for all Primary Procedures

It would also help if you put in 2 or more patients with different and similar procedures to see if your reports and portals are showing the correct data.

Edited January 17, 2018 by Steve Martino

[Lee Smith]

Hi GarthT

Here is a couple of demo files at databasepros.com that may help?

[GarthT]

Thanks Steve,

I understand the analogy.

The actual database (now) attached makes your method difficult to implement (I think!)

ProcedureEvent (The surgical operation in normal language) as well as being the child of Patient and the parent of PrimaryProcedureEvent is also the parent of RepairEvent, SurgeonEvent, PathologyEvent, AntibioticEvent and ComplicationEvent.

The ProcedureEvent form is where I envisage putting most of my data into the database. This seems to work well using a series of portals.

I have moved some of the fields out of ProcedureEvent into RepairEvent - similar to your suggestion

I have left some of the attributes (height, weight, BMI etc) in the ProcedureEvent table rather than moving them to the patient table as they are those measurements at the time of the ProcedureEvent and they could be different for different ProcedureEvents - hope that makes sense.

On the Patient form I have set up a series of tabbed reports containing portals to summarise (and create some parts of) Referrals, Reviews, Comorbidities and Pathology. These all work well. I plan to make the black boxes a button to jump to the full forms

The problem I am having is that Procedures (PrimaryProcedureEvent) can have more than one value for a given date, and they do not show in the portal.

Any more thoughts? !

Plionidal solution 2017.fmp12

Thanks Lee

I have downloaded the first file - it looks difficult - I am not sure I am up to three coffee cups!

The second link is broken

G