Count related records w/ desired criteria

[Marcel1]

Hi,

I have a database with two tables called "patients" and "sequences" which are indirectly related via another table called "visits". Patients may make one or more visits, and provide one or more sequences on each visit. Each sequence is unique and can only come from one patient. Sequences can be of types A, B and C.

Problem: I would like to have a standing field in the patients database that keeps track of how many sequences of each type this patient has in the sequence table. A user would then be able to quickly scan through patients and see at a glance that a given patient has 10 As, 5 Bs, and 0 Cs, for example.

I originally posted this problem in the scripts forum, and got very helpful feedback indicating that I could do this with relationships. I have made some progress, and I can get patients to show the total number of related sequences through a calculation field, but I can't get a breakdown by type. Any thoughts greatly appreciated.

Marcel

[Paul de Halle]

I have attached a file that I beleive offers what you are trying to do.

The key to this is the multi predicate join between the Visit and Sequence tables. By using the Patient Primary Key as well, this will populate that field in the Sequence table and then allow you to link it directly to Patients. Once you are able to create a direct link the rest is fairly straight forward.

Hope this helps.

Relational_Counter.fp7.zip

[Marcel1]

Hi,

Thank you for your solution! I tried to implement and have gotten it to work, mostly. However, as I understand it, the relationship structure requires that when adding new sequences, the user must enter a value for [color:brown]visitID AND a value for [color:brown]patientID. Previously, the user only needed to enter a single value to uniqely identify the sample, visit, and patient associated with a sequence. Is it necessary to have an additional data entry step to make the relational counter? (I am worried about potential for data-entry errors and extra work.)

Note: I mention "[color:brown]sample", because in this database, the table heirarchy actually includes a table for samples. Thus, a patient makes a [color:brown]visit, provides a [color:brown]sample, from which is derived a [color:brown]sequence. A sequence can come from one or more [color:brown]regions. Patients is linked to Visits by [color:brown]PatientID, then Visits is linked to Samples via [color:brown]sampleID, and Samples is linked to sequences via [color:brown]SampleID as well. (All relationships are one-to-many.) Therefore, adding a sequence and entering the [color:brown]sample ID resulted in an unambiguous link to the other tables.

I am attaching my database to clarify, in case this is helpful. Original main database in green, new occurances of Sequence table created to model your relational counter in Orange. Tables in black are used to automatically create names, etc...

Marcel

[Fenton]

In all cases you must create the intermediate records. In no case should you have to manually enter an ID. You should never (I repeat) require or allow user data entry into the key fields. It's not needed and it's dangerous.

In Paul's solution the relationship have multiple criteria, and the relationships "Allow creation of related records". This means that if you enter ANY other value in a portal to the next table down the line, all the keys (IDs) will be automatically populated. (Whether they are all needed is another matter; but definitely at least the PatientID and one other key.)

The trouble with your solution is that it doesn't have portals, or accurate navigation, to the next level down. The buttons just go to the layouts. They should, if possible, Go To Related Record []. There is no real mechanism to create the proper related record.

I modified Paul's file somewhat, adding the Sample table, and adding portals for data entry. As it is, you enter the date, which automatically creates a record in the next level down. Then you "Go" to the next level to finish data entry, and continue down to the next level.

You can view all the Sequences for a person from the top, but you need a method to create and populate the intermediary tables' records.

You would undoubtedly want a better interface. And there's several possibilities. I didn't want to mess with yours, as it looks finished, but is missing functionality.

I also wonder why you have such fields as "number of samples" on the Sample layout. Do you mean "# of samples for this patient"? Or "# of samples this visit"? Either of which are more easily counted from their own table. It seems to me that for a Sample there is only 1 "# of samples."

A lot of the other fields there are broken also. If you want to count things within its own table you'll need self-relationships. Something to consider.

Relational_Counter2.zip

[Paul de Halle]

Sorry, probably should have made that a bit clearer. Thanks Fenton.

[Marcel1]

Wow. I can see how this database more naturally creates a top-down work flow, by creating patients then creating and going to the related records in visits, etc... I would love to incorporate this, but I am daunted by the task of adapting this to mine. I will spend some time on this.

One problem remains, however. In most cases, the user will have a large number of new sequences to enter into the database, and then link them up to existing samples, visits, patients, ie "working from the bottom up". I notice that in the improved example database, if I start from the sequences level and create a new sequence record and enter a source sample, the patient and visit fields do not populate automatically. This will be an issue, because as soon as the database is ready, i will begin entering very large numbers of sequences using the munge function (also thanks to you, I believe), and link them up as mentioned.

Any way around this?

Marcel

[Fenton]

That's funny. I glanced at the Munge script, and thought it might be mine ("munge" being a word I use). But I didn't recognize the comments. I posted that at the end of June.

When you have "flat" file imports, the "top down" creation method (which is the norm) isn't going to work. What you need instead is either a "gather" method, if you receive the data for the patient, visit and sample in the flat file, or a "manual choice" method, where you don't receive the data, but must create new records for patient, visit and sample manually, then fill in the fields.

So the big question is, "Do you receive the patient, visit and sample data in the flat file?" And, if you don't, how do you know what it is?

Your earlier "import file" (back in June) was just the gene sequences, no patient data at all.

There are some problems with your file, especially the way the fields are on the layout. In the Sequence layout you have these fields on the layout, enterable: Patient::Project, Patient::PatientID, Visit::VisitDate, etc..

