14 Replies Latest reply on Jun 2, 2017 2:37 PM by MarcoB

    Sorting Fields

    MarcoB

      Hi.

       

      I have a table with 5 fields, F1, F2, F3, F4, F5, ..., F15 formatted as Number that holds numbers from 1 to 40.

      I input the numbers in an aleatory order.

      I need to make a script for a button that when pressed reads the numbers, sort them and wright back to fields Fn in the sorted ascending way.

      I tried to make a script comparing groups of 2 fields, making it write the lower number to the first field and the bigger number to the second field.

      It end up like this: 22, 03, 11, 33, 2, 37, 35, 9, 7, 21, 14, 1, 6, 27, 40 -> 03, 22, 11, 33, 2, 37, 9, 35, 7, 21, 1, 14, 6, 27, 40

      But from that, I could not establish a good idea to follow.

      Please, how can I accomplish that?

        • 1. Re: Sorting Fields
          philmodjunk

          Created a related table with one number field. Put your numbers into 15 related records. Now you can have FileMaker sort these records in order to put them into order. Note that you can use a sorted relationship to put them in order.

          • 2. Re: Sorting Fields
            MarcoB

             

             

            I'm afraid I did not follow.

            I'm not a pro user.

            • 3. Re: Sorting Fields
              MarcoB

              If I create a new related table to use the Sort option, wouldn't it be creating double data in the database, thus making the database heavier.

              This "list" that I do I have to do many times a day, I think in a short time I will have a massive database.

              How about Variables?

              • 4. Re: Sorting Fields
                philmodjunk

                There is no double storage of your data. Remove fields F1...F15 from your solution and use 15 related records instead.

                 

                Any database system comes with with built in, optimized algorithms for sorting data. Thus, it is almost always better to use them instead of creating your own.

                • 5. Re: Sorting Fields
                  MarcoB

                  OK, now I get it what you said to use related. For example, instead of storing the numbers in the Numbers table, they will be at the numbers_sort table and will be shown by the relation.

                   

                  But I still don't get how the Sort option in the relation will work this out because the 15 numbers fields are in the same Record.

                   

                  Does the Sort Option work across fields in the same Record?

                  • 6. Re: Sorting Fields
                    erolst

                    Instead of one record with 15 number fields, use one parent record (the table that you already have) with 15 related records (from a new table) with one number field.

                    • 7. Re: Sorting Fields
                      TomHays

                      Storing the numbers as separate records in a different table instead of fields F1... F15 is the right way to go to do things the FileMaker-way and make your life easier.

                       

                      But if you already have the data in fields F1 through F15 and want to keep the data in those fields, here is a script that you can use to sort the values into ascending order as you described.

                       

                      # Assign all of your fields here following the pattern

                      # 5 fields are currently assigned

                      #

                      # Put all of your fields here following the pattern

                      Set Variable [ $f; Value:Right("0" & TheTable::F1; 2) & ".01" ]

                      Set Variable [ $f[2]; Value:Right("0" & TheTable::F2; 2) & ".02" ]

                      Set Variable [ $f[3]; Value:Right("0" & TheTable::F3; 2) & ".03" ]

                      Set Variable [ $f[4]; Value:Right("0" & TheTable::F4; 2) & ".04" ]

                      Set Variable [ $f[5]; Value:Right("0" & TheTable::F5; 2) & ".05" ]

                      #

                      # Change the $count variable to be the number of fields you assigned above

                      Set Variable [ $count; Value:5 ]

                      Loop

                      Exit Loop If [ $count < 1 ]

                      # put the $f[] variables here in the Max() that you defined above.

                      Set Variable [ $maxFoundIdx; Value:Let([ m = Max($f; $f[2]; $f[3]; $f[4]; $f[5]) ]; GetAsNumber(100*(m - Int(m)))

                      )]

                      Set Variable [ $theList; Value:List($f[$maxFoundIdx]; $theList) ]

                      Set Variable [ $f[$maxFoundIdx]; Value:0 ]

                      Set Variable [ $count; Value:$count - 1 ]

                      End Loop

                      #

                      # Put your fields here following the pattern

                      Set Field [ TheTable::F1; Int(GetValue($theList; 1)) ]

                      Set Field [ TheTable::F2; Int(GetValue($theList; 2)) ]

                      Set Field [ TheTable::F3; Int(GetValue($theList; 3)) ]

                      Set Field [ TheTable::F4; Int(GetValue($theList; 4)) ]

                      Set Field [ TheTable::F5; Int(GetValue($theList; 5)) ]

                       

                       

                      This script makes no assumptions about the field names in case your description of the fields F1...F15 is just for convenience in asking the question and isn't the reality. 

                      If the fields are named the same but differ only by the appended 1 through 15, then you can exploit this by using GetField() and Set Field By Name[] in a loop instead of having separate lines for each field hard coded.

                       

                      -Tom

                      • 8. Re: Sorting Fields
                        erolst

                        TomHays wrote:

                        # Put all of your fields here following the pattern

                        Set Variable [ $f; Value:Right("0" & TheTable::F1; 2) & ".01" ]

                        Set Variable [ $f[2]; Value:Right("0" & TheTable::F2; 2) & ".02" ]

                        Set Variable [ $f[3]; Value:Right("0" & TheTable::F3; 2) & ".03" ]

                        Set Variable [ $f[4]; Value:Right("0" & TheTable::F4; 2) & ".04" ]

                        Set Variable [ $f[5]; Value:Right("0" & TheTable::F5; 2) & ".05" ]

                        #

                        # Change the $count variable to be the number of fields you assigned above

                        Set Variable [ $count; Value:5 ]

                        )) ]

                        With fields that don't have a consistent naming/numbering scheme, I use

                         

                        Set Variable [ $fieldList ;

                          List (

                            GetFieldName ( Table::field) ;

                            GetFieldName ( Table::field )

                            // etc.

                          )

                        ]

                         

                        which is user maintainable, robust against field changes, allows use of a loop (incl. correct calculation of the exit condition) and only needs to be defined once.

                        • 9. Re: Sorting Fields
                          TomHays

                          I can see how that is useful

                          erolst wrote:

                           

                          Set Variable [ $fieldList ;

                          List (

                          GetFieldName ( Table::field) ;

                          GetFieldName ( Table::field )

                          // etc.

                          )

                          ]

                           

                          which is user maintainable, robust against field changes, allows use of a loop (incl. correct calculation of the exit condition) and only needs to be defined once.

                           

                          I can see how that is very useful to make the work easier in setting up the script for the beginning and end sections that reference the fields.

                           

                          To scale with the number of fields in the $fieldList, the script step

                          Set Variable [ $maxFoundIdx; Value:Let([ m = Max($f; $f[2]; $f[3]; $f[4]; $f[5]) ]; GetAsNumber(100*(m - Int(m)))

                          )]

                          would have to be rewritten to use Evaluate() and a calc constructed to hold an arbitrary number of items in Max() to scale with the $fieldList.

                           

                          -Tom

                          • 10. Re: Sorting Fields
                            TomHays

                            Here is a variation of the script that requires you to enter the list of fields in only one place at the beginning of the script.

                             

                            This script uses SortValues() from FileMaker 16 or an equivalent custom function.  To accommodate a custom function that only sorts alphabetically instead of numerically, the numbers are left-padded with "0" so that alpha-sorting will work.

                             

                            # Put your fields here

                            Set Variable [ $fieldList; Value:List(

                               GetFieldName( TheTable::F1);

                               GetFieldName( TheTable::F2);

                               GetFieldName( TheTable::F3);

                               GetFieldName( TheTable::F4);

                               GetFieldName( TheTable::F5)

                               )]

                            #

                            Set Variable [ $count; Value:ValueCount($fieldList) ]

                            Loop

                               Exit Loop If [ $count < 1 ]

                               Set Variable [ $theNumberList; Value:List($theNumberList;

                                              Right("0" & GetField(GetValue($fieldList; $count)); 2)

                                              ) ]

                               Set Variable [ $count; Value:$count - 1 ]

                               End Loop

                            #

                            # Use SortValues() in FileMaker Pro 16 or provide a custom function

                            Set Variable [ $sortedNumberList; Value:SortValues($theNumberList; 2) ]

                            #

                            Set Variable [ $count; Value:ValueCount($fieldList) ]

                            Loop

                            Exit Loop If [ $count < 1 ]

                            Set Field By Name [ GetValue($fieldList; $count); GetAsNumber(GetValue($sortedNumberList; $count)) ]

                            Set Variable [ $count; Value:$count - 1 ]

                            End Loop

                             

                             

                             

                            -Tom

                            • 11. Re: Sorting Fields
                              siplus

                              If I understand well, you want to generate 15 RANDOM UNIQUE numbers out of 1...40 and have them sorted in fields F1 to F15.

                               

                              FilterValues will do the sorting for you.

                               

                              See attached, hit the button.

                               

                              For the TLDR, the script (adjustable in many ways - data pool and number of results) is:

                               

                              New Record/Request

                              Loop

                                Exit Loop If [ Let ( $L = FilterValues("1¶2¶3¶4¶5¶6¶7¶8¶9¶10¶11¶12¶13¶14¶15¶16¶17¶18¶19¶20¶21¶22¶23¶24¶25¶26¶27¶28¶29¶30¶31¶32¶33¶34¶35¶36¶37¶38¶39¶40"; Int(Random*46) & ¶ & Int(Random*46) & ¶ & Int(Random*46) & ¶ & Int(Random*46) & ¶ & Int(Random*46) & ¶ & Int(Random*46) & ¶ & Int(Random*46) & ¶ &   // 15 of them Int(Random*46) & ¶ & Int(Random*46) & ¶ & Int(Random*46) & ¶ & Int(Random*46) & ¶ & Int(Random*46) & ¶ & Int(Random*46) & ¶ & Int(Random*46) & ¶ & Int(Random*46) ); ValueCount($L) = 15  // stop value ) ]

                              End Loop

                              Set Variable [ $i ; Value: 0 ]

                              Loop

                                Exit Loop If [ Let ($i = $i + 1; $i > 15) ]

                                Set Field By Name [ Get(LayoutTableName) & "::" & "F" & SerialIncrement("00"; $i) ; GetValue($L; $i) ]

                              End Loop

                              1 of 1 people found this helpful
                              • 12. Re: Sorting Fields
                                MarcoB

                                siplus,

                                 

                                Your solution for my problem worked very nice.

                                Thank you very much.

                                 

                                Thanks to all others that tried to help me in this great community.

                                • 13. Re: Sorting Fields
                                  siplus

                                  Hi Marco,

                                   

                                  As per your request in private message, I enclose a version in which you can decide how many fields you want assigned, as well as a min to max interval for the values to be picked.

                                   

                                  Please note that a custom function is used.

                                  1 of 1 people found this helpful
                                  • 14. Re: Sorting Fields
                                    MarcoB

                                    Hi Siplus,

                                     

                                    It is just what I was looking for, you did a great job RandomValuesin15fields.

                                    Thank you very much.