Automate counting related records (how)

[Marcel1]

Hi,

I would like to automate counting of all records related to a a given record in a database but I am having a little trouble.

Database: I have a database of patients and a related database of sequences. A patient can contribute 0 or more sequences to the sequence database, but a sequence can only come from one patient. (The relationship between sequences and patients is actually via another database called sample, in which every sample comes from one patient, and may yield many sequences)

Objective: I would like the script to go through each patient in the database, and count how many related sequences each patient has.

Problem: I am trying to do this by setting a global holder for the current patient ID, going to the sequence database, entering find mode, and finding all related sequences. However, I seem to be unable to specify a find request that accepts a field value from the related patient database. What I get instead is a literal interpretation of the reference to the field.

Any help much appreciated,

Marcel

[mr_vodka]

If patients is related as a one to many to sample, and then sample related to sequences as a one to many as well, you can have a calc field (cSumSeq) with the count function add up all the sequences for each sample, then in your patients table, you can have another calc field with the count function add up all the realted cSumSeq field to give you a total of all sequences for each patient. No script would be needed.

[Fenton]

I don't think you need a script for this. It is a straight relational calculation. There is a one-to-many relationship from Patients to Samples, then one-to-many from Samples to Sequences; therefore it is one-to-many from Patients to Sequences, thru Samples (which happens automatically in 7, if the lines connect). Look at the Relationship Graph to see what I mean.

So, from (in) Patients, with a relational line to Sequences, it would be a calculation field:

Count ( Sequences::any field with data )

It would be Unstored, as it's relational.

[Marcel1]

Thanks for the idea. this works well. Two questions regarding this solution:

1- Is there a way to narrow the result so that you can count only a subset of all sequences related to a given patient record, based on the value in a text field (values from a value list) of the sequence record?

2- Is there a way to store and work with the result of the caluclated sequence field?

Marcel

[Fenton]

1. Yes, you can add a text field to the Patient table to choose the desired value. Then add that field to a new relationship, from Patients to Sequences (direct). It would match the corresponding field in the Sequence file.

The most convenient way for this relationship to work is if the PatientID is also in Sequences. It can be a auto-enter calculation, based on the Sequences to Sample TO.

The "type of sequence" field in Patients could be either:

a. Text field; to change the value for just that patient.

b. Text field, global; to change value for all patients.

c. Calculation field, fixed text, global; to set a fixed value for all patients.

2. I don't know what you mean. You can work on any regular field that you have access to, either directly or via a relationship. You can't edit a calculation field however.

I made a quick example file with the basic relationships. It has no data though.

FilterSequences.zip

[Marcel1]

Hmm..

In my database, patients can have several sequences of different [color:brown]types (all in the sequence database). My intent was to have a standing field in the patients database that keeps track of how many sequences of each [color:brown]type this patient has in the sequence table. A user would then be able to quickly scan through patients and see at a glance how many sequences of each [color:brown]type the patient has.

Not sure I understand the relationship structure of your example. Under relationships, I notice that the three existing tables are related exactly as in my database. I also see another box that looks like a table (but isn't) that defines relationships between patients and sequences, but there are no relationship lines between the icons representing patients and sequences. (sorry for my basic ignorance of FM).

I added some records to your exampe, and I notice that in patients there is a field for [color:brown]type, and it has an automatic drop-down that contains all of the different type values present in the sequence file, and I can actively toggle back and forth between these while in patients. How can I use this to create a standing tally of sequences by [color:brown]type for each patient?

Marcel

Edited September 21, 2005 by Guest