Weird Relationship I cannot grasp

[rkass068]

Hello,

 

I am making a database for Bone Marrow Transplants and I am having trouble with some relationships. Currently I have tables: Patients, BMT, Collections, Regimens

 

I have related Patients to BMT in a 1:many relationship which is fine and each BMT can have multiple collections (works too). What is tricky though is that each BMT can have multiple collections spread over a couple of days. That means each record in the collections table will be for a certain collection day regarding that BMT. That would make a 1: many relationship. Now each collection will have multiple regimens... but the catch is the collections related to the specific BMT will have the same multiple regimens. In that case should I:

 

a) relate them somehow in a many: many relationship to pull those same regimen records

make the regimens 3 repeating fields of up to 10 repeats in the BMT table

 

In the end I have to pull a report that includes the patients BMT info and demographics (patient table) along with the collection info and regimens info displaying all of the records from the regimens table related to that collection or BMT.

 

Thanks

[GisMo]

I've made a crude table and you have the following relationships. correct?

 

One | Many

Patient | BMTs

BMT | Collections

Collection | Regimens

 

 

 

Now each collection will have multiple regimens... but the catch is the collections related to the specific BMT will have the same multiple regimens. 

 

This is a little unclear to me.

 

Maybe it would help if you also carried the Patient ID all the way through to each related table in addtion to the regular relationships listed above. Meaning Patent ID would be an additional foreign key in BMT, Collection, and Regimen.

[rkass068]

Ya sorry for the confusion. Your table is correct. Each collection refers to a collection instant per BMT. For example, if someone has a BMT and their collection is taken over 2 days, they would have two records for collection. The reason is that each collection instant has specific procedures that are recorded and we would want to record the number of stem cells per collection for reference. Now the protocol (high dose chemo regimen) they receive will only occur once even if they have more than one collection instant. So a protocol would include the day, date, and description of the protocol fields. There would be multiple protocols per BMT and only done once per BMT. 

 

So 1 BMT ---> many protocols ----> many collections.

 

In the end I want a report that shows all of the protocols per collection per BMT 

[David Jondreau]

Protocols and collections are independent, right? They're only tied to each other because they're tied to a BMT.

 

So..

 

     -->Protocols

BMT

     --> Collections

 

Also, it looks like regimens are a separate thing than Protocols?

 

I'd say you'd want two tables BMT Regimens and then Collection Regimens. Whenever you create a Collections Record, you can script the creation of the appropriate Collection Regimens records (by looking at the related BMT Regimens records).

[rkass068]

Hi David,

 

Yes protocols=regimens. Sorry for the confusion they are synonyms for BMT preparation (high dose chemo) sometimes I get ahead of myself. So far my DB is set up as follows

 

Patients---->BMT -----> collections

                             ------> Protocols (regimens)

 

So really what I want is to have a report with fields from BMT, collections and protocols. The layout must be based on the protocols table so that all the portals will show for each BMT. The problem is that if I put fields from collections, only the information from the first collection will show in the layout. There will be a series of protocols per BMT (multiple records) but once those records are created, they are the only ones done for that 1 specific BMT and the set of protocols should show up for all of the collections done for that BMT record. 

 

I tried using a portal in the layout, which does work and I based the report on the collections table. My problem is that I have a field in protocols table called description and a lot of text can be in that field. So I have the field set to shrink when the report is printed to the amount of text in the field. This feature was able to be done in access but we decided to move to filemaker. The problem is that portal in filemaker cannot shrink or grow depending on the amount of text in the fields. I attached a word document to show how my report is laid out.

 

Thanks

 

 

Autologous BMT.zip

[David Jondreau]

If you're printing, it's generally not a good idea to use portals.

 

I'd say your best bet is to base your layout on Collections. Since the BMT is the same for all the collections, you don't need a portal, just the fields. Showing Protocols is tricky. I'd say you should have a field in BMT that's a calculation field that grabs all the Protocol data and use that field on the layout.

 

Or have two separate layouts, one based on Collections and one based on Protocols and print them separately.

 

I'm still not totally clear on what a Protocol is...couldn't they be related to Collections? Does a Collection use a particular Protocol?

[rkass068]

Hi Dave, 

 

Yes I was thinking of basing my layout on collections and maybe using SQL or another calculation to list the protocols.

 

 Protocols are a bunch of high dose chemotherapies given to the patient before collection. So for example, I give patient A chemotherapy A, B, C, and D. Those 4 chemos would be considered protocols because they are pre collection. The patient is only given their protocols once even if they have more than 1 collection. So really to display the protocols and all of the collection i will have to duplicate the protocols for the other collection which I would not want to do. Maybe the calculation route is better or I use a repeating field for protocols?

[rkass068]

Also, I am assuming you saw the document i posted? Is there a way to make a table with all of the protocol records by a calculation? Maybe executeSQL?