Solved: Need help setting up relationships based on status of line items

[soulicious]

Hello,

I'm having trouble with the initial set up of my relationship.  For the most part it is a simple 3 table (one to many...many to one) design with a sort of "line-item" table in the middle.  There are two sticking points for me:1) I would like to set up the relationships based on the "status" of the line items and 2) the portal should only show records that belong to the user logged in.

I am attaching a picture to show the simple table relationship between my current tables: user, charges, patients.

I have set up the privilege set so that the user can only see his/her charges when logged in, so I think I have sticking point 2 taken care of without requiring the relationship to handle it.

The problem with sticking point 1 is that I want the "Patients" layout to contain 2 portals, one pulling the charges with a "current" status.  I would use table occurence "Patients" for this portal.  The second portal would be for showing the charges with status "archived" and would use table occurence Patients 2  (normally I would just use portal filtering to get the results I need, but I am calling the data via PHP and portal filtering doesn't seem to apply while using PHP, so I need to figure out how to do it based on the relationships).  How can I incorporate the status of the Charge into the relationships between the patients and their Charges where the Patients table is related to Charges with the corresponding kf_ID_Patients and ChargeStatus = "Current" and at the same time  where the Patients 2 table is related to Charges with the corresponding kf_ID_Patients and ChargeStatus = "Archived" ?

Currently the patients are simply related to the charges via the Primary to Foreign key relationship, but this allows the portals to "see" all the charge records for each patient.  I need them to reflect only the charges dependent on the appropriate status.

Thank in advance for your help!  I feel so close, yet I can't seem to visualize how to do this!

Edited August 3, 2015 by soulicious
Added "Solved" to subject

[Agnes Riley]

If you upgraded your FM version you could filter the portal for the status: one portal for Archived and another for Current.

So first of all if you want to show the charges from the Patients' perspective you should dupe the Patients TO and move it down below users.

Then et up a field "statusA" and a field called "statusC" in Patients. Then you can connect another set of two Tos to then: one called Current Charges and another one called Archived charges.

Of course I'd recommend you read about the anchor buy method and naming conventions. It will help you greatly in future development if you learn to do things right from the start.

 

[soulicious]

Hi Agnes, thanks for your response.  Upgrading is not an option right now, but thanks for the tip.

As far as the relationships go, I do want to show the charges from the Patient's perspective.  Since I have 2 table occurrences for Patients, you're saying I need another table occurrence for Charges?  How do I relate the "statusA" and "statusC" fields in Patients...relate them to which field in Charges?  Do I need to relate both the Patient IDs and the status fields?  

Thanks for your help!  Sorry for all the follow up questions, but I don't think I fully understood your explanation.

[comment]

Could you explain this part?

 I am calling the data via PHP and portal filtering doesn't seem to apply while using PHP

 

 

 

[Agnes Riley]

On the left side you always have the parent table (table occurrence, aka TO): Patients. That has the two aforementioned text fields set up (A and C for the different statuses), and on the child side (Charges) you have ChargeStatus. So you link both portals to that. Portal top linked to StatusA, portal bottom linked to StatusC.

If you're limiting the view from the Security perspective you don't need to link to the Users table. That would only be needed here if you wanted to scroll through the users and see everything from their perspective.

Upgrading is really cheap and totally worth it. I can give you a quote when you're ready.

[comment]

Upgrading is really cheap and totally worth it.

I guess I am missing something here: how exactly would upgrading help in this case?

[soulicious]

Hi Comment,

Thanks for responding.  In answer to your question explaining this statement:

"I am calling the data via PHP and portal filtering doesn't seem to apply while using PHP"

 I was able to "filter" the different results between what the two portals show by using portal filtering so that in FileMaker the "current" portal only showed current charges and the "archived" folder only showed archived charges.  However, when I do a query from PHP for the data coming from these portals, it still shows me "All" the charges instead of just the filtered data.  In any case, I have found a good solution for my issue which I will post in a separate reply within this thread.  Thanks!

[soulicious]

SOLVED!

I don't know why it didn't dawn on me at first, but I ended up finding a clean and simple solution without needing to upgrade (not sure that upgrading would have solved the issue anyways since I specifically was looking for a relationship based solution and not portal filtering).

As you can see in the screen shot attached of my relationship between the tables, the kp_ID_Patients in the "Patients" table occurrence is related to the kf_ID_Patients in the charges table.  I simply created 2 new fields in the charges table called kf_ID_PatientsCurrent and kf_ID_PatientsArchived.  I then relate the kp_ID_Patients field in the Patients table occurrence to the kf_ID_PatientsCurrent field.  When this field is populated with the Pt ID, it means that the charge is current. 

I create a second table occurrence of the Patients table (which I already had anyways as you can see in the screen shot), and simply relate this to the kf_ID_PatientsArchived field.   When the kf_ID_PatientsArchived  field is populated with the Pt ID, it means that charge is now archived.  Each table occurrence only relates to the charges that meet the relationships requirements...thus "filtering" between "Current" and "Archived" charges!  It is very simply in a script step to simply fill in the PtID in the proper field (especially since I already had a script marking an "Archived" field in the Charges table as true...now I don't even need to mark it archived, because by definition the field populated with the ID is based on whether it is considered archived or not).  It is also very simple to "view" only the archived or current charges by simply viewing the layout that corresponds to the Patients table or the Patients 2 table.  This also works via PHP!

Hope this helps someone else down the road on dealing with "conditional" relationships!

 

[comment]

However, when I do a query from PHP for the data coming from these portals

Are you talking about Custom Web Publishing? I cannot reconcile Filemaker Advanced 11, PHP and portals.

[Agnes Riley]

SOLVED!

I don't know why it didn't dawn on me at first, but I ended up finding a clean and simple solution without needing to upgrade (not sure that upgrading would have solved the issue anyways since I specifically was looking for a relationship based solution and not portal filtering).

As you can see in the screen shot attached of my relationship between the tables, the kp_ID_Patients in the "Patients" table occurrence is related to the kf_ID_Patients in the charges table.  I simply created 2 new fields in the charges table called kf_ID_PatientsCurrent and kf_ID_PatientsArchived.  I then relate the kp_ID_Patients field in the Patients table occurrence to the kf_ID_PatientsCurrent field.  When this field is populated with the Pt ID, it means that the charge is current. 

I create a second table occurrence of the Patients table (which I already had anyways as you can see in the screen shot), and simply relate this to the kf_ID_PatientsArchived field.   When the kf_ID_PatientsArchived  field is populated with the Pt ID, it means that charge is now archived.  Each table occurrence only relates to the charges that meet the relationships requirements...thus "filtering" between "Current" and "Archived" charges!  It is very simply in a script step to simply fill in the PtID in the proper field (especially since I already had a script marking an "Archived" field in the Charges table as true...now I don't even need to mark it archived, because by definition the field populated with the ID is based on whether it is considered archived or not).  It is also very simple to "view" only the archived or current charges by simply viewing the layout that corresponds to the Patients table or the Patients 2 table.  This also works via PHP!

Hope this helps someone else down the road on dealing with "conditional" relationships!

 

I was recommending something similar. :-)

 

But when I recommended the portal filtering I haven't seen the PHP relevance yet. Of course portal filtering won't work for PHP, because the portal is filtered on the layout of the user's machine.

 

At any rate, glad you found a solution. I STILL highly recommend you learn about the squid or anchor buy method, and not go backwards in your tree.

[soulicious]

Are you talking about Custom Web Publishing? I cannot reconcile Filemaker Advanced 11, PHP and portals.

Hi Comment,

I am using the FileMaker PHP API to access FileMaker Server 11 Advanced, so I guess that is considered custom web publishing.  What are you trying to "reconcile"?