* Create a Burial Location Report

[ColinH]

Hi

I am fairly new to Family Historian and would appreciate some help in creating a report that only includes individuals not living and displays columns that list Name, Birth Date, Death Date, Cemetery, Plot reference.

The idea is to have a compact report that I can take with me on cemetery visits and find graves and make additions or corrections as I go round the cemetery.

I can see that there is already a "not living" flag, but I cant see a way of identifying every individual that the flag should be applied to, For example, I may want to define that someone "not living" is either someone who has a death date entered on their record or is older than 120 (or whatever an appropriate age is).

Thanks.

[Jane]

What you are looking for is a query. Perhaps starting with something like
http://www.fhug.org.uk/wiki/doku.php?id ... th_details

and adding and removing columns and selections to match what you want.

[tatewise]

Hi Colin, welcome to the world of Family Historian.

I would advise against using the Flag approach, because the management of Flags can become an annoying chore. You have to remember to update them whenever your data changes, by adding Flags to new entries, and removing them from inapplicable entries.

There is by default a Living Flag, but there isn't a Not Living Flag, unless you have added it.

I would advise using Expressions in a Query to obtain the summary Report you want.

There is a Custom Query that nearly fits the bill: fhugdownloads:contents:death_details|> Query:Individual ~ Death Details.
Download and install that to investigate the Result Set.

You will need to edit this Custom Query to get what you want.
See how_to:index#intermediate_guide|> Family Historian > Intermediate Guide > Queries & Expressions for general advice.

Start by using the Query Menu > Save As Custom Query and give it a Name such as Death and Burial Details.

It does not include Individuals with no Death Event who are older than 120 so on the Rows filter tab add:
Condition: Add if
Expression: =EstimatedAgeAt(%INDI%,Today(),MIN,2)
Operator: is greater than
Value: 120
Finally click the Add button bottom right.
You may want to review the =EstimatedAgeAt() Function in the FH Help > Family Historian Help > Using Family Historian > Advanced Topics > Understanding Functions and how_to:understanding_functions|> Understanding Functions.

The Query does not list exactly the details you asked for, so this can be changed on the Columns tab.
To add Birth Date, use the left-hand Fields pane to select Individual > Events > Birth > Date, then click the central > button to add to Columns pane. Select it and use the lower left Move Up arrow to position it where required.

To add the Cemetery and Plot reference requires some feedback from you.
These are not standard FH data fields, so you need to tell us which fields you have stored them in.
Perhaps they are in the Note field of the Death Event?

Any Column you don't want can be removed by selecting it and clicking the X Delete button lower left.

At a later stage, when you have reviewed some graves, you will need to consider how to eliminate those Individuals from your Report, and this will depend on which data fields you choose to record that review.

[ColinH]

Thank you both for your very helpful replies. I have a fact called Burial, with the Place field used for the Cemetery and Address for the Plot number.

[tatewise]

Thank you for that detail.
So you can add those two Columns using the same technique as for Birth Date above.

Select Individual > Event > Burial > Place and add to Columns tab.
Select Individual > Event > Burial > Address and add to Columns tab.
You may wish to Update the Heading of this last one to Burial Plot.

One way of excluding those reviewed entries would be to add a Source Citation to the Burial Event then the Query could exclude those Individuals with a Burial Citation with an appropriate Type or Title.

[delwoodman]

Colin:

I think the query Mike and Jane have referred to is one that I submitted (apologies if I am misremembering or if the version in the query store has subsequently been modified). One point to bear in mind is that it will only return people with burial entries if you have also entered a death fact. If that is so for your file, all well and good. If not, then you will need to add the following filter to the rows tab of the query window:
Add unless %INDI.BURI[1]% is null.

I do in fact already have a burial details version of the death details query which I will be more than happy to send you if it would help.

Mike:

I wasn't sure where the reference to age 120 comes from? If we are talking about the same query, it doesn't return any individuals for which no death event has been entered, regardless of age.

Derek

[tatewise]

Derek, you are correct regarding your Query, but above I have modified a variant of it with the Rows tab Expression: =EstimatedAgeAt(%INDI%,Today(),MIN,2) which does Add if anyone has an estimated Age > 120 even with no Death Event.

[jmurphy]

Thank you both for your very helpful replies. I have a fact called Burial, with the Place field used for the Cemetery and Address for the Plot number.

Colin -- where do you put the information about what town the cemetery is in?

For my burial fact, I use Place for the city or town in which the cemetery is located, and address for the name of the cemetery. I don't have Plot numbers for any of the people in my database yet, so I would be interested in hearing what others do.

I do it this way to keep the Place hierarchy consistent with the other Places in my database.

