1

Sort Table by Surname in another table / show Titles as text

Hey Team,

I have various tables within my database based on tblPeople.  I populate a dynamic field in each of the connected tables called "FullName" based on various fields in tblPeople.  Looks like this:

Now I have a column in my "connected" table  called FullName but I don't want the records sorted by the first name or Title but by the Surname (from tblPeople that is not a field in the connected table).  

  • Any chance there is an option for this somewhere?

I also have a second problem with the field "FullName" a calculated field made up of
"Title" / "Forename" / "Middle Name" / "Prefix" / "Surname".  Looks like this:

if 'Middle Name' = null then
    Title + " " + Forename + " " + lower(RoyalFix) + " " + Surname
else
    Title + " " + Forename + " " + substr('Middle Name', 0, 1) + ". " + lower(RoyalFix) + " " + Surname
end

As you can see from the above example it works with one exception.  If someone has a Professional Title (e.g. Dr., Prof., Rt. Hon., ect) it populates as a number.  

  • If I change the script to "text(Title)" then I get the titles as text but then everywhere else (where there are no titles) I get a funny error message.

Thanks for your help,
Richard (Lost in Translation)

3 replies

null
    • Fred
    • 1 yr ago
    • Reported - view
    Richard v. L. said:
    Now I have a column in my "connected" table  called FullName but I don't want the records sorted by the first name or Title but by the Surname (from tblPeople that is not a field in the connected table).

     If you are sorting a column in a table then you have to create another column that just has Surname so you can sort by it.

     

    Richard v. L. said:
    If I change the script to "text(Title)" then I get the titles as text but then everywhere else (where there are no titles) I get a funny error message.

     What is the "funny error message"?

      • Struggling Student of Conference Interpreting
      • Rvl
      • 1 yr ago
      • Reported - view

      Fred i really did not want to trouble you with this as well.  you've already been a life saver on my other major problem.  hier is what the column looks like with "text(title)"

      I wort of figured I had to have the "surname" column to sort the table appropriately, but I really don't want to see it.  Anyway I can sort the table by a column that is not visible?

      • Struggling Student of Conference Interpreting
      • Rvl
      • 1 yr ago
      • Reported - view

      Fred  Jacques TUR  Thanks you two.  Jacques has helped me to realise that every empty "case" has to be addressed individually so that the end formula becomes quite long but nonetheless straight forward.  

      if Title = null then "" else text(Title) + " " end +
if Forename = null then "" else Forename + " " end +
if 'Middle Name' = null then "" else substr('Middle Name', 0, 1) + ". " end +
if RoyalFix = null then "" else RoyalFix + " " end +
if Surname = null then "" else Surname end

      That way, come what may, the formula is solid and delivers the proper result every time.  NB:  The Field "title" gets pulled in from a set list (tbl) so that the added "text()" was needed. 

Login to reply

Content aside

  • Status Answered
  • 1 Likes
  • 1 yr agoLast active
  • 3Replies
  • 82Views
  • 2 Following