9 Replies Latest reply on Apr 12, 2017 12:47 PM by siplus

    Calculation

    garry107

      I have a calculation and I am trouble having writing it.

       

      I have two fields both with values.

      field 1 with values A, B and C

      field 2 with Yes or no.

       

       

      so the field 3 should be calculated as

       

      if field 1 = B

      and field 2 = Yes

      then field 3 = (count of all records in a database with field 2=no)/(count of all records in a database  with values with field 1 = B)

       

      Is this possible

       

      Thanks,

      Garry

        • 1. Re: Calculation
          Jaymo

          This could be done but might be slow as you add more records. It sounds like you might be better off with a report. Have you every worked with summary fields and subsummary parts?

           

          Give us a little more detail why you want to do this and maybe we can provide an alternative solution.

          • 2. Re: Calculation
            philmodjunk

            ExecuteSQL can return the needed values for use in your calculation.

             

            Relationships that match only to records with a specific value can also return the needed counts.

             

            A script can find all record with a given value and set a variable or field to the resulting number of records in the found set.

             

            But as Jaymo has already posted, if this for a large number of records, you could see delays in getting a value calculated. Sometimes a scripted "transaction" approach where each time that a record is added or a field value is modified, a script updates a count in a number field in a separate table can provide the same values without the delays encountered by having to query the entire table twice to get the two different counts.

            • 3. Re: Calculation
              garry107

              Basically I want a field which shows the percentage probability over all records.

               

              So whenever field 1 is B and field 2 is Yes, I want field 3 to auto populate with the percent of all the records with field 2 with all records of field 1 is B

               

              field 3 = ((number of records with field 2 = yes)/(number of records with field 1 = B))*100

               

              I understand with more records there are delays but what are the alternatives?

               

              Thanks,

              Garry

              • 4. Re: Calculation
                Jaymo

                A report should serve you well. Here's a link to a presentation I did at the FileMaker Developer Conference. It's in FileMaker 7 format but is still relevant and can easily be converted. Pay close attention to the report layout in the PERCENT file.

                 

                Database Pros:List

                • 5. Re: Calculation
                  garry107

                  I don't know about a report, but I wanted a field in the same layout showing me the percentage. So I need field 3 in the same layout as field 1 and field 2.

                   

                  Thanks,

                  garry

                  • 6. Re: Calculation
                    garry107

                    I know you were talking abt fraction but how can fraction for count of records be achieved instead of the values.

                    And that too there's a condition when field 1 is B and field 2 is Yes.

                     

                    Thanks,

                    Garry

                    • 7. Re: Calculation
                      garry107

                      I think ExecuteSQL is best in my case. Can you help me write the calculation.

                       

                      Thanks,

                      Garry

                      • 8. Re: Calculation
                        garry107

                        I wrote this Execute SQL statement in an unstored calculation field 3

                         

                        (ExecuteSQL (  "SELECT Count ( * ) FROM \"Table name\" WHERE \"Table name\".Field 2 = 'yes' "   ; "" ; "" )*100)/

                        ExecuteSQL (  "SELECT Count ( * ) FROM \"Table name\" WHERE \"Table name\".Field1 = 'B' "   ; "" ; "" )

                         

                        Thanks,

                        Garry

                        • 9. Re: Calculation
                          siplus

                          Remember that ValueCount(Select....) is faster than Select Count (...)

                           

                          Remember to never unleash a search on that field