* Custom queries

Questions regarding use of any Version of Family Historian. Please ensure you have set your Version of Family Historian in your Profile. If your question fits in one of these subject-specific sub-forums, please ask it there.
avatar
Overloaded
Platinum
Posts: 43
Joined: 05 Feb 2012 02:37
Family Historian: None

Custom queries

Post by Overloaded »

I'm trying to build my first custom query, and I'm feeling challenged.

This is a two part question.

1) I'd like to list all 'Place' fields, regardless of which event of attribute it occurred in, and be able to list it in just one column.
-->> the only way I can figure out to come close would be to list each possible fact that has a 'place' field, each in its own column?  I'd also like to list the fact that it comes from, and possibly other fields, too.  I'm hoping there's a more elegant solution.

2) failing an elegant solution - and I'd just like to know anyway - how do I create a CSV file with a custom query?  I could download all the fields and massage the data into a reasonable looking report that way.  I've seen information about moving CSV files into gedcoms, but not a way to create a data file that could be loaded into a spreadsheet, etc.

Thanks

ID:6415
User avatar
Jane
Site Admin
Posts: 8508
Joined: 01 Nov 2002 15:00
Family Historian: V7
Location: Somerset, England
Contact:

Custom queries

Post by Jane »

I am not sure how you would list a place once, but still list all the Facts for it, but you can use a Fact Query to list and sort by place easily enough.

The result set from any query can be saved as a CSV file using the menu button or simply CTRL-A and CTRL-C to copy all the fields and paste them into Excel.
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Custom queries

Post by tatewise »

Have a look at Creating a Query in the Knowledge Base.

There are many different types of Query but if you are using FH V4 or V5 then a Fact Query is best suited in your case.
avatar
Overloaded
Platinum
Posts: 43
Joined: 05 Feb 2012 02:37
Family Historian: None

Custom queries

Post by Overloaded »

tatewise said:
Have a look at Creating a Query in the Knowledge Base.
Been there, read that; Saw the video; read chapter 14; did the exercises;

Still confused
avatar
Overloaded
Platinum
Posts: 43
Joined: 05 Feb 2012 02:37
Family Historian: None

Custom queries

Post by Overloaded »

Jane said:
I am not sure how you would list a place once, but still list all the Facts for it,  but you can use a Fact Query to list and sort by place easily enough.

The result set from any query can be saved as a CSV file using the menu button or simply CTRL-A and CTRL-C to copy all the fields and paste them into Excel.
Trying to achieve something that might look like this:
Date        Fact type    Place
-----       ---------    ----------
Mar 1865   Occupation    London, England
Apr 1881    Death          Bristol, England
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Custom queries

Post by tatewise »

I don't have access to FH at present but I think the Standard Query called All Facts (in FH V4 & V5) does that already.

Remember to click on any column header to sort by that column.

If it does not list exactly what you want, then use the Query Menu top left to Save as Custom Query and alter the Columns tab to list the data items you want using the Fields pane on the left.

To exclude Facts with no Place field, then on the Rows tab, add Exclude if FACT.PLAC is null.
avatar
Overloaded
Platinum
Posts: 43
Joined: 05 Feb 2012 02:37
Family Historian: None

Custom queries

Post by Overloaded »

OK, I'm exploring 'All Facts' and customizing it to search for facts related to a specific individual[smile]

Found the query-results to CSV option [smile]

And I've found the section on data references in the FH Help [smile]


[question] but how can I put the parameter [that I prompted for in the query] into the Query Subtitle?

e.g.
Report of all Places [Query Title]
for John Smith [Subtitle, using the name the parameter given by the user]

I've looked in Ch 14 and the Help section. I don' see that option listed in the 'Special Codes in Query Titles and Subtitles'
avatar
Overloaded
Platinum
Posts: 43
Joined: 05 Feb 2012 02:37
Family Historian: None

Custom queries

Post by Overloaded »

Also, I'd like to use an 'OR' operator for record selection (check parameter against both factowners):
=FactOwner(%FACT%,1,MALES_FIRST) or =FactOwner(%FACT%,2,MALES_FIRST)

but it isn't accepting it.

What am I doing wrong?
User avatar
PeterR
Megastar
Posts: 1135
Joined: 10 Jul 2006 16:55
Family Historian: V7
Location: Northumberland, UK

Custom queries

Post by PeterR »

