Would it be possible to sort the Name field by surname (last name)?

Baserow Help
#1

Hi,
in my database (table) I have the field Name (type is Single line text), where every row of my table contains the full name and format like this:

John Norton
Evelyn Baker
Peter Smith
…

So it is Name and Surname. Sometimes (rarely) it may be also like this (with middle name):

John William Norton
Evelyn C. Baker
…

If I want to sort it by Surname, itš not possible, it is always sorted by first name.
Is there any workaroud how can I achieve the sorting by surname (which means second, or better last word - if the middle name is present - from the field)?

I can even imagine that there could be a small update of Baserow product where an ew type of field (it could be called Name type) is introduced and there could be an additional “Sort by last word” checkbox when editing this type of field.

#2

Hi Marcus,

This is possible with the following formula: right(field('Name'),length(field('Name')) - search(field('Name'),' '))

The formula returns the right part of the text after the first space in the field ‘Name’. For example:

  • John Norton → Norton
  • Evelyn Baker → Baker
  • Peter Smith → Smith

If you have a middle name, you need to add an extra field with the same formula. Except that you now refer to your previous formula field. This will repeat the same operation and search for a new space in the text: right(field('Formula'),length(field('Formula')) - search(field('Formula'),' '))

image
image1338×408 58 KB

You can now hide the field Formula and sort on the field Formula2

Regards
Frederik

#3

Hi Frederik, thanks a lot for the solution! I will try it.
That means if I have the name mixed (some with while others without middle name), then I should use the Formula 2 method (field) to sort my table, but I also need have the Formula field set-up, even that it can be hidden, right. without Formula field it would not work (sort) correctly. Am I correct?

#4

Correct, you always sort the table on the field Formula2. But you need to have the field Formula because the result of this field is used to calculate the final result in Formula2.

I did not rename the fields to set up a small demo, but I suggest you rename the fields in your table:

  • Intermediate result instead of Formula
  • Surname instead of Formula2
1 Like