3 Replies Latest reply on Feb 18, 2013 1:59 PM by philmodjunk

    Sorting data in a calculation field

    DanielWiiki_1

      Title

      Sorting data in a calculation field

      Post

           I am building a DB for my church that needs to have a directory function.  As such, amongst other data, I have a table for "families" and a table for "people". Each family is obviously made up of various individuals. However, getting the data in an appropriate list for a directory listing is proving challenging and I can't find any info on how to sort it appropriately.

           What I want is:  [family name] -- [people:first name], [people:first name], [people:first name], [people:first name] & [people:first name]  

           Smith -- Bill, Mary, Susie, Sammy, Dorah & Diego

           The challenge is getting the first names in the order that I want. I created a calculation field to get the output that I need (see below). This might be an ugly way to do it, and I can't find any way to sort the order of the names. I have a separate field to designate head of household, husband, wife & children. I also have a field for birthdate (although this may or may not be populated).  The calculation seems to automatically alphabetize the list, regardless of the entry order, and I can't figure out a way to insert the head of household first (or alternately husband & wife), and then the remaining children, preferably sorted by age from oldest to youngest.  Any suggestions on how to make this happen?

           FAMILY NAMES LIST (calculation)

            

           Family Name  &  " -- "  

           &  People::First Name 

           & If ( Count ( People::First Name )  =  2 ; " & " & GetNthRecord ( People::First Name ; 2 ) ;"")

           & If ( Count ( People::First Name )  > 2 ; ", "  & GetNthRecord ( People::First Name ; 2 ) ;"")

           & If ( Count ( People::First Name )  =  3 ; " & " & GetNthRecord ( People::First Name ; 3) ;"")

           & If ( Count ( People::First Name ) > 3 ; ", "  & GetNthRecord ( People::First Name ; 3 ) ;"")

           & If ( Count ( People::First Name )  =  4 ; " & " & GetNthRecord ( People::First Name ; 4) ;"")

           & If ( Count ( People::First Name ) > 4 ; ", "  & GetNthRecord ( People::First Name ; 4 ) ;"")

           & If ( Count ( People::First Name )  =  5 ; " & " & GetNthRecord ( People::First Name ; 5) ;"")

           & If ( Count ( People::First Name ) > 5 ; ", "  & GetNthRecord ( People::First Name ; 5 ) ;"")

           & If ( Count ( People::First Name )  =  6 ; " & " & GetNthRecord ( People::First Name ; 6) ;"")

           & If ( Count ( People::First Name ) > 6 ; ", "  & GetNthRecord ( People::First Name ; 6 ) ;"")

           & If ( Count ( People::First Name )  =  7 ; " & " & GetNthRecord ( People::First Name ; 7) ;"")

           & If ( Count ( People::First Name ) > 7 ; ", "  & GetNthRecord ( People::First Name ; 7 ) ;"")

           & If ( Count ( People::First Name )  =  8 ; " & " & GetNthRecord ( People::First Name ; 8) ;"")

           & If ( Count ( People::First Name ) > 8 ; ", "  & GetNthRecord ( People::First Name ; 8 ) ;"")

           & If ( Count ( People::First Name )  =  9 ; " & " & GetNthRecord ( People::First Name ; 9) ;"")

           & If ( Count ( People::First Name ) > 9 ; ", "  & GetNthRecord ( People::First Name ; 9 ) ;"")

           & If ( Count ( People::First Name )  =  10 ; " & " & GetNthRecord ( People::First Name ; 10) ;"")

           & If ( Count ( People::First Name ) > 10 ; ", "  & GetNthRecord ( People::First Name ; 10 ) ;"")

           & If ( Count ( People::First Name )  =  11 ; " & " & GetNthRecord ( People::First Name ; 11) ;"")

           & If ( Count ( People::First Name ) > 11 ; ", "  & GetNthRecord ( People::First Name ; 11 ) ;"")

           & If ( Count ( People::First Name )  =  12 ; " & " & GetNthRecord ( People::First Name ; 12) ;"")

           & If ( Count ( People::First Name ) > 12 ; ", "  & GetNthRecord ( People::First Name ; 12 ) ;"")

           & If ( Count ( People::First Name )  =  13 ; " & " & GetNthRecord ( People::First Name ; 13) ;"")

           & If ( Count ( People::First Name ) >13 ; ", "  & GetNthRecord ( People::First Name ; 13 ) ;"")

        • 1. Re: Sorting data in a calculation field
          philmodjunk

               The calculation seems to automatically alphabetize the list, regardless of the entry order,

               Hmmm, sounds like you have specified an ascending sort order on the name field in the relationship between family and people.

               If you can set up a calculation field that uses the fields for head of house hold etc to assign a number, you can change this sort order to sort by this field instead of the name. If you need the alphabetical order in the relationship for other places in your database, you may need to add a new related table occurrence of people so that you can specify this order. (Or you may want to use the original sort order as a setting in a portal instead of the relationship.)

               Here's a possible exmple:

               Define cSortKey as:

               Case ( Not IsEmpty ( HeadOfHouseHold ) ; 1000 ;
                           Satus = "Husband" or Status = "Wife" ; 500 ;
                           Age )

               Be sure to select Number as the return type.

               Then you can specify cSortKey for a descending sort to list a head of household first, followed by husband or wife and all other in descending order by age.

               And to get your comma'd list, try this expression:

               Let ( [TheList = List ( People::First Name ) ;
                         CommaList = Substitute ( LeftValues ( TheList ; ValueCount ( TheList ) - 1 ) ; ¶ ; ", " ) & "& " & GetValue ( TheList ; valueCount ( TheList ) )
                       ];
                       Family Name  &  " -- " & CommaList
                     )

          • 2. Re: Sorting data in a calculation field
            DanielWiiki_1

                 Thank you -- this was a tremendous help. I've got it all working. I've got to think through the string in the comma's list -- it is working, although I'm getting the name after the '&' when there is only one family member, so I'll have to think through that to get it to leave it out. Tomorrow when I have a mind to think again :-)  Thank you!

                  

            • 3. Re: Sorting data in a calculation field
              philmodjunk

                   Let ( [TheList = List ( People::First Name ) ;
                             CommaList = If ( ValueCount ( TheList ) > 1 ;
                                                        Substitute ( LeftValues ( TheList ; ValueCount ( TheList ) - 1 ) ; ¶ ; ", " ) & "& " & GetValue ( TheList ; valueCount ( TheList ) )
                                                       TheList )
                           ];
                           Family Name  &  " -- " & CommaList
                         )