Surgical database - Unable enter portal field to create or view related field

[GarthT]

Hi everyone!

I am a surgeon trying to create a FM solution to analyse some of the cases I have been involved in.

The main table I have set up is: Patients and one patient can have many "Referrals"; "Consultations"; "Admissions" and "Operations" - I have set up one to many relationships and this all seems to work fine.

I am working on the relationships between "Operations" and "Surgeons"; "Procedures"; "Antibiotics" and "Complications". I believe these should be many to many relationships; i.e. one operation can have many surgeons and one surgeon can do many operations. Thus I have set up a join table containing the foreign keys for Operation and Surgeon.

I want to be able to enter surgeon specific data (Name, Grade etc) in the Operation table by looking for it in the Surgeon table.

I believe this would be best done using a portal.

Try as I might, I cannot get this to work. The portal just appears as a box in browse mode and I cannot enter it, let alone, look up records from it.

I have set the portal to allow entry in Browse mode and have set the relationship to allow creations of records via this relationship in the Operation table.

I am going around in circles and not getting anywhere! I would be grateful for any help.

I have attached to file

Thanks

Garth

Pilonidal Solution v.2.fmp12

[eos]

I have set the portal to allow entry in Browse mode and have set the relationship to allow creations of records via this relationship in the Operation table.

But you want to create records in the join tables, not the Operation table; so you need to set the table occurrences of these join tables to allow creation.

