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?
* Query on a parameter that can be present or not
- ColeValleyGirl
- Megastar
- Posts: 5465
- Joined: 28 Dec 2005 22:02
- Family Historian: V7
- Location: Cirencester, Gloucestershire
- Contact:
- Jane
- Site Admin
- Posts: 8508
- Joined: 01 Nov 2002 15:00
- Family Historian: V7
- Location: Somerset, England
- Contact:
Re: Query on a parameter that can be present or not
You could do something like this
So the user can enter (or can be defaulted to *all)
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."
My Family History : My Photography "Knowledge is knowing that a tomato is a fruit. Wisdom is not putting it in a fruit salad."
- ColeValleyGirl
- Megastar
- Posts: 5465
- Joined: 28 Dec 2005 22:02
- Family Historian: V7
- Location: Cirencester, Gloucestershire
- Contact:
Re: Query on a parameter that can be present or not
Thanks, Jane.
Helen Wright
ColeValleyGirl's family history
ColeValleyGirl's family history
- ColeValleyGirl
- Megastar
- Posts: 5465
- Joined: 28 Dec 2005 22:02
- Family Historian: V7
- Location: Cirencester, Gloucestershire
- Contact:
Re: Query on a parameter that can be present or not
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.
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.
Helen Wright
ColeValleyGirl's family history
ColeValleyGirl's family history
- ColeValleyGirl
- Megastar
- Posts: 5465
- Joined: 28 Dec 2005 22:02
- Family Historian: V7
- Location: Cirencester, Gloucestershire
- Contact:
Re: Query on a parameter that can be present or not
OK, I think I've solved it:
However, I can't have a default value for the parameters doing it this way -- is there any way round it?
However, I can't have a default value for the parameters doing it this way -- is there any way round it?
Helen Wright
ColeValleyGirl's family history
ColeValleyGirl's family history
- Jane
- Site Admin
- Posts: 8508
- Joined: 01 Nov 2002 15:00
- Family Historian: V7
- Location: Somerset, England
- Contact:
Re: Query on a parameter that can be present or not
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
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."
My Family History : My Photography "Knowledge is knowing that a tomato is a fruit. Wisdom is not putting it in a fruit salad."
- tatewise
- Megastar
- Posts: 28341
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Re: Query on a parameter that can be present or not
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.
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
- ColeValleyGirl
- Megastar
- Posts: 5465
- Joined: 28 Dec 2005 22:02
- Family Historian: V7
- Location: Cirencester, Gloucestershire
- Contact:
Re: Query on a parameter that can be present or not
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!]
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!]
Helen Wright
ColeValleyGirl's family history
ColeValleyGirl's family history