4 Replies Latest reply on Jan 13, 2016 5:35 AM by mikebeargie

    how to summarize a text in portal

    Phyre

      HI!

       

      I have a self join relationship table to create a portal in my layout. but I don't know how to summarize or sub summarize a text field so it will appear only one record each category will show. Anyone can help please.

       

      Justone.jpg

        • 1. Re: how to summarize a text in portal
          siplus

          See attached file for a possible way of doing it.

          • 2. Re: how to summarize a text in portal
            mikebeargie

            The easiest thing to do is create a value list of whatever that field is in the related table. That way FileMaker will take care of those for you.

             

            You can then retrieve the values via calculation:

            ValueListItems(Get(FileName) ; "thatValueList")

            Or you can skip having a relationship or value list completely, like:

            ExecuteSQL("SELECT DISTINCT(category) FROM yourTable";"";"")

             

            If you must display them in the portal, then you can do some portal filtering, however the performance is really bad as it requires GetNthRecord() to do it on the fly.

             

            What's the purpose of your portal?

            • 3. Re: how to summarize a text in portal
              Phyre

              Hello there!

               

              The purpose of the portal is to show the list of the category in the record, and when you click it another portal will show the list of information on that category. But the portal looks so messy and repeating category words will appear . That is my problem.

              • 4. Re: how to summarize a text in portal
                mikebeargie

                If it's only for display, I'd suggest a calculation field using the ExecuteSQL() calculation I showed above. You'll need to augment it to show only what's related to your parent table.

                 

                So in your parent table, create a calculation field, with a calculation of:

                ExecuteSQL("SELECT DISTINCT category FROM childTable WHERE foreignKey = ? ORDER BY category ASC" ; "" ; "" ; primaryKey )

                Replace the following with your field names:

                category - the name of the category from the child table

                childTable - the table name of your child table

                foreignKey - the keyfield that relates your child table to the parent table

                primaryKey - the primary keyfield that relates your parent table to the child category records

                 

                Then you can display this new field on your layout instead of the portal. Note I added a sort there using ORDER BY, so it will automatically sort the category names in ascending alpha order.