Jump to content
Sign in to follow this  
PathGeek

New to FM and Databases... Beginner Question

Recommended Posts

Starting from scratch I seem mostly to be scratching my head. I'm trying to put together a database of my pathology patients--

Patients have multiple tests (CBC, Marrow, FC, CG, MolecDx) and may have these (some or all) done on multiple occasions.

Attaching file for review of tables and relationships.

Haven't added data fields in and relationships from all tables because the existing relationships are not correct yet.

Current structure adds a PatientID w/ every new test; I really need to be able to add tests to the same patient w/o adding an ID-- I think. I know I need to be able to search for a PATIENT and find TESTS from different DATES while maintaining the logical connections between tests from the same date.

Thanks in advance.

Dr.d

PatientCases.zip

Share this post


Link to post
Share on other sites

OK, I took a stab. I changed quite a few things in your file, so you're going to have to study it a bit.

First, don't you names for relationships. It's not a good idea for several reasons. Use IDs. I couldn't match some of the records, so I deleted them (in case you wonder what happened; I had to create an abbrev of the name to even try to match).

I created a CBC_ID and a Marrow_ID in Patients. This would normally be terrible relational design :)-]. But I had a reason. They are just "temporary" values, for the "left" side of a relationship, so that you can use them to switch which record is visible in the CBC and Marrow tabs. You can only see 1 record fully. There's a little portal showing test dates of the multiple records. Click in the portal to toggle which record is visible. The portal is sorted Descending, latest on top.

Oh yeah, I created a CBC_ID in CBC and a Marrow_ID in Marrow, both auto-enter serial IDs. You could use Timestamps instead, but IDs are useful, especially if you extend the solution and tie those records to other entities. Like if there was a relationship between CBC and Marrow, which you hinted at, but didn't explain.

There are also regular relationships to the CBC and Marrow tables, based on only the PatientID; which should be in ALL tables related to Patients. The patient name is optional in the other tables. Personally I wouldn't put it there at all, but would look back to Patients for it.

Records are created by a "New" button script. There is no "Allow creation of related records," because portals are sorted descending.

PatientCases.fp7.zip

Share this post


Link to post
Share on other sites

Wow. Thanks!

You're right, I'll have to sit and wrap my head around this. Thanks for taking the stab; stabbing was something I was considering (of the hara kiri sort).

Appreciate your help

D.

Share this post


Link to post
Share on other sites

Fenton-

Couple more questions:

The tables names (Patient/CBC/Marrow/Flow) remained identical to my neanderthal attempt.

With your solution:

In the Relationships graph, there are tables with other names (pat_CBC, CBC~Patient/MarrowID

These are also seen in the "occurence in graph" line in the fields windows...

I was looking in FMHelp and FMpro8.5 Bible and don't see how you created these.

What is this called exactly-- need to find source to understand how you did this, what they mean, to try to recreate, etc.

Thanks

Share this post


Link to post
Share on other sites

Those are just the names I gave to the table occurrences (TOs) on the graph. It's just a naming convention. The basic reason (besides the fact that I generally use it) is to make the table occurrence "groups" sort alphabetically together in drop-down lists, and keep them separate when there's more than 1 group.

A table occurrence group (TOG) is a group of table occurrences which are connected, in some way, by lines. In your case the Flow TO is not connected. If I didn't use "pat_" for the Patients TOG, then Flow would sort between CBC and Marrow. Doesn't much matter for only one, but it does if there were a lot of TOs connected to Flow.

BTW, I didn't really finish it properly. Patients should be "PAT__Patients". This sorts it to the top of its TOG list, and makes it very easy to recognize in drop-downs.

Further, the naming convention can let me see what the path is, and a general idea what fields are involved:

pat_CBC~Pat|CBC_ID

means (to me):) In the Patients' TOG, the CBC table, targeting fields Patient_ID and CBC_ID, in a compound relationship.

