* censuses - query with different values for an output column

[ississi]

Hi Mike L,
I've had a look but can't find where to download CensusMissingAges, could you tell me where to find it, please?
Many thanks

Try https://fhug.org.uk/kb/download/ages-on-census-3/

Thanks so much,Mike, I was looking at queries starting with "Census"

[ississi]

Interesting!
Why do you choose to use only explicit Birth Dates instead of Estimated, but do use Estimated Death Dates?
Have you considered the following more compact function instead of the nested TextIf(...) function:
CombineText( , %INDI.CENS[year=1841].AGE%, , " find " )
Also, by using DateAt(1841,0,0) you may be excluding/including some people by mistake.

I was just experimenting with the original query (https://fhug.org.uk/kb/download/ages-on-census-3/).
i didn't design it per se, it just evolved. If it ain't broke, don't fix it.

It did what I wanted and didn't seem to miss anyone, so I left well alone

Now if only we could get colour into the reultset

Yes! I was wanting colour too

[ColeValleyGirl]

Mike L,

They're only the same if you think the most important thing about them is the technical mechanism for acquiring them.

Downloads:

• are hosted by the kb, and approved by the kb admins
• are 'passive' -- they customise the appearance of FH, or the presentation of data; they don't change any data within a project, or any FH settings or files -- and so are 'low risk'

Plugins:

• are hosted by the Plugin Store, and approved by the CP admins for the store
• are 'active' -- capable of modifying data within a project, FH settings and files -- and so are 'higher risk' (which is why we took the decision not to host any plugins in the KB)

Some users won't be happy downloading anything from a website maintained by a random bunch of other users (the KB) but will trust a website managed by the software author (the Plugin Store) and linked directly from the product. Other users won't trust plugins but will be happy to use downloads. Conflating the two IMO does nobody any favours.

The relevant menu item originally said 'Downloads/Snippets' and there was no menu link to the Plugin Store. The Plugin Store link was added as a result of this discussion: Knowledge Base "Presentation" Headers/Footers.

The actual word 'Downloads' isn't ideal, but it's the word that was used from the inception of FHUG/the old wiki (when plugins weren't even a glint in anybody's eyes). We retained it when we created the new KB because it has the virtues of (a) brevity (b) familiarity to long-standing users (c) consistency with references in the Forums (if somebody sees a mention of a KB Downloads section, they really ought to be able to find it under that name). The tip that comes up when you hover over the Downloads/Plugins menu item is

Download Family Historian customisations, plugins and code snippets for plugin developers

which seems to me to be explicit enough, coupled with the fact that if you click on the menu item you see it decomposed into the three elements. We might call it something different if we were starting from scratch, but we aren't, and there's more work that you might expect involved in changing it retrospectively.

One thing I am considering is moving Snippets out of that menu, as it's a very niche subject that perhaps muddies the water, and making it accessible via an article in the 'Writing Plugins' sub-topic.

[Mark1834]

which is why we took the decision not to host any plugins in the KB)

That’s a bit of a fine distinction though, as the KB is full of links to FHUG plugins that would probably not be appropriate for the CP-hosted store, either because they are still under development, very niche or highlight a limitation in FH.

On balance, I think it is the right decision, as it avoids the inevitable confusion that would arise with two plugin repositories. It does have consequences though, as it makes version control for these “tier 2” plugins significantly more complex, particularly for authors who are not forum moderators.

[ColeValleyGirl]

I've always been uneasy about plugins in the forums, although at least you can see who is responsible for each (or at least, see their username and posting history) and decide whether you're comfortable or not. IMO, the KB should only have links to plugins in the store, but that horse bolted.

[Mark1834]

IMO, the horse should remain free - we are getting a healthy interest in new plugin authors having a go, and anything that discourages that would be a retrograde step.

It's also a useful reminder of a discussion a while ago after a Store plugin was withdrawn when the plugin police realised that it was changing Windows settings (which they missed on initial review).

