6 Replies Latest reply on Mar 24, 2017 3:29 PM by lindseym

    Length (text), then get highest value field contents?

    lindseym

      I was able to figure out how to calculate the length of a field, and now I want to have it return the actual contents of the longest field, not the character count.

       

      If it has two or more instances with the same character count, I want it to show me all unique outcomes only.

       

       

      Sub Summary Field                   Text                     Length(text)       

      Alpha                                         Apple                   5

      Alpha                                         Banana                6

      Alpha                                         Banana                6

      Alpha                                         Carrot                  6     

      Beta                                           Orange                6

      Beta                                           Rutabaga            8

       

       

       

      What I want to get:

       

      Alpha      Banana, Carrot

      Beta        Rutabaga

       

      Thanks,

      -L

        • 1. Re: Length (text), then get highest value field contents?
          philmodjunk

          Define a summary field to return the maximum of the Length calculation field. If you put this in your sub summary part, it would return the value 6 for Alpha and 8 for Beta. We don't need to do that, but it helps to keep that detail in mind.

           

          Now set up an ExecuteSQL query:

          ExecuteSQL ( "

          SELECT DISTINCT TextField FROM YourTable

               WHERE

                        LengthField = ? AND

                        SummaryField = ?"

          ;"" ; ", " ; GetSummary ( YourMaxField ; SubSummaryFIeld ) ; SubSummaryField )

           

          PS this is not how I write actual queries as I use a system of custom functions to avoid referring to field and table occurrence names as quoted text.

          1 of 1 people found this helpful
          • 2. Re: Length (text), then get highest value field contents?
            beverly

            Excellent usage of GetSummary() and passing as parameter to ExecuteSQL(), Phil!

            • 3. Re: Length (text), then get highest value field contents?
              lindseym

              I want you to know I had to look up what executeSQL means... so this may be above my abilities, but hey I gotta learn at some point.

               

              I was able to define a summary field to return the maximum of the length calculation field and it is in my sub summary part

               

              I then defined another field called:   Longest Message  and set the type to: Calculation

               

              Up popped the specify Calculation box:

               

              Calculation Result is: Text

              Number of repetitions: 1

              Checked Box: Do not evaluate if all referenced fields are empty

               

               

              Here is what I entered:

               

               

              ExecuteSQL ( "

              SELECT DISTINCT Copy Side A  C  FROM Message Schedule

                   WHERE

                            LengthField = ? AND

                            SummaryField = ?"

              ;"" ; ", " ; GetSummary ( Message Length Max ; Dwg Sheet C ) ; Dwg Sheet C )

               

               

               

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

               

              When I  preview my layout it shows a    ?   , I have made sure the box is big enough to fit all the characters.

               

               

               

              Table Name: Message Schedule

              Field which the characters are counted from: Copy Side A

              Field which counts the characters: Message Length         = Length (Copy Side A)

              Summary Field which displays the max character count from Copy Side A: Message Length Max   = Maximum of Message Length

              • 4. Re: Length (text), then get highest value field contents?
                lindseym

                I also tried this:

                 

                 

                ExecuteSQL ( "

                SELECT DISTINCT Copy Side A  C  FROM Message Schedule

                     WHERE

                              Message Length = ? AND

                              Message Length Max = ?"

                ;"" ; ", " ; GetSummary ( Message Length Max ; Dwg Sheet C ) ; Dwg Sheet C )

                 

                 

                (thinking that I was suppose to put MY fields in these spots:

                LengthField = ? AND    SummaryField = ?"  )

                • 5. Re: Length (text), then get highest value field contents?
                  philmodjunk

                  You have more to learn about SQL.

                   

                  A ? i s also returned when there is a calculation error such as dividing by zero or when, as is the case here, you have a syntax error in your SQL query.

                   

                  I used some made up field names in my example. You can't use them in the query unless you define fields in your table with exactly the same names. SQL also will produce syntax errors if your names have spaces in them. When they do, you have to enclose them in double quotes to get SQL to interpret them correctly as field or table occurrence names. Since this is all inside a quoted string, you have to use the escape character \ to get the " character to be treated as a character and not the operator that marks the end of some quoted text.

                   

                  ExecuteSQL ( "

                  SELECT DISTINCT \"Copy Side A  C\"  FROM \"Message Schedule\"

                       WHERE

                                \"Message Length\"= ? AND

                                \"Dwg Sheet C\" = ?"

                  ;"" ; ", " ; GetSummary ( Message Length Max ; Dwg Sheet C ) ; Dwg Sheet C )

                   

                  Two more things:

                  1. Select "do not store" in storage options
                  2. Sort your records by Dwg Sheet C or the GetSummary function call will not evaluate correctly.
                  1 of 1 people found this helpful
                  • 6. Re: Length (text), then get highest value field contents?
                    lindseym

                    I will learn more about SQL. I looked at this: ExecuteSQL  which helped a little... but I still have a lot to learn, but my FMP is light years beyond where it started. SQL seems like the next logical path.

                     

                    You mean it wasn't enough for me to remove the parenthesis from my field names? It use to be Copy Side (A) and I know the chaos that caused

                     

                     

                    I selected "Do not store" and am very mindful of sorting order.

                     

                    Thanks, I got it to work after adding the \"