4 Replies Latest reply on May 19, 2010 11:11 AM by philmodjunk

    setting up a custom sorting order

    cloud4521

      Title

      setting up a custom sorting order

      Post

      hello,

       

      I'm new to using file maker pro. I am trying to order the files in a database. We check fire dampers for different buildings every five years. sometimes new dampers get added and a record has to be made for them. This record appears at the bottom of the list. I have tried sorting the list to get the new dampers added. The problem with this is that it mixes up the floor order of the building. We list ground floor first and then put floor 1, 2, 3, .... When I try to sort it puts the ground floor on the bottom of the list. I want to make a custom list that tell the sort to put the ground floor first then to sort by numerical, or alphabetical order.  It seems like it shouldn't be that hard to do, but I can't figure it out. Is there any way to resolve this problem?

       

      Thanks

       

        • 1. Re: setting up a custom sorting order
          philmodjunk

          I've used two different soutions to this problem over the years...

           

          1) Define a calculation field like this:  If ( Floor = "G" ; 0 ; Floor ) and set it to return Number as its return type. Now sort on this new field instead of floor.

           

          2) Define a custom value list as:

          G

          1

          2

          3

          4

          5

           

          Sort your records by floor, but select "custom order based on .." and select this value list for your sort order.

          • 2. Re: setting up a custom sorting order
            raybaudi

            cloud4521 wrote:

             

            We list ground floor first and then put floor 1, 2, 3, ....


            A trick can be defining the field "floor" as number field and insert "ground (0)" unstead of "ground"

             


            • 3. Re: setting up a custom sorting order
              cloud4521

              I tried the second method. I'm not sure how to do the first method. The list looks like this:

              c1-001

              c1-002

              c2-001

              cg-001

              cg-002

               

              c: building c

              g: ground

              00*: damper number.

               

              I try using to custom list, but it still puts c1 at the top instead of cg.

              • 4. Re: setting up a custom sorting order
                philmodjunk

                You've just included details not in your original post. I assumed the floor number was entered in a separate field--not part of a text string. I don't think you can get a value list to work for this as you have too many possible values.

                 

                Try this calculation in a stored calculation field: (I'm assuming one letter for "building" and one digit for "floor". If this is not the case, the following calculation will have to be changed.)

                 

                If ( Middle ( Floor ; 2 ; 1 ) = "g" ; 0 ; Middle ( Floor ; 2 ; 1 ) ) & Right ( Floor ; 3 )

                 

                Set result type as Number.

                 

                Another alternative would be to separate building, floor and damper values into separate fields. Then sorting becomes much easier to set up. You'd use a calculation field to glue all the parts back in to a single string (concatenate them) when you need that particular item. That calculation would look like:

                 

                Building & Floor & "-" & Damper