10 Replies Latest reply on Feb 25, 2014 11:38 AM by MichelleJoachims

    Counting field values



      Counting field values


           Hi everyone,

           I have tried and tried to figure this out, and decided to ask for help (instead of continuing to bang my head against the wall!).

           I have a simple database with 2 main tables- patient data and cell information. One of the fields in my table of cell information describes the plate which the cell was on. I need to count the number of plates processed for each patient to display on the patient summary layout. Every iteration I've tried either gives me a large number (for example, if I have 100 cell records, it returns 100 when I try to count the plate field, because it counts every instance as one), or it gives me 0. I have looked at the other forum posts on this topic and can't seem to make it work. The plates are simply numbered p1, p2, etc. Can anyone help?

           I am using FMP 11- thanks in advance!


        • 1. Re: Counting field values

               Is this what you want?

               Patient ABC

                    Plate P1    2
                    Plate P2    5
                    and so forth....

               Patient XYZ
                    Plate P1    1
                    Plate P2    3

               And so forth....

          • 2. Re: Counting field values

                 I think what I want is more like this:

                 Patient ABC

                 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?


            • 3. Re: Counting field values

                   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.

              • 4. Re: Counting field values

                     My database is as such:

                     Patients--------<Cell Properties

                     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.

                • 5. Re: Counting field values

                            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.

                  • 6. Re: Counting field values

                         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?

                    • 7. Re: Counting field values

                           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.

                      • 8. Re: Counting field values

                             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?

                        • 9. Re: Counting field values

                               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.)


                          • 10. Re: Counting field values

                                 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!