Jump to content

  •  

Photo

Something possibly wrong with my relationships...


  • Please log in to reply
18 replies to this topic

#1 NewtoFileMaker88  novice

NewtoFileMaker88
  • Members
  • 47 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Jaguar
  • Skill Level:Beginner
  • Time Online: 2d 14h 49m 51s

Posted 27 February 2012 - 08:01 AM

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.
  • 0

#2 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,680 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 206d 17h 21m 40s

Posted 27 February 2012 - 09:52 AM

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:
  • Check the data-type of both key fields. They must be the same.
  • 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.
  • If you are wanting to add a record in the portal, make sure your relationship is checked to 'allow creation' in the graph.
  • 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.
  • 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.
  • 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? :^)
  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#3 We Can Deb  member

We Can Deb
  • Members
  • 21 posts
  • LocationCape Cod, Massachusetts
  • FM Application:12 Advance
  • Platform:Windows 7
  • Skill Level:Novice
  • Time Online: 1d 9h 7m 46s

Posted 06 March 2012 - 02:06 PM

Dear LaRetta,

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

Debbie
  • 0

#4 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,680 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 206d 17h 21m 40s

Posted 06 March 2012 - 05:09 PM

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.
  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#5 NewtoFileMaker88  novice

NewtoFileMaker88
  • Members
  • 47 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Jaguar
  • Skill Level:Beginner
  • Time Online: 2d 14h 49m 51s

Posted 22 March 2012 - 06:53 AM

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.

Attached Files


  • 0

#6 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,680 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 206d 17h 21m 40s

Posted 23 March 2012 - 09:27 AM

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.
  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#7 NewtoFileMaker88  novice

NewtoFileMaker88
  • Members
  • 47 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Jaguar
  • Skill Level:Beginner
  • Time Online: 2d 14h 49m 51s

Posted 26 March 2012 - 09:53 AM

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"
  • 0

#8 comment  consultant

comment
  • Members
  • 23,962 posts
  • Time Online: 319d 14h 36m 34s

Posted 26 March 2012 - 11:45 AM

This seems to be a replay of:
http://fmforums.com/...able-for-a-tab/
  • 0

#9 NewtoFileMaker88  novice

NewtoFileMaker88
  • Members
  • 47 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Jaguar
  • Skill Level:Beginner
  • Time Online: 2d 14h 49m 51s

Posted 27 March 2012 - 10:53 AM

yes, same database, different question.
  • 0

#10 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,680 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 206d 17h 21m 40s

Posted 27 March 2012 - 11:24 AM

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? :laugh2:

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.

Attached Files


Edited by LaRetta, 27 March 2012 - 11:43 AM.

  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#11 imoree  master

imoree
  • Members
  • 384 posts
  • LocationBahamas
  • FM Application:11 Advance
  • Platform:Mac OS X Lion
  • Skill Level:Intermediate
  • Membership:TechNet
  • Time Online: 50d 11h 26m 39s

Posted 28 March 2012 - 01:35 AM

LaRetta; You always go way above and beyond..
Great job..

Edited by Lee Smith, 28 March 2012 - 08:29 AM.
Quote was not necessary

  • 0

#12 NewtoFileMaker88  novice

NewtoFileMaker88
  • Members
  • 47 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Jaguar
  • Skill Level:Beginner
  • Time Online: 2d 14h 49m 51s

Posted 30 March 2012 - 07:26 AM

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.
  • 0

#13 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,680 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 206d 17h 21m 40s

Posted 30 March 2012 - 07:31 AM

No attachment. :^)
  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#14 NewtoFileMaker88  novice

NewtoFileMaker88
  • Members
  • 47 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Jaguar
  • Skill Level:Beginner
  • Time Online: 2d 14h 49m 51s

Posted 30 March 2012 - 10:46 AM

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?

Attached Files


  • 0

#15 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,680 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 206d 17h 21m 40s

Posted 30 March 2012 - 11:12 AM

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:
  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#16 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,680 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 206d 17h 21m 40s

Posted 30 March 2012 - 12:17 PM

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.

Attached Files

  • Attached File  OK2.zip   40.44KB   32 downloads

Edited by LaRetta, 30 March 2012 - 03:17 PM.

  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#17 NewtoFileMaker88  novice

NewtoFileMaker88
  • Members
  • 47 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Jaguar
  • Skill Level:Beginner
  • Time Online: 2d 14h 49m 51s

Posted 02 April 2012 - 09:16 AM

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!

Attached Files


  • 0

#18 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,680 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 206d 17h 21m 40s

Posted 02 April 2012 - 09:18 AM

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.
  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#19 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,680 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 206d 17h 21m 40s

Posted 09 April 2012 - 06:46 AM

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. :^)
  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.




FMForum Advertisers