It can also show what TOs a line goes through. In your original file you had two TOs of the Marrow table, one of which was hanging off of CBC. I deleted it, because I didn't see why/how to connect it properly (though it may be). If I put it back, it would be named something like:

pat_cbc_Marrow

Which would mean: In Patients' TOG, from Patients' TO through CBC TO (because "cbc" is lower case) to the Marrow TO. This would differientiate it from pat_Marrow.

The ~tilde in front of the field name is not really standard naming convention. I saw someone use it for this purpose and decided to use it also. Because I found using only _underscores for everything left too much ambiguity. I don't always put the field name, if it's dead obvious; such as Patient_ID. Nor would I put all the fields if there were more than 2 or 3; it would get too long.

The ~tilde may cause problems with ODBC or PHP (?), but it works OK in IWP.

Here's the official comprehensive guide to naming.

http://www.filemaker.com/downloads/pdf/FMDev_ConvNov05.pdf

Share this post


Link to post
Share on other sites

Boy, do I have alot to learn. Thanks again for the explanation-- and for the attachement.

Share this post


Link to post
Share on other sites

Finally back on track with this and now another question:

In Table pat_CBC (Table contains blood test data):( determining the presence or absence of anemia depends on age and gender as well as the

RBC count. In Table Patient, I have pat_Sex (= M/F).

If age and gender didn't matter I could calculate

RBCDx = Case (RBC < 4.7 ; "Anemia" ; RBC > 5.4 ; "Erythrocytosis"; "RBCs wnl")

Attempting to result RBCDx as dependent on patient gender, I tried:

RBCDx = if (pat_sex="M" (RBC < 4.7 ; "Anemia" ; RBC > 5.4 ; "Erythrocytosis"; "RBCs wnl"))

else if (pat_sex="F" (RBC < 4.2 ; "Anemia" ; RBC > 5.0 ; "Erythrocytosis"; "RBCs wnl"))

but else if seems not to be the correct syntax.

I've also tried multiple iterations of (Case....

Help? (I presume relating output to age will be similar....)

Thanks

Share this post


Link to post
Share on other sites

Try:

Let ( [

low = Case ( pat_sex="M" ; 4.7 ; 4.2 ) ;

high = Case ( pat_sex="M" ; 5.4 ; 5 )

] ;

Case (

RBC < low ; "Anemia" ;

RBC ≤ high ; "RBCs wnl" ;

"Erythrocytosis"

)

)

Share this post


Link to post
Share on other sites

wow.

Where do I actually find this stuff? I have piles of papers and books... and seem to spin in circles.

This site is far and away the best resource I've found, and yet would like to feel like I can advance a step or two on my own.. Hints appreciated.

Thank you! for your help, comment.

Share this post


Link to post
Share on other sites

Help manual? Actually quite useful if you have some time to just run through all the functions and see how they're used.

You'll also find a good amount of function usage in most of the books. Another good site with a small subscription fee is www.filemakermagazine.com - has a whole bunch of filemaker techniques with video's and sample files. Tend's to use a variety of different functions to show you how to achieve some end - in general its a also a pretty good learning tool.

... or browse this site.

... or just pick a book and go through it.

Share this post


Link to post
Share on other sites

I've tried this and somehow it's not doing things as I'd hoped. If I use pat_Sex= "M", a low RBC still gives a result as if it was a female "anemia". Conversely, if"M" and RBc=5.0, the result is given as "erythrocytosis", when the correct result for males shouldn't appear until rbc=5.4.

The script runs without showing errors... suggestions?

CBCwoes.fp7.zip

Edited by Guest

Share this post


Link to post
Share on other sites

You are using "Male" and "Female" to indicate patient's sex - not "M" and "F" as stated originally. Therefore the test pat_Sex = "M" fails consistently, and the default (female) result is always applied.

BTW, it's not a script, it's a calculation.

Edited by Guest

Share this post


Link to post
Share on other sites

Obvious. and I didn't see it. Thanks.

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.