* Function to split text strings in to parts (not commas)

Questions regarding use of any Version of Family Historian. Please ensure you have set your Version of Family Historian in your Profile
Post Reply
avatar
jelv
Platinum
Posts: 41
Joined: 03 Feb 2020 22:57
Family Historian: V6.2
Location: Mere, Wiltshire

Function to split text strings in to parts (not commas)

Post by jelv » 30 Apr 2020 16:12

Using Ancestral Sources I'm entering the reference ID as something like RG11/1234/56/7.

I have a query that lists all the individuals in a particular census and would like to sort it in to correct reference order. The problem is that each of the parts of the reference number (for 1881 Piece, Folio and Page) are variable length. Just sorting on the whole reference id doesn't work because it just does a text sort of the whole string so RG11/567/8/9 would come after RG11/1234/56/7.

I've managed to improve it significantly by extracting the Piece and making it numeric so it sorts correctly using this expression:

Code: Select all

=TextToNumber(GetLabelledText(%INDI.CENS[year=1881].SOUR>PUBL%,"RG11/"))
TextPart doesn't help as it only works for comma separated strings. What I need is a similar function where you can specify the separator character (in this case "/").

Have I missed the obvious for how to extract the other parts to make them separate numerically sortable columns?
John Elvin

avatar
jelv
Platinum
Posts: 41
Joined: 03 Feb 2020 22:57
Family Historian: V6.2
Location: Mere, Wiltshire

Re: Function to split text strings in to parts (not commas)

Post by jelv » 30 Apr 2020 17:07

OK - there is a way to do it iteratively but it's going to end up with a horrendous expression for the 1841 census which has four parts. The second part for 1881 can be extracted using

Code: Select all

=TextToNumber(GetLabelledText(%INDI.CENS[year=1881].SOUR>PUBL%,Text("RG11/" . TextToNumber(GetLabelledText(%INDI.CENS[year=1881].SOUR>PUBL%,"RG11/")) . "/")))
Is there a way to refer to previous columns and use that in a queries expression? That would make it simpler.
John Elvin

User avatar
tatewise
Megastar
Posts: 18488
Joined: 25 May 2010 11:00
Family Historian: V6.2
Location: Torbay, Devon, UK
Contact:

Re: Function to split text strings in to parts (not commas)

Post by tatewise » 30 Apr 2020 17:25

The answer to all your questions is No.
One possible solution is to write a Plugin which has far superior string manipulation functions.
However, for exactly the purpose of sortability, I record Census refs as fixed width fields with leading zeroes.
e.g. RG11 0373 067 01
So I would record your RG11/567/8/9 as RG11 0567 008 09
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry

avatar
brianlummis
Famous
Posts: 190
Joined: 18 Dec 2014 11:06
Family Historian: V6.2
Location: Suffolk, England
Contact:

Re: Function to split text strings in to parts (not commas)

Post by brianlummis » 30 Apr 2020 17:58

Just thinking out aloud, would it help to achieve your objective to manipulate the data in Excel? That way you could use the standard query for 'Source Record - All' and export it as a csv file.

However it does depend what your final objective, is as the data wouldn't be sorted in FH and any changes that you wish to make would mean a manual exercise.

User avatar
tatewise
Megastar
Posts: 18488
Joined: 25 May 2010 11:00
Family Historian: V6.2
Location: Torbay, Devon, UK
Contact:

Re: Function to split text strings in to parts (not commas)

Post by tatewise » 30 Apr 2020 18:25

I think it would be much easier to edit the data so it is inherently sortable.
That way the job is done once and for all.
Where is it defined that Census refs must be separated using / slash and can't have leading zeroes?
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry

avatar
jelv
Platinum
Posts: 41
Joined: 03 Feb 2020 22:57
Family Historian: V6.2
Location: Mere, Wiltshire

Re: Function to split text strings in to parts (not commas)

Post by jelv » 30 Apr 2020 19:51

Do you have a list of all the maximum lengths of the varying parts for each census?

I'd actually go for slashes and leading zeroes.
John Elvin

User avatar
tatewise
Megastar
Posts: 18488
Joined: 25 May 2010 11:00
Family Historian: V6.2
Location: Torbay, Devon, UK
Contact:

Re: Function to split text strings in to parts (not commas)

Post by tatewise » 30 Apr 2020 20:04

These are what I use:
1841 HO107 0307 15-20 07
1851 HO107 1524 562 45
1861 RG09 0214 105 35
1871 RG10 0824 033 04
1881 RG11 1197 084 17
1891 RG12 0931 027 05
1901 RG13 0936 016 24
1911 RG14 06061 RG78 0276 100 2 13 106

So mostly they are RG number, 4 digits, 3 digits, 2 digits, but with 1851 and 1911 slightly different.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry

avatar
brianlummis
Famous
Posts: 190
Joined: 18 Dec 2014 11:06
Family Historian: V6.2
Location: Suffolk, England
Contact:

Re: Function to split text strings in to parts (not commas)

Post by brianlummis » 30 Apr 2020 20:45

Where is it defined that Census refs must be separated using / slash and can't have leading zeroes?
Although not defined, the practice of using slashes started in 1841 when the class reference HO107 was followed by a piece number which was in two parts being the box number and the folder - these 3 references were divided by slashes and the reference became, for example HO107/670/1 to identify the exact place in the census you needed the folio and/or page number in the enumerators book. It then became common in many archives to give the full reference using slashes. So, whilst there is nothing stopping anyone using leading zeros or omitting slashes, it might be frowned upon in some academic circles - but not me :) .

avatar
jelv
Platinum
Posts: 41
Joined: 03 Feb 2020 22:57
Family Historian: V6.2
Location: Mere, Wiltshire

Re: Function to split text strings in to parts (not commas)

Post by jelv » 30 Apr 2020 21:08

Thanks.

1911 is odd - and also odd in the way the search works on FindMyPast.

To uniquely identify a household you only need the piece and schedule number but in the FMP advanced search there is no option to enter the schedule number - but the parameters in the URL accept it. So your example of piece 6061 and schedule 106 you add &pieceno=6061&schedule=106 only after the dataset name and it works! The full URL is

https://www.findmypast.co.uk/search/results?datasetname=1911+census+for+england+%26+wales&pieceno=6061&schedule=106

You'll notice on that results page it shows the schedule number at the top left, but if you click advanced options it loses it and you have 1618 results!
John Elvin

User avatar
tatewise
Megastar
Posts: 18488
Joined: 25 May 2010 11:00
Family Historian: V6.2
Location: Torbay, Devon, UK
Contact:

Re: Function to split text strings in to parts (not commas)

Post by tatewise » 30 Apr 2020 21:27

I actually add the Schedule number to all the 1841-1901 references too.
The way various online web sites organise their searches is independent of the way the records should be numbered.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry

avatar
victor
Famous
Posts: 146
Joined: 08 Jan 2004 16:53
Family Historian: V6.2
Location: Thatcham, Berkshire, England

Re: Function to split text strings in to parts (not commas)

Post by victor » 30 Apr 2020 21:42

An example of what I use
1891 RG12-0931_Fo-027_Page-05

Fo is the Folio number
I do it this way for all the census years

Victor

Post Reply