Jump to content


Photo

Sorting Date Columns in People View


  • Please log in to reply
7 replies to this topic

#1 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3595 posts

Posted 26 June 2019 - 07:57 AM

People View allows you to sort by any one column. It's well known that I think it would be very helpful to be able to sort on multiple columns, but let's allow that to slide for now.

 

For any fact, you can make the Description field into a column in People View, you can make the Date field into a column in People View, you can make the Place field into a column in People View, and you can make the Place Details into a column in People View. If you sort People View by column for a fact's date, RM doesn't actually sort the column by the fact date. Rather, RM sorts the column by the sort date associated with the fact date for the column.

 

In some ways, this makes sense. But a fact date and a fact sort date don't have to be the same. And indeed a fact can have a null date and a real sort date. For example, I use this technique to place the burial fact after the death fact when the burial place is known but the burial date is not known. I don't consider "after death date" (whatever the death date is) to be a meaningful piece of information because a person is always buried after they die, and sometimes they may be buried on the same day they die. So I think an unknown burial date should be left null.

 

I use People View a great deal to identify and correct situations where data is missing. If the data in question is made into a column in People View and if People View is sorted by that column, then people with the missing data are all lumped together at the top of the list or the bottom of the list. And this technique is especially useful if I restrict People View just to a group.

 

But this technique doesn't really work quite right to find truly missing data for date fields if the date is forever unknowable but can be adequately approximated with a sort date. Therefore, I think it would be most helpful if People View supported separate columns for fact dates and for fact sort dates. The fact date column would be sorted by the fact date (including when it is null) and the fact sort date column would be sorted by the fact sort date (including when it is null). And indeed, sort dates are pretty hard to see in RM on any kind of global basis. You can see them for any individual facts, but not globally. So this would be a good view into all your sort dates.

 

Jerry

 



#2 John_of_Ross_County

John_of_Ross_County

    Advanced Member

  • Members
  • PipPipPip
  • 663 posts

Posted 26 June 2019 - 08:44 PM

At the RootsMagic presentation at the Rutherford B. Hayes Presidential Museum, I asked Bruce about automatically sorting the birth, death, and burial facts in that order.

 

He gave some convoluted answer about interaction with other people in the database.  I did not understand his answer and let the topic drop.  I use the sort date to force the correct order when month and day are not given

 

We have a family legend about a woman buried alive near Easton, Maryland in the 1700's.  She was in some kind of a trace and was awaken by the grave robbers.  That is about the only case I can think of where the automatically sorting of life & death  events would not work.



#3 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3595 posts

Posted 27 June 2019 - 06:34 AM

He gave some convoluted answer about interaction with other people in the database.  I did not understand his answer and let the topic drop.  I use the sort date to force the correct order when month and day are not given

 

I would like to hear that answer. It's hard to see how the order of facts for person A should be affected by interactions with person B.

I have always agreed with Bruce that birth, death, and burial facts should not be placed in that order unconditionally. If somebody was  born in 1852 and died in 1851, death should go before birth and you obviously have some kind of problem with your data that you need to correct. My complaint has been about same date events. If somebody was born in 1851 and died in 1851 (maybe the dates came from a grave stone), then sort dates should not have to be used to place the events in their correct order.

Ah, perhaps I do see a convoluted way that there could be an interaction with other people, like if you are making sorted lists of events that include multiple people. It's common to sort data - any kind of data - on multiple fields. The most common such sort in genealogy is sorting names. You sort first by last name and don't even look at first name as a sort field unless the last names are equal. But RM's sorting of dates works only on a single field - namely, the sort date. I think that design feature is the place where the fault lies.

RM's sort dates can default from the the fact date field or they can be specified explicitly by the user. When a sort date defaults, the process is a bit more complicated than it might seem. A sort date is coded internally as a single numeric value, not as multiple fields. So dates such as 1851, before 1851, after 1851, 5 Jan 1851, before 5 Jan 1851, after 5 Jan 1851, etc. each have to be encoded into a single numeric value. So perhaps Bruce is picturing that the fact type such as birth, death, and burial would also have to become encoded into the single numeric value for the sort date so he could continue to sort on just one field. If so, I could see where he is coming from. But I simply wouldn't do it that way. I would leave the encoding of the sort dates as it works now, and I would sort on two fields - sort date as the primary sort field and fact type as the secondary sort field and tie breaker for identical sort dates. It's very easy to do. If you do it that way, there shouldn't be any adverse interaction between people.

