*Query on a parameter that can be present or not

Questions regarding use of any Version of Family Historian. Please ensure you have set your Version of Family Historian in your Profile
Post Reply
User avatar
ColeValleyGirl
Megastar
Posts: 1202
Joined: 28 Dec 2005 22:02
Family Historian: V6.2
Location: Cirencester, Gloucestershire
Contact:

Query on a parameter that can be present or not

Post by ColeValleyGirl » 08 Aug 2019 15:51

I suspect I know the answer to this, but the hive mind may know better.

I would like to create a query that will prompt a user for a value (for some labelled text, say "Day"), but allow the user to not specify the parameter and default to items with all values of Day, including none.

Is there a way to do this in a query?

User avatar
Jane
Site Admin
Posts: 7676
Joined: 01 Nov 2002 15:00
Family Historian: V6.2
Location: Somerset, England
Contact:

Re: Query on a parameter that can be present or not

Post by Jane » 08 Aug 2019 16:09

You could do something like this
2019-08-08_170826.jpg
2019-08-08_170826.jpg (11.04 KiB) Viewed 287 times
So the user can enter (or can be defaulted to *all)
Jane
My Family History : My Photography "Knowledge is knowing that a tomato is a fruit. Wisdom is not putting it in a fruit salad."

User avatar
ColeValleyGirl
Megastar
Posts: 1202
Joined: 28 Dec 2005 22:02
Family Historian: V6.2
Location: Cirencester, Gloucestershire
Contact:

Re: Query on a parameter that can be present or not

Post by ColeValleyGirl » 08 Aug 2019 17:19

Thanks, Jane.

User avatar
ColeValleyGirl
Megastar
Posts: 1202
Joined: 28 Dec 2005 22:02
Family Historian: V6.2
Location: Cirencester, Gloucestershire
Contact:

Re: Query on a parameter that can be present or not

Post by ColeValleyGirl » 09 Aug 2019 10:20

Still working away at this, trying to generate a query that takes multiple parameters, each of which can be specified or default to all.

My logic is (roughly):

Add if Parm1 is matched.
Add if Parm1 is *all.
Add if Parm2 is matched.
Add if Parm2 is *all.

... Until a result set has been created that match ONE of the specified criteria. Now whittle them down to a list of results that match all of the specified criteria.

Exclude unless (Parm1 is matched or Parm1 is all)
Exclude unless (Parm2 is matched or Parm2 is all)

... Until there is a result set that meets all of the criteria.

Two questions:

1. Is my logic OK or is there a better way to do it? (If my thinking is correct, the first stage is necessary to generate the Parameter names/values forthe second stage).
2. How do I code the second set of row criteria (the exclusions)? I'm OK with simple expressions but this has me foxed.

User avatar
ColeValleyGirl
Megastar
Posts: 1202
Joined: 28 Dec 2005 22:02
Family Historian: V6.2
Location: Cirencester, Gloucestershire
Contact:

Re: Query on a parameter that can be present or not

Post by ColeValleyGirl » 09 Aug 2019 11:46

OK, I think I've solved it:
Screenshot 2019-08-09 12.44.23.jpg
Screenshot 2019-08-09 12.44.23.jpg (53.3 KiB) Viewed 213 times
However, I can't have a default value for the parameters doing it this way -- is there any way round it?

User avatar
Jane
Site Admin
Posts: 7676
Joined: 01 Nov 2002 15:00
Family Historian: V6.2
Location: Somerset, England
Contact:

Re: Query on a parameter that can be present or not

Post by Jane » 09 Aug 2019 12:20

You could add a "dummy" line at the top for each search value for example
Add If =Text(%INDI.NOTE[1000]%) matches ['Status']
with your default value
Jane
My Family History : My Photography "Knowledge is knowing that a tomato is a fruit. Wisdom is not putting it in a fruit salad."

User avatar
tatewise
Megastar
Posts: 16203
Joined: 25 May 2010 11:00
Family Historian: V6.2
Location: Torbay, Devon, UK
Contact:

Re: Query on a parameter that can be present or not

Post by tatewise » 09 Aug 2019 15:41

Try the following:
Add if =Text(GetLabelledText(%FACT.NOTE2%,"Status:") . "*") contains ["Status"]
Exclude unless =Text(GetLabelledText(%FACT.NOTE2%,"Priority:") . "*") contains ["Priority"]
Exclude unless =FactLabel(%FACT%) matches 'Task'

The defaults work, but you have to tolerate using contains instead of matches so that wildcard * works.
But if the values are drawn from a constrained set of text then that should be Ok.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry

User avatar
ColeValleyGirl
Megastar
Posts: 1202
Joined: 28 Dec 2005 22:02
Family Historian: V6.2
Location: Cirencester, Gloucestershire
Contact:

Re: Query on a parameter that can be present or not

Post by ColeValleyGirl » 09 Aug 2019 15:48

Unfortunately, the values are unconstrained so Jane's idea is a better fit in this instance.

I'm now trying to include an optional parameter (keywords) in the fact's value; and another set of keywords anywhere in the fact's note.

After which I shall be trying to do comparison options between a data parameter and a date from GetLabelledText...

I don't believe I'll be able to include an optional source parameter or a repository one, which would complete the set.

And yes, I might be able to do all this from a plugin, but it may be more convenient for users if they have a query to use (which the plugin will generate, based on configuration options they specify ).

[Edited to make sense -- must replace this keyboard!]

Post Reply