Relating records in the same table

[rkass068]

Hello, 

 

I am having trouble wrapping my head around this concept:

 

I am building a Bone Marrow Transplant (BMT) database. In it I have 3 tables that are important for the purpose of this question

 

1. Table 1 Demographics

Fields: pk_demo, Last Name, First Name, Chart#

 

2. Table 2 Diagnosis

 

fields: pk_Dx, fk_demo, disease, other fields that are not important

 

3. Table 3: BMT

 

Fields: pk_BMT, fk_Dx, IUBMID, Date etc

 

Relationships (so far):

Demographics:pk_Demo--->Diagnosis:fk_demo

Diagnosis: pk _Dx---->BMT: fk_Dx

 

What we have in this database is a bunch of patients (both donors and recipients) for Bone Marrow Transplants. Each patient can have multiple diagnoses and multiple transplants related to each diagnosis. Donors (in rare cases) may also have a transplant down the line. In this case, we made sure the BMT is related to the diagnosis and a donor would have the diagnosis as "donor". 

 

What I want to do is relate the donor patient BMT with the recipient patient BMT. Donor related to recipient. In this case when I input a recipient BMT record, I want to relate it to the donor that way on forms, their donor info also shows up and the fields are repopulated just by selecting the donor IUBMID from a value list of donors only. 

 

Sounds kind of complicated I know. If there is a way by changing up my relationships or keeping them please help and thank you in advance. 

 

This database used to be an access database by the way and they want it migrated to filemaker because the old version is very unstable.

 

Thank you

 

 

 

 

 

[doughemi]

You need to use two TOs of Demographics, one for recipients, and one for donors:

 

Demographics_rec::k_recip >-------BMT::fk_recipient

.................................................BMT::fk_donor-------------------<Demographics_don::k_donor

Diagnosis::pk >-----------------BMT::fk_diagnosis

 

Each BMT record would be one transplant incident. Donor or recipient is an attribute of Demographics, not a diagnosis. Use a checkbox field to cover the rare case where a donor becomes a recipient.  The field k_donor would be a calculation field

 

If(PatternCount(donor_or_recip ; "donor"); Demographics::pk; "") 

 

Likewise with recipient. 

[rkass068]

You need to use two TOs of Demographics, one for recipients, and one for donors:

 

Demographics_rec::k_recip >-------BMT::fk_recipient

.................................................BMT::fk_donor-------------------<Demographics_don::k_donor

Diagnosis::pk >-----------------BMT::fk_diagnosis

 

Each BMT record would be one transplant incident. Donor or recipient is an attribute of Demographics, not a diagnosis. Use a checkbox field to cover the rare case where a donor becomes a recipient.  The field k_donor would be a calculation field

 

If(PatternCount(donor_or_recip ; "donor"); Demographics::pk; "") 

 

Likewise with recipient. 

I Understand what you are getting at here. Yes I can make donor and recipient a demographic attribute. The only problem is that each donor can be a donor more than once, and each recipient can have multiple transplants. For example, say one donor had a collection of bone marrow stem cells, how would I relate that to a specific transplant for the recipient? That same donor can be a donor for someone else after another collection. I know this is a rare case but it can happen and I am trying to cover all grounds. This is the reason why in the BMT table I have an IUBMID number. This ID number is specific to the transplant and not the patient. The type of transplant/collection determines if the patient is a donor or recipient in the current database. 

 

I can make it patient specific but I don't understand this calculation If(PatternCount(donor_or_recip ; "donor"); Demographics::pk; "") and what do you mean by likewise with recipient? 

[doughemi]

Everything is based on the BMT table.  Each transplant is a separate record. It has one donor and one recipient. If  a recipient has 10 transplants from 5 different donors, there will be 10 transplant records, each with a link to its own donor and recipient. If you want to report all the transplants received by a single recipient, create a layout based on Demographics with a portal to BMT.  Because of the relationship to Demographics_don, that portal can display information about each donor.

 

Since there may be many collections from each donor, then you need a Collections table between the BMT table and the Demographics_don table to create the many-to-many relationship between transplants and donors. The Collections table would have a record for each cell collection.  The foreign key for a BMT record would be the Collections ID, which would then be related to the donor id.

 

The purposed of the calculation is to uniquely identify donors in the Demographics table (which should really be named People for clarity).  By using the  k_donor field as the the primary key for the relationship, only those people designated as donors can be used as donors in a BMT.  A similar calculation k_recip = If(PatternCount(donor_or_recip ; "recipient"); Demographics::pk; "") can be used as the primary key for the recipients TO to ensure that only people identified as recipients can be linked as recipients to the BMT.

[rkass068]

So in this case, I would need an extra field in the demographics table that mentions whether or not the patient is a donor or recipient correct? I am having trouble understanding your relationship arrows. I think thats why I'm confused. fk would be the foreign key and k is the parental as  serial number is that correct? 

Edited August 22, 2014 by Lee Smith
Deleted quoted as it served no purpose for this reply

[eos]

Why not lean back and think this through once more:

 