Every other genealogy program that I have ever looked at besides RM seems to get this issue right. It's only RM that gets it wrong. RM gets so many other things right it's just hard to see how it gets this very simple issue wrong.

 

Jerry



#4 Vyger

Vyger

    Advanced Member

  • Members
  • PipPipPip
  • 3426 posts

Posted 27 June 2019 - 05:50 PM

 

Every other genealogy program that I have ever looked at besides RM seems to get this issue right. It's only RM that gets it wrong. RM gets so many other things right it's just hard to see how it gets this very simple issue wrong.

 

Jerry

 

I find the lack of live filtering and single column sorting on People View the obvious deficiencies which drag one of the best enhancements of many years down.

 

Unlike Jerry I find so many features in RM let down by lack of thorough planning and implementation leaving gaps which have forced many users to back away from using many of those enhancements over the years. I personally find it very disappointing that these unique features and selling points have not eventually been polished to a professional standard which users desire and have detailed. The deficiencies, limitations and draw backs deliberately left by development have, for many, virtually nullified these valuable steps forward over many years.

 

I am long enough with Bruce & Team to remember a day when that was not the case but things are different now and I am not expecting any change in the present development strategy.


We are all limited by our visions and abilities

Whilst we can borrow from the visions of others we cannot always deliver.

 

User of Family Historian 6.2.7, Rootsmagic 7.6.2, Family Tree Maker 2014 & Legacy 7.5

 

Excel to Gedcom conversion - simple getting started tutorials here

 

Root


#5 Kamolga

Kamolga

    Advanced Member

  • Members
  • PipPipPip
  • 69 posts

Posted 30 June 2019 - 12:35 AM

Hi,

 

I faced a case quite similar, found some kind of workaround but no real solution. As I did not want the entire list of people ,what I did first was to use a query (not mine) to sort same date events logically (birth, baptism,death, cremation, burial) -which works perfectly on edit person and timeline view-

/* SortDateSameDayOrder.sql
   2011-12-19 ve3meo
   Alters SortDates of Birth, Christen, Baptism, Death, Cremation and Burial
   to a natural order when any pair or more occur on the same date. 
   Could be extended to order other facts also. SortDates are effectively assigned 
   (by arithmetical offsets) an absolute suffix -1, -2, ... related to the FactType.
   Affects only those events whose SortDates correspond to the Fact Date, as computed 
   by a Date encoding algorithm. The algorithm does not handle Date modifiers so not all 
   Event dates are handled, e.g. "Bef 1960". 
   2011-12-20 order of Cremation and Burial corrected
*/

UPDATE EventTable 
SET SortDate = SortDate-6692012023*(EventType 
  IN (1,3,7,2,4,5))  -- list of FactTypes we want to sort, in no particular order except this corresponds to the desired order
  +1048576*(SUBSTR('1426503',EventType,1)-1) -- the substr maps the FactType to its order
