* Expressions in Custom Query

[Gowermick]

I am still getting familiar with queries, and have modified a few quite successfully.

I now wish to customise the 'Ancestor' Query, so that it always runs with my 'FileRoot' as the "Starting Person" without prompting.

A lot of the columns include ["Starting Person"] in their expression. Which I understand to be a Parameter called 'Starting Person', obtained from a prompt (it is enclosed in [ ] ).

By changing this to "FileRoot", I thought it would suffice, and query would run without prompting, but whenever I tried to change it, I got following error:-

"The Expression entered is not recognised as a valid function, or some parameters were entered incorrectly"

e.g. What is correct syntax for modifying the expression "=RelationCode(["Starting Person"],,GENS_UP,1)" to use "FileRoot" instead of ["Starting Person]"?

The answer may be staring me in the face, but for the life of me I can't see what I'm doing wrong.

Any suggestions?

[tatewise]

Mike, checkout how_to:understanding_functions|> Understanding Functions where it explains Functions need an = sign (except when used within another Function), e.g. =RecordId(), and may have parameters withing the (parentheses).

For full details see the Family Historian Help > Using Family Historian > Advanced Topics > Understanding Functions and under Functions (all) you will find FileRoot with the example =Relationship(FileRoot()).

Whereas, ["Label"] is the format for a parameter prompt.

So within =RelationCode(...) since it is within another function, the format is FileRoot():
=RelationCode(FileRoot(),,GENS_UP,1)

I suspect you had overlooked the () parentheses needed on every Function even if it has no parameters.

[PeterR]

You can use the following two filters on the Query Rows tab (by using the General tab at the bottom):

Add if =IsAncestorOf(,FileRoot()) is true
Add if =IsSameItem(,FileRoot()) is true

[Gowermick]

PeterR,

I had just worked that out when I saw your reply.

I came up with a slight variation :- =IsAncestorOf(%INDI%,FileRoot())

It works!!

Thanks

[Gowermick]

Well, almost right.

The resultset didn't include FileRoot()

I got round it by creating a list call 'Root' and added individual to it.
I then used Add if =IsInList(%IND%,"Root") and that works, but surely there must be a better way?

What expression can I use in the Add if.. field? in order to include FileRoot in the ResultSet?

Any suggestions?

[Gowermick]

Mike,

My reply to your suggestion I was missing the ( )'s seems to have disappeared into the ether. Sorry about that.

You're quite right, it was the missing brackets that was the problem.

Thanks

[PeterR]

@Gowermick – Please see my earlier reply, above, where the 2nd row filter is there to include the file root.

[Gowermick]

PeterR,

Sorry, teach me to read your reply properly.

Thanks again

[tatewise]

Guys, assuming this is an Individual Query there is a slightly neater solution.
On the Rows filter tab, on its General tab enter Add if =IsSameItem(FileRoot()) is true.
Then on Relations tab, choose Add if an Ancestor of Anyone in the current result set.

By changing the General tab Expression to choose someone else, needs no change to the Relations tab to include all their Ancestors.

[Gowermick]

Mike,

I must be learning something, I actually understood where you're coming from on your last suggestion.

Mike