Jump to content
John Stark

Sorting List of People report

Recommended Posts

Can I sort by Birth Group* tag; Date ? It doesn't seem to work.

 

 

I have 6 output columns:

 

1. ID Number

2. NameGroup* tag; SortGiven

3. NameGroup* tag; SortSurname

4. Birth Group* tag; Date

5. Birth Group* tag; Place

6. Birth Group* tag; Coordinates

 

Column/Sort Order

2 = 1

3 = 2

4 = 3

 

The sort on columns 2 and 3 works, but not on column 4.

 

I have tested swapping the sort orders, but the date sort never works.

I have tried variations in the date format using Preferences.

I have also tried directing output to screen and to Excel.

 

I am using TMG UK latest version.

Share this post


Link to post
Share on other sites

Terry and I both tested your report and it sorts correctly for us in every instance. Have no ideas. I'd like to test in a large one-name project but don't have the data to do so. Whether the version is US or UK is not pertinent to the issue.

Share this post


Link to post
Share on other sites
... I'd like to test in a large one-name project but don't have the data to do so.

Looks fine for me for the most common surname in my large project, but even that probably only gave me say 40 of any one forename, surname combination.

Removing forename from the sort and leaving it just as surname, date, it still looked fine for the several pages I checked

Share this post


Link to post
Share on other sites

I have placed a backup of the project on my website. URL = http://www.johnstark.org/Downloads/TMG.

 

I again replicated the problem this morning. The report concerned was List of People - conguration name "Website - Births 1770-1779" using filter "births 1770-1779"

 

Output was to Excel then processed by Excel and Word macros to produce a webpage. The results can be seen in http://www.johnstark.org/fh/hewitson/hewit...-1770-1779.html. An example of the problem is the entries for John Hewison, which are not in the specified date order. (Note that I did not perform any sorting with the macros - output is in the order produced by TMG).

 

Feel free to hang onto the data for future testing if you wish to.

 

 

A screenshot of the report definition is attached.

post-3614-1180096866_thumb.png

Share this post


Link to post
Share on other sites

John,

 

Now I'm even more confused. :( I downloaded your backup and restored it, and ran the report with the same report definition. But when I do (sending it to screen or txt file) the John Hewisons sort correctly:

 

17654 John			 Hewison	   23 Sep 1772	   OKI SCT, Westray And17622 John			 Hewison	   16 Sep 1777	   OKI SCT, Westray And17635 John			 Hewison	   16 Sep 1777	   OKI SCT, Westray And16844 John			 Hewison		c 1778	   Westray, OKI SCT, La17659 John			 Hewison	   28 Jan 1779	   OKI SCT, Westray And

