9 Replies Latest reply on Jun 27, 2017 10:32 AM by gofmp15

    Count Occurrences in Portal

    jarrodjb

      Hi Everyone.

      Long time lurker, been using FM about 2 years now.

       

       

      I have a database that we collect audit information.

      Table one is Header and location info.

      Table 2 is a violation list

      Table 3 is portal with violations associated with table 1

       

      Trying to count how many times "Major" and "Minor" occur when filtering out all records to specific audits.

      IE show me how many Majors were on "Auditxyz"

       

      Familiar with countifs and power pivots but cannot find the correct command/syntax for FM.

      Countif(Portal::Vtype ; "Major") is what i would assume it would look like if it existed.

      Also to note an Audit has many locations(Records) and each location can have 1-8 violations (Records in portal).

       

      Thanks in advance!

      -Jarrod

        • 1. Re: Count Occurrences in Portal
          fitch

          There are a few ways to handle this. Here's one:

           

          First, get a list of the related values: List( violations::Vtype )

          Next, isolate the type you want: FilterValues( List( violations::Vtype ) ; "Major" )

          Now count them: ValueCount( FilterValues( List( violations::Vtype ) ; "Major" ) )

          2 of 2 people found this helpful
          • 2. Re: Count Occurrences in Portal
            jarrodjb

            Hi Tom,

             

            First thanks for the quick response!

             

            Tried that but get an error, expecting an operator here

             

            Ideas???

            • 3. Re: Count Occurrences in Portal
              fitch

              Here's a way to write the calculation that isolates each component:

               

              Let ( [

              type = "Major" ;

              typeList = List ( violations::Vtype ) ;

              filteredList = FilterValues ( typeList ; type ) ;

              result = ValueCount ( filteredList )

              ];

              result )

               

              This allows you to test the calculation by changing the final word from 'result' to 'type' or 'filteredList' or any of the other variables that you've declared. That should help you isolate what went wrong. If you have FileMaker Advanced you can use the Data Viewer for this.

              • 4. Re: Count Occurrences in Portal
                fitch

                Another way to do this is with SQL, but I'd caution you to only use this method to set a field or variable in a script -- don't make this a calculated field:

                 

                ExecuteSQL (

                "SELECT COUNT (*) FROM violations WHERE ID = ? AND Vtype = ?" ;

                ""; "" ;

                header::ID ; "Major" )

                1 of 1 people found this helpful
                • 5. Re: Count Occurrences in Portal
                  jarrodjb

                  fitch

                  Thanks for your help here.

                  Got what i needed to work!

                   

                  -J

                  • 6. Re: Count Occurrences in Portal
                    philmodjunk

                    Assuming that you have these relationships:

                     

                    Table1---<Table3>----Table 2

                     

                    You can create a summary report based on table 3 that includes data from the other two tables. Such a report could be set up to look like this:

                     

                    Audit XYZ

                        Major:   23

                        Minor: 50

                     

                    Audit ABC

                        Major

                        and so forth....

                    1 of 1 people found this helpful
                    • 7. Re: Count Occurrences in Portal
                      jarrodjb

                      Pretty much what i ended up doing. Got it working for this part.

                      Thanks Phil and Tom

                      • 8. Re: Count Occurrences in Portal
                        philmodjunk

                        Please note, however, that neither filtervalues, nor executeSQL are needed to get that report.

                         

                        I'm posting this more for the benefit of others that might research a similar issue.

                        1 of 1 people found this helpful
                        • 9. Re: Count Occurrences in Portal
                          gofmp15

                          One idea is to add calculated fields:

                           

                          A: if field x = Major; 1; 0

                          B: if field x = Minor; 1; 0

                           

                          Now in parent record

                          Total Major: sum field A

                          Total Minor: sum field B

                           

                          This will work to sum ideas in a portal display.

                           

                          If you are interested in the contents of a second field being the determinant, you could use a global field to show your interest:

                           

                          A; If (myfield = global field; ( if field x = Major ; 1;0) ; 0 )

                           

                          This works on an individual record in form view with a portal.