These are related fields, the actual fields of their respective parent tables. They should almost never be enterable in a child table. Modifying the PatientID here would be changing the actual PatientID of a patient in the Patient table, destroying the relational integrity of the entire file. It is one of the worst scenarios.

PatientID could be on the layout, but it would the local Sequence::PatientID, and it would be enterable only in order to choose a patient, in the case where manual entry from a flat file import was needed.

It is OK to show something like Visit::VisitDate. In some (very) rare cases even allow modification here (in Visit's child table). But you must be clear what related parent fields are in a child layout.

[Marcel1]

Right, "munge" is an interesting word. I had to look it up. Similar to the French "mange", which I tend to use.

In most cases, there will be a small number of patients, visits and samples in any given database, and these will be entered manually at the outset of a project. Then, the major work comes in later when large sequence files with many records get entered and must be triages to the correct related files. Typically, the sequence files will not have explicit information regarding patient, visit, sample, but all of the sequences will be from the same patient/visit/sample.

That is why I have a "replace field" button, that will copy the value of the active field into the corresponding field of all of selected records. This is an admittedly dangerous function to have, but the database is only intended for use by a limited number of trusted users, so I am accepting that risk.

Point well taken about the extraneous fields from the patients, visits and samples tables in sequences. I really want to view the related fields, or use them to find sequence records while working in sequences.

Questions:

1. How does one make related record field entries from pt, visit, sample visible in sequences and be allowed to use them for finds, without having them be modifiable?

2. How can I set up relationships to make the relational counter to be able to see number of seqences of each region while perusing patients without adding extra data entry steps in sequences (esp. since sequences will be extensively modified and expanded during normal use).

(Also, please feel free to use the database file I uploaded if you have a thought, and it is easier to demonstrate using this file.)

Marcel

[Fenton]

First, I found a bug. Visits has a SampleID field, which it uses as a key to Samples. This is wrong. Visits is above Samples, it cannot have a Sample ID. You need either a Visit ID field. Or, if a patient will never have more than 1 visit per day, the Visit Date can be used instead.

1. As far as showing the related fields on layouts, you can do that, but they should be non-enterable in Browse mode (via Format, Field Behavior). Only local fields should be enterable (usually).

I don't really see how you can create a Patient, a Visit and a Sample record automatically from Sequences. Especially if you do not know whether there is already an existing record for any one the above.

If you wanted to create them first, that would work. I believe I'd do it by creating the above records, then setting them into global fields, so I could see the current values for all in one place. Then import a Sequence file and set the values into every record with a Loop using the globals. No Replace, no copy/paste. It seems safer.

I still don't know where you get those values to start with. The file name? A folder name? A sticky note :-?

2. I thought you had the different count things going on already. Do you mean create dynamic count fields, which could handle new types automatically, which you don't know ahead of time?

SeqDatabase2.zip

[Marcel1]

Thanks for picking up the bug. Missed that one. Patients will only have one visit per day, so I will use date.

1. Ok, got it about non-enterable fields in browse mode. I have fixed all of those.

2. I don't hope to actually create new patient, visit, and sample records by entering new sequences. I think new P, V and S records will be entered manually beforehand, which is fine because there will be relatively few of them. What I am hoping is that when I enter a lot of new sequences (say 200), and identify the sample from which they came (enter a value in sample Id field in sequences, then copy to all selected sequences using my "replace" button), the sequences will be related to the correct sample, visit & patient. I think I have this working, but I am pretty inexperienced and in trying to implement the relational counter, my relationship definitions are a bit in flux

3. The patient, visit and sample info come, for all practical purposes, from a sticky note. That is, I know this info from the context of the project I am working on, but the info does not appear in the sequence files, folders, etc. I will just put this in manually for now, since I don't anticipate many records in these tables - at most 30 patients in one project.

4. Counting - I don't have the relational counter going yet. I have fields in place, but they are not working yet because I am still a bit unsure about how to structure the relationships (see prev. post). My goal here is modest - just to count the number of sequences of each region each patient has. There are only be a limited number of possible regions and these are defined in a value list in sequences. I don't need to have the counter accomodate previously undefined regions or anything unexpected like that.

I think I will be very close if I can figure out the relationships. Many thanks for your help, and I really appreciate the advice.

Marcel

[Fenton]

First, another bug, which is why your relationship counts weren't working. The calculations for the region words must have Text result, not Number. Also, they can be "Do not store" in Storage Options.

You do not really need a compound relationship between Sample and Sequence, as any particular sample can only be from 1 person (right?). But you need the PatientID in Sequence for the Region counts. You can just make PatientID an auto-enter by calculation from Sample.

That way the only ID you need to fill in after importing sequences is the Sample ID. The PatientID will auto-enter.

SeqDatabase3.zip

[Marcel1]

I think the database works!

Firstly, thanks for finding the bug - got it. checked to make sure all such fields are OK. Also, I think you are right about the relationships between sample and sequence, and between patient and sequence. Thanks for clarifying, and also for the auto-enter tip. That takes care of the data entry problem.

To my great delight, the relational counter now works! And maybe more importantly, I learned a ton about databases and FM. In my excitement, I made an auto-counter that adds up the number of sequences in each region by sample as well, and cleaned up a little.

I would like to finish by thanking you, Fenton, and the others who have been so generous with time and ideas. I really would have probably given up due to time constraints if it had not been for your help. Thank you so much.

Marcel