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
5

Hi @frederikdc ! Could you please help me create a similar formula, but now generating (extracting) the first name from my “Name” field, which is a simple text field that usually contains two words (first name and surname), sometimes three (first name, middle name and surname)?
Thank you!

6

Hi, of course.

left(field('Name'),search(field('Name'),' '))

This will return all the characters from the field Name that occur before the first space.

Regards
Frederik

7

Thank, it work.
But I see one difference ifcomparing to this formula (which extracts Surname from the Name field): right(field('Name'),length(field('Name')) - search(field('Name'),' '))

If I click the field where the First Name is generated by formula and press CTRL+C and later I do paste, teher are quotes added before and after the copied content. If I do copy/paste the Surname (generated by the formula, see above), it works correctly and no quotes are added.

Would you know where is a problem?

8

Hi,

I am not able to simulate the problem. What is the type of the field you are trying to paste the value?

9

Try simple pasting the copied value from that new formula field to let’s say Notepad or anywhere. It always results in: "Firstname "
So quotes added from left and right and space added before right quote.

Formula field is fetching the content from my Name field, which is the first field in my table and is Single line text.

10

ok, now I understand.

This is because the final character of the firstname includes the space. Removing this last character with - 1 solves this.

left(field('name'),search(field('name'),' ') - 1)
11

@marcus The latest baserow version 1.20 has a function split_part which solves your problem in a more readable way

Syntax

split_part(text, delimiter, position)

Example for your case

split_part('John Smith', ' ', 1)

will return the firstname

12

wow, fantastic! tried, works like charm!

Btw. another, more complicated scenario:
If I have some names just First and Surname, some other Firstname and Middlename and Surname.

Then, some of both types have also (but not all!) a university degree after the name, separated by comma AND space, some of them have even two titles f.e.: “Peter Smith, MBA” or “Dan A. Foster, Ph.D., FEBO”

How will I remove the comma? Now it is interpreted by the formula in such a way, that the limiter is space, so the comma is supposed to be a part of the surname. And also, I would like to create another formula field (or two fields, if more titles are present), which would recognize the comma and used the text after this comma (removing a space first) and until next comma (which would be a limiter).
Hope that I explained it clearly.

13

Yes, you can remove punctuation with the regex_replace() function.

regex_replace(field('address'),'[,.]',' ')

This removes all dots and comma’s from the field address and replaces it with a space

14

Great. Need to figure out, how to create the whole formula so it will work as I need.

15

Please, is there any guide, how to build the correct formula structure (syntax)? Would like to learn to work with it.
Right now I just don’t understand, where and in which way should I use the regex_replace function.
Thanks.

16

Hi,

You can check the baserow guidelines about formula’s.

My advice is to always start simple and work from the inside to the outside. In your case:

  1. start with replacing dots and comma’s and check the result
regex_replace(field('address'),'[,.]',' ')
  1. wrap your result with the split_part function and check the result
split_part(regex_replace(field('address'),'[,.]',' '), ' ', 1)