Counting records in a distantly related field

[Marcel1]

Hi

having a little trouble with a counting function in a database with several related tables. The database consists of 4 tables related in a linear fashion from patients to visits to samples to sequences. Each relationship from left to right is one-to-many. Sequences is related to samples via sample ID which is defined in both tables. Sequences also has a calculated field "PatientID", which is a manually entered value (stored) in samples, and a calculated field (patientID = samples::patientID) in sequences.

I am hoping to be able to go to a record in patients, and count how many sequences from a particular region (for example, the "gag" region) from that patient are present in the table sequences.

At the moment, I have a global variable "gag" in patients, and created a second occurrence of the table sequences called "gagsequences" that is related to patients via patient ID and region. I then just defined numberofgagsequeces in patients as count(gagsequences::anyfield). Unfortunately, the table patients does not seem to "see" the value patient ID in the gagsequences occurrence, and therefore the result is always 0.

I played around, and if I turned the field "patientID" in sequences into a simple number entry (no longer a calculation), the relationship worked, so that seems to be the problem. However, in its original configuration with patientId calculated, a search in sequences will find these values as if they were stored. Only the relationship with the table patients doesn't see the values. Is there any other way to configure this?

Relationship photo attached. Any thoughts greatly appreciated!

marcel

[T-Square]

Try making a calc field in the Patient table that has this as the formula:

Count(Sequence::SerialID)

HTH,

David

[Marcel1]

Hi,

Thanks David. Unfortunately, this counts ALL sequences from a given patient in the related table sequences, instead of the subset of sequences from that patient that have "gag" in the region field. This was why I thought I needed another occurrence of sequences with a join to both region and patient ID, but I can't get the relationship to work.

Marcel

[comment]

For a relationship to work, the matchfield in the related (child) table must be indexable. Unstored calculations are searchable (though slow), but not indexable.

Either make PatientID in Sequences a lookup/auto-enter, or put the global filter field in the Samples table, and define another relationship:

Samples::SampleID = Sequences2::SampleID

AND

Samples::gRegion = Sequences2::Region

This will enable you to count, from Patient, the sequences that have the region entered in the global gRegion field.

Global fields are accessible from anywhere, so you can put the global filter on Patient's layout, for example, and control it from there.

[Marcel1]

Hi, just tried both of these approaches.

Making patientID a lookup would work, but the field only seems to update when I enter a new sequence, but not in "real time", if the sample ID gets edited somewhere down the road. this means that the count of sequences from a particular patient and from the region gag may not be correct if other data changes and the lookup entry is not corrected manually.

I tried method 2, by making an occurrence of sequences (sequences2) with the relationships you suggested, ie

Samples::SampleID = Sequences2::SampleID

AND

Samples::gRegion = Sequences2::Region

However, when I attempt to count the number of gag sequences with region gag from a given patient in the patient table by defining

gagsequences = count(sequences2::anyfield)

I get the total number of gag sequences in the table sequences, and not just those gag sequences from the particular patient in the active record of Patients. Perhaps I am still not doing this correctly...

Marcel

[comment]

Making patientID a lookup would work, but the field only seems to update when I enter a new sequence

That's what relookup is for.

I get the total number of gag sequences in the table sequences, and not just those gag sequences from the particular patient

Mhmm.. I don't know why. See the attached demo for how it can work.

Note that my tables are connected in a cascade, always using the parent table SerialID as the matchfield. These fields are auto entered, and should NEVER be edited by the users. In fact, users don't even need to see them or know about them at all.

This is much simpler than trying to get the PatientID from high above, and use it as a match for the level below. And it allows you to put any parent field on the child/grandchild/etc. layout - so on a sequence layout you can have fields telling you any detail of the parent sample/visit/patient, without storing redundant information.

PatientSamples.fp7.zip

[Marcel1]

Ok, I think I have it working. Thank you! Couple of beginner questions, though;

1- There is a field named "-" which seems unusual to me. Anything special about this field?

