Jump to content


Photo

Sorting all children in the database by birth order


  • Please log in to reply
17 replies to this topic

#1 greebo

greebo

    New Member

  • Members
  • Pip
  • 2 posts

Posted 19 January 2015 - 02:50 PM

I have a database of over 13,000 people with loads of children. I know that you can sort children by birth date for individual families but is there a way to sort the whole database of children by birth date?



#2 Renee Zamora

Renee Zamora

    Advanced Member

  • Support
  • PipPipPip
  • 8470 posts

Posted 20 January 2015 - 11:58 AM

Confirming this is listed as an enhancement request in our tracking system.

 

The only reason it hasn't be implemented is because massive changes to data can have unintended results.

 

Twins may sort out of their birth order. If you have children with no birth dates, but a given birth order, it may scramble their order if some children in the family have dates and others don't.


Renee
RootsMagic

#3 Ludlow Bay

Ludlow Bay

    Advanced Member

  • Members
  • PipPipPip
  • 868 posts

Posted 21 January 2015 - 12:29 AM

Legacy Family Tree has figured out how to do it.   You can feed a RM gedcom to Legacy (ver. 8) and use the global sort tool. I think it will also alert you to birth dates out of order during the gedcom import process.



#4 Laura

Laura

    Advanced Member

  • Members
  • PipPipPip
  • 4276 posts

Posted 21 January 2015 - 09:47 AM

Create a play database.

Add a family with children.

Set up a situation where you have children in a known order and some of those children have birth dates and some do not.

Children:

John Smith, born 1 Jan 1900

Mary Smith, [no birth date]

Eliza Smith, [no birth date]

Jane Smith, born 12 Dec 1909

Daniel Smith, [no birth date]

This child order tells me that I have 2 children probably born between 1901 and 1908 and 1 child born after 1909.

If I rearrange the children by birth date order, I get:

Eliza Smith, [no birth date]
Daniel Smith, [no birth date]
Mary Smith, [no birth date]
John Smith, born 1Jan 1900
Jane Smith, born 12 Dec 1909

I have lost any indication of when the 3 children without birth dates may have been born. Plus, the 3 children without birth dates are not in the order I entered them.
I entered them as Mary, Eliza and Daniel.

Before I rearranged by birth date, I could have added birth dates of between estimated 1901 and 1908 to Mary and Eliza and after estimated 1909 to Daniel. When I do that and rearrange by birth date order, Mary and Eliza are in the order I entered them between John and Jane and Daniel is last.

By adding estimated birth dates, Mary, Eliza and Daniel will not be selected in a search for people without birth dates. You would need to search for each date modifier separately. In the play database I added estimated to the between and after dates so I would only need to search for est.

In my opinion, globally sorting children or doing it manually for every family by birth date order is a very bad idea unless you know that every child has a birth date or an estimated birth date entered.

I don't know if Legacy sorts children without birth dates to the top of the list or the bottom of the list, but, it will be in one of those 2 positions.

#5 Vyger

Vyger

    Advanced Member

  • Members
  • PipPipPip
  • 3421 posts

Posted 21 January 2015 - 10:24 AM

By adding estimated birth dates, Mary, Eliza and Daniel will not be selected in a search for people without birth dates. You would need to search for each date modifier separately. In the play database I added estimated to the between and after dates so I would only need to search for est.

 

The original poster does not say why they want to do this but I think I am in alignment with Laura by not adding anyone without my best estimate of a birth, marriage, death date. These might me wild like Bef 1700 but it differentiates that John Doe from the one born 1920.

 

Until proven enter the best estimate you can and maybe elaborate on why you would like to do this.


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


#6 Laura

Laura

    Advanced Member

  • Members
  • PipPipPip
  • 4276 posts

Posted 21 January 2015 - 11:31 AM

I don't add guessimates to everyone without a birth date. I only add estimated dates to the Birth fact when I have a pretty solid idea of when they may have been born like birth order amomgst other children with birth dates.

If I am absolutely guessing, I add a user defined fact, No birth date, and guesstimate the person's birth year by other data, parent's births, marriage dates, etc. I also use the fact as a search filter to reset the Living tag for people with no birth or death dates which was my reason for creating that fact to start with. I mark this fact as Private.

#7 Renee Zamora

Renee Zamora

    Advanced Member

  • Support
  • PipPipPip
  • 8470 posts

Posted 21 January 2015 - 12:12 PM

Everyone in my database has a birth date. I use "Est" as a modifier on those that I truly had to estimate a date for. Then I cite myself as the source of the estimated date and what it was based on. This source also cites genealogically standards for estimating dates.  I can then filter on that source citation or the Est in the date field to find everyone that I have estimated dates on and need more research done. 


Renee
RootsMagic

#8 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 22 January 2015 - 10:12 PM

The only reason it hasn't be implemented is because massive changes to data can have unintended results.

 

Twins may sort out of their birth order. If you have children with no birth dates, but a given birth order, it may scramble their order if some children in the family have dates and others don't.

While twins are a special case, the SQLite script I posted earlier today has been revised so that it only sorts families in which all of the children have a sort date for the dominant birth or christening fact. And the sequence for children born on the same day can be controlled with offsets in their respective sort dates which one would have to do anyway using RM's current Rearrange > Sort by Birth Date control. There is no reason that a global Sort Children by Birth Date function in RootsMagic could not be designed to make the same exceptions. Would that not answer Laura's and Renee's concerns about the risks? 

 

