Is this what you want?
Plate P1 2
Plate P2 5
and so forth....
Plate P1 1
Plate P2 3
And so forth....
I think what I want is more like this:
No. Plates Processed/tissue=X number of plates
Where in I will have a variable number of cell records linked to patient ABC, each denoted with a plate number. For instance, I might have 50 cell records from plate 1 (p1), 75 from p2 and 25 from p3 for a total of 150 cell records. What I want to return then is how many plates these 150 cells came from - 3 plates. Does this make sense?
It sounds like you need three relationships with three tables instead of two, but it is not clear to me what your plate identifier represents here.
Patient::__pkPatientID = Plates::_fkPatientID
Plates::PlateIdentifier = CellCounts::PlateIdentifier AND
Plates::_fkPatientID = CellCounts::_fkPatientID
That would make counting your plates a simple matter of counting the number of related records in Plates.
But in other circumstances, you might actually have these relationships (I can't tell from the info provided which is the best approach)
Patients::__pkPatientID = CellCounts::_fkPatientID
Plates::__pkPlateID = CellCounts::_fkPllateID
Count ( Plates::__pkPlateID ), if defined in Patients would return the number of plates used in this case as well as the first example.
My database is as such:
Where each field in patients, describes an attribute of the patient and the patient ID is the pk. Each field in the cell properties tables describes an attribute of the data generated from the analysis of one cell from that patient- plate number, well ID, date processed, etc linking to the main patients table using the patient ID as the fk. So, plates is a just a field in my cell properties describing the plate ID number the cell derived from. I just need to count the single value of p1 as ONE plate, rather than if I count the field alone it counts each occurrence as a separate number. So if 50 cells have the value "p1" in the plate number field, I only want to count that one time, and then count each occurrence of "p2" one time and so on to get the total number of plates, not plate occurrences. I keep getting the total field occurrences or zero.
My database is as such:
And I am suggesting that a change in that design will make it much easier to get what you need out of your data.
So if 50 cells have the value "p1" in the plate number field, I only want to count that one time,
And do these "50 cells" become 50 related records in Cell Properties? Or do you have 50 fields in one Cell Properties record? I am assuming the first.
I just need to count the single value of p1 as ONE plate
Exactly. and with either of the two modified data models, that becomes very easy to do. There are other options but they are more complex to set up.
I think I see what you are saying. My original thought in not setting up a "plates" table was that I wanted to avoid having an intermediary table since all the data for each cell would have that information associated with it in a field. But, if I understand you correctly, without this intermediary relationship, it becomes very difficult to parse the data in the way I want. Correct?
The alternatives tend to become more complex. But keep in mind that I don't know what a plate number really represents here and that could modify my suggestion. If it's just a unique identifier and a Plate P1 is only used for one patient, then the possible benefits of the added table get a bit thinner.
Other options for your count:
Sum the reciprocal: How to count the number of unique occurences in field.
Define a conditional value list of Plate ID's for a given patient. Then
ValueCount ( ValueListItems ( Get ( Filename ) ; "yourConditionalValuelistnameHere" ))
Could return a count of the number of plates used.
If you had a newer version of FIleMaker we could also use the ExecuteSQL() function to count them, but you indicated that you are using FIleMaker 11.
I work in a molecular biology laboratory. Patients are recruited and samples of tissue are taken. We then place single cells in a plate which is analyzed. So, in my setup, each cell is identified by Patient ID-Plate Number-Well ID, but each of these are separate fields. So for each patient, there are many plates, and for each plate there are many cells. The plate number is not unique to each patient, it's just the designation for the number in a serial process. Counting the number of processed plates/patients gives us a benchmark of progress. I tried the other two methods, but neither one was working for me. Based on this, would you still advise to add the new table PlateNumber?
There is less advantage to using a related table as one of the reasons for doing so would be to record data specific to the plate instead of recording it over and over again in each Cell properties record. But if all it is is a number written on a label, that's not something that you need. But the method still works.
All of the methods work, sum the reciprocal, however, requires its own layout for showing the totals for each patient.
Counting values in the conditional value list also works and may, in retrospect be simpler to set up and doesn't require changing the structure of your data. It will work, but you have to know how to define a conditional value list based on your relationship between patients and Cell Characteristics.
One option that comes to mind is to use:
CellCharacteristics::_fkPatientID = PlateNmbers::_fkPatientID AND
CellCharacteristics::PlateNumber = PlateNumbers::PlateNumber
Enable "allow creation of records via this relationship" for PlateNumbers in the above relationship.
A script run from a script trigger on the CellCharacteristics::PlateNumber field could run this script:
Set FIeld [PlateNumbers::PlateNumber ; CellCharacteristics::PlateNumber
The first time that it runs for a given CellCharacteristics record, it will create a single related record in PlateNumbers if a record with those platenumber and patientID values does not already exist. If the record already exists, no change to the data in PlateNumbers takes place.
Then the records are added in PlateNumbers automatically and the count function I posted earlier can return the correct count. (but if you ever change a plate number in cell characteristics, there's a chance that you might be left with an extra record in PlateNumbers that creates an error in your plate count.)
OK- I'm going to digest all of this and see what I can come up with.
I certainly appreciate all your time and expertise- feel like such a newbie!
If I get stuck, I'll comment back...thanks again!