1 2 Previous Next 18 Replies Latest reply on Sep 19, 2011 11:01 AM by philmodjunk

    Sort calculated field


      Sort calculated field



      I have 4 fields in my List view of my 'Enquiries' table. Company, First name, last name and sort name.

      The sort name is a calculation to show the company (if applicable) or else show surrname, First name. The field actually visible on the list view are sort name, creation date and company. I want to create a button that will sort the list alphabetically by looking at the 'sirt name' field. But at the moment is doesnt really work - I just want the sort to look at the first latter of the field and sort using that.

      I think I know how to do this, just unsure of the script to get it working!

      I am also getting a little confused with all this chat about variables and global fields. I am trying to grasp the logic behind it and have read a bit but need a bit more help with some examples of how it can come in handy. THe databse I am creating is a simple Contacts, invoices, payments database with automated functions such as mail merges, emails, reports and gant charts....


      Thank you for the help in advance...



        • 1. Re: Sort calculated field

          "that will sort the list alphabetically by looking at the 'sirt name' field. But at the moment is doesnt really work - I just want the sort to look at the first latter of the field and sort using that."

          I am unsure what you mean when you say it doesn't work.  If you sort ascending on the 'sort' field, it WILL be sorted completely ascending by first letter, and then by second and third ...

          Can you explain more about the issue?

          • 2. Re: Sort calculated field

             I'll let someone else comment on the sort.

            Variables: If I want to create a compilation field from multiple records, I can grab what I want from the first record ($var1) and the second ($var2) then stuff the result into somewhere else { $var1 & "  " & $var2 & "  " & "days late." }

            There are other ways to do it, this is just one.

            If I want to copy and paste a value from one field to another without touching the 'clipboard', I can SetVariable in place of copy, then SetField in place of paste...no clipboard involved.


            Global fields: If I want to look at currency conversion, the conversion multiplier {rate of exchange} is the same throughout the Dbase.  Set the global field and you can use it wherever you want regardless of relationships or lack thereof.

            Global fields are also live in Find Mode, so if I want to search between two date ranges I can put a start date (global) and an end date (global) on the layout, type dates into them and do my find via script (  SetField [TransactionDate ; gStart & "..." & EndDate]  ) very easily.  Without globals here, you would have to trap the entered values into Variables first, then Setfield using the variable (see point #1 ;)  )

            In previous versions of FMP, and still handy now, globals can be used as filtering mechanisms for relationships (I only want to see "active" invoices...my relationship is ID# AND Status = gActive and so only "active" ones are related...

            Hope this helps in some way.

            • 3. Re: Sort calculated field

              Hi LaRetta

              Firtly, I apologise for my appalling spelling! I was in a coffee shop and couldnt concentrate properly - now back home finally!


              What I meant was that the 'sort field' is a calculation field. The result is either the company or the name of the person, with the surname first. So the field will either have "Smith, Peter" or (if the company field is filled in), "Apple". But It doenst seem to have sorted to the first letter as you suggest it should. And also, if a double barrelled name is the surname, then it sorts from the second part of the name, and not the first.

              The incorrect sort that I mentioned first (sort name - company or surname) seems to be almost random. It doesnt do one and then the other or anything that makes any sense - I cannot see what criteria it is following. The sort is set to 'sort order' and then descending (ascending). This is why I thought the sort may be looking at formula characters as opposed to calulated result.

              The formula I have for the sort name is:

              If ( Company > 0;Company; Surname & ", "  & First Name)

              I hope this is clearer!


              Thank you


              • 4. Re: Sort calculated field

                "If ( Company > 0;Company; Surname & ", "  & First Name)"

                This will only return 'true' if Company field has a number in it.

                You want:

                If ( not IsEmpty ( Company ) ; Company ; Surname & ", " & First Name )

                Also make sure the calculation result is set to text.

                • 5. Re: Sort calculated field

                  Of course - that makes sense! Thank you..

                  • 6. Re: Sort calculated field

                    Hi LaRetta

                    I have adjusted the field calculationbut this has made no difference. The field will still not sort by the first letter.

                    I cannot think why. I have tried everything!


                    I have also got a rolodex style system working where user clicks on a letter to display relevent records. I want to add an option to this to show inactive records from the found set. How do I do this? Ihave got the filter working from a dialogue box asking to show all, inactive active records. But on clicking sn option it filters the whole dtabase and not from the relevent letter that was selected previously. This is second really to the original problem of sorting by company and surnames. Ask any questions thatuyou may need answers to to help me sort this out. Thank you very much for your time!

                    • 7. Re: Sort calculated field

                      First issue:  Sort - have you made sure the result of the calculation is text?  Also, are you sorting a related table (clarification ... is the calculation in a related table)?  We will need to see your file otherwise.  YOu can upload it and provide a link to it here.

                      Second issue:  Find - if you want to take what a user selects, such as alpha character, and then modify the find to see if User wants to include inactive, then it might be something like this:

                      Enter Find Mode [ pause to accept user  input ]
                      Show Custom Dialog [ "Yes" ; "No" ; "Do you want only Inactive?" ] ... note this takes place after User hits carriage return to activate their find.
                      If [ Get ( LastMessageChoice ) = "Yes" ]
                      Modify Last Find
                      Set Field [ Status ; "Inactive" ]
                      End If
                      Set Error Capture [ On ]
                      Perform Find [ ]
                      If [ Get ( LastError ) ]
                      Show Custom Dialog [ "No Records Found"]
                      ... do whatever if no records found
                      End If

                      Sorry, I should add error trap and give User a way to say yes or no to wanting inactive.  I added portion in blue red.  

                      • 8. Re: Sort calculated field

                        LaRetta - You are a star. I had the result as a number! Doh...

                        Sort now working perfectly..

                        However, still having probs with the inactive field etc. The field has a single check box with value list containing "1". If the cross is put in the check box it thereofre equals "1". So when I omit, i ask FM to look for any character in order to find/omit the record. 

                        Ihave three options to choose from, Active, Inactive and ALL. (buttons 1,2,3).I have used an If Statement to look for these once I have already used a find function to filter the records in either category (customer, supplier, trade etc) or by letter uinsg variables. (a,b,c etc).


                        My find calculation is attached..

                        Thank you for your patience....I agree, if Iget stuck beyond this then I will either think of a workaround/not offer it as a function or last resort, send youthe file to take a peek at!

                        • 9. Re: Sort calculated field

                          Just seen your post - it is rather different to the verison i recieved in my email! Will adapt it to your post's suggestion and see if it works..

                          • 10. Re: Sort calculated field

                            still stuck!

                            • 11. Re: Sort calculated field

                              "have three options to choose from, Active, Inactive and ALL. (buttons 1,2,3).I have used an If Statement to look for these once I have already used a find function to filter the records in either category (customer, supplier, trade etc) or by letter uinsg variables. (a,b,c etc)."

                              Then you can branch and CONSTRAIN the already-found set, testing for the message choice.  It might look something like:

                              If [ Get ( LastMessageChoice ) = 3 ]  // user chooses to constrain to inactive
                              Constrain Found Set [ table::Inactive = 1
                              Else If [ Get ( LastMessageChoice ) = 2 // user chooses active only
                              Constrain Found Set [ table::Status = "Active" // user wants to constrain to active
                              End If

                              ... etc.  So if User selects button 1, ALL will be found meaning your found set won't be further constrained.

                              but really, I can't say for sure without seeing the script.

                              • 12. Re: Sort calculated field

                                BTW, you had asked about globals.  I thought this would be a good place to tell you the truth about them so I found, what I consider to be one of the best posts ever made on the subject.  I would prefer to just point to a link but unfortunately we can't find it right now.  However, I have the webpage stored so I will paste it in its entirety with an apology to CobaltSky ( Ray Cologon, Ph.D.) for the shoddy manner of presenting one of his responses.  I will add a link later to the actual thread if we can find it.  Everyone should print and paste this somewhere handy.

                                ==BEGIN QUOTE =========================================================================

                                Post: Some reasons to use Globals!...   (Topic#151065)  FMForums.com

                                03/09/05 12:01 PM – Post#151065 by CobaltSky

                                10/03/2005, at 4:00 AM, Albert Harum-Alvarez wrote:
                                "You Still Use Globals?"

                                Hi Albert,
                                Yes I still use globals. I say it without shame.

                                I don't use them for the same things (at least not in all cases) that I did prior to the advent of v7, but I find myself using them for *other things* that they weren't so useful for before.

                                Off the cuff, here's a short list of good reasons to keep them in your 'toolkit':

                                1. Because they can be accessed from any table on the graph without a relationship.

                                This alone is reason enough to use globals. Without them, the task to ensure that a single value can be accessed from any layout throughout a solution is daunting - and it can turn the relationships graph into an unintelligible mess.

                                2. Because their value is persistent in find mode.

                                There are any number of reasons why one might wish to have some fields still displaying their contents in find mode, but perhaps ensuring that the company logo does not disappear (and that as a result, you get paid) is one of the most compelling.

                                3. Because their value does not depend on a record being present in the table where they reside - nor does their accessibility from other tables depend on any records being present in those other tables.

                                And that, dear friends, can save you and your users some positively hair raising moments, when a reference (or preference) value temporarily disappears during an update process etc.

                                4. Because their value is specific to the client in a hosted solution.

                                As part of that, a global field does not suffer from record locking in a hosted solution (no trapping for #301 required!) - nor in a multi-window scenario on a single workstation. Writing to it cannot fail, regardless of what other users may be doing (and also regardless of relationship context, as per point 1 above).

                                Whilst there are other ways to ensure that each user will see (and can always update) his/her own values whilst a solution is being hosted, or while multiple windows are displayed, there is none that requires so little overhead, nor which is so seamless and reliable in its operation in this respect, as is the humble global field.

                                5. Because when calcs which reference standard data fields are defined as global, they independently/automatically reference the record on which the referenced value/s were most recently edited on the current client workstation.

                                Think for a few moments about this one. Its applications for returning results to the user dynamically, for enabling the user to retrace his/her steps, indeed as input for a range of audit and control options - are extensive. Yes there are other ways to achieve this feat - but none I've seen that are so instant, so reliable and so simple to implement.


                                Well, you asked for only five reasons, but being generous natured, I'll offer you a sixth. Precisely *because* they're (as you put it) 'weird' within the database world - and so characteristically 'FileMaker'! And which of us actually *wants* FileMaker to be exactly like every other database platform (and would we use it if it was)?!!

                                ==END QUOTE ============================================================================

                                • 13. Re: Sort calculated field

                                  Hi LaRetta, love the post and I've been seeking reasons for using global calcs so that last item is giving me some real food for thought.

                                  just so I can rattle the appropriate cages, what's keeping you from posting a link? I haven't encountered any new issues in that regard today--using firefox on XP...

                                  • 14. Re: Sort calculated field

                                    CobaltSky has not posted on this forum, Phil.  It is from another forum which recently migrated to new software.  I'm sure we'll get the link and plug it in later.

                                    Yeah, item #5 can catch a person if not careful.

                                    1 2 Previous Next