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

[woodr2011]

I would like to sort the results of the following search by surname first (rather than by given name):

Tools > Work With Data > (Places or Addresses)>Where Used [search for cemetery]
--sort results by surname first

Many thanks in advance!

[tatewise]

That is not possible within FH. There are two possible workarounds.

1) Select the Individual records in column one, or simply press Ctrl+A to select all cells.
Then use cog Query Menu > Add Selected Cell Records to Named List... and choose a new list or replace current contents.
Named List sorts entries by surname if Tools > Preferences > Records Window > Display individuals surname first is ticked.

2) Save the Result Set as a CSV file and use a spreadsheet to reorganise the data so it sorts by surname, but I'm not sure how to achieve that.

[ColeValleyGirl]

2) Save the Result Set as a CSV file and use a spreadsheet to reorganise the data so it sorts by surname, but I'm not sure how to achieve that.

Format the data as a table; that will make it sortable and filterable on any column.

[tatewise]

The saved CSV file will have the full name (Forenames SURNAME) in one column of cells. So I was unsure how to separate the Forenames from the SURNAME so that they are in different columns to allow sorting by SURNAME.

[Mark1834]

Not that easy - names are output as single strings with no delimiter between given and surnames.

You'd have to do it as a two-step process. Import into your spreadsheet of choice, then further split the name into separate columns for each element in the name (which will be variable in number, including compound surnames).

It's not that difficult if you are familiar with manipulating string data in a spreadsheet, but it's tedious.

That's a disadvantage of the FH data model. It would be trivial to do if it were a proper relational database.

[Mark1834]

Snap - we must have sat at the desk at about the same time! The easy but tedious way is the one I described. The more elegant way would be a VBA macro that splits the string according to upper case and mixed case elements, but that is more advanced and most casual Excel users wouldn't have the experience to set it up. The advantage of an Excel macro would be that it is reusable, rather than having to repeat the manual manipulation each time.

[tatewise]

Ideally, those Result Sets should honour the Tools > Preferences > Records Window > Display individuals surname first option, as in the Records Window, Named Lists, Select Individual Record dialogue, etc. Then they could have the surname first for sorting.

[Mark1834]

If anybody wants to use it, this is a simple Excel user-defined function that splits a name with an UPPER CASE surname in a comma-separated given name(s) and surname.

Code: Select all

Public Function SplitName(FullName As String)

' Converts a name with surname in UPPER CASE to Given Name(s),SURNAME

Dim Given As String, Surname As String

NameArray = Split(FullName, " ")

Given = ""
Surname = ""

For Each Name In NameArray
    If Name = UCase(Name) Then
        Surname = Surname + Name + " "
    Else
        Given = Given + Name + " "
    End If
Next

SplitName = RTrim(Given) + "," + RTrim(Surname)

End Function

It handles any sequence and number of given names and surname.

[ColeValleyGirl]

How does your macro deal with family records, Mark?

[Mark1834]

It doesn't, but could be extended to do so if required.

[ColeValleyGirl]

Probably not worth it, unless encountering a family record actually breaks it... And of course it introduces the complication of between zero and two surnames per row.

[ColeValleyGirl]

Another approach to solving the original problem would be to use a query which would then give total control over the columns and sorting.

[Little.auk]

You can use the following Excel equation to extract a surname at the end of a string, assuming that the name is in cell C3.
To make it clearer I have inserted # - replace this with space.

=RIGHT(C3,LEN(C3)-FIND(CHAR(134),SUBSTITUTE(C3,"#",CHAR(134),(LEN(C3)-LEN(SUBSTITUTE(C3,"#","")))/LEN("#"))))


the FIND and SUBSTITUTE part of the equation searches the string in cell C3 for the first instance of the character # and replaces it with CHAR(134), until it gets to the last instance of #. It then returns the position of the last #.

For example using my name Peter John ROLLIN, the last space is position 11. The total length is 17.

It is just a case of adding the RIGHT() statement to return the right hand string of LEN(C3) [17] minus the FIND value [11], which returns the six characters ROLLIN.

NOTE - This will only work for single name surnames.

[tatewise]

I originally thought about a Query, but not sure how the 'Where Used by Place or Address' features can be implemented.
Presumably, it could be a Fact Query and limited to only finding usage by Individuals (and Families) in facts.
However, choosing the Place or Address to focus on is much more clumsy than via Tools > Work with Data.

[ColeValleyGirl]

However, choosing the Place or Address to focus on is much more clumsy than via Tools > Work with Data.

True.

I don't think anyone has suggested viewing the Places record list, selecting the place of interest and using View > Record Links. This allows sorting by surname, but doesn't provide as much context. The Where Used Records Link plugin provides more context but doesn't support the required sorting. Neither technique works with Addresses.

[Little.auk]

I don't think anyone has suggested viewing the Places record list, selecting the place of interest and using View > Record Links. This allows sorting by surname, but doesn't provide as much context. The Where Used Records Link plugin provides more context but doesn't support the required sorting. Neither technique works with Addresses.

