1 2 Previous Next 29 Replies Latest reply on Aug 17, 2016 5:02 AM by cillion

    Sort found set by first field then second field

    cillion

      Hi there!

      Am trying to sort a found set after when search string has been found in first field and then in a second field. Example: when searching for "filemaker" the result should be sorted by "found in current_employer" first and then "found in previus_employer" after. How could this be done?

       

      The search is working as expected, but not the sort.

       

      my search script has one sort records at the end where I sort by current_employer ASC and previus_employer ASC. and that does almost do the job but some record show up at the bottom where the current_employer="filemaker"

        • 1. Re: Sort found set by first field then second field
          Johan Hedman

          You have records like this

           

                              Customer Name           Old Customer Name           Country

          Record 1     IKEA                                                                           Sweden

          Record 2     Apple Could               Apple Storage                         USA

          Record 3     NASA                         Rocket Science                       USA

           

          In your Sort dialog

               First Customer Name

               Second Old Customer Name

           

          Then the sort order will be

          Apple Cloud

          IKEA

          NASA

          • 2. Re: Sort found set by first field then second field
            Johan Hedman

            Read FileMaker Training Series Basic Lesson 4 about Sorting

            • 3. Re: Sort found set by first field then second field
              cillion

              In my Sort dialog i tried:

                   First Customer Name

                   Second Old Customer Name

               

              did not work, but I tried:

                   First Old Customer Name

                   Second Customer Name

               

              That produced almost the wanted result. But still some of the records that match in the current customer name will located at the bottom of the search result, with the match of previus company

              • 4. Re: Sort found set by first field then second field
                Johan Hedman

                Show me some Customer information like I wrote and how you want it to be sorted.

                • 5. Re: Sort found set by first field then second field
                  cillion

                                      Customer Name           Old Customer Name           Country

                  Record 1     IKEA                           Slippery AS                               Sweden

                  Record 2     Apple Could               Apple Storage                           USA

                  Record 3     ATEW                         Ikea                                          USA

                  Record 4     NASA                         Rocket Science                          USA

                  Record 5     Mcdonnalds               ikea                                           USA

                   

                  When searched on this: "ikea" the result is going to be:

                   

                  Record 1     IKEA                           Slippery AS                               Sweden

                  Record 3     ATEW                         Ikea                                          USA

                  Record 5     Mcdonnalds               ikea                                           USA

                  • 6. Re: Sort found set by first field then second field
                    Johan Hedman

                    If you search for IKEA and Sort Order IS

                    Customer Name

                    Old Customer Name

                     

                    Your search result are going to be if we go back to the discussion we had yesterday

                    ATEW      IKEA

                    IKEA          Slippery AS

                    MacDonalds IKEA

                     

                    If you always want to have all Customer that have the name right now (Customer Name) first and used to have it after (Old Customer Name) you need to consider doing a branch relationship. But dont go there yet until you figure out what it is that you really want

                    • 7. Re: Sort found set by first field then second field
                      cillion

                      If you always want to have all Customer that have the name right now (Customer Name) first and used to have it after (Old Customer Name) you need to consider doing a branch relationship. But dont go there yet until you figure out what it is that you really want

                      This is what we what. only the "current empoloyer=searched string" on the top of the result. What is a branch relationship?

                      • 8. Re: Sort found set by first field then second field
                        Johan Hedman

                        You got to ask yourself a number of questions.

                             What if Customer change name again?

                             In that case how would I keep track of the second last Old company name.

                         

                        What you should start thinking about instead is when a Customer change name, maybe duplicate that original record and then at your new record start from there. You can then have a new field in your Customer table ID Old Customer so that you can link your old Customer to the new one. That is making your customer have relations. That would make your sort thing work so much easier

                        • 9. Re: Sort found set by first field then second field
                          cillion

                          yes, but we can not make changes on the database model, and reengineer the database. Is it possible to do this kind of sorting of the two text fields without reinventing the structure?

                          • 10. Re: Sort found set by first field then second field
                            Johan Hedman

                            Not they you want it to be

                            • 11. Re: Sort found set by first field then second field
                              cillion

                              Possible to do with a Leading Grand summery or a ExecuteSQL ?

                              • 12. Re: Sort found set by first field then second field
                                cillion

                                Possible to use the ThenBy sql possibly? I tried this, but it not work;

                                 

                                ExecuteSQL (

                                "SELECT *

                                FROM \"Person\"

                                WHERE Person::Current_Employer=? OR Person::Previus_Employer=?

                                ORDER BY Person::Current_Employer

                                THEN BY Person::Previus_Employer" ;

                                "|" ; "¶" ;

                                Find_Person::company ;

                                Find_Person::company

                                )

                                • 13. Re: Sort found set by first field then second field
                                  beverly

                                  THEN BY is not used with ExecuteSQL() function. Just comma-delimit the list of fields to be sorted (in the same order you would in the SORT dialog):

                                  NOTE you have the "::" as the table/name separator. SQL uses "."

                                   

                                  ExecuteSQL (

                                       "SELECT *

                                       FROM \"Person\" p

                                       WHERE p.Current_Employer=?

                                            OR p.Previus_Employer=?

                                       ORDER BY p.Current_Employer, p.Previus_Employer"

                                   

                                       ; "|" ; "¶" // result delimiters

                                       ; Find_Person::company // 1st ? param

                                       ; Find_Person::company // 2nd ? param

                                  )

                                   

                                  beverly

                                  • 14. Re: Sort found set by first field then second field
                                    cillion

                                    Dont know if this worked or not, my solution crached when doing this. But basicly all this finding previus in the script, but now its only the sorting left to do. Can I only do this advanced ORDER BY in the ExecuteSQL script step? Or maybe I need to use GROUP BY? (Example: when searching for "filemaker" the result should be sorted by "found in current_employer" first and then "found in previus_employer" after)

                                    1 2 Previous Next