More specific than match feilds?

[Cannonball]

I need to display a related feild in a layout with a more specific query than just math feilds. Here's the scoop:

It's a database of patients and professionals. Each patient has a relationship with several professionals for consultation, but each patient also has a unique relationship with just one professional, their "attending physician". I can easily set up a portal for the related professionals, but I want to single out the attending physician and display it elsewhere on the layout, by itself.

I believe I have set up the tables in the proper way to do this, I have a tabl for patients, and professionals, and also a patient/professional relationships table, where the feilds are like so:

patient_id

professional_id

subject_of_relationship

rel_begin_date

rel_end_date

The feild "subject_of_relationship" is the key here. It is used for two purposes... to report the subject matter of consulting relationships (i.e. "oncology", or "knee specialist"), but also to distinguish which doctor is the attending physician of which patient.

It would be extra nice if I could also omit the attending physician from the portal results, but I'm okay if we skip that.

Thanks!

[comment]

This part is not clear:

The feild "subject_of_relationship" ... is used ... also to distinguish which doctor is the attending physician of which patient.

What (or who) determines which doctor is the attending physician? Is it a deliberate selection, or is given by the contents of the 'subject' field?

[Cannonball]

Sample data from this table:

patient__pro___relationship______begin___end

=============================

2________5____psychiatry_______1/1/01___NULL

2________3____attending_phys__3/3/02___6/1/02

2________7____attending_phys__6/1/02___NULL

5________5____psychiatry_______7/8/01___NULL

In this example, patient #2 has a relationship with pro #5 which is based on psychiatric services, as does patient #5. But you also see patient #2's attending physician relations. in my particular case, the client has stated that there should never be two attending physicians, but that they may change, and we do want to keep a historic record of the changes. Now, here's how I might phrase my SQL to acheive what I want:

SELECT pro FROM pro_relations WHERE patient=2 && relationship='attending_phys' && end=NULL

This query would return the pro who was the current attending physician of patient 2.

I guess the answer to your question is, the "relationship" field (or "subject_of_relationship") is a designation of the nature of the relationship, and may include any subject matter you would have a special doctor for. The thing is, our attending physicians are these same specialists, just for maybe a different set of patients. Data-wise there is nothing special about an attending physician relationship, but on the reporting side, it's different, and I do need that relationship to be special.

I guess the easiest solution would be if I could program the SQL that FMP would use to fill a feild in a layout. But I can't find that functionality, so I'm hoping there's some way. If you think I should ask this question in another topic area, let me know...

Thanks so much!

-Chris

[comment]

Try defining a calculation field in the join table =

Case ( not EndDate and SubjectOfRelationship = "attending_physician" ; PatientID )

Then define a new relationship between Patients and a second occurrence of the join table, matching PatientID with the calculation field.

One a more general level, something seems amiss here. As you said, the SubjectOfRelationship field is being used for two purposes, and this goes against the 'one fact per field' rule. I believe there should be a separate true/false field to mark the relationship as "attending physician".

Edited October 12, 2007 by Guest

[Cannonball]

Thanks for your reply, I'll try it and post my results...

about this...

One a more general level, something seems amiss here. As you said, the SubjectOfRelationship field is being used for two purposes, and this goes against the 'one fact per field' rule. I believe there should be a separate true/false field to mark the relationship as "attending physician".

... for this particular database, this table lists relationships between patients and professionals. Since the same pro can relate to a patient in multiple ways, it is best to have a new record for each 'context' or 'subject of relationship', instead of one line for each actual patient/pro pair. To take your suggestion to the extreme, as I understand it, we would have a primary key made up of the pair 'patient'+'pro', and seperate feild for 'is_attending?', and if we repeat the relationship, we would have to replicate the bolean value of this feild, or, to eliminate that, we would have a laundry list of feilds, such as 'is_psychiatrist?' etc...

It seems best in light of this to have a primary key of four feilds, 'patient'+'pro'+'relationship'+'begin_date'

In this way, the table keeps up with each connection between a patient and a pro, instead of trying to fully describe each pair. Does that make sense? The data in the relationship field is not multi-functional, we just need to report the occurances of a certain value different than occurances of any other value.

[comment]

To take your suggestion to the extreme, as I understand it, we would have a primary key made up of the pair 'patient'+'pro', and seperate feild for 'is_attending?'

No, all I suggested is a separate field for the 'attending' status. It may not be very important, it just doesn't feel right as you have it, because the data in the SubjectOfRelationship field IS multi-functional: it defines the field of specialty AND it defines a status.

However, I may be wrong about this, not knowing your exact situation. I can easily think of an another example where a single field might be totally acceptable. Let's say the subject is given by the doctor's field of expertise, so if the field is "family medicine" then automatically that doctor becomes the attending physician.

It seems best in light of this to have a primary key of four feilds, 'patient'+'pro'+'relationship'+'begin_date'

Well, Filemaker doesn't really have the same concept of a primary key as SQL. Generally, you would define an auto-entered serial number field in every table, and call it the primary key (and use it as such). But Filemaker doesn't care about keys being primary or not - it just looks at the definition of a relationship, and uses the matchfields listed there.

I suppose you are really talking about preventing duplicate entries. For this, you could define a field that auto-enters the unique concatenation, and validate it for being unique. But in Filemakerese, this has nothing to do with primary keys.

[Cannonball]

Is there a way to add criteria to a layout feild beyond the match-feilds of the table relationship? In your suggestion above, wouldn't we have to set the calculation feild as the match feild in the relationship table?

I think one of the problems is that I cannot setup two relationships between tables. Otherwise I could have a second relationship table for JUST attending physicians, and both the consultant and attending physician relation tables would link patients to pros, but Filemaker will not allow this... nor the simpler solution of forgoing historical attending physicians and just adding an 'attending_phys' feild to my patients table...

although, that gives me an idea.

could I (and I'll try all these when I get off work later today) define a calculation feild based on relational values? That is, have a calc feild IN the patients table for attending physician, and use logical phrases similar to your example above? Then I could report this feild seperate from the protal which reports all pro-relations...

Anyway, I appreciate your time and input, Thanks!

[comment]

Is there a way to add criteria to a layout feild beyond the match-feilds of the table relationship?

I'm afraid that doesn't make much sense to me. You need to try and leave SQL-talk, and more importantly, SQL-think, behind.

In your suggestion above, wouldn't we have to set the calculation feild as the match feild in the relationship table?

No. In your basic model, that table links by foreign keys alone (one at a time). But if you ever wanted to use the table as the parent in another relationship, you would use the serial ID for this.

I cannot setup two relationships between tables.

Sure you can. That's exactly what I suggested you do. Note the difference between a table and a table occurrence. An occurrence is an "alias" to a table. You can have as many of them as you want.

could I (and I'll try all these when I get off work later today) define a calculation feild based on relational values? That is, have a calc feild IN the patients table for attending physician, and use logical phrases similar to your example above? Then I could report this feild seperate from the protal which reports all pro-relations...

You probably could, but it would be difficult and cumbersome. You would need to use a custom function to go over all related records.

[Cannonball]

Well, I spoke with my client and we decided to continue development with LAMP services instead of FileMaker... I'm kinda sad, I was looking forward to working with FMP, but I know that there's not much I can't do with PHP/MySQL... and I just don't know what the limits of FMP are. Frankly I don't have the time to find out. I very much appreciate your help, comment. I know where to come when I finally do develop an app with this software. Thanks!