There was talk then of setting up a code of practice for both Store and FHUG plugins to avoid such issues in the future. A plugin can always do a, b, and c (e.g., interact with user data and read Windows and FH settings), can do e, f, g only under very strict circumstances (e.g., updating FH Registry Settings), and never do h, i and j (e.g., change any PC settings outside FH, even temporarily).

Was it ever formalised, or did it drop off the attention radar...?

[ColeValleyGirl]

I'm not unhappy about them in the forums per se, just about linking to them from the KB when they might still be work in progress.

I'm not aware of any formal guidance -- I suspect it's handled on a case by case basis.

[Mark1834]

OK - personally, I think it would be better formalised, but getting all the FHUG moderators and CP to agree on one common approach is probably akin to herding cats...

[jelv]

Interesting!
Why do you choose to use only explicit Birth Dates instead of Estimated, but do use Estimated Death Dates?
Have you considered the following more compact function instead of the nested TextIf(...) function:
CombineText( , %INDI.CENS[year=1841].AGE%, , " find " )
Also, by using DateAt(1841,0,0) you may be excluding/including some people by mistake.

Mike, this suggestion doesn't do the same thing as his original. If there is a fact for the year but no age entered, your version comes up with "find" instead of showing the blank date.

[tatewise]

I was not commenting on the original but on the Plugin and screenshot posted by Gowermick on Wed 2nd Nov 2022 16:20 in which find was shown instead of the census date.
CombineText( , %INDI.CENS[year=1841].AGE%, , DateAt(1841,6,6) ) would give the date.

[Gowermick]

Miketate,
I did not want the date printed in any row at any point. The census date is in the column header so would be redundant.
My query lists 4 outputs for each census year
1. X
2. Find
3. blank
4. Age

[tatewise]

Mick, I know You wanted find and I originally commented on an alternative expression to do that.

My suggestion for showing the Date was aimed at John Elvin who is referring to the Original Plugin.

[Gowermick]

Mick, I know You wanted find and I originally commented on an alternative expression to do that.

My suggestion for showing the Date was aimed at John Elvin who is referring to the Original Plugin.

Nae bother, as they say up north

[jelv]

Sorry I didn't express the issue correctly.

The original I was referring to was this:

There is a CensusMissingAges query downloadable from the knowledgebase, which I have modified and which gives a result similar to what you're after.

For each census year, it gives the following entries for each individual:
1. x means not relevant, individual was not alive on census date
2. Blank, individual has a census entry, but age has not been entered
3. Age, age as appears on census
4. find, Alive on census day, but haven't yet found their census entry

<snip>

The formula I used for each column is as follows (adjust for different census years)

=TextIf((%INDI.BIRT.DATE% > DateAt(1841,0,0)) or (EstimatedDeathDate(%INDI%,MID,2) < DateAt(1841,0,0)),"x",TextIf(Exists(%INDI.CENS[year=1841].DATE:YEAR%),%INDI.CENS[year=1841].AGE%," find "))

I attempted to do the changes you suggested and came up with this:

Code: Select all

=TextIf((EstimatedBirthDate(%INDI%,MID,2) > DateAt(1841,6,6)) or (EstimatedDeathDate(%INDI%,MID,2) < DateAt(1841,6,6)),"x",CombineText(,%INDI.CENS[year=1841].AGE%,," find"))

But on an individual looking like this:

Clipboard01.png (4.07 KiB) Viewed 2136 times

