1 Reply Latest reply on Aug 4, 2016 7:43 AM by PeterDoern

    List of top number of occurrences in a portal?

    tkessler45

      This has been somewhat challenging for me. I have a portal who's entries include a text field of item categories, for instance colors such as Red, Blue, Green, Cyan, Orange, Brown, Yellow, etc. The portal's contents will dynamically change based on various conditions, but for any condition I need to get a list of the top three categories that exist in the portal. So for instance, with the following data I would need an output of Red,Green,Yellow in that order, seeing that there are four Red, three Green, and two Yellow:

       

      Green

      Blue

      Green

      Red

      Brown

      Red

      Red

      Orange

      Green

      Yellow

      Red

      Yellow

       

      The conditions are that the list of items would need to be limited to the top 3, that it displays the items in order, and that the items all come from a field in a portal.

        • 1. Re: List of top number of occurrences in a portal?
          PeterDoern

          Given two tables,Parent and Child, where:

          Parent::ID = Child::ID_Parent and

          "colour" is the field you're counting,

          try this Let() statement:

           

          Let ( [

           

            ~fs = Char ( 9 ) ;

            ~rs = Char ( 13 ) ;

           

            ~q =

            List (

            "SELECT" ;

            " COUNT ( ~colour ) as c," ;

            " ~colour" ;

            "FROM" ;

            " ~table" ;

            "WHERE" ;

            " ~id = ?" ;

            " GROUP BY ~colour" ;

            " ORDER BY c DESC" ;

            " FETCH FIRST 3 ROWS ONLY"

            ) ;

           

            ~sql =

            Substitute (

            ~q ;

            [ "~id" ; "\"ID_Parent\"" ] ;

            [ "~colour" ; "\"colour\"" ] ;

            [ "~table" ; "\"child\"" ]

            )

           

          ] ;

           

            ExecuteSQL ( ~sql ; ~fs ; ~rs ; parent::ID )

           

          )

           

          EDIT: Here's a link to an example: Dropbox - ColourCount.fmp12.zip