The other thing (for the portal on your layout) is that you cannot use the surgeon pk in that portal; if you want to assign a surgeon to an operation procedure, you should add a surgeon fk to that table and set that to a surgeon pk. (Or use an id from one of the OperationSurgeon join records of that operation; I'm not familiar with your intended data model, so either may be right.)

That id would then refer to the Surgeon table. (Be careful: you probably want to record an attribute like “grade” that changes over time with the operation – or each operation would be displayed with the surgeon's grade at the time of display, not of operation …)

See if the attached file helps you.

PS: Any reason to prefix your table names with “tbl”?

Pilonidal Solution_v2_eosMOD.fmp12.zip

[GarthT]

Thanks for this - I have just made the simple changes to create records in the join table and this has worked fine.

I have also put the surgeon's grade in the procedures join table to reflect that which was in effect at the time of the operation.

I will have a play around with the other stuff, over the weekend.

Thanks again for taking the time to do this - I have been struggling to get my head around the many-to-many relationship and the portal thing for ages.

Oh - I started using the tbl prefix after a brief dabble with Access (!!) - soon gave that up; I have got rid of them in the latest version of my solution.

I will post another version as I progress

cheers

Garth

[GarthT]

I seem to have that part working; but I have come across another issue!!

An "Operation" can have many "Procedures performed" and a "Procedure performed" is done by many "Surgeons".

But, each "Procedure performed" within an "Operation" can have a different set of "Surgeons"

Any idea how I would set things up so that Procedure 1 is done by surgeons A, B and C and Procedure 2 is done by surgeons B, C and D

Do I need two Portals? One for each procedure.

Thanks for any help

I have attached the latest file for interest

Garth

Pilonidal Solution v.2.fmp12

[eos]

An "Operation" can have many "Procedures performed" and a "Procedure performed" is done by many "Surgeons".

But, each "Procedure performed" within an "Operation" can have a different set of "Surgeons"

Any idea how I would set things up so that Procedure 1 is done by surgeons A, B and C and Procedure 2 is done by surgeons B, C and D

I suggest you think first in terms of relationships, not portals – first get the data model right, then think about implementation.

If you spell out the relationships (and their cardinalities) like above, you should automatically see what you need:

One Operation can have many Procedures, and every Procedure belongs to one operation:
Operation --< Procedure

One Procedure can have many Surgeons, and a Surgeon can partake in many procedures – so you need a join table to denote these people's presence:
Procedure --< SurgeonInProcedure >-- Surgeon

Combine this, and you get:
Operation --< Procedure --< SurgeonInProcedure >-- Surgeon

To implement this, you could have a Procedure portal on an Operation layout; selecting a Procedure could set a field in Operation to this procedure's primary key, which you use to display and create SurgeonInProcedure records for the selected Procedure:

Operation --< SurgeonInProcedure_forSelectedProcedure

which is simply another table occurrence of SurgeonInProcedure, related via the selection field:

Operation::selectedProcedureID = SurgeonInProcedure_forSelectedProcedure::id_procedure

 

[GarthT]

Thanks again. I understand the one to many concept and resolving the many to many with a join table; however, the statement really reads 

One operation can have many procedures, but a procedure can be in many operations 

So I think I need another join table of "Procedure performed" as I designed things orginally. 

I think I understand The rest of the logic is as you have written, but How do I set up two join tables in the same statement? 

Is the following correct ?

Procedure performed--<SurgeonInProcedure>--Surgeon

with

Operation--<ProcedurePerformed>--Procedure

Sorry if I am being dense!

 

[eos]

Well, this simply means that a Procedure is set of rules (a protocol), and that a ProcedurePerformed (or to use my nomenclature: ProcedureInOperation) is an actual event, scrubs and all, based on that concept.

Sorry if I am being dense!

Not at all; you're correct. It's hard to show in text, but it amounts to

Operation --< ProcedureInOperation >-- Procedure
                                      |
                                     ^
             SurgeonInProcedureInOperation >-- Surgeon

[GarthT]

Thanks. That's exactly how I drew it on paper:-)

Now I just need to make the portal work. 

In the words of Arnie....

"I'll be back"!!

[GarthT]

Well - I have made some progress and I have a working portal - just a of nagging doubt! 

The relationship diagram looks correct, based on what we have discussed above.

I have created a portal, called ProcedureInOperation. I populated the three Surgeon fields using a value list based on "Surgeon::__pkSurgeonID and "Surgeon::SurgeonLastName" taken from the Surgeon table.

It seems to work, but is not using the fkSurgeon field and so I wonder if this is wrong and it will catch me out later,

Grateful for any further help.

Pilonidal Solution v.2 28June.fmp12

[bruceR]

Here is a modified file to consider. Any time you have numbered field (role1, role2, role3) etc it strongly suggests that the data structure isn't normalized.

 

Pilonidal Solution MODBFR.fmp12.zip

Edited June 28, 2015 by BruceR

[GarthT]

Hi

Thank you for this. I am a bit of a novice - so need to read up on normalised data structures!!

I like the way your interface looks; the problem I have is that an operation consists of perhaps three or four procedures (which seems to be how you have set things up); but the surgeons often change roles during the operation for the different procedures. There may be a maximum of up to 3 or 4 surgeons - I've only made space for 3 (could not work out how do do more!!)

So I will do one procedure in an operation "P" and my registrar will assist me "A" ; then I will teach them to do another part "T"; whist they are supervised (SS). I hope that makes sense.

thanks again

Garth

 

Pilonidal Solution v.2 28June No NHS data.fmp12

[eos]

There may be a maximum of up to 3 or 4 surgeons - I've only made space for 3 (could not work out how do do more!!)

I haven't seen Bruce's file, but what you describe is what the table

SurgeonInProcedureInOperation

is supposed to provide: any number of people involved in a procedure. Just add a role attribute to that table (and whatever other ones you may need to describe that involvement)

[bruceR]

Garth: you did not need to change anything in my example.

YES, you need to learn about normalization.

It provides for any number of procedures associated with an operation; and any number of surgeons and roles associated with an operation-procedure.

Edited June 29, 2015 by BruceR

[GarthT]

Thanks Both

Bruce - When I tried to open your file my system defaulted to Filemaker Pro 12 advanced and the buttons did not work. However, I tried again this morning with Filemaker Pro 14 - wow - brilliant!!! Thank you so much. 

The file I posted was what I had been doing in the background - I did not change anything in your file!

I have understood that entities need attributes and that data should only need to be entered once; I felt something was wrong with my implementation, but could not work it out; obviously my understanding is a limited!

I see from your solution that you have prefixed some of the fields with a "z" - what convention are you using this for? 

I also see that in the ProcedureinOperation table you have a __pkProcedureinOperationID field and also a zProcedureinOperationID field - puzzled........

I would love to understand how you made this work.

thanks again

BW

Garth

[bruceR]

I use a z prefix to designate global fields. It is a fairly common practice. The globals are selectors for an auto-create relationship. You will also notice existence of some scripts, used to create the join record.