* Sorting a Query by Ahnentafel Number.

[Little.auk]

I have a custom query for all Individuals where one of the fields is the Ahnentafel Number. This query is set to sort records in ascending order by Ahnentafel Number. The expression I am using is "=AhnentafelNumber(["Root"],%INDI%,1)", with sort set to ascending.

This works (almost!), Ahnentafel Numbers are assigned correctly and are sorted in ascending order, but there is one major problem - they appear at the bottom of the list, below all those with no Ahnentafel number!

How can I modify this to ensure that blank records are forced to the bottom of the listing.

NOTE - If I export this query result to a text file and open it in EXCEL the Excel sort works as I would expect - numbers at the top and blanks below.

Peter Rollin

[tatewise]

There are several strategies that work:

Eliminate the rows that have no Ahnentafel number with Rows tab filter:
Exclude if =AhnentafelNumber(["Root"],%INDI%,1) is null

Change the Column Expression to the following so that those without an Ahnenetafel number are assigned 999:
=NumberIf(AhnentafelNumber(["Root"],%INDI%,1) > 0,AhnentafelNumber(["Root"],%INDI%,1),999)

Combine that with your original =AhnentafelNumber(["Root"],%INDI%,1) column:
=AhnentafelNumber(["Root"],%INDI%,1) with Sort: None Type: Normal
=NumberIf(AhnentafelNumber(["Root"],%INDI%,1) > 0,AhnentafelNumber(["Root"],%INDI%,1),999) Sort: Ascending Type: Hidden

The 1st Column is your original Column but unsorted.
The 2nd Column determines the sort order and is a hidden column.

[Gowermick]

As only people who have an Ahnentafel number, are the direct Ancestors of your Root, another way to achieve what you want is to only include direct ancestors of the Root person in your Resultset!

To achieve this, you need two Row filters
1. On the General Tab, select Condition Add if Expression =IsSameItem(,FileRoot()) Operator is true
2. On the Relations Tab, use condition Add if, Relationship Ancestor, Of Anyone in the current result Set

This will restrict results to just the Root person and their Direct Ancestors.

[Little.auk]

Hi Guys,

Thanks for your responses.

I am already using the filter option in another "Direct Line Only" query, but, for this particular query, I need to show both direct line and non-direct line people.

I had thought of using Numberif to fill blanks with a large number like 999, (which would be an acceptable workaround for this particular problem), but this sorting issue applies to sorts based on any column with blank (empty) fields, a "Sort Ascending" request results in the empty fields appearing at the top.

This is not what I would expect from any database or spreadsheet SORT function, the accepted SORT protocol is that "blank" or "null" fields are ignored when sorting, and always appear at the bottom of the sorted list, regardless of whether the sort is ascending or descending.

I have exported the results of this query to a CSV file, and imported it into Excel. Both ascending and descending sorts behave exactly as I would expect - cells with data populated first and blank cells always at the bottom!

This infers that there is a flaw in the SORT Algorithm in Family Historian. I find it difficult to believe that no-one has raised this issue before,

Peter

[AdrianBruce]

... I find it difficult to believe that no-one has raised this issue before ...

If I'd found it, I'd have thought that was exactly where a space character would be sorted, based on its position in the collating sequence.

I can see that the suggested protocol possibly makes sense, I'm just trying to explain why I'd never have noticed the issue.

[tatewise]

Adrian, it is NOT a space character that is being discussed but a blank entry with no characters at all, which in Excel always appear last for both ascending and descending sorts.

[AdrianBruce]

... it is NOT a space character that is being discussed but a blank entry with no characters at all ...

Err, oh, OK.

I guess I still read "blank" as synonymous with "space". Anyway, aside from my mainframe mind's difficulties with null fields, I would suggest that my own point still stands - since a blank / null / empty entry in a column looks remarkably similar to a space, I for one wouldn't see anything wrong with the output, hence wouldn't raise the issue.

[Little.auk]

Rather than think "Blank" think "Empty" - an empty field contains nothing, a field with a space will contain the Character Code "32" for a space. They might display the same on screen, but they are definitely not the same in computer code.

I have worked with computers since my university days (mid 1960's), and have never come across this sort algorithm in a database or spreadsheet. Empty cells contain no data, so are pushed to the bottom of the list if they are the first, or only sort element, because the cells with data are the ones people are most interested in. I have never had to filter out empty fields to get a useable sort.

If you are doing a multi element sort then relegating empty records to the bottom presents a much neater, and more user friendly output