* How to sort results by surname when using Work with Data option?

[Mark1834]

What is currently displayed depends on the Display individuals surname first option so that users can choose how the records themselves are displayed and sorted by given name or surname when clicking the column header.

OK, my mistake - that's what happens when you rush to post to get the dog out for its morning walk before the rain sets in .

Not sure I understand what you mean here though - doesn't that setting just affect how the Records Window displays? It still sorts by surname when you click on the column heading, even if displayed given name first. So isn't the nub of the problem that the Records Window sorts by surname, irrespective of how it displays, and the Tools > Work with Data output can only display and sort by given name first. That's the key inconsistency isn't it?

[tatewise]

We both seem to be guilty of misunderstanding exactly how FH behaves, but agree that FH is inconsistent.

Yes, the Display individuals surname first option only affects (some) displays and not the sort order
The displays affected are the Records Window, Named Lists, Record Select dialogues, etc, but NOT any Result Sets.
They all sort by Surname first regardless of the display option, including Query Result Sets, but NOT Plugin Result Sets or Tools > Work with Data > Where Used Result Sets that sort by given name.

So perhaps the Wish List should be for consistent application of the display and sorting to all Result Sets?

[Mark1834]

Sounds good - I think we were both getting confused by the other person's mistakes, but we've ended up at a point that takes the best elements of both options. All such listings to mirror how the Records Window behaves, with consistent surname-first sorting and a display that reflects the Options setting.

[Little.auk]

You can use the following Excel equation to extract a surname at the end of a string,
=RIGHT(C3,LEN(C3)-FIND(CHAR(134),SUBSTITUTE(C3,"#",CHAR(134),(LEN(C3)-LEN(SUBSTITUTE(C3,"#","")))/LEN("#"))))

In an earlier post I gave the above equation for stripping the Surname in Excel - but there is a simpler way that handles all variants of names including double barrelled surnames like Du Sautoy.

It is much easier to split names into First Names and Surnames with an FH query, than it is to do it in Excel, so ----

First create an FH "ALL NAMES" query with three columns NAME (%INDI.NAME%), FIRST NAME (%INDI.NAME:GIVEN_ALL%) and SURNAME (%INDI.NAME:SURNAME%) and save the results set as a CSV file.

Open this file in Excel and save it as an Excel workbook to create a names data set.

You can then use the Excel LOOKUP() function to find the name in the names data set and retrieve the already stripped Surname and/or First Name.