The Rows Filter needs two entries:
Both have Condition: Add if...
Expression1: =FactOwner(%FACT%,1,MALES_FIRST)
Expression2: =FactOwner(%FACT%,2,MALES_FIRST)
Both have Operator: is
Both have Parameter ticked and both have the same Label.
Image
avatar
Overloaded
Platinum
Posts: 43
Joined: 05 Feb 2012 02:37
Family Historian: None

Custom queries

Post by Overloaded »

PeterR said:
The Rows Filter needs two entries:
Both have Condition: Add if...
Expression1: =FactOwner(%FACT%,1,MALES_FIRST)
Expression2: =FactOwner(%FACT%,2,MALES_FIRST)
Both have Operator: is
Both have Parameter ticked and both have the same Label.
http://www.fhug.org.uk/images/uploads/2 ... 184122.png
I'll try it, but I would have guessed that
a) this would require both fields to be equal to the parameter
b) what I read made me think that condition 1 was applied first, then condition 2. A record where the parameter doesn't exist in field one, isn't going to make it to the second pass?

but I'll try it. back soon.
avatar
Overloaded
Platinum
Posts: 43
Joined: 05 Feb 2012 02:37
Family Historian: None

Custom queries

Post by Overloaded »

You're right, that worked.  The discussion on page 158/159 of Getting Started made me think that BOTH conditions must be met to meet the inclusion!  


What about putting the parameter in the subtitle?
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Custom queries

Post by tatewise »

An undocumented feature of Title and Subtitle > Custom is that they support Functions.
So enter for =Text(['Person']) (or whatever Parameter name you use) into the Subtitle > Custom field.
User avatar
PeterR
Megastar
Posts: 1135
Joined: 10 Jul 2006 16:55
Family Historian: V7
Location: Northumberland, UK

Custom queries

Post by PeterR »

Overloaded said:
... The discussion on page 158/159 of Getting Started made me think that BOTH conditions must be met to meet the inclusion!
The following paragraph from the book explains what is happening:
For each filter, if the filter has an Add condition (Add if… or Add Unless…), Family Historian will look through the Input Set to find items that match (if it is an Add If…) or don't match (if it is an Add Unless...) the condition specified in the filter. Any items found will be moved from the Input Set to the Result Set.
avatar
Overloaded
Platinum
Posts: 43
Joined: 05 Feb 2012 02:37
Family Historian: None

Custom queries

Post by Overloaded »

Still trying to display the name selected for my parameter into the title.

My prompt has been simplified to 'Name'
Add if =FactOwner(%FACT%,1,MALES_FIRST) is ['Name']

On the General page, the Subtitle box is
Custom and the box that follows is
=Text(['Name'])
Then the parameter prompt box has 2 prompts in it (boxes represented with dashes)

Name -----------
Name -----------  
(with a browse option)

and if I enter 'This is the subtitle' in the first box, it appears in the subtitle of the printout.

Of course, what I want is just one prompt for the individual's name, and that individual's name appears in the subtitle.
thanks
User avatar
PeterR
Megastar
Posts: 1135
Joined: 10 Jul 2006 16:55
Family Historian: V7
Location: Northumberland, UK

Custom queries

Post by PeterR »

There are two prompts because the first (for the Subtitle) needs a Text item, while the second needs a specific Record.

If you change the expression for the Subtitle to:

Code: Select all

=Record(['RecordId'])
(or the prompt could still be 'Name' if you prefer) you can then input the relevant Individual RecordID when prompted.  This means that you have to note the RecordId when you browse for the Individual.

I know this is not ideal and others may have a better solution.
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Custom queries

Post by tatewise »

There must be some minor difference between the two parameters.
Check that there are no lurking space characters in the Subtitle =Text(['Name']) before N or after e, nor in the Parameter Label on the Rows tab.
If they are EXACTLY the same there will only be one prompt.
avatar
Overloaded
Platinum
Posts: 43
Joined: 05 Feb 2012 02:37
Family Historian: None

Custom queries

Post by Overloaded »

You've actually tried this yourself?

I've retyped it. I've copied all facts and started over. I've cut and paste. I've deleted the field, moved my cursor off the field, tabbed back into it (so the whole field is highlighted) and started typing.

Not having any luck yet.
User avatar
PeterR
Megastar
Posts: 1135
Joined: 10 Jul 2006 16:55
Family Historian: V7
Location: Northumberland, UK

Custom queries

Post by PeterR »

