PathGeek Posted May 16, 2014 Posted May 16, 2014 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 contentFunctionality: 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 Posted May 16, 2014 Posted May 16, 2014 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 Posted May 16, 2014 Author Posted May 16, 2014 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 Posted May 16, 2014 Posted May 16, 2014 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 Posted May 17, 2014 Author Posted May 17, 2014 Have attached what I've been playing with so far. I'm sure i have more questions than this, but here goes: patient-- don't really understand how to associate patientID / to HLID (our internal patient number), and individual tests in the Tests table Why isn't the tesst name showing up in the browsemode (or in the calculation for the CreateTheReport table? 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? 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) CreateReport Table Test name shows only number (so it's consistent) 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 Posted May 17, 2014 Posted May 17, 2014 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 1
PathGeek Posted May 18, 2014 Author Posted May 18, 2014 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 Posted May 22, 2014 Author Posted May 22, 2014 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
Recommended Posts
This topic is 4112 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now