Something possibly wrong with my relationships...

[NewtoFileMaker88]

I have made a table ("A"), and made a table occurrence for this table. Then in a layout entitled "main table," I have a tab control, and placed a portal within one of those individual tabs. Within that portal, I provided the fields from that table occurrence for table A. The relationship between the two tables (and the main table and the TO for A) is made by one field -- a unique number.

However, whenever I type information into the portal for that table, it does NOT sync up with the individual table A (in a separate/it own layout)...this has worked well for me before. That is to say, I have entered information into multiple portals in different tabs, and everything has been "synced" rather nicely.

Any ideas what is going wrong?? I am really at a loss as to why this suddenly isn't working?

Thank you very much in advance for your help.

[LaRetta]

We need more information. Please use real table names so we understand the context and natural relationship between your tables. Here are the steps to track down why a relationship won't work:

1. Check the data-type of both key fields. They must be the same.
   
2. Both of these fields must be indexable (go to their Options in Field Definitions) and make sure that 'do not store calculation results' is unchecked.
   
3. If you are wanting to add a record in the portal, make sure your relationship is checked to 'allow creation' in the graph.
   
4. Drop into layout mode and tell me the table occurrence name of the portal (it is the child side). Make sure that is the same name as the one in the graph.
   
5. Check every field within the portal and make sure it is same name as the portal (sometimes we put other fields in the portal as supplement but the fields should usually be same as portal.
   
6. Go to Layouts > Layout Setup and make sure that the name under 'Show Records From' popup is the same name as the other TO in your graph (it is the parent side).
   

How does that work out? :^)

[We Can Deb]

Dear LaRetta,

Thank you for your thorough response--it helped me with my Portal Row issue!

Debbie

[LaRetta]

I am pleased to hear it, Debbie. :^)

But upon re-read, I made a typing error that I would like to correct: Item 2 said:

Both of these fields must be indexable (go to their Options in Field Definitions) and make sure that 'do not store calculation results' is unchecked.

... and that is only true for the child side of a relationship. The parent side can be unstored calculation or global.

[NewtoFileMaker88]

Hi thank you for your response -- here is a copy of my database. It might be best if I describe first what I want to do, then what i did, and then what the problem is.

I want to create a table that lists all of a patient's drugs taken (past and present). I want this table to be able to list every drug, dates taken, dosage, date stopped, and any side effects of the drug.

I then want to create a portal in my main table that will show the list of drugs and the rest of the information for said patient.