It shows "find" instead of the blank (case 2 Gowermick's post above).

What I'd like to do is show a "-" as a better indication that a census entry is present but missing the age.

BTW: How on earth do you develop queries such as Kinsfolk Census To Do Table with such deep embedding of functions? I get lost following the parentheses and the error messages when you get it wrong give you no clue as to how far in to the expression it doesn't like.

[tatewise]

Sorry, I overlooked the Blank case as that is a 'hidden' default.
So this is the expression you will need (I've split it over 4 lines to make it easier to follow):
=TextIf(
( EstimatedBirthDate(%INDI%,EARLIEST,2) > DateAt(1841,6,6) ) or
( EstimatedDeathDate(%INDI%,LATEST,2) < DateAt(1841,6,6) ), "x",
TextIf( Exists(%INDI.CENS[year=1841]%), CombineText( ,%INDI.CENS[year=1841].AGE%, , "-" ), "find") )

I suggest using EARLIEST and LATEST to ensure anybody who might exist on 6 June 1841 is included.
The tip is that there are 4 alternative displays of x, Age, - or find so 3 conditional text functions are needed.
i.e. 2 x TextIf(...) and a CombineText(...)

Set the Column so Align: is Centre then the various values are neatly centred.

Yes, it can take some effort to get the syntax and nesting and logic correct.
But ensuring parentheses are in matched pairs is a start.

[jelv]

Thanks, that does the trick.

I'd already reached the conclusion that, considering what the object of the query is, using EARLIEST and LATEST would be better.

I've also increased the generations on the estimated functions to 5.

For anybody following this my row filters are:

Code: Select all

Exclude if =EstimatedDeathDate(%INDI%,LATEST,5) is less than 6 JUN 1841
Exclude if =EstimatedBirthDate(%INDI%,EARLIEST,5) is greater than 29 SEP 1939

[Little.auk]

Now if only we could get colour into the reultset

There are some things FH can't do - or doesn't do very well!

The queries in this thread are classic examples. The result set may provide the information asked for, but it is not very easy to interpret. In cases like this, I use an Excel spreadsheet. There I can create a much more visual picture of the result set.

I have a query, similar to the ones in this thread, that tells me if I have FH sources for births, baptisms, deaths, burials and Censuses. I run the query, which I "Save as Comma-Separated (CSV) file". I then open the exported file and cut and paste the data into a pre-formatted Excel Spreadsheet as per the example below.

My FH query only outputs the "=Exists" records. Excel conditional formatting colours the Y cells green.

To find and display years where there should be a census record, I use an Excel conditional format expression, based on birth and death years, to highlight those cells where a person should have a census source record in salmon pink.

In the case of this query, which is my basic research overview planner and is going to be updated fairly regularly, I have gone one stage further and linked the raw data cells in the spreadsheet to the records in the csv file.

So, just running the FH query and updating (overwriting) the csv file, automatically updates this spreadsheet, no need to cut and paste!

Census Sources.jpg (450.02 KiB) Viewed 1997 times

[davidf]

I remember (pre Windows 3.0) that you could get an "add-in" for I think it was, Lotus Symphony (an "extended functionality spreadsheet" from the same people as did Lotus123), that allowed you to embed queries on dBase files (all the old names!).

What is the potential for an add-in to Excel (or similar) which can do queries on a GEDCOM? Perhaps such an add-in already exists?

Then you could have a pre-formatted "analysis" spreadsheet with the query embedded in the top left hand corner of the data area, such that when you press recalc (still F9?) the query would update the data.

[sbell95]

I have a query, similar to the ones in this thread, that tells me if I have FH sources for births, baptisms, deaths, burials and Censuses. I run the query, which I "Save as Comma-Separated (CSV) file". I then open the exported file and cut and paste the data into a pre-formatted Excel Spreadsheet as per the example below.

My FH query only outputs the "=Exists" records. Excel conditional formatting colours the Y cells green.

To find and display years where there should be a census record, I use an Excel conditional format expression, based on birth and death years, to highlight those cells where a person should have a census source record in salmon pink.

In the case of this query, which is my basic research overview planner and is going to be updated fairly regularly, I have gone one stage further and linked the raw data cells in the spreadsheet to the records in the csv file.

So, just running the FH query and updating (overwriting) the csv file, automatically updates this spreadsheet, no need to cut and paste!

I would be interested in a more detailed step-by-step instruction on how you do this FH <=> Excel. Looks very handy!

[jelv]

Excel can link to any column structured file for which you can define an ODBC link - CSV files are just a simple example. Most (all?) databases have ODBC drivers.

[tatewise]

John, GEDCOM files are NOT a structured database but structured plain text files so I doubt if a plugin query is feasible.

[jelv]

I know that. I was referring to how Little.auk was reading the csv files created by saving queries.

It would be very difficult (impossible?) to write a driver to allow updating GEDCOM files, but I wonder if it would be possible to create one that gave limited read only access? You'd have to have a way to define the pseudo tables. Far easier to have something that extracted required data from the GEDCOM which could then be saved as a CSV. I believe that's called a query in FH!

[Little.auk]

I would be interested in a more detailed step-by-step instruction on how you do this FH <=> Excel. Looks very handy!

There have been a number of posts regarding saving query results - which is done by clicking on the floppy disk icon, or on the gear wheel and Save Results Set As.... on the Query Result Set tab - both routes seem to do exactly the same thing!

I use the CSV (comma-separated variable) option, because, if you use Microsoft Office, the usual default app for opening CSV files, is Excel. So, just clicking on the file name opens it in Excel. TXT files need a bit more effort!

A couple of points worth noting - by default FH gives the saved csv file the same name as the query name. If you accept this file name, then every time you save the query you will be asked if you want to overwrite the old file.

When you first open the CSV file in Excel, before doing anything else, use File / Save As to save the file in Excel format (.xls or .xlsx). If you don't do this any formatting changes or added equations will be lost if you save as csv.

All of the colouring is done using Conditional Formatting (about halfway along the Home ribbon). There are loads of YouTube videos showing how it works.

One of the first conditional formats I put in place is the one that makes the alternate blue and white rows - this makes it much easier to track across the page. It is a good introduction to using conditional formatting.

the equation that achieves the Row fills is "=MOD(ROW(),2)" where MOD stands for Modulus. This is the remainder when one number is divided by another.

For Example

For Row 1 - 1 divided by 2 = 0 with remainder = 1
Row 2 - 2 divided by 2 = 1 with remainder = 0
Row 3 - 3 divided by 2 = 1 with remainder = 1
Row 4 - 2 divided by 2 = 2 with remainder = 0

NOTE Row() is the worksheet Row number, Row 1 being the first line of the block of cells chosen, and 2 is the divisor.

So, alternate rows generate alternating values of 1 and 0, the odd number rows that return 1 get formatted, the even numbered rows that return 0 don't.

You can apply the colour to the even number rows, either by changing ROW() to ROW() + 1. or by adding = 0 to the end of the equation i.e "=MOD(ROW(),2) = 0"

In the formatting section you have options to change things like fill colour, Font colour / formatting and lines around cells.

Hope this gives you a feel of how it works.

[HoagyM]

This is a requirement near and dear to my heart - a colour-coded table showing missing census entries.

I have found an exceptionally easy way to do this, which was to use the (free) utility tool FTAnalyzer. IMHO it’s absolutely wonderful, and every home should have one!

Download FTAnalyzer, run it against your current GEDCOM and within about 30 seconds you have a multitude of tables and reports showing the “health” of your family tree. Just one of the many tabs is a “census colour chart” giving exactly such a colour-coded “missing census” table. You can also restrict the table to given surnames etc. There’s a similar tab for BMD, as well as a “data errors” tab (showing dates which look odd, etc), and loads more.

This gives me a comprehensive “things to do” list, and also means (I think) that the custom flag I added a few months ago to FH (“have all censuses been added to this individual?”) is now redundant and can be deleted.

I think FTAnalyzer is great, but am very much a novice around here - any views from the seasoned experts on this?

PS And you can export FTAnalyzer tables to Excel too, if you feel the need…

[Woodg]

I, too, am a fan of FTAnalyzer, although I've not used it a lot. I find it useful for finding things like person married after they died, or children born before birth of parent. It can tell you much more, too.
Glenn