14 Replies Latest reply on Jan 21, 2014 1:17 AM by jrenfrew

    Sorting Variables

    robrickard

      I have a record with multiple fields. Each field on the record holds a summited number. The request is that the numbers are sorted before placed into the fields (largest to smallest). This is pretty easy to do if there are only 3 numbers as I can do it via CASE. But once it was bumped up to 4 I found out that I can have only one 'AND' in a case script. Which turns normal scripting in a real mess real quick. (trying to keep this simple and my time invested, down)

       

      What i need is to take the 4 (one day may be up to 12) $varibles and sort them from greatest to smallest number and then place them in the layout so users can see the numbers listed from greatest to smallest. Im no longer clear on best practice on how to sort them.

       

      I must be missing some simple function that does this - i have been reading most of the morning and cant find a real solution. Maybe It is not as easy as im thinking it should be. I must be missing something. Maybe my brain is not working today and i just cant think though it. Thanks for any help,

        • 1. Re: Sorting Variables
          jrenfrew

          Seems like you are trying to do something in a complicated way. But I dont quite get what you are trying to do as at first you mention fields then talk about variables....

           

          You can have nested if and case statements therefore more than one AND in a CASE

          If ( a ; case ( a AND b ; 1 ; a AND c ; 2 ; a AND d ; 3 ; 4 ) ; 5 )

           

          1 of 1 people found this helpful
          • 2. Re: Sorting Variables
            robrickard

            Sorry for any confusion - i was explaining what i had and what i wanted to do with it.

             

            Im not even sure why i didnt think about nested CASE scripts. For some reason i gave up quick and moved on to looking for a function. I really thought i would find a solid function to take care of what i needed. Great idea and im sure that will take care of my needs. Thank you, jrenfrew.

            • 3. Re: Sorting Variables
              erolst

              robrickard wrote:

              For some reason i gave up quick and moved on to looking for a function.

              Well, Case () is a function …

               

              If you describe in more detail where your values are coming from and where they're supposed to go, there may be more efficient ways.

               

              btw, you can write jrenfrew's example without nesting as

               

              Case (

              not a ; 5 ;

              a and b ; 1 ;

              a and c ; 2 ;

              a and d ; 3 ;

              4 )

               

              or even shorter with nesting and an implicit AND

               

              Case (

                a ;

                Case (

                  b ; 1 ;

                  c ; 2 ;

                  d ; 3 ;

                  4 ) ;

                5 )

              • 4. Re: Sorting Variables
                robrickard

                I now have $var1 $var2 $var3 $var4 (all numbers)

                I will be listing them on a record in order from greatest to smallest. (view friendly for staff to see the numbers in that order as the largest number means more to them)

                 

                Im trying to script this with nested CASE. Not as pretty so far. I would think there is a better way than to have nested CASE or IF statements.

                 

                In the future this may grow to 12.

                • 5. Re: Sorting Variables
                  jrenfrew

                  So you have a list of variables which need sorting

                  There are a number of custom functions to do this especially if they are all numbers ( see Brian Dunning)

                   

                  How are you inteding that this list iis the ndisplayed to the end user??

                   

                  The most obvious way might be to put that list into a text field once sorted as a concatenated list.

                   

                  If you want to put the lowest into field1 and then the next into field2 etc then surely a loop in the script

                   

                   

                  SetFieldByName (fieldname & $i) to getvalue ( $sortedlist ; $i )

                  • 6. Re: Sorting Variables
                    erolst

                    Consider using a related table with fields for type and value (which may be better than field1, field2, field3 …); then collect your values into one $var (List ( $var ; newValue ) instead of individual $vars, and also collect their type (which maybe would be the previous field names) by the same method.

                     

                    Then create new records

                     

                    Loop

                    Set Variable [ $i ; $i + 1 ]

                    New Record

                    Set Field [ ValueTable::type ; GetValue ( $typeList ; $i ) ]

                    Set Field [ ValueTable::value ; GetValue ( $valueList ; $i ) ]

                    Set Field [ ValueTable::foreignKey ; $primaryKeyOftheOtherTable ]

                    Exit Loop If [ $i = ValueCount ( $valueList ) ]

                    End Loop

                     

                    sort the relationship, and display the results in a portal, or create a calc field with List ( ValueTable::cTypeAndValue ).

                     

                    More flexible than using a bunch of serialized fields.

                    • 7. Re: Sorting Variables
                      robrickard

                      Thank you for the input, erolst.

                      But, im not following you. You example is not addressing the sorting needed.

                       

                      I only need to read the fields in the records once (set to $Vars). Then Process the sort. Then place the new sorted numbers back into the fields.

                      thanks for any ideas.

                      • 8. Re: Sorting Variables
                        erolst

                        Well, you never explicitly stated where those values were coming from in the first place.

                        robrickard wrote:

                        The request is that the numbers are sorted before placed into the fields (largest to smallest).

                         

                        This should then read "The request is that the numbers already in these fields are sorted before placed back."

                         

                        You may still want to consider using a related table, so any entered values (aka records) will simply sort themselves via the relationship.

                         

                        Having multiple similar (named) fields in a table is usually a sign that you should use a relational structure.

                        • 9. Re: Sorting Variables
                          Mike_Mitchell

                          Rob -

                           

                          Let me see if I understand your process correctly. You have a series of numbers, stored in a series of fields. You're placing them in a series of variables, which you want to sort in numeric order, after which you want to place them back into the original fields.

                           

                          If that's the case, what would be wrong with this process:

                           

                          1) Use List to put the numbers together in a return delimited list in a single variable (prepended with enough "0" characters so they sort correctly).

                          2) Use one of several available custom functions to sort the list.

                          3) Loop over the list to place them back into the fields.

                           

                          Would that work for you?

                           

                          Mike

                           

                          P.S. erolst is correct in stating that your table structure is probably not set up correctly. Multiple similarly-named fields in an array-like structure is usually a red flag that says, "I need to be in a related table."

                          1 of 1 people found this helpful
                          • 10. Re: Sorting Variables
                            robrickard

                            I really thought this was going to be simple.

                            I do again apologize for not being clear in my orginal post where the $vars came from in the first place (i didnt think that mattered and what i really wanted was to work with the $vars as that what i have and do not have to rewite anyting). I also apologize that i didnt make clear that i was placing them back in those fields when finished processing.

                             

                            Im still newer to Filemaker and this community. sorry.

                            • 11. Re: Sorting Variables
                              erolst

                              No need to apologize; just try to give all the information necessary, as best as you can tell (imagine seeing your database for the first time; what would you want to know about it?)

                              robrickard wrote:

                              I really thought this was going to be simple.

                              It's not that difficult, if you have a plan …

                               

                              Here's an example for a script-based solution – side by side with using a related table. Take note of the difference in programming effort …

                              • 12. Re: Sorting Variables
                                robrickard

                                Beautiful.

                                Makes what i was doing look like a monkey was coding.

                                Thank you for the amazing example. WOW - so many answers in there. I now see the best practice for using a related table (LIST). I had my nose in a book and searching the web for hours looking for better examples and here it is.

                                 

                                Thank you, erolst! - i owe you a drink.

                                • 13. Re: Sorting Variables
                                  erolst

                                  robrickard wrote:

                                  I now see the best practice for using a related table (LIST). I had my nose in a book and searching the web for hours looking for better examples and here it is.

                                  Thank you, erolst! - i owe you a drink.

                                  Hi Rob,

                                   

                                  I'm more a coffee person, but I appreciate the thought.

                                   

                                  Just so we're clear: the coding part was to show that you can even use a sub-optimal structure to get at results (and coding is fun!), and that you don't necessarily need a sort CF; but the effort required would be better spent building a sound structure, instead of patching the holes. So don't do this alone at home!

                                   

                                  The other thing: the best practice for using a related table is knowing when you need one, then create and use it. FM is a relational database, so you better make use of its fortes (or get Excel for life, w/o parole!).

                                   

                                  To underline the difference in the two approaches: If the day comes when you need to track value #7, and you're using fields, you have to create a new value field, and check/maybe modify the scripts that use these fields.

                                  When using related records, you simply create a new value record by putting in the value, and if necessary, show one more portal row. Scripts that aggregate or otherwise access the related records will simply process one additional value/record.

                                  • 14. Re: Sorting Variables
                                    jrenfrew

                                    >>The other thing: the best practice for using a related table is knowing when you need one, then create and use it.

                                     

                                    The part here is knowing when to use one.

                                    The  use case here is ambiguous still.

                                    Is there semantic value being placed on the display of sorted data, or sorted data - as the two things are quite different?

                                     

                                    The portal makes it APPEAR as if the data is sorted, but has only two options, up and down (well technically three as none is also an FM sort), but the records need something external to bring that apparent meaning when out of that context, as they are saved in creation order.

                                     

                                    To capture and store a sorted list of values on some other record at a known date and time may be the point of the exercise, so that whole data sets may be recovered in the future IN context.