1. You have patients; a patient per se is neither a donor nor a recipient

 

2. Patients have diagnoses, either as donors or as recipients: putting the attribute here allows a patient to play each role multiples times 

 

3. You have BMT incidents; a BMT is related to a diagnosis (or two …), and you want to bring together a donor (diagnosis) and a recipient (diagnosis) 

 

4. A BMT can be created from the context of either a donor or a recipient diagnosis

 

5. This means you have one out of two patients; now you need to find the counterpart: this must be a diagnosis of the respective other type

 

6. Once you have that, either

 

a ) use an additional table in which you keep a set of two records ( (which would be based on a diagnosis) for a BMT, or –

 

b ) – if each diagnosis can only ever be used for a single BMT incident – store the primaryID of the new BMT directly within the involved Diagnoses records

 

7. Either way, a donor can see his recipient and vice versa by relating via the common BMT foreign key.

 

So you have either

 

a ) Patients --< Diagnoses --< DiagnosisInBMT >-- BMTs

 

b ) Patients --< Diagnoses >-- BMTs

 

Makes sense?

[rkass068]

Makes some sense to me, could you break down the primary and secondary key fields for me. Like how this would relate?

 

 

3. You have BMT incidents; a BMT is related to a diagnosis (or two …), and you want to bring together a donor (diagnosis) and a recipient (diagnosis) 

 

4. A BMT can be created from the context of either a donor or a recipient diagnosis

 

5. This means you have one out of two patients; now you need to find the counterpart: this must be a diagnosis of the respective other type

 

6. Once you have that, either

 

a ) use an additional table in which you keep a set of two records ( (which would be based on a diagnosis) for a BMT, or –

 

b ) – if each diagnosis can only ever be used for a single BMT incident – store the primaryID of the new BMT directly within the involved Diagnoses records

 

7. Either way, a donor can see his recipient and vice versa by relating via the common BMT foreign key.

 

So you have either

 

a ) Patients --< Diagnoses --< DiagnosisInBMT >-- BMTs

 

b ) Patients --< Diagnoses >-- BMTs

 

Makes sense?

Edited August 22, 2014 by Lee Smith
Deleted quote as it serves no purpose in this reply.

[Lee Smith]

rkass, 

 

Please don’t quote an entire post when replying to it. If your only intent is to only let the other person know you are replying to them, just use their name in your post.

i.e.

Hi eos, 

@ eos,

eos,

 

If on the other hand, if you are going to make inline comments to the reply, an example of this is shown in this recent thread Filter portal by this calc

[rkass068]

Lee Smith,

 

Sorry

[eos]

could you break down the primary and secondary key fields for me.

 

As I showed this in the relationship diagram above.

 

Either put the foreign key field for the BMT directly into the Diagnoses table, or create a new table where each record is a child of a Diagnoses record and has a BMT foreign key.

 

A BMT would look either into Diagnoses or into that new table to find its two record set of donor and recipient.

 

Each record in either Diagnosis or the new table would use a self-join where BMT foreign key = BMT foreign key and primary key ≠ primary_key (or diagnosisType ≠ diagnosisType) to find its counterpart for the BMT.

[rkass068]

@ doughemi,

 

Thanks for your help. I did what you said and it seems to work. I just want to know how I can display the info from the donor and recipient on one form for submitting for funding. I want some fields from the BMT record for both the recipient and donor and some fields of the donor collection to be displayed. Is this possible or will I need 2 table occurrences as well for collection to do that?

 

thanks 

[doughemi]

You should be able to create a report with the existing TOs.  If the report is from the perspective  of recipients,the report layout would be set up from the Demographics_rec table.  If the report is by transplants, the report layout should be based on the BMT table.

[rkass068]

@ Eos

 

Can I send you a cloned file so you can see what I did for the relationships in order to get that? 

[Lee Smith]

@ Eos

 

Can I send you a cloned file so you can see what I did for the relationships in order to get that? 

 Please attach your files here.

 

If you do not know how to do this, just follow the instructions/steps here. ATTACH FILE

[eos]

Can I send you …

 

As Lee said, please post your file here; this is a public thread, and everybody should have a chance to have their say.

[rkass068]

Hi EOS I attached a file to the thread. Sorry I was away quite some time. I am trying your solution and I can't wrap my head around it. It don't think i explained myself properly. 

 

The purpose of this is the have a bunch of patients in the database (donors and recipients). Each recipient has a diagnosis which may result in a series of transplants (bone marrow or BMTs). Each donor and recipient is given a specific IUBMID # that needs to be reported to on all documents. In the case of allogeneic transplants, a recipient must me linked to their donor and any reports contains the information from their cell collection. (You can see the fields that are required). In the case of an autologous transplant there is no donor per se, they are their own donor. The most important report is in the database on the layout called "AllBMT Cell Processing-physician order 1/3" under the forms---> Allo folders. maybe the document will give you a sense of what I'm trying to do. Thanks again for all the help.

 

 

BMT Protocol v.1.0 Clone.fmp12.zip