13 Replies Latest reply on May 23, 2014 7:18 AM by jmproulx

    Order of the Field Names

    jmproulx

      How can I get the field names of a table in the same order as specified in the table definition tab of the Manage Database dialog box ? GetFieldNames doesn't do it.

        • 1. Re: Order of the Field Names
          Mike_Mitchell

          You can use any of a number of custom functions that sort return delimited lists on the results of FieldNames. Here's one from Jeremy Bante that's quite fast:

           

           

          /**

          * cfValueSort ( valueList )

          *

          * PURPOSE:

          *                    Sorts the contents of valueList. Values are sorted as text; even

          *                    numeric-only values will be treated as text ("10" < "2"). To sort

          *                    according to numeric values (2 < 10), use the NumberSort function.

          *

          * PARAMETERS:

          *                    valueList: A ¶-delimited list of values to sort

          *

          * RETURNS:

          *                    A ¶-delimited list of the values from valueList in increasing order.

          *

          * NOTES:

          *                    This function uses a variation on the timsort algorithm. This provides

          *                    very good performance in terms of function call stack depth and

          *                    calculation speed.

          *

          * HISTORY:

          *                    CREATED on 2011-04-13 by Jeremy Bante <http://scr.im/jbante>.

          *

          * REFERENCES:

          *                    Timsort: http://bugs.python.org/file4451/timsort.txt

          */

           

           

          Case (

                    /* Step 0, initialize variables */

                    not $~sort.step ;

                              Let ( [

                                        $~sort.runStart                    = 1 ;

                                        $~sort.runMin                    = GetValue ( valueList ; 1 ) ;

                                        $~sort.runMax                    = GetValue ( valueList ; 1 ) ;

                                        $~sort.valueCount          = ValueCount ( valueList ) ;

                                        $~sort.step                              = 1

                              ] ;

                                        cfValueSort ( valueList )

                              ) ;

           

           

                    /* Step 1, search for runs */

                    $~sort.step = 1 ;

                              Let ( [

                                        ~value                              = GetValue ( valueList ; $~sort.runStart ) ;

                                        ~isLastRun                    = $~sort.runStart > $~sort.valueCount ;

                                        $~sort.runStart          = $~sort.runStart + 1

                              ] ;

                                        Case (

                                                  ~value < $~sort.runMin and not ~isLastRun ;

                                                            Let ( [          // Append ~value to current run

                                                                      $~sort.runList          = List ( ~value ; $~sort.runList ) ;

                                                                      $~sort.runMin          = ~value ;

                                                                      $~sort.runSize          = $~sort.runSize + 1

                                                            ] ;

                                                                      cfValueSort ( valueList )

                                                            ) ;

           

           

                                                  ~value ≥ $~sort.runMax and not ~isLastRun ;

                                                            Let ( [          // Append ~value to current run

                                                                      $~sort.runList          = List ( $~sort.runList ; ~value ) ;

                                                                      $~sort.runMax          = ~value ;

                                                                      $~sort.runSize          = $~sort.runSize + 1

                                                            ] ;

                                                                      cfValueSort ( valueList )

                                                            ) ;

           

           

                                                  /* Else */

                                                            Let ( [

                                                                      // Save run for merge

                                                                      $~sort.mergeList =

                                                                                List ( $~sort.mergeList ; $~sort.runList ) ;

                                                                      $~sort.mergeSizeList =

                                                                                List ( $~sort.mergeSizeList ; $~sort.runSize ) ;

                                                                      $~sort.mergeCount = $~sort.mergeCount + 1 ;

           

           

                                                                      // Reset run

                                                                      $~sort.runList          = ~value ;

                                                                      $~sort.runMin          = ~value ;

                                                                      $~sort.runMax          = ~value ;

                                                                      $~sort.runSize          = 1 ;

           

           

                                                                      $~sort.step =

                                                                                If ( ~isLastRun ; $~sort.step + 1 ; $~sort.step )

                                                            ] ;

                                                                      cfValueSort ( valueList )

                                                            )

                                        )

                              ) ;

           

           

                    /* Step 2, initialize merge passes through valueList */

                    $~sort.step = 2 ;

                              Let ( [

                                        ~oddRunSize =

                                                  If ( Mod ( $~sort.runCount ; 2 ) ;          // Odd

                                                            GetValue ( $~sort.runSizeList ; $~sort.runCount )

                                                  ) ;

                                        $~sort.sortedList =

                                                  List (

                                                            $~sort.mergeList ;

                                                            RightValues ( $~sort.sortedList ; ~oddRunSize )

                                                  ) ;

                                        $~sort.runSizeList          = List ( $~sort.mergeSizeList ; ~oddRunSize ) ;

                                        $~sort.runCount                    = ValueCount ( $~sort.runSizeList ) ;

                                        $~sort.runPair                    = 1 ;

                                        $~sort.run2End                    = "" ;

           

           

                                        $~sort.mergeList                    = "" ;

                                        $~sort.mergeSizeList          = "" ;

                                        $~sort.mergeCount                    = "" ;

           

           

                                        $~sort.step =

                                                  If (          // There are no more merges to do

                                                            $~sort.runCount ≤ 1 ;

                                                                      5 ;

                                                            /* Else */

                                                                      $~sort.step + 1

                                                  )

                              ] ;

                                        cfValueSort ( "" )

                              ) ;

           

           

                    /* Step 3, initialize merges of successive pairs of runs */

                    $~sort.step = 3 ;

                              Let ( [

                                        $~sort.run1Start =

                                                  $~sort.run2End + 1 ;

                                        ~run1Size =

                                                  GetValue ( $~sort.runSizeList ; $~sort.runPair * 2 - 1 ) ;

                                        $~sort.run1End = $~sort.run1Start + ~run1Size - 1 ;

                                        $~sort.run1Value =

                                                  GetValue ( $~sort.sortedList ; $~sort.run1Start ) ;

                                        $~sort.run1Max = GetValue ( $~sort.sortedList ; $~sort.run1End ) ;

           

           

                                        $~sort.run2Start = $~sort.run1End + 1 ;

                                        ~run2Size = GetValue ( $~sort.runSizeList ; $~sort.runPair * 2 ) ;

                                        $~sort.run2End = $~sort.run2Start + ~run2Size - 1 ;

                                        $~sort.run2Value =

                                                  GetValue ( $~sort.sortedList ; $~sort.run2Start ) ;

                                        $~sort.run2Max = GetValue ( $~sort.sortedList ; $~sort.run2End ) ;

           

           

                                        $~sort.mergeSizeList =

                                                  List ( $~sort.mergeSizeList ; ~run1Size + ~run2Size ) ;

                                        $~sort.mergeCount          = $~sort.mergeCount + 1 ;

                                        $~sort.runPair                    = $~sort.runPair + 1 ;

           

           

                                        $~sort.step = $~sort.step + 1

                              ] ;

                                        cfValueSort ( "" )

                              ) ;

           

           

                    /* Step 4, merge pair of runs */

                    $~sort.step = 4

                    and $~sort.run1Max < $~sort.run2Value ;          // run1 is less

                              Let ( [

                                        ~run1 =

                                                  Substitute (

                                                            MiddleValues (

                                                                      $~sort.sortedList ;

                                                                      $~sort.run1Start ;

                                                                      $~sort.run1End - $~sort.run1Start + 1

                                                            )

                                                            & "¶END" ;

                                                            [ "¶¶END" ; "" ] ; [ "¶END" ; "" ]

                                                  ) ;

                                        ~run2 =

                                                  Substitute (

                                                            MiddleValues (

                                                                      $~sort.sortedList ;

                                                                      $~sort.run2Start ;

                                                                      $~sort.run2End - $~sort.run2Start + 1

                                                            )

                                                            & "¶END" ;

                                                            [ "¶¶END" ; "" ] ; [ "¶END" ; "" ]

                                                  ) ;

                                        $~sort.mergeList = List ( $~sort.mergeList ; ~run1 ; ~run2 ) ;

           

           

                                        $~sort.step =

                                                  If ( $~sort.runCount > $~sort.runPair * 2 - 1 ;

                                                            $~sort.step - 1 ;

                                                            /* Else */ $~sort.step - 2

                                                  )

                              ] ;

                                        cfValueSort ( "" )

                              ) ;

           

           

                    $~sort.step = 4

                    and $~sort.run1Value ≥ $~sort.run2Max ;          // run2 is less

                              Let ( [

                                        ~run1 =

                                                  Substitute (

                                                            MiddleValues (

                                                                      $~sort.sortedList ;

                                                                      $~sort.run1Start ;

                                                                      $~sort.run1End - $~sort.run1Start + 1

                                                            )

                                                            & "¶END" ;

                                                            [ "¶¶END" ; "" ] ; [ "¶END" ; "" ]

                                                  ) ;

                                        ~run2 =

                                                  Substitute (

                                                            MiddleValues (

                                                                      $~sort.sortedList ;

                                                                      $~sort.run2Start ;

                                                                      $~sort.run2End - $~sort.run2Start + 1

                                                            )

                                                            & "¶END" ;

                                                            [ "¶¶END" ; "" ] ; [ "¶END" ; "" ]

                                                  ) ;

                                        $~sort.mergeList = List ( $~sort.mergeList ; ~run2 ; ~run1 ) ;

                                        $~sort.step =

                                                  If ( $~sort.runCount > $~sort.runPair * 2 - 1 ;

                                                            $~sort.step - 1 ;

                                                            /* Else */ $~sort.step - 2

                                                  )

                              ] ;

                                        cfValueSort ( "" )

                              ) ;

           

           

                    $~sort.step = 4

                    and $~sort.run1Value < $~sort.run2Value ;          // run1Value is less

                              Let ( [

                                        $~sort.mergeList = List ( $~sort.mergeList ; $~sort.run1Value ) ;

                                        $~sort.run1Start = $~sort.run1Start + 1 ;

                                        $~sort.run1Value = GetValue ( $~sort.sortedList ; $~sort.run1Start )

                              ] ;

                                        cfValueSort ( "" )

                              ) ;

           

           

                    $~sort.step = 4 ;          // run2Value is less

                              Let ( [

                                        $~sort.mergeList = List ( $~sort.mergeList ; $~sort.run2Value ) ;

                                        $~sort.run2Start = $~sort.run2Start + 1 ;

                                        $~sort.run2Value = GetValue ( $~sort.sortedList ; $~sort.run2Start )

                              ] ;

                                        cfValueSort ( "" )

                              ) ;

           

           

                    /* Step 5, clean-up and return result */

                    $~sort.step = 5 ;

                              Let ( [

                                        ~sortedList = $~sort.sortedList ;

           

           

                                        // purge variables

                                        $~sort.mergeList                    = "" ;

                                        $~sort.mergeCount                    = "" ;

                                        $~sort.mergeSizeList          = "" ;

                                        $~sort.run1End                              = "" ;

                                        $~sort.run1Max                              = "" ;

                                        $~sort.run1Start                    = "" ;

                                        $~sort.run1Value                    = "" ;

                                        $~sort.run2End                              = "" ;

                                        $~sort.run2Max                              = "" ;

                                        $~sort.run2Start                    = "" ;

                                        $~sort.run2Value                    = "" ;

                                        $~sort.runCount                              = "" ;

                                        $~sort.runList                              = "" ;

                                        $~sort.runMax                              = "" ;

                                        $~sort.runMin                              = "" ;

                                        $~sort.runPair                              = "" ;

                                        $~sort.runSize                              = "" ;

                                        $~sort.runSizeList                    = "" ;

                                        $~sort.runStart                              = "" ;

                                        $~sort.sortedList                    = "" ;

                                        $~sort.step                                        = "" ;

                                        $~sort.valueCount                    = ""

                              ];

                                        ~sortedList

                              )

          )

          • 2. Re: Order of the Field Names
            erolst

            If you've FM ≥ 12, this article may interest you. The ExecuteSQL() approach works layout-independently, lets you filter the result list (WHERE) and sort it by any criterion.

             

            AFAIK, there is no way to query the current sort order of the Fields tab in the Manage Database dialog (and thus be able to replicate it).

            • 3. Re: Order of the Field Names
              Mike_Mitchell

              After I posted, I realized you might want something other than sort by field name, so erolst’s suggestion will cover you if you want to grab field type. The ExecuteSQL approach is excellent. Unfortunately, you can’t get creation order or any custom sort order out of it. I don’t know of a way to get those.

              1 of 1 people found this helpful
              • 4. Re: Order of the Field Names
                beverly

                Except if you create a LAYOUT with any order (so the fields are placed in the specified order automatically). Then use the Design function:

                     FieldNames ( fileName ; layoutName )

                 

                For "fileName", you can use Get ( FileName ) . If you are ON the layout when you call a script, then you can use this for "layoutName", otherwise hard code the specific layout:

                     Get ( LayoutName )

                 

                Yes, you need to have a layout created after any schema changes for this to work.

                What you get (quote from HELP):

                     "Returns a list of the names of all fields on layoutName, in fileName file, separated by carriage returns."

                 

                Help doesn't say "in the order placed", but in my tests I found this to be true as long as you create the layout after arranging the fields in the dialog.

                 

                BTW, there are other Design functions for field info:

                     FieldBounds, FieldComment, FieldIDs, FieldREpetitions, FieldStyle and FieldType

                Combined with the Get() functions, we have a very powerful way to extract some of the information you may want.

                 

                HTH,

                Beverly

                1 of 1 people found this helpful
                • 5. Re: Order of the Field Names
                  Mike_Mitchell

                  Now THAT is good information. Thanks, Beverly.

                  • 6. Re: Order of the Field Names
                    jmproulx

                    Thank you both for your advice. I will use the ExecuteSQL function to solve my problem.

                     

                    I have a Notes table linked to all other entity tables (Personel, Orders, Equipment, Stores, Products, etc..) in my solution via TOs. I have also a Notes layout in order to review all the notes taken for all entities and focus on those for which an issue is still pending. I have a button on the Notes layout that send me to the entity bearing such a note, and another button on the entity layout that sends me back to the Notes layout.

                     

                    GTRR works fine if the note is about an entity of the base table. It doesn't work for the notes of all the other TOs.

                     

                    I have the value of the primary key of the note record and I know from which TO it comes from. I can get there, but I can't retrieve the note record unless I use ExecuteSQL with some gymnatics to specify the table_name and the primary key name as an expression.

                     

                    Before I read your answers, I thought that, since the primary key is always positionned in 3rd place in all my tables, I could use :

                     

                                                                      GetFielName( GetValue( FieldNames( Get(FileName) ; Get(LayoutName) ) ; 3) )

                     

                    to get its value and compare it to the value of the primary key.  But the field names returned by FielNames are those of the layout where the primary key is NOT in 3rd place.

                     

                    I hope my description of the situation is not too convoluted.

                     

                    Jean-Marc

                    • 7. Re: Order of the Field Names
                      raycon

                      This is great thankyou. I can use this in several of my recalcitrant value lists.

                       

                      Sent from my iPhone

                      • 8. Re: Order of the Field Names
                        Mike_Mitchell

                        Jean-Marc -

                         

                        I'm not 100% certain what you're describing, but it sounds like you're suffering from the lack of a naming convention. Why isn't your note primary key / foreign key named the same in all tables (to avoid all these calculation gymnastics)?

                         

                        Mike

                        • 9. Re: Order of the Field Names
                          jmproulx

                          I am following Ray Cologon's naming conventions and schema architecture for the Notes TOs. I bought its excellent training video Learning FileMaker Pro 12 from Infinite Skills.

                           

                          Every table has 2 keys, one is an autogenerated serial number and the other is a text derived from the serial number with the SerialIncrement function. The first 2 characters of this text primary key are letters identifying the table. For example, EQ00011 identifies record # 11 in the Equipment table. Each identity table (not the join tables) in my solution have a related Notes TO. All the notes of those TOs are aggregated in the base Notes table which comprises this text primary key of the record from its source table (Equipment table in this example).

                           

                          I can extract from this text primary key the name of the source table (Equipment table). With this name I go to the appropriate layout (Equipment layout) and try to locate the proper record using the primary key. Using text gymnastics I can reconstruct the names of the primary keys. ExecuteSQL will get me to the record.

                           

                          Jean-Marc

                          • 10. Re: Order of the Field Names
                            Mike_Mitchell

                            I'm familiar with this architecture. But it identifies the values held in the key fields, not their names. As you mention, EQ00011 means "record #11 from EQ" (meaning Equipment). What is the actual name of the field in which this information is held? You should be able to parse that out without using FieldNames.

                            • 11. Re: Order of the Field Names
                              jmproulx

                              EQ00011 is the value of the field ID_Reference in the Notes table. ID_Equipment is the name of the text primary key in the Equipment table. From the value EQ00011 contained in the ID_Reference field I can reconstruct all the necessary names of all the TOs.

                               

                              I wanted to avoid the text gymnastics necessary to do that by using the appropriate Design functions in my button script. I would have given more portability to my script. Now, it is dependant on the names of the schema.

                              • 12. Re: Order of the Field Names
                                Mike_Mitchell

                                Okay, good. You can do that; you just have to change your method a bit.

                                 

                                Once you've navigated to the appropriate parent layout, you can extract the name of the table using Get ( LayoutTableName ). So you can get the name of the appropriate ID field using:

                                 

                                     "ID_" & Get ( LayoutTableName )

                                 

                                From there, you can get its value using:

                                 

                                     Let ( tableName = Get ( LayoutTableName ) ; Evaluate ( tableName & "::" & "ID_" & tableName ))

                                 

                                That should get you what you need.

                                 

                                Mike

                                • 13. Re: Order of the Field Names
                                  jmproulx

                                  Thank you Mike, very helpful. I will try that.

                                   

                                  Jean-Marc