WHERE EventID 
IN (SELECT EventID FROM EventTable
    INNER JOIN 
    (SELECT -- matching dates
     SortDate, OwnerID, COUNT()-1 AS Matches FROM EventTable
     WHERE OwnerType = 0 AND EventType IN (1,3,7,2,5,4) 
     AND 
     SortDate =   -- equals encoded event Date (if not a match, suggests that user has modified SortDate so don't touch it)
       (CASE 
        WHEN Date LIKE '.%' 
        THEN 1 
        ELSE Substr(Date,3,5) END 
        +10000
        )*562949953421312 
        + Substr(Date,8,2)*35184372088832 
        + Substr(Date,10,2)*549755813888 
        + 17178820620 
      GROUP BY SortDate, OwnerID, OwnerType
     )
     USING (OwnerID, SortDate)
     WHERE Matches
    )
;

, then created views with who and what I wanted to finally update temporary groups for each case scenario I wanted to check and correct.


Rootsmagic 7.5.9.0 with a lot of SQL queries (SQLiteSpy) and a bit of Family Historian 6.2 (tree view and map)


#6 Vyger

Vyger

    Advanced Member

  • Members
  • PipPipPip
  • 3426 posts

Posted 30 June 2019 - 05:28 AM

Hi,

 

I faced a case quite similar, found some kind of workaround but no real solution. As I did not want the entire list of people ,what I did first was to use a query (not mine) to sort same date events logically (birth, baptism,death, cremation, burial) -which works perfectly on edit person and timeline view-

 

I presented an argument recently regarding how Rootsmagic applied the sort date algorithm to Between dates, it did not meet with RM approval so it would appear they don't see the merit and did not add it it the ever growing enhancement request list, link below.

 

I have a large database and therefore many John Does some with birth dates approximated to example Bet 1 Jan 1900 and 31 Dec 1901 the sort date applied by Rootsmagic is 1 Jan 1900 and not 1 Jan 1901, the mid point. Using People View this skews the sorting of BET dates to the extreme start date and therefore often out of scope of the current view. Whilst I believe this to be wrong I have dropped the point and given up on pointing out such things in the future as I can easily reapply the sort dates for calculated dates in other ways.

 

I never leave a Birth Date blank and always estimate to the best of my current knowledge for the very reason of sorting individuals within a time line so the way People View sorts is an important issue. To please all and overcome objections I would have thought providing the option for how to calculate the sort date in these cases would have been the simple universal answer but it would appear not.

 

I often wonder just where Rootsmagic would be as a program if it weren't for 'other ways' and feel sorry for the majority of users who are stuck within the constraints of the program  :(

 

http://forums.rootsm...sort-is-skewed/


We are all limited by our visions and abilities

Whilst we can borrow from the visions of others we cannot always deliver.

 

User of Family Historian 6.2.7, Rootsmagic 7.6.2, Family Tree Maker 2014 & Legacy 7.5

 

Excel to Gedcom conversion - simple getting started tutorials here

 

Root


#7 Kamolga

Kamolga

    Advanced Member

  • Members
  • PipPipPip
  • 69 posts

Posted 30 June 2019 - 12:52 PM

Between can work for death, e.g. died after birth of last child and before wedding of second (sometime see that on the wedding certificate), but I would rather use the first line of note than between in RM.

I changed all my 'between' dates to estimated (est) dates and put a range in notes (if there is no range in notes, 'est' for me is possibly + or -  30 years). I know I should not adapt to the software but that the easiest workaround for me since I hated the minimum date in my left pane: as I also never leave a birth date blank and most of the time I get the parents names from a child birth certificates or child marriage certificate (so generally I have no clue about parents birthdates when I enter them), if I know their child is born in 1900, 'est 1875' works good since plenty of chances they gave birth when about 25 years old, low chances they were born before 1845 (-30 years rule). That would have worked with about (abt) but abt means for me the source is not reliable (shared tree online for example).

Based on that I only check people with same names who have less than 30 years difference in their birthdates. 

 

Now, if I putted 'between 1845 and 1885' (15-55 years old to become parent is my normal guess), if later I have a birth certificate with someone born 1880 and same name, I would not see this is the same person in my left pane (because it shows 1845 instead of 1865) or I would have to extend my checks to more than 30 years. It is usually + or - 10 years, 30 years is exception, working with min is working systematically with exceptions and the issue is multiplied by other spellings, people called by second names, etc...this makes a really big difference in time consumption and blur the view (1845 is misleading my interpretation, not 1875). 


Rootsmagic 7.5.9.0 with a lot of SQL queries (SQLiteSpy) and a bit of Family Historian 6.2 (tree view and map)


#8 Vyger

Vyger

    Advanced Member

  • Members
  • PipPipPip
  • 3426 posts

Posted 30 June 2019 - 01:37 PM

 I know I should not adapt to the software but that the easiest workaround for me since I hated the minimum date in my left pane: .....

 

Kamolga, I don't know how long you have been with this product evolution but you have just defined the essence of Rootsmagic in the except above. I have probably been around too long now and completely frustrated, if one doesn't 'adapt' to the constraints of the software then one is not going to achieve the answers they require, whilst off topic this applies to various unfinished features including Shared Events, Reporting and Place Details.

 

I am not an advocate of workarounds, I believe in eradicating the needs for workarounds through constant improvement but Rootsmagic would appear not to agree, also they have some workaround experts on various platforms that they actively support in their methods.


We are all limited by our visions and abilities

Whilst we can borrow from the visions of others we cannot always deliver.

 

User of Family Historian 6.2.7, Rootsmagic 7.6.2, Family Tree Maker 2014 & Legacy 7.5

 

Excel to Gedcom conversion - simple getting started tutorials here

 

Root