1 2 Previous Next 18 Replies Latest reply on Mar 2, 2012 4:09 AM by comment

    multiple sorting via script


      I have the following issue.


      I have a reporting layout that users can choose a 'date range', 'status' and 'sort by1' 'sort by 2'. Once they have selected they hit "process report" and it send them to the correct layout and performs a find based on their selections. the script sorts based on what they entered into the "sort field". it could be "state" , "City", etc.

      The layout has subsummary parts for every option the user can choose. My question is how do I perform 2 sorts in a script so that two subsummarys show up.

      For instance, ::sort1 = city and ::sort2 = salesperson, indicating the user wants to sort the cities by salesperson.

      Is there a way to do this in a script without having to write an "If" statement for every combination of sort data that could be entered by the user?



        • 1. Re: multiple sorting via script

          Hello, brushfire.


          Yes, this can be done. Here's what you do:


          1) Create a pair of calculation fields in the targeted table called "SortBy1" and "SortBy2" (or whatever). The calculation will look something like this:


          SortBy1 = Case ( sort1 = "city" ; city ; sort1 = "state" ; state ; ... [etc] )


          SortBy2 is similar.


          2) In your report script, simply sort by SortBy1 and SortBy2, which will contain the actual field values of the fields the user has selected.


          A bit of a cavet: This method CAN result in a slowdown, depending on how many records you have, because FileMaker will need to process the unstored calculation (parsing out the value of the global field you use for the "sort field"). Test before exposing your users to it.   





          • 2. Re: multiple sorting via script

            Gack. I just realized a much easier way to do this.


            Try this:


            SortBy1 = Evaluate ( sort1 )





            • 3. Re: multiple sorting via script

              Speed is not the only issue here; there is also data type to consider.

              • 4. Re: multiple sorting via script

                Yes, that's true. You may need to incorporate GetAsDate, GetAsNumber, etc., as necessary.

                • 5. Re: multiple sorting via script

                  You cannot "incorporate GetAsDate, GetAsNumber, etc., as necessary" when all you have is one calculation field with a preset type (presumably Text) for its result.

                  • 6. Re: multiple sorting via script

                    True, Michael. The problem presented dealt only with text fields, so I really hadn't thought it through that thoroughly. I'm really quite busy at the moment.


                    It might be helpful if you would actually suggest a solution instead of just throwing stones at what other people are doing.



                    • 7. Re: multiple sorting via script

                      Mike_Mitchell wrote:


                      It might be helpful if you would actually suggest a solution instead of just throwing stones at what other people are doing.


                      Do I detect a hint of criticism here?


                      Let me ask you a question: suppose I don't think there is a good solution to the problem. Or that the solution is so complex it would take 3 pages to explain, and 30 seconds to sort a couple of hundred records. What then?


                      Should I just shut up and not point out that the problem is more difficult than you made it out to be? Would that be considered more helpful to the person who asked the question? Or to the next person who comes looking for the answer to the same or similar question?

                      • 8. Re: multiple sorting via script

                        Perhaps if you think the suggested solution isn't adequate for the presented problem, explain why. As in, there was nothing suggested about data typing.


                        Or perhaps you might explain that, in the targeted table, the person might create a field that could be stored that would sort correctly - such as, for a date field, Year ( Date ) & Right ( "0" & Month ( Date ) ; 2 ) & Right ( "0" & Day ( Date ) ; 2 ), stored as Text, then point the calculation at that instead?


                        Or perhaps you could ask the individual who posed the question if he has any fields of type other than text to see if he actually might have this issue?


                        Or, as you suggest, you might just remain silent. Contrary to popular belief, it's not always necessary to insert oneself into someone else's discussion - especially when you've not contributed anything constructive.


                        Lots of possibilities here, Michael. All of them preferable to throwing out a verbal bomb. Or two verbal bombs in a row.



                        • 9. Re: multiple sorting via script

                          My goodness, Mike.  There has been nothing wrong in Michael's approach ... on the contrary, it is refreshing.  And you just keep sending the zingers ... wow.

                          • 10. Re: multiple sorting via script

                            Perhaps I'm missing something. Which part is "refreshing"? The part where he told a newbie that there was "data type to consider" without explanation? Or the part where he (correctly) pointed out that the data typing functions won't work when resolving inside a Text field without explanation of what would work?


                            Michael has proven to be quite intelligent, and clearly is very experienced with FileMaker. However, I find this little habit he has of just throwing out what's wrong with someone else's solution without explanation to be really annoying, not refreshing.


                            But perhaps it's useful to you.



                            • 11. Re: multiple sorting via script

                              We each have different styles.  Who says your way is better?  Maybe it is good to let the OP ask - we never know whether someone is experienced or brand new on here.  But regardless if someone suggested stand on your head, your tone and belittling comments to him is very unpleasant ... and that is the OPPOSITE of refreshing to ME.


                              and now if you don't mind, I would like to excuse myself from your hostility.  I have other things to do, thank you.

                              • 12. Re: multiple sorting via script

                                Well, LaRetta, it's like this ...


                                You find my manner belittling and unpleasant. I find Michael's to be belittling and unpleasant. As you say, who says your way - or mine, or Michael's - is better? After all, if you think it's appropriate to dress me down for the way I speak to him, then why is it inappropriate for me to dress him down for the way he speaks to me? (Especially when, after all, you weren't even involved in the discussion in the first place ... )


                                I'm sorry I offended you; no offense was intended, either to you or, truthfully, to him. I'm just pointing out that, IMHO, posting that someone has done something wrong without giving an offer of help on how to do it right is really not that helpful.





                                • 13. Re: multiple sorting via script



                                  i truly appreciate all the impassioned help. Its good to know how far people will go to help others. i am sure that your suggestions are awesome, I will try them out this weekend. I'll let you know how it goes.


                                  God bless

                                  • 14. Re: multiple sorting via script

                                    You can point these things out without insulting and calling names and dropping to a personal, name-calling level - that is the line you crossed.  You would be reprimanded on most forums for it.  All I know is that I certainly won't risk ever speaking up on a post of yours ... it is not worth the risk that it will set you off.


                                    And that is real shame because only if we are willing to speak up with OUR ideas (whether others approve or even understand them), will we get better at this business.


                                    You could even private message him and discuss it if you felt offended.  And to tell me basically that is none of my business getting on this thread is incorrect ... we ALL are affected by these things.  You just ruined my night.

                                    1 2 Previous Next