5 Replies Latest reply on Jun 1, 2010 8:51 AM by Jon1009

    Counting

    Jon1009

      Title

      Counting

      Post

      Hello I have 3 tables

       

      The first table has the samples which then creates related records in the second table

       

      but I can have up to 3 related records in the second table so the ID from table one and appear up to 3 times. Once the sample is approved it creates related records for each test in table 3.

       

      what I want to do is count the related records in table 2 and then count the related records in table 3 to see if there equal which will mean the sample is approved ready for reporting

       

      Iam looking to do this in a if statment in a script

       

      Many thanks

       

      Jon

       

       

        • 1. Re: Counting
          Mystick

          Let's say all your tables have a field name ID which is unique.

           

          in table 1 you can create a calculated field (NbRelRecords) that is  count(table2::ID)

          in table 2 you can create a calculated field (NbRelRecords) that is  count(table3::ID)

           

          then in table 2 you can have calculated field number (IsApproved) saying  table2::NbRelRecords = table3::NbRelRecords

          • 2. Re: Counting
            Jon1009

            I dont have Unique ID in table 2 or 3 as After the first table I have up to 3 related record that have the same ID depending on the tests

             

            for example this is what i have

             

            ---------------------------------------------------------

                 Table1::ID                     Table1::Test                  

                                 1                     ASP,DSP,PSP

                                 2                     ASP,DSP

             

            ---------------------------------------------------------

                 Table2::ID                     Table2::Test                     Table2::Result                   

                                 1                     ASP                                     32

                                 1                     DSP                                     12

                                 1                     PSP                                     42

                                 2                     ASP                                     52

                                 2                     DSP                                    62

            • 3. Re: Counting
              Mystick

              Ok...

               

              in table1, create calculated field saying  count(table2::ID)

              in table2, create calculated field saying  count(table3::ID)

               

              will work event if the ID is ID from parent table.

               

              • 4. Re: Counting
                Jon1009

                Mystick wrote:

                Ok...

                 

                in table1, create calculated field saying  count(table2::ID)

                in table2, create calculated field saying  count(table3::ID)

                 

                will work event if the ID is ID from parent table.

                 


                table1 count of table2 worked, how ever the count of table3 just returns the value of 1


                • 5. Re: Counting
                  Jon1009

                  Managed to get it to work by

                   

                  table1, create calculated field saying  count(table2::ID)

                  table1, create calculated field saying  count(table3::ID)

                   

                  Thanks for your help