Query - find text anywhere

gbrowne · Post by **gbrowne** » 05 May 2010 11:24

Can anyone please put me on the right lines please ?
I am creating a query that will enable me to search anywhere within any text field in the database for a specific text string. You might want to search for '@' if you wanted to find email addresses amongst various note fields, or 'Manchester' for all references to that city.

I can't quite see how to create a column expression that will match all text fields.

Any hints much appreciated.

Cheers,
Geoff.

ID:4550

PeterR · Post by **PeterR** » 05 May 2010 12:11

Try the Standard Query Contains Text.

gbrowne · Post by **gbrowne** » 05 May 2010 12:20

Hi Peter,

Thanks for that helpful response. That does indeed get me a list of the records that contain the search text, which is a good start, but I want to list the actual matched text phrase. Do you have any idea how do I get the result set to show the context of the match? So, if searching for email addresses say, I would search for '@' and it would return a result set listing the individual name, recordID and the phrase that contains the match.

Maybe it's not possible. I could do it externally using grep on the GEDCOM file, but it would be nice to keep it inside FH if possible.

Cheers,
Geoff.

PeterR · Post by **PeterR** » 05 May 2010 13:13

Geoff,
You can get a bit closer to what you would like, if you have FH v4, because then you can use a Fact query. The row filter would be the same as for the standard Individual Query using Add if =ContainsText(,['Enter Text'],EXT) is true. The columns could be:

The advantage of using =GetRecord(%FACT%) is that it also works for Family records, e.g. to identify a matching marriage fact. Of course, this only works if the text you are searching for occurs in a Fact linked to the Individual (or in a Source or Note linked to a Fact, etc.).

gbrowne · Post by **gbrowne** » 06 May 2010 07:55

Thanks Peter, again very helpful indeed.

I do have V4, but when I try to use those expressions it throws an error: 'Invalid expression for the current query type'. Any idea what I have done wrong please ?

Cheers,
Geoff.

Post by **hsw** » 06 May 2010 08:52

Have you set the query type to Fact? (on the General tab of the query window).

gbrowne · Post by **gbrowne** » 06 May 2010 11:29

Thanks CVG,

Yes, that was the problem - I'm on my way again now.
Thanks to all !

Cheers,
Geoff.

PeterR · Post by **PeterR** » 06 May 2010 12:39

You're welcome, Geoff.
I worked out a few refinements to the above Fact query, which you may care to incorporate:

The first (hidden) column provides sorting by surname (only available if using v4.1 or later), and the third column provides the relevant RecordId, which may be useful to distinguish different Individuals with the same name. You can of course double-click on the record name in the first visible column, to bring up the relevant property box.
For copying and pasting:

Code: Select all

=FieldText(FactOwner(%FACT%,1,MALES_FIRST),'INDI.NAME:SURNAME')
=RecordId(GetRecord(%FACT%))

gbrowne · Post by **gbrowne** » 08 May 2010 11:09

Peter,

That's great. Many thanks - that's shown me exactly what I needed.

Geoff.

Family Historian User Group