* Relationship expression for a query
Relationship expression for a query
Further to my recent posting asking for help with a relationship expression I would now like to use a query to find all of my DNA facts and list them together with the calculated relationship.
I do have this working but was hoping to streamline it a little more.
My query columns are:
1. %INDI%
2. %INDI._ATTR-DNA_MATCH_R[1]%
3. =Relationship(["Individual"],%INDI%,TEXT,1)
4. =RelationCode(["Individual"],%INDI%,GENERATION,1)
and the query is searching for where 2. is not null
The whole query actually works OK and does what I want except it pops up the "Individual" selection box when it is run.
I would prefer it if I could skip this selection stage and explicitly define myself as the individual, if that is possible.
I have tried various combinations of the previous suggestions i.e. {=Relationship(Record(9999,'I'),%INDI%,TEXT,1)} and {=Relationship( Record(9999,"I"), %CUR_PRIN%, TEXT, 1 )} but can't seem to find the correct syntax.
Again any help is appreciated.
I do have this working but was hoping to streamline it a little more.
My query columns are:
1. %INDI%
2. %INDI._ATTR-DNA_MATCH_R[1]%
3. =Relationship(["Individual"],%INDI%,TEXT,1)
4. =RelationCode(["Individual"],%INDI%,GENERATION,1)
and the query is searching for where 2. is not null
The whole query actually works OK and does what I want except it pops up the "Individual" selection box when it is run.
I would prefer it if I could skip this selection stage and explicitly define myself as the individual, if that is possible.
I have tried various combinations of the previous suggestions i.e. {=Relationship(Record(9999,'I'),%INDI%,TEXT,1)} and {=Relationship( Record(9999,"I"), %CUR_PRIN%, TEXT, 1 )} but can't seem to find the correct syntax.
Again any help is appreciated.
- johnmorrisoniom
- Megastar
- Posts: 904
- Joined: 18 Dec 2008 07:40
- Family Historian: V7
- Location: Isle of Man
Re: Relationship expression for a query
For the relationship column try this
=Relationship(FileRoot())
This is assuming that you are the file root.
=Relationship(FileRoot())
This is assuming that you are the file root.
Re: Relationship expression for a query
I have seen that reference in the help file and got it to work unfortunately the file root is something I use a lot moving it around to the head of whichever family group I am working on at the time.
I was rather hoping I could find a way to anchor it to myself instead using my INDI number somehow.
I was rather hoping I could find a way to anchor it to myself instead using my INDI number somehow.
Re: Relationship expression for a query
I think you have an error in your syntax. I just created a simple query as follows
Col1: %INDI%
Col2: =Relationship(Record(1,"I"),,TEXT,1)
If 1 is the normal record ID of your root, this will always produces a list of individuals and their relationship to your 'real' root, regardless of you changing the root whilst you work.
Changing the Record ID to another valid Record ID, will produce a resultset with the relationships adjusted accordngly
NB An Invalid Record ID will result in an empty relationship column in your resultset
Col1: %INDI%
Col2: =Relationship(Record(1,"I"),,TEXT,1)
If 1 is the normal record ID of your root, this will always produces a list of individuals and their relationship to your 'real' root, regardless of you changing the root whilst you work.
Changing the Record ID to another valid Record ID, will produce a resultset with the relationships adjusted accordngly
NB An Invalid Record ID will result in an empty relationship column in your resultset
Mike Loney
Website http://www.loney.tribalpages.com
http://www.mickloney.tribalpages.com
Website http://www.loney.tribalpages.com
http://www.mickloney.tribalpages.com
Re: Relationship expression for a query
Mike
The query was working and producing the results expected, it was just prompting me for the individual each time it was run.
However I replaced
=Relationship(["Individual"],%INDI%,TEXT,1) with your suggestion, in my case =Relationship(Record(30,"I"),,TEXT,1) because I am INDI 30.
I also did the same for the next line of the query that produces the number of generations =RelationCode(Record(30,"I"),,GENERATION,1) and it all works beautifully.
Thanks for your help
The query was working and producing the results expected, it was just prompting me for the individual each time it was run.
However I replaced
=Relationship(["Individual"],%INDI%,TEXT,1) with your suggestion, in my case =Relationship(Record(30,"I"),,TEXT,1) because I am INDI 30.
I also did the same for the next line of the query that produces the number of generations =RelationCode(Record(30,"I"),,GENERATION,1) and it all works beautifully.
Thanks for your help
- tatewise
- Megastar
- Posts: 28414
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Re: Relationship expression for a query
This is related to your recent posting Relationship expression (20881).
The syntax is =Relationship( Record(9999,"I"), %INDI%, TEXT, 1 ) as before but no { curly brackets } and %INDI% not %CUR_PRIN% in this context.
Where 9999 is your Record Id number.
Ditto for =RelationCode( Record(9999,"I"), %INDI%, GENERATION, 1 )
The syntax is =Relationship( Record(9999,"I"), %INDI%, TEXT, 1 ) as before but no { curly brackets } and %INDI% not %CUR_PRIN% in this context.
Where 9999 is your Record Id number.
Ditto for =RelationCode( Record(9999,"I"), %INDI%, GENERATION, 1 )
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
Re: Relationship expression for a query
The help file for the Relationship function shows parameters 1 & 2 as references to an individual but does not state parameter 2 is optional.
The suggestion from Mike Loney omits the second parameter yet still works. I can see no difference in the query output if the parameter is present or missing.
Perhaps the help file needs an update.
The suggestion from Mike Loney omits the second parameter yet still works. I can see no difference in the query output if the parameter is present or missing.
Perhaps the help file needs an update.
- tatewise
- Megastar
- Posts: 28414
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Re: Relationship expression for a query
Yes, it is a bit tricky. Function parameters such as that are typically dependent on context.
In this case, the Query type is Individual, so any blank parameter that refers to an Individual record will default to %INDI%.
However, as you found in the Sentence Template context that does not work.
Similarly, in a Query of type Fact such parameters cannot be left blank.
In this case, the Query type is Individual, so any blank parameter that refers to an Individual record will default to %INDI%.
However, as you found in the Sentence Template context that does not work.
Similarly, in a Query of type Fact such parameters cannot be left blank.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
Re: Relationship expression for a query
Actually is was FH itself that auto-corrected/ tidied up my attempt at the expression. It wasn't a conscious effort on my part
Mike Loney
Website http://www.loney.tribalpages.com
http://www.mickloney.tribalpages.com
Website http://www.loney.tribalpages.com
http://www.mickloney.tribalpages.com