2- You mentioned that your tables are linked by using the parent table SerialID. ( I assume you mean in your own databases, because I don't see any serialID's in your example ). But if I do that, how would a user enter new records at the level of "sequences", and have them link up with the correct sample, visit and patient? The user wouldn't know the serialIDs to use. using sampleID is my shortcut, since a sample always comes from only one visit, one patient, etc...

3- I notice that gregion is defined as a global number variable. However, the actual entry is a text variable. what's up there?

4- your DB relationship structure is a little cleaner than mine. For example, between visit and sample, you have sample ID as the match whereas I have used patientID and visit date. Is there an easy way for me to redefine my database (create a visit ID, have them match up to the correct samples, etc...) to match your example there without manually editing all of the entries in the sample table (there are more than 250 entries)?

[comment]

1. It's only a separator (cosmetic), to divide between groups of fields in the field definitions.

2. Each table has a serialID field. It is named PatientID in Patients, VisitID in Visits, etc...

how would a user enter new records at the level of "sequences", and have them link up with the correct sample, visit and patient

First, a sequence only needs to link up to a sample. Once that is done, it is automatically linked to the visit - patient. Each link in the chain needs only to know its parent - note that I have the related field Patient (from table Patients) on the Sequences layout, and it is correctly identifying the name of the patient.

Next, there are many ways to get the ParentID into a new record: this is more of a user interface issue. For example, you can enter new Visit records into a portal to Visits on a Patient layout (provided the relationship is set to allow creation). Or, you could have a button "New visit.." which would run a script to create a new record in Visits, with the PatientID being transferred as the script parameter. It all depends on how you want to organize the user's workflow.

3. My mistake - it should be global text.

4. Yes. Make a backup first. Create the VisitID (as serialID auto-enter) in Visits, and populate it by finding all records, then replacing the field contents with serial numbers (+updating the next serial). Next, create the VisitID in Samples, find all records, and populate the field by replacing field contents with a calculation of Visits::VisitID.

[Marcel1]

Thank you!

One last question:

I tried to insert a portal in "patients" as you have in your example. In my case, I hoped to show all of the samples for a given patient, and the number of sequences from each sample next to it. I was able to do this but found the behavior of portals to be a bit strange, both in terms layout and formatting.

In terms of layout, when I insert the portal and go through the dialog boxes to insert the desired fields, I get a portal grid, where the entries would be, and I also get two free-floating fields just as I would if I inserted a new field in the layout. For a long time, I couldn't get the portal to show my data, until I put the two fields on top of the top row of the portal, after which the portal grid showed the data correctly in browse mode. Is this the way portals are supposed to behave?

Also, my portals look a little rough and amateurish with a lot of background lines, etc... whereas the ones in your example look very neat with left-justified. Unfortunately, I found that it was difficult manipulate portal formats. I looked at the FM help section on portals, but didn't find it to be of much help. Are there any tricks that I should be aware of?

Marcel

Edited January 18, 2006 by Guest

[comment]

I also get two free-floating fields

I have never seen anything like this. The only thing that comes to mind is make sure you're patched up to version 7.0v3.

There's not much to format in a portal. The text justification is done on field level.

[LaRetta]

The first png above can happen this way:

File created in 7.0v1. File then modified in 7.0v3. File then again opened in 7.0v1. The version upgrade changed rendering (you will also notice vast font-rendering differences). I have the same issue when I open a 7.0v3 file on a 7.0v1 machine. Portals disappear - half my buttons disappear. In fact, if the screen resolution on the 7.0v1 machine is set to greater than 1024x768, the text disappears completely - not just the portals and buttons. This is on Windows ... I don't know whether it causes font display problems on Macs at all. But I would bet, as Michael indicated, that it's a version thing ...

When it first happened to me, I thought I was going blind! :wink2:

[Marcel1]

OK, many thanks. I will check versions. anyway, it is cosmetic and the database is working well. thanks for all the help.

Marcel