I still have no clue why you get different results. :(

Share this post


Link to post
Share on other sites

I get the same results as Terry. There are 50+ John Hewisons sorted in correct birth date order. Doing an analysis of your project.

 

Terry notes that fresh index files were created when we looked at your project. Please reindex and see if that changes your results. In fact indexing is the only difference.

 

I would open the project folder with Windows Explorer, sort by filetype, and delete all CDX files. Then open your project and, after it indexes, try the report.

 

I see that your project has been optimized but you need to run Validate File Integrity. You will get a very high count of problems fixed but almost all of that is housekeeping. I'm looking at issues beyond what VFI fixes.

Edited by Jim Byram

Share this post


Link to post
Share on other sites

Is it possible the dates are being sorted in alphabetical order instead of chronological order? Could the setting for the date format on a user's computer cause the dates to look correct for one user but not for another?

 

I don't trust the ouput listed since it was processed thru Excel and Word. I'd like to see what the output looks like sent to a text file.

 

Alvin

Share this post


Link to post
Share on other sites
Was any sorting done in Excel? Excel won't sort dates properly if the set includes dates before 1900.

 

 

Nothing only was there sorting done in Excel, but was there any date conversion done by Excel, either intentional or unintentional?

 

Alvin

Share this post


Link to post
Share on other sites
<snip>

 

I see that your project has been optimized but you need to run Validate File Integrity. You will get a very high count of problems fixed but almost all of that is housekeeping. I'm looking at issues beyond what VFI fixes.

 

 

I have followed your advice:

- produced an Excel file. Dates were mis-sorted

- re-inexed, optimised, VFI

- produced another Excel file and compared. Problem persists.

[in both cases, data was not passed through any of my macros (and I have double checked the code to ensure that I don't invoke a sort, just in case)].

- output then directed to screen. Problem persists.

 

 

Although a solution does not seem imminent, may I mention that I appreciate the time and effort you are expending on my behalf!

Share this post


Link to post
Share on other sites

Is your collate sequence in Preferences set to something other than 'General'?

 

File / Preferences / Current Project Options / Other / Collate sequence

Share this post


Link to post
Share on other sites

John,

 

I also get correct sorting results when I run the LOP report with your report definition.

 

After that I did a few experiments and was able to reproduce the incorrect (or better say: unwanted) sorting result you reported. ;)

16844 John Hewison c 1778

17622 John Hewison 16 Sep 1777

17635 John Hewison 16 Sep 1777

17654 John Hewison 23 Sep 1772

17659 John Hewison 28 Jan 1779

 

(Please notice the sequence of ID numbers. That's the key.... they are correctly sorted in ascending order.)

 

To replicate the issue: go to Report > List of... > People

On Report Definition Screen say Filtered Group "births 1770 - 1779"

Click on Options

On the tab Output Columns (Report Options screen) in the Sort Order column for line 1 (ID number) leave TMG's default / standard setting = 1 in place

... then set the sort order for line 2 also to 1 / for line 3 to 2 / for line 4 to 3 (I guess that is what you are doing, right?)

 

now the content of this window looks like this

 

John_Stark_Report_Options.jpg

 

Next click on OK on Report Options screen.

On Report Definition Screen say Print & Save (doesn't matter whether you print to Screen or File) and you end up with the wrong sort order as listed above.

 

To get a correct sorting result you need to set the value in the Sort Order column for line 1 (ID number) to 0 (zero)!!

As long as you don't do that TMG will always sort (i.e. identical Given names / Surnames) according to the ID number and since on the web-site the ID number isn't shown that wasn't obvious immediately in case of "John Hewison's data".

Share this post


Link to post
Share on other sites
Is your collate sequence in Preferences set to something other than 'General'?

 

File / Preferences / Current Project Options / Other / Collate sequence

 

It's set to 'General'

Share this post


Link to post
Share on other sites
John,

 

<snip>

To get a correct sorting result you need to set the value in the Sort Order column for line 1 (ID number) to 0 (zero)!!

As long as you don't do that TMG will always sort (i.e. identical Given names / Surnames) according to the ID number and since on the web-site the ID number isn't shown that wasn't obvious immediately in case of "John Hewison's data".

 

 

I can't set the sort to 0 - I can enter it but it resets to blank when I exit the field. I'm sure you are correct in your observation that the ID is being used. I have experimented with various sort options and will document the reults in a separate post.

 

Hertzlichen Dank.

Share this post


Link to post
Share on other sites
I can't set the sort to 0 - I can enter it but it resets to blank when I exit the field.

Hertzlichen Dank.

 

My fault, John, sorry. :(

 

I should have said: go to the Sort Order for the first line = ID Number and press Delete so that the field is empty.

 

You're welcome - Bitte gerne. :)

Share this post


Link to post
Share on other sites

I have experimented with different sort orders / output formats, and have been able to produce a difference between the screen output and Excel. The attached screenshots of Excel output show 4 different sort orders.

 

Sort 1 is the intended sort order on fields 2, 3, and 4, as previously discussed. Sort errors are highlighted in red.

post-3614-1180266273_thumb.png

 

Sort 2 is the same as the first with the addition of a sort value of 4 in field 1, the ID. Results appear to be identical.

post-3614-1180266393_thumb.png

 

Sort 3 sorts on the date field only. The results suggest that the date field is being treated like a text string instead of a date. There appears to be an implied sort order to field 2, but not field 1.

post-3614-1180266809_thumb.png

 

Sort 4 The implied sort on field 2 can be over-ridden by specifying a secondary sort on field 1

post-3614-1180267004_thumb.png

 

If the output of sort 4 is directed to screen, it appears to be correct

post-3614-1180267462_thumb.png

 

This sort to an ASCII file also appears to fail as per sort 3

post-3614-1180268016_thumb.png

 

Output to Database format also fails in the same way. I can't check output to RTF or Word as I always get a Win16 Subsystem error.

 

I have rechecked screen output for Sort 1.

post-3614-1180269572_thumb.png

This appears to be correct. Although I checked the original screen output many times, I didn't keep screenshots and I'll have to accept the probability that I must have made a mistake when I claimed they were wrong. Somewhat galling if so, and I apologize.

 

But why is TMG failing to perform a calender sort for me when outputting to other formats, and why is it working for others? Some difference in the setup of our machines, perhaps?

Share this post


Link to post
Share on other sites

John,

 

In your Sort 1 example, I don't see those marked as errors. Your specification, as listed in your first message, was to sort first on given name, second on surname, and third on date. In each of the examples you marked in red, the surnames differ, so that over-rides the date order.

 

That detail aside, I do in fact see the sort errors you originally described when I send the report to Excel - most notably for John Hewison - they sort as:

 

c 1778

16 Sep 1777

16 Sep 1777

23 Sep 1772

28 Jan 1779

 

But when output to screen or text file they sort correctly as:

 

23 Sep 1772

16 Sep 1777

16 Sep 1777

c 1778

28 Jan 1779

 

Seems to be a problem with the output to Excel. I'd not looked at output to Excel before as you had said you got the same results regardless of the output.

Share this post


Link to post
Share on other sites
John,

 

In your Sort 1 example, I don't see those marked as errors. Your specification, as listed in your first message, was to sort first on given name, second on surname, and third on date. In each of the examples you marked in red, the surnames differ, so that over-rides the date order.

 

 

Sorry Terry, I didn't make this clear. Each example highlighted in red should have been sorted before the entry above. (I highlighted line 60 in error).

Share this post


Link to post
Share on other sites
To get a correct sorting result you need to set the value in the Sort Order column for line 1 (ID number) to 0 (zero)!!

You never set the sort order in any column to '0'. If you want no sort on a given column and some value is already entered, you simply delete that value.

 

When I tested John's issue, I deleted the default value for column 1 before entering the sort order that he specified for the other columns.

Share this post


Link to post
Share on other sites
You never set the sort order in any column to '0'. If you want no sort on a given column and some value is already entered, you simply delete that value.

 

When I tested John's issue, I deleted the default value for column 1 before entering the sort order that he specified for the other columns.

 

As you probably already noticed I already corrected my fault. :(

Share this post


Link to post
Share on other sites
Sorry Terry, I didn't make this clear. Each example highlighted in red should have been sorted before the entry above. (I highlighted line 60 in error).

My error, John - looks like I focused on line 60 and somehow thought I saw the same issue on other lines. The other errors are valid, as you said. And I get the same result when I output to Excel.

Share this post


Link to post
Share on other sites

It seems, as you described, that the dates are coverted to text before sorting when the output is to Excel, while the sorting seems to occur before the date is converted to text for other outputs. I'm trying to think of work-arounds. One would be to output to Word, since it sorts correctly, but in the Word output the fields are separated by varying numbers of spaces. Cleaning that up in order to get it copied to Excel seems daunting.

 

A better solution might be to use text forumlas in Excel to create a "real" Excel date from the date in text format. You could then re-sort in Excel based on that number. But I recall that John Cardinal says that Excel doesn't deal well with dates before 1900, so that would complicate that approach. :(

 

Maybe you could create some sort of "date number" by using text forumals that would work better than Excel's normal data system.

Share this post


Link to post
Share on other sites

Excel imports pre-1900 dates as text fields. That makes it complicated to sort by a date column in Excel. For datasets that include pre-1900 dates, there are a couple ways to sort in Excel...

  1. Put each date sub-element in its own column: year in column 1, month (as a number) in column 2, day of month in column 3.
  2. Convert the date to an Excel string (before opening in Excel) and use a text format that sorts properly like "yyyy-mm-dd". You can force a string value in Excel using single quotes, I think, so an actual value would be
     
    ' 1878-08-03
     
  3. You can use a formula to create a text column that acts as a sort key, but that takes some technical skill.

You can probably produce #1 directly from TMG, but you'll use up three columns, and if you attempt to sort in Excel, you can only use three columns as the sort key. I don't think you can produce #2 directly from TMG.

 

Dates with modifier words (ex: "before 1870") are complications for both solutions.

 

My memory is hazy on this, but I think MS Access handles pre-1900 dates and so it might be better to use it in place of Excel. Depending on the task at hand, it might be a better choice for other reasons, too.

Share this post


Link to post
Share on other sites

John's project includes lots of "before" "circa" and "between" dates, and has both years only and full dates as one might expect. These would seem to complicate manipulation of the text strings into some sort of date number in Excel, as John has noted. I know it could be done in Lotus 123, although it would take some effort. I assume it could also be done in Excel though I'm not very familiar with it.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×