ISO Relationships 101?

[PathGeek]

Hello all.
Have been working with FM for a bit, but still have very basic problems with relationships— help appreciated.

Background
Work in a laboratory and am needing to create a db w/ labs, tests, and report content

Functionality: The user  
                               example: tech from LabID= 2 (Molecular lab) )
                      should be able to 
                                   select TESTS from the lab in question
                                             example: JAK2 test
                                   then select the result ( positive / negative etc)
                                      and that selection should populate results (TestTitle, TestResult, TestResultText,

So far this my thought process is:
     Table1: Labs with LabID (serial# auto generated) and Labs
                        Labs (Molecular, Flow cytometry, cytogenetics,…. etc
     Table2: Tests: TestID, Test, and kf_LabID
                         example above from Molecular Lab Test =JAK2

                        (the table contains name of test, test methodologies)
     Table3: Results: resultID, result, kf_TestID
                      example above positive (heterozygous / homozygous) or negative
                      Table contains Text, Reference, Comments associated w/ the result
     Table4: Join: JoinID, LabID, TestID, resultID

     I think my TOCs should be something like this:
    Join : Join
    Table1: Labs
    "       2: Tests
                 Test~Tests in Labs
   "        3: Results
                Results~Results in Tests

    This is as far as I get. I;ve tried to build things and get stuck every time.

Questions:
1: How do I actually put these together?
2:  where do I put the patient identifier (Patient#)?
3:  How do I build this so that patientID and PatientTests can be joined so that I can monitor results if a patient has more than one test over time?

Thanks in advance.

[comment]

How many results can one test have? And how many labs?

 

 

BTW, I suggest you make yourself an ERD before addressing "functionality" (or anything else for that matter).

[PathGeek]

The possible results for a test are many

        pos/neg/mutated/unmutated/suspicious/fail, etc

 

For each patient specimen, a specific test can have one result

       MrSmith--Jak2--Positive, Homozygous

 

currently we have 5 labs.

patients can have tests in more than one lab and more than one test within a lab

   --- we've decided to leave the linkage of (if TestA is negative, automatically do TestB) out of the FM solution initially

 

patients can be tested over time, such that tests / results for each isolated test would need to be captured

         as some of the tests have quantitative information that is monitoried

 

On the functionality bit-- trying to explain what this needs to do so I can figure out the how to build the bridge to get there

 

Thanks in advance

[comment]

IIUC, you need to have three, possibly four, tables:

 

Patients -< Tests >- Labs

 

The fourth table would be for storing the possible results of a test (and their respective textual representations on a report) - however, it's really not much more than a glorified value list. In any case, the relationship to Tests would be:

 

Tests >- Results

 

and not the other way around as indicated in your post.

 

I also don't see a many-to-many relationship here, hence no need for a join table (that is other than Tests, which is a join table between Labs, Patients and Results).

[PathGeek]

Have attached what I've been playing with so far.

I'm sure i have more questions than this, but here goes:

 

1. patient-- don't really understand how to associate patientID / to HLID (our internal patient number), and individual tests
2. in the Tests table
   1. Why isn't the tesst name showing up in the browsemode (or in the calculation for the CreateTheReport table?
   2. If a test has an assigned number (Say 1 = jak2) -- how can I either select TestNumber or TestName and have the other columns lookup the value?
   3. How can the tests_Laboratory be set to look-up and fill with the lab name if test name is chosen or vice versa (if Lab, the TestName will allow pulldown of the tests offered by Lab)
3. CreateReport Table
   1. Test name shows only number (so it's consistent)
   2. Methods from referred table is obviously not set up correctly

It seems I have a recurrent lack of understanding about this still.

Continued help is greatly appreciated.

Test.fmp12.zip

[eos]

  On 5/17/2014 at 12:43 AM, PathGeek said:

It seems I have a recurrent lack of understanding about this still.

 

The gist of your questions indicate that your understanding of relationships is indeed lacking (he said primly … ):

 

Internally, you relate your entities (Labs, test, Patients etc.) by auto-entered serial IDs; e.g. a patient to a test by creating a relationship where Patient::Patient_primaryKey = Tests::patient_foreignKey. This gives you access to all the related patient's data, including HLID; you can reference them in calculations and/or simply display the related fields via the relationship on the layout, e.g. a patient's name on a test record (or, in a Patient context, all of their tests).

 

The same method is used for all other relationships. The point here is that in a relational database, you don't “lookup and fill in” other “columns” (the FileMaker term is “field”) – unless you want to retain values from a certain point in time (think invoices and product prices) – but instead use IDs to create relationships and display related data.

 

So ideally, there will always be exactly one point where you store (and edit) any single piece of data – a patient's name, their HLID, a test date etc.

 

Open the attached file and study the (heavily annotated) Relationship Graph – seeing these things in (not quite) action may work as a practical primer; be sure to read the note about relationships and what keys go where, depending on the type of a relationship.

 

Note that you don't necessarily need all the tables within the supporting section; but it may help you think about the data structure you need to get your results.

 

Once you have that structure correct, the next step would be the analysis of your workflows and the desired outputs (screen, file, paper), and modelling them as editing/report/print layouts incl. the necessary scripts – e.g. to create a test with a template-based set of test items, or generating a report on all tests for a patient).

 

As they say: we leave that as an exercise to the reader …

Test_eosMOD_relationshipPrimer_seeRG.fmp12.zip

[PathGeek]

Many thanks, eos- just saw the posting. I've downloaded and will try to wrap my head around the RG.

thanks for the time you've taken to help me understand.

 

besten Dank u. mfG

[PathGeek]

So, after much thinking about what you said, EOS and COMMENT

I've read about and drawn many entity relationship diagrams, worked on a very basic model, and have attached it. At least some of the relationships I was trying to define are now working!

Obviously I'm not even skimming the surface. But, this is FUN!

 

Eos, I do have questions for you about your schema

• what did you envision as possible values for "test items"?
• Were you thinking (Week 2 Test---- questions in week2 test)?
  • do you see anything in my solution that would require a test item table?
• The test results / result sections are different in different Lab/Tests, so am wondering how to deal with that.
  • Molecular/TestX /Results: generally all have same format, which works well as I have it currently
  • Flow cytometry/TestX /Results have a different format
    • not sure how I put that into Generic(ResultsTable)
    • is there a way to do that or do you think I'll need to split to have MoleTests/MoleTestResults and FlowTests/FlowTest/Results 
    • If only one table, will formatting of results need to be via calculation or more scripted?

Wish my day job actually allowed me to think about this during best brain time. Thanks for your continued help.

 

Testv101.fmp12.zip