Please see my reply at bottom of previous page at 18:14:55 which, I still think, explains the situation.
avatar
Overloaded
Platinum
Posts: 43
Joined: 05 Feb 2012 02:37
Family Historian: None

Custom queries

Post by Overloaded »

PeterR said:
There are two prompts because the first (for the Subtitle) needs a Text item, while the second needs a specific Record.

If you change the expression for the Subtitle to:=Record() (or the prompt could still be 'Name' if you prefer) you can then input the relevant Individual RecordID when prompted.  This means that you have to note the RecordId when you browse for the Individual.

I know this is not ideal and others may have a better solution.
Yes, sorry, missed this response, saw the previous about INPUT file!

haven't tried this solution, but do I understand what this will do:
This will generate two prompts, first for the record id of the individual, second to re-identify the individual?? No, that's not what I'm trying to achieve.

The individual facts written in the report still have access to the individual's name, it just looks kinda silly written over and over again.  I already have 35 facts for this individual, and I haven't finished putting in all the places he served.  It seems quite emphatic to repeat the name each time!

Thanks, Peter
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Custom queries

Post by tatewise »

Sorry, I did not check the type of parameter being used.
PeterR is on the right track, but the Subtitle Function should be:
=Individual(['Name'])

See the FH Help > Family Historian Help > Using Family Historian > Advanced Topics > Understanding Functions > Prompts and then follow link to Type-Specifier Function where the 6 data type Functions are explained.
User avatar
PeterR
Megastar
Posts: 1135
Joined: 10 Jul 2006 16:55
Family Historian: V7
Location: Northumberland, UK

Custom queries

Post by PeterR »

Thanks, Mike, for reminding me of the function =Individual(). I can now go one better.

To avoid the persistent double prompting, you can change the Rows filter expressions to:

Code: Select all

=IsSameItem(FactOwner(,1,MALES_FIRST),Individual(['Name']))
=IsSameItem(FactOwner(,2,MALES_FIRST),Individual(['Name']))
and the Operator will now be is true.  There will be only a single prompt because the context of the parameter is now the same for the filter as for the subtitle.

You can avoid the repeated appearance of the Individual's name throughout the query results by dropping the columns for =FactOwner(...) and adding a new column with heading e.g. 'Other Person' and the following Expression:

Code: Select all

=TextIf(IsSameItem(FactOwner(,1,MALES_FIRST),Individual(['Person'])),'',FactOwner(,1,MALES_FIRST))
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Custom queries

Post by tatewise »

There is no need to change the Row filters to obtain only one prompt. The original filters posted by Overloaded should be just fine.

The tip about removing unwanted columns is good.
avatar
Overloaded
Platinum
Posts: 43
Joined: 05 Feb 2012 02:37
Family Historian: None

Custom queries

Post by Overloaded »

Thanks to both of you, will try these changes.

... I only intended to leave the name column if there was no way to avoid the double prompt to run the report.

... my next modification may involve suppressing information that repeats when I have facts for the same date... :)
avatar
Overloaded
Platinum
Posts: 43
Joined: 05 Feb 2012 02:37
Family Historian: None

Custom queries

Post by Overloaded »

tatewise said:
Sorry, I did not check the type of parameter being used.
PeterR is on the right track, but the Subtitle Function should be:
=Individual()

See the FH Help > Family Historian Help > Using Family Historian > Advanced Topics > Understanding Functions > Prompts and then follow link to Type-Specifier Function where the 6 data type Functions are explained.

[next post by Tatewise]
...There is no need to change the Row filters to obtain only one prompt.
Right.  The second prompt was definitely coming from the subtitle.  The two row filters only generated one prompt total.  But Pete, I couldn't get the filters you gave to work - the screen wouldn't accept them, so I must need to tweak the parameters to the functions. I haven't tried that.

But Tatewise, I still am not getting the name to print in the subtitle of the report using:
Row filters:
=FactOwner(%FACT%,1,MALES_FIRST)is ['Person']
=FactOwner(%FACT%,2,MALES_FIRST)is ['Person']

and subtitle:
=Individuall(['Person'])

It just prints that phrase (=Individuall(['Person']))

off to read more about functions
avatar
Overloaded
Platinum
Posts: 43
Joined: 05 Feb 2012 02:37
Family Historian: None

Custom queries

Post by Overloaded »

Have you ever used a function in a subtitle? I'm starting to conclude that it's not an option?
Post Reply