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

Content aside

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