See Children - Set Order by Birth SortDate.


Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#9 Vyger

Vyger

    Advanced Member

  • Members
  • PipPipPip
  • 3421 posts

Posted 23 January 2015 - 03:11 AM

While twins are a special case, the SQLite script I posted earlier today has been revised so that it only sorts families in which all of the children have a sort date for the dominant birth or christening fact.

See Children - Set Order by Birth SortDate.

 

Thanks TomH, your scripts are a very valuable and welcome asset in overcoming many user needs.


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


#10 Vyger

Vyger

    Advanced Member

  • Members
  • PipPipPip
  • 3421 posts

Posted 23 January 2015 - 06:01 AM

Legacy Family Tree has figured out how to do it.   You can feed a RM gedcom to Legacy (ver. 8) and use the global sort tool. I think it will also alert you to birth dates out of order during the gedcom import process.

 

What I would judge to be the other main competitor has also introduced this feature.


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


#11 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 23 January 2015 - 04:37 PM

the SQLite script I posted earlier today has been revised so that it only sorts families in which all of the children have a sort date for the dominant birth or christening fact. ...There is no reason that a global Sort Children by Birth Date function in RootsMagic could not be designed to make the same exceptions. ...

 

See Children - Set Order by Birth SortDate.

The corollary (and as was indicated in a 2010 post by Ludlow Bay) is an outstanding "request to include incorrect birth order and marriage order on the Problem List (that) has been around since at least RM3". Sooo, until the RootsMagician follows through, here's an outboard answer for the incorrect birth order request. It is not exactly the same concept - it reports those families that need manual attention because they have not been sorted by either the global sort script or by the RootsMagic Rearrange children tool. It also reports families with one child lacking a birth event or birth date.

 

See Children - Needing Manual Arranging.


Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#12 Kamolga

Kamolga

    Advanced Member

  • Members
  • PipPipPip
  • 69 posts

Posted 17 May 2019 - 11:46 AM

Links are broken, would you have a query to soort all children of the database by any chance?


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


#13 Vyger

Vyger

    Advanced Member

  • Members
  • PipPipPip
  • 3421 posts

Posted 17 May 2019 - 12:03 PM

Links are broken, would you have a query to soort all children of the database by any chance?

 

Let Google be your friend

 

https://sqlitetoolsf...birth-sortdate/


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


#14 John_of_Ross_County

John_of_Ross_County

    Advanced Member

  • Members
  • PipPipPip
  • 663 posts

Posted 17 May 2019 - 02:41 PM

This question goes back to 2015.

 

I normally work with the "Pedigree" or "Family" RM display header.

 

A solution to the original question by "Greebo"  follows:

 

1] Click on the RM "People" heading.

 

2] Then click on "Birth Date" heading to sort by birth date.  I rarely use this, but Bruce Buzbee mentioned this feature in his recent presentation at the Rutherford Hayes Presidential Library in Fremont, OH.

 

I did notice that I have some poorly formed alphabetic birth dates from data entered many years ago that should be fixed.



#15 Renee Zamora

Renee Zamora

    Advanced Member

  • Support
  • PipPipPip
  • 8470 posts

Posted 17 May 2019 - 02:48 PM

The Fact List report will help you find those. Create a list of: Facts with text dates. 


Renee
RootsMagic

#16 Vyger

Vyger

    Advanced Member

  • Members
  • PipPipPip
  • 3421 posts

Posted 17 May 2019 - 05:15 PM

The Fact List report will help you find those. Create a list of: Facts with text dates. 

 

Yes, but the ability to create named groups from some of the options under the Fact List would also be useful. On a general search adding the qualifier to Date of 'is text' would allow group creation of Any Fact > Date > IsText, a simple query and more intuitive imo.


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


#17 Renee Zamora

Renee Zamora

    Advanced Member

  • Support
  • PipPipPip
  • 8470 posts

Posted 20 May 2019 - 03:50 PM

Confirming that is on the enhancement request list. 


Renee
RootsMagic

#18 Kamolga

Kamolga

    Advanced Member

  • Members
  • PipPipPip
  • 69 posts

Posted 21 May 2019 - 06:20 AM

I did notice that I have some poorly formed alphabetic birth dates from data entered many years ago that should be fixed.

I have the same issue with an imported file. 

I created a group of person called "Date_Text" with a random person in it.

I went to SQLiteSpy, opened the RMDatabase and launched

--Delete everyone from the group 'Date_Text'
DELETE FROM GroupTable
WHERE GroupID IS (SELECT LabelValue FROM LabelTable WHERE LabelName='Date_Text')
;
--Find All unique person that have a date that is text in any fact
DROP VIEW IF EXISTS vDateText;
CREATE TEMP VIEW vDateText AS
SELECT DISTINCT OwnerID AS OwnerID
FROM EventTable
WHERE Date LIKE 'T%';
--Put those people in the group 'Date_Text'
INSERT INTO GroupTable
SELECT NULL AS RecID
       ,(SELECT LabelValue FROM LabelTable WHERE LabelName='Date_Text') AS GroupID
       ,OwnerID AS StartID
       ,OwnerID AS EndID
FROM vDateText
;

so when I am in People view and select 'Date_Text' instead of 'Show everyone' in the dropdown menu, I have the (very long) list of people who have a bad date entered.

I then 2x click each person, go very quickly through the facts with the down arrow and when the date font is yellow, it means it is text and should be corrected.

Note: the group is not dynamic, to take the corrected people out of the group, you need to execute the query again


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