Custom Query - attributes

willowherb · Post by **willowherb** » 23 Jan 2011 16:44

Hi
I am having a bash at doing a custom query and have looked at a lot and downloaded some from the web site.

I have tried to follow by example but don't seem to be getting anywhere.

Every person on my file has a custom FAMILY attribute which is listed in the colums tab as %INDI_ATTR-FAMILY% and is a number.

I would like to run a query and get all the people listed in Family 19 (say) then run it again and get another family.

In the Rows tab I have the expression
add if %INDI_ATTR-FAMILY%matches['']

If I run the query for members of family 19 it lists 31 individuals when there should be 41. If I run it again and ask for family 18 then it gives no answer at all (there are over 100). It won't give any answer except to 19 now (unless I close and re-open) but then if I return to 19 then it lists only 10 people this time!

What am I doing wrong?

I have tried using the downloaded query for surname and find that that always wants the surname 'Traubman' which is no problem as I can overtype it, but where is that stored in the code?

TIA

Fiona

ID:4960

PeterR · Post by **PeterR** » 23 Jan 2011 17:20

Since the operator is matches rather than equals I'm pretty sure that FH is treating the Family custom attribute as a character string rather than a number. A possible explanation is that 31 individuals have the value '19' and 10 individuals have the value ' 19'. If you run the query with parameter value '19' you would get 31 records, and if you run the query with parameter value ' 19' you would get 10 records. What results do you get if you update the filter to use the operator contains or ends with?

PeterR · Post by **PeterR** » 23 Jan 2011 18:52

A better solution may be to update the filter to incorporate the :NUMERIC qualifier, so that FH does treat the value as a number:

Code: Select all

Add if %INDI._ATTR-FAMILY:NUMERIC% matches ['Family']

willowherb · Post by **willowherb** » 23 Jan 2011 19:53

Hello Peter

Thanks for your reply.

Please bear with me as I am a really new user of this program...

But how do I change the automatically generated code to what you sent?

If I try to overtype it it won't let me do it.

Sorry

Fiona

Post by **tatewise** » 23 Jan 2011 20:03

There are two solutions - one using a Family Query and the other a Fact Query.

General tab Query Type: Family

Columns tab
Heading Expression
Family %FAM%
Value 1 %FAM._ATTR-FAMILY[1]%
Value 2 %FAM._ATTR-FAMILY[2]%
Value 3 %FAM._ATTR-FAMILY[3]%

Rows tab
Filter
Add if %FAM._ATTR-FAMILY[1]% equals ['Value']
Add if %FAM._ATTR-FAMILY[2]% equals ['Value']
Add if %FAM._ATTR-FAMILY[3]% equals ['Value']

Where the parameter 'Value' is 18, or 19, etc.
The extra rows are needed to cater for up to three Family Attributes per Family Record.
Add more Columns and Rows if more than three needed.

General tab Query Type: Fact

Columns tab
Heading Expression
Family =GetRecord(%FACT%)
Fact %FACT%

Rows tab
Filter
Add if =FactLabel(%FACT%) matches 'FAMILY'
Exclude if =Not(FactValue(%FACT%) = ['Value']) is true

The 1st row finds all the Family Attribute Facts.
The 2nd row excludes those with the wrong 'Value'.
If there is more than one Fact with Label='FAMILY' then another Row will be needed to Exclude if =Not(FactSet(%FACT%) = 'SETNAME') is true.

Post by **tatewise** » 23 Jan 2011 20:19

Re: your problem with changing the Row Expression.

Use the [...] at right-hand end of Expression box and navigate to the Family > Attribute > ... > :NUMERIC then click OK.
To get the ['Family'] parameter, tick the Parameter box lower right and enter Family in the Label box below.
Finally click the Update button.
If this is greyed-out then the Expression is invalid.

To add Filters with index values [2] or [3] you must first select an existing similar Filter, then click in the Expression box to remove the highlight, edit the [2] index into the Expression, and finally click Add button.

willowherb · Post by **willowherb** » 23 Jan 2011 21:15

Thank You TATEWISE and PETER R

It all works like a dream!

I am moving from Generations where all this is done for you so it is a steep learning curve for me to DIY

Many thanks

Fiona

Post by **tatewise** » 24 Jan 2011 14:09

Firstly, sorry, I mis-read your initial posting, and in my suggestion the first Query Type should be Individual and wherever I use %FAM it should be %INDI.
My second Query Type: Fact suggestion works for both Individual and Family facts.

Just to satisfy my curiosity -
What is the 'Generations' Family Number attribute?
What is its purpose?

FH may have its own way of satisfying this purpose.

willowherb · Post by **willowherb** » 24 Jan 2011 15:53

Sorry,but I also did not make myself clear....

The attribute FAMILY in my project is a user derfined field which allows me to put individuals into specific family groups.

This is used in conjunction with an ACCESS database which builds a website about people with a certain surname in a Lancshire village.

Generations does have a 'family number' but I did not have control of it so I used my own to tie in with the database. All individuals also have a unique reference number so I can track them across census years.

I may have done it differently in other circumstances, but the database came before the genealogical software and I didn't want to have to totally rebuild the database when I could use user-defined fields in the software.

Generations allows me to build a query by just putting the field name into a box , then setting 'equals,not equals' etc and the value in another box. Quick and easy!
However,I cannot save the query code permanently and FH is obviously a more powerful tool.

Now I have to get my head round customising reports in FH!

[eek]

Post by **tatewise** » 24 Jan 2011 16:57

Have you looked at the FH =RelationPool(%INDI%) function?

FH associates individuals with the same pool, if they are related to one another directly or indirectly, and arbitrarily assigns a number to each pool.

See FH Help > Family Historian Help > Contents > Using Family Historian > Advanced Topics > Understanding Functions > Functions (all) > RelationPool for details.

You can build FH Queries using =RelationPool(%INDI%) and you can configure a Column in the Individual Records Window to list the =RelationPool(%INDI%) number in asc/descending order.
I could provide more details if you are interested.

willowherb · Post by **willowherb** » 24 Jan 2011 17:52

Yes I had seen that as part of the 'Orphans' query.

But I don't want to have to change the database, especially as the number is arbitrarily allocated. I just haven't got the time at the moment to cope with the knock-on effect of changing the website

I think I am happier this way at the moment until I do a massive rewrite of the database (but that's a few months/years down the line)

Thanks for the tip that will be very useful!

Fiona

Family Historian User Group