AnsweredAssumed Answered

Sorting data in a calculation field

Question asked by DanielWiiki_1 on Feb 15, 2013
Latest reply on Feb 18, 2013 by philmodjunk

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 ) ;"")

Outcomes