Is there a way to do this other that how I currently have it set up? (I made a table for drug info, and linked it with the patientID # to set up the parent-child relationship, and then for each drug...assigned them a drug #, so like for Patient A, he will have drugsID 1-10 for 10 drugs, Patient B will have drugsID 1-4 for four drugs taken.

thank you very much for your help, i appreciate it so much.

madeupcopy.fp7.zip

[LaRetta]

Hi NewtoFileMaker88,

I reviewed your file and I am confused on why you have all of the patient child table occurrences related to the main table instead of relating to the Patient? Neither do I understand why you have created duplicate table occurrences to use as portals since the original table occurrences would work just as well. Your PatientID in Main table is not a global and it probably should be. I cannot even find a table referencing drugs.

so like for Patient A, he will have drugsID 1-10 for 10 drugs, Patient B will have drugsID 1-4 for four drugs taken.

This does not sound relationally correct. I am sorry ... I would love to assist you I am not sure how in this instance.

I then want to create a portal in my main table that will show the list of drugs and the rest of the information for said patient.

You mean you want a single portal to show the patient demographics and the drug information ... or do you want the Patient MRI and all patient tests to also appear?

whenever I type information into the portal for that table, it does NOT sync up with the individual table A (in a separate/it own layout)

Might it just be a refresh issue? I am grabbing at straws since I am not sure if you mean it won't create a related record, or whether you type information which does not then immediately display in a form or ? If you provide more information (or more specific example) then maybe we can assist.

[NewtoFileMaker88]

Hi thanks for your response.

I have all of the child tables related to the main table, which is basically just a field that has "patientID" in it (and is used to create a layout that has a list of all patientIDs). The child tables are all related to the main table by patientID. Is this not the best way to have this set up? What do you mean by "Your PatientID in Main table is not a global and it probably should be"

I would like to enter all of the drugs for a patient into the "treatments" table. The only way I can think to do this is for each drug, to have its own drugID number. Is there a better way to do this? Essentially, what I would like to do is enter all of the drugs for a single patient into the table, and then have only those drugs for that particular patient come up inside a portal, when viewing that patient's record in "main table"

[comment]

This seems to be a replay of:

http://fmforums.com/forum/topic/79927-creating-a-new-table-for-a-tab/

[NewtoFileMaker88]

yes, same database, different question.

[LaRetta]

Hi

The child tables are all related to the main table by patientID. Is this not the best way to have this set up? What do you mean by "Your PatientID in Main table is not a global and it probably should be"

Your Patient Demographics is your Patients table. Know why? It has the patient's date of birth. Unless a patient can have two dates of birth, that table holds one of each patient as a unique entity. It should therefore hold one each unique PatientID (FM-generated, auto-enter serial).

I have provided a sample file (and I've attached 3-4 of your tables as example). It opens on Patient Demographics. You have no Patient Name. Not sure if that was planned; I've worked on some confidentials where name is omitted but I added a name for this example (in the Patient Demographics table).

For your question about entering treatment based upon Drugs, please look at the relational graph. Your Treatment table is your join table. You enter the possible drugs you prescribe into the Drugs table. Clicking into the first empty row in the portal will add a drug to that Patient's treatment because.'allow creation of related records' is checked in the relationship between Patient Demographics and Treatment.

So what am I still missing in understanding what you need to do with either the Treatment portion or the structure?

EDITED ... oh, and you can also add new Drugs from that Patient screen if you need to but I did not want to complicate things unless you needed it. AND, you can actually create both records simultaneously without script ... entering a new drug can add the newly created drug right back into the treatment for that Patient as well.

If you wish to go that direction at all, let me know. But before we work on the UI, we need to be sure you understand the structure and that we've set you up properly.

Patients.zip

Edited March 27, 2012 by LaRetta

[imoree]

LaRetta; You always go way above and beyond..

Great job..

Edited March 28, 2012 by Lee Smith
Quote was not necessary

[NewtoFileMaker88]

this is incredible, thank you!

I have attached an update of my database.

I set the demographic information as the "main table" and have included tabs with portals to information in other child tables.

For the medications table, I have a column for "drug ID." Is there a way that I can automatically generate a unique # for this field for each new drug for an individual patient (but can repeat a set of #s for another patient)? So if Patient A had 5 drugs. #s 1 through 5 would be used. If Patient B had 7 drugs, #s 1 through 7 would be used. Also...is a "drug ID" field even necessary in this case? For right now, it seems to be working fine without a unique drug number.

[LaRetta]

No attachment. :^)

[NewtoFileMaker88]

sorry! here is the attachment.

also, do you have any idea why i can add new rows (records) for drugs within the portal in the medications tab, but not add additional rows for the two portals in the SEBBD tab?

updated.fp7.zip

[LaRetta]

do you have any idea why i can add new rows (records) for drugs within the portal in the medications tab, but not add additional rows for the two portals in the SEBBD tab?

Because if you look at the portals in layout mode, they are actually belonging to Seizure Type. It looks like you correctly re-specified the fields but not the portal itself. Easy mistake.

For the medications table, I have a column for "drug ID." Is there a way that I can automatically generate a unique # for this field for each new drug for an individual patient (but can repeat a set of #s for another patient)? Also...is a "drug ID" field even necessary in this case? For right now, it seems to be working fine without a unique drug number.

YES, DrugID is necessary. You are missing a Drug table. Take another look at my file. Pass the DrugID from the drug table relationship to use where you are currently typing the drug - use a pop-up list like I show. If the drug name was the only thing you want, you could use a custom value list but truly ... you want a separate Drug table where you can list drug information.

So if Patient A had 5 drugs. #s 1 through 5 would be used. If Patient B had 7 drugs, #s 1 through 7 would be used.

I am afraid I still do not understand what you want to see. 1 through 5 would be used of what? When you are viewing a Patient's medications, you will see only that Patient's medications. I need to understand 'can repeat a set of #s for another patient)? Please explain again what this means and we'll try to help you out.

But it looks like you are making great progress!! :smile:

[LaRetta]

I have corrected your copy - see the blue table occurrences in the graph and how I have turned on 'Allow Creation' (indicated by the _A). And I created the Drugs value lists for you. and attached the pop-up on your Medications portal on the Medications tab.

If you want to add a drug to a Patient medication portal and the drug isn't in the pop-up, just type into the drug portal (below). Just by typing the drug name into the drug table, it will automatically also add a record to your drug table AND your join table (in its attempt to reconcile the IDs) - two for one - no script or trigger; filtered portal handles it by itself. I hope this gets you moving forward again. :^)

UPDATE: I always end up thinking I should put disclaimer on filtered portals so here goes: If your drugs table contains more than three or four thousand records, use script to create the drug then create the join record instead. And you can include more fields from the drug table in that one-row portal.

OK2.zip

Edited March 30, 2012 by LaRetta

[NewtoFileMaker88]

thanks! this is so helpful!

i added an MRI tab. do you happen to foresee any problem with the way I set up my MRI portal in the demographics layout?

also, i have another question.

I want to generate a list (report?) of all patient IDs that have a condition called "mesial temporal sclerosis." This field appears at etiology::mesial temporal sclerosis. It has an associated value list with "definite" and "possible" as checkerboxe options. Do you happen to know if it is possible to generate a list of all patients who have both or one of these boxes checked for this field?

thanks again very much for your help!

040212.fp7.zip

[LaRetta]

I am pleased that you are moving forward!! I am currently unavailable for assistance but I am sure others will help you out. I'll check in again when I get the chance in a few days.

[LaRetta]

Hi NewtoFileMaker88,

I've finally had a chance to review your file. Your MRI connection looks fine; seems you are getting the hang of using keys for your relationships! A few things I noticed ... you do not have primary keys in all your tables and you need one. Really you should name Demographics to Patients.

A Patients table should have PatientID (unique auto-enter serial)

A Medications table should have a MedicationID (unique auto-enter serial)

... etc

Also in Medications, you can remove the field 'Drug' since the name now comes from your Drugs table instead. I also notice that you have some data-type issues - be sure your dates are of type date instead of text.

I want to generate a list (report?) of all patient IDs that have a condition called "mesial temporal sclerosis." This field appears at etiology::mesial temporal sclerosis. It has an associated value list with "definite" and "possible" as checkerboxe options. Do you happen to know if it is possible to generate a list of all patients who have both or one of these boxes checked for this field?

Since you want a list of Patients as your result, you can perform the find right from a Patients layout. And the related field you search does not even have to be on the layout at all. A find script would look something like this, if fired from Patient layout:

Script name: Find Patients with MTS

Freeze Window

Set Error Capture [ On ]

Perform Find [ and in Specify Find Request specify NEW, find your Etiology table in the bottom left pop-up and highlight your MTS field. Then in Criteria, only type an asterisk (*). An asterisk tells FM to find Etiology records if anything exists in the MTS field. ]

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ "No patients found" ]

Show All Records

Go To Layout [ original layout ]

Exit script

End If

.... at this point, you have patient records with MTS so switch to your list layout

Go To Layout [ list of patients ]

If you need anything further, please let us know. :^)