I was about to do that after I had posted about the Excel equation - but I was going to post an example which WAS with Addresses. Find is not as reliable with Addresses, but the Where Used and Record Links work the same as for places on my data.

I originally thought about a Query,

That was going to be my next suggestion.

A suitably sorted, Fact based query, with an "Exclude Unless Contains" Parameter Row filter would work.

The filter works much better in a query than in the Work with Data option.

However, the query approach would be a bit more complicated for marriage Places and Addresses, where two people are involved.

[cwhermann]

Not sure if this would make it easier, but there is a built in function within excel to split comma separated values into rows or columns.
I have used it often in mail merges to tale a cell that contains, for example Surname, Given and create a cell with the Surname in one column and the Given in another column. I have also used it to separate out mail address into separate columns. The function also allows the user to define the delimiter such as a space. One can then sort by any one column.
Here is a link to instructions.
https://www.extendoffice.com/documents/ ... alues.html

I also see the site provides some VBA code. (I have no idea what that is other than Mark mentioned it in one of his posts )

[Mark1834]

See it as MS Office’s equivalent to FH plugins - I used to use it a lot in my FTM days for creating formatted reports from a GEDCOM export. It’s still useful for fine-tuning stuff that FH doesn’t quite get right (e.g. a Word macro that tidies up formatting of an RTF report save).

It seems to me that most of this hoop jumping could be avoided if CP improved the original output to give the option of splitting given and surnames. Worth adding to the wish list?

[davidf]

It seems to me that most of this hoop jumping could be avoided if CP improved the original output to give the option of splitting given and surnames. Worth adding to the wish list?

You may find this thread of interest. I am trying to formulate a wish list request at the moment (the actual input is relatively easy - give access to the GIVN SPFX and SURN GEDCOM fields - but the actual processing to keep some form of alignment with the NAME field (i.e. the "Given Names/Surnames/" structure) with different surname prefixes depending on language is proving more problematic).
Surname prefix (SPFX) -- more generally, handling structured names. (20719)

[tatewise]

It seems to me that most of this hoop jumping could be avoided if CP improved the original output to give the option of splitting given and surnames. Worth adding to the wish list?

Or those Result Sets honoured the Tools > Preferences > Records Window > Display individuals surname first option, as in the Records Window, Named Lists, Select Individual Record dialogue, etc. Worth adding to the Wish List?
Actually, all Results Sets could honour that option.

[woodr2011]

It seems to me that most of this hoop jumping could be avoided if CP improved the original output to give the option of splitting given and surnames. Worth adding to the wish list?

Or those Result Sets honoured the Tools > Preferences > Records Window > Display individuals surname first option, as in the Records Window, Named Lists, Select Individual Record dialogue, etc. Worth adding to the Wish List?
Actually, all Results Sets could honour that option.

That would be very worthy of being added to the Wish List for sure!

[Mark1834]

A better option would be to request that any full name display in the Query Window sorts in the same way that the Records Window does - alphabetically by surname, then given name. That would save an awful lot of further hoop-jumping for plugins and queries in general.

[tatewise]

Mark, I don't see how that differs from my proposal for all Results Sets to honour the Display individuals surname first option. Since that is the option that governs the Records Window display of Individual names.
I'm not sure it should go as far as actually sorting the names. The Records Window does not necessarily sort the names.

[Mark1834]

I think you've got the wrong end of the stick, but I'm probably guilty of being a little over-ambitious in what I was hoping to achieve.

Let's bring it back to the specifics of the original question. The issue is that the display sorts by the text representation of the record (the name for individuals and the two names for families). Your suggestion of displaying names surname first is a sticking plaster, but a step forward as it gives a more useful sort (should related plugin functions such as fhGetDisplayText(...) do the same?).

I was hoping for something more fundamental, where the display is the record itself rather than its text representation. It would then sort in a logical alphabetical order when the heading is clicked, irrespective of how the preferences parameter is set. That may or may not be more complex to implement when you have mixed Record types (e.g. Individuals and Families) but we are all just users and our role is to ask for what we think would be worthwhile and at least credible. CP can judge on how practical it is and how it fits with all their other jobs.

[tatewise]

I was hoping for something more fundamental, where the display is the record itself rather than its text representation. It would then sort in a logical alphabetical order when the heading is clicked, irrespective of how the preferences parameter is set.

Sorry, I still don't understand what you mean by "the display is the record itself rather than its text representation".
The data reference involved is the record itself, i.e. %INDI%, %FAM%, %SOUR%, etc, and not %INDI.NAME%, etc.
So clicking on the display allows the record Property Box to be opened.
What would be displayed if not a text representation?
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.
The default is Display individuals surname first enabled, i.e. how to display the record itself, which is what most users appear to be happy with in the Records Window, Named Lists, Record Select dialogues, etc.
The 'snag' is that the option is NOT applied to everywhere Individual and Family records are listed.
That is why there is a convoluted workaround in An Overview of Queries under Sorting a Fact Query on Owner Surname which my/our proposal would eliminate.
However, the Result Sets the OP identifies cannot use that workaround because they are automatically generated.