I agree that it is useful to have a specific field for the Plot numbers, but I don't want to do it at the expense of everything else in the Place hierarchy. This is a case where it would be useful to have another field in the address. (The USPS, on its online addressing systems, offers an Address1 and Address2, especially for business addresses where more fields are needed.)

Jan

[ColinH]

Hi All

I am making progress, thanks to your help. I have downloaded the query and adapted it by including the expression to include anyone without a death fact but who would be over 110 (I originally said 120, but changed this). I have also removed some columns that I didn't need and added others so that I now have the cemetery and plot numbers.. It's looking good.

Now I want to get the records into an order so that they are grouped by cemetery name and I have made the cemetery column sorted in Ascending order. However, there are some hidden columns in the original query that I don't understand the function of and they are stopping the records from being sorted as I want. So, can you tell me what these are for and if I still need them?
DA?
AT
Rel?
Spouse Rel
Code2
Code
Rel spouse

All of the above are both hidden and sorted.

Thanks

[tatewise]

In a Query with several Columns that have Sort selected, then by default the left-most Column takes precedence. Subsequent Columns only take effect where all preceding Columns are identical on more than one Row.

The purpose of the leading Columns being Sorted and Hidden is to utilise the above defaults, but allow the visible Columns to be displayed in any order.

If you click the F1 key in the Query Window an associated Help page is displayed. Against What order do I want the columns to appear in? click on the The Columns Tab link and all is explained.

You can safely delete them all.

[delwoodman]

Colin:

The hidden columns to which you refer are solely there to achieve the sort order set out in the query description, namely:

The list is sorted by directness of relationship, with ancestors of the file root listed first, followed by siblings of ancestors then by cousins of varying degrees. The same pattern is then repeated for relatives of the spouse of the file root.

So they can all be deleted as unnecessary since you are sorting by cemetery name.

Derek

[ColinH]

Thanks, that's just what I wanted.

Just one more thing; I cant see any way to format the result set.

[Jane]

I am not sure what you mean by formatting? You can change the font etc on the Query menu options. You can also save the results as a CSV file and load it into excel (or other spreadsheet program) for more control.

[tatewise]

The Query Menu > Options opens the Tools > Preferences > Query Window settings where Margins, Fonts & Colours can be chosen.

You can change the Result Set Column widths by dragging the join between Column Headings using the twin arrowed cursor.

On the General tab there are settings for Title, Subtitle, and page Orientation.

If you need more sophisticated formatting then you will probably need to write a Plugin to create exactly what you want.

[ColinH]

Brilliant. That's everything I need. Hopefully I will be able to do some more reports myself.

Thanks very much for your help.

[ColinH]

Hi

Since upgrading to V7, I now can't find the Burial Location query that I had so much of your help to create. I've looked in View> Queries> All Custom Queries but there is nothing there. I'm hoping that it's just hidden somewhere, or do I have to recreate it?

Thanks

[tatewise]

If you simply installed FH V7 over your existing FH V6 on the same PC then it should inherit all your custom Queries, Diagrams, Reports, and everything else you customised in FH V6. There are a few customisations that need special treatment for FH V7 but your Burial Location Query should still work.

Can you summarise how you upgraded to FH V7 please?

BTW: When you get to View > Query > All Custom Queries... do you click on All Custom Queries... to get a list?

[ColinH]

I just installed over the existing version.

Just had a thought - Is the query a separate file? If so, perhaps I could search for queries. Would that work?

[tatewise]

All custom Queries are saved as .fhq files in the C:\ProgramData\Calico Pie\Family Historian\Queries\Custom\ folder.

If you have never visited that C:\ProgramData\... folder before then see Family Historian Program Data Folder to discover how to reveal it, as it usually hidden and often confused with C:\Program Files... which you must NOT touch.

[LornaCraig]

I see that the original topic when the query was created was back in 2014. Have you changed computers since then? If so you may not have transferred the query to the new computer. Upgrading to V7 on the same computer won't have removed the query. Are you sure you still had the query on your current computer immediately before upgrading to V7?

[ColinH]

All custom Queries are saved as .fhq files in the C:\ProgramData\Calico Pie\Family Historian\Queries\Custom\ folder.

If you have never visited that C:\ProgramData\... folder before then see Family Historian Program Data Folder to discover how to reveal it, as it usually hidden and often confused with C:\Program Files... which you must NOT touch.

Knowing the file type and where it is stored, I looked for it in a backup from last year. I have certainly run the query on this computer within the past year so have no idea why it disappeared.

Thanks for your help - panic over.

[tatewise]

Do you run the Backup and Restore Family Historian Settings plugin from time to time?
That makes sure that such things as Queries are backed up and easily restored.
It is all too easy to delete a custom Query by accident.

[ColinH]

I have just installed the Backup and Restore Family Historian Settings plugin and created my first settings backup.

Thank you and Happy New Year to all.