* Query building

[Ron Melby]

I have read some on the queries. I dont think it is much like SQL, I dont see much in the help nor the KB. I probably dont know what I am looking for.

Here is the situation
I am trying to get a list of everyone who has a certain event. It would be nice if that name were variable at run time.

I would like to search for say; OBIT, or BIO or SSDI or TODO in different runs.
I could change it for any run hard coded. Nice to; but not need to have it variable.

But I dont understand where in the form, or how in the form to do this, i.e:

Select [ID], [NAME] from [INDI] where [EVENT] = :EVENTNAME (variable)
or alternatively:
Select [ID], [NAME] from [INDI] where [EVENT] = 'OBIT' (hard coded per run)
or its equivalent.

There must be a way to accomplish this. Thank you in advance for your help.

[Jane]

I presume you have watched the basic video in the knowledge base for building queries and have reviewed some of the sample queries there.

Use the All Facts Query as a base, open it and then use the Gear icon button to Save as Custom Query.

Now you can edit it.

Go to the Rows tab and add a new filter as shown below

2017-11-23_153647.png (4.72 KiB) Viewed 6244 times

You can then run the query and enter part or all of the Fact Label eg Obituary or To Do (note this is the FactLabel not the data reference)

[Ron Melby]

I have a fact definition
Name OBIT
Label OBIT

in the file I have more than 100 instances
1 EVEN
2 TYPE OBIT
yadda yadda yadda blah blah blah

I ask for columns
%INDI%
%INDI.NAME%
Selection criteria is:

add if =FactLabel(%FACT%) contains ["FactLabel"] --parameter is checked
the box comes up and OBIT <<<<exactly like that, is filled in the value after the run.
I get no rows.

[tatewise]

Ron, open the General tab of the Query and inspect the Query Type.
I suspect it says Individual and not Fact as advised by Jane earlier.

That =FactLabel(%FACT%) filter does not work in an Individual type Query.

Checkout how_to:creating_a_query|> Creating a Query and in this context the how_to:creating_a_query:general_tab#query_window_general_tab|> Query Window ~ General Tab.

[Ron Melby]

The event type got me output, I though about that at the time I went to originally write the query, but then I lose what I am looking for. That got me some of the output I am looking for but I do not have ability to get %indi% and %indi.name% which on the events that are hosed up will not lead me back to the event on the indi. I click on those, nothing happens, so I can see that there is a problem, but there is no way to solve it or find it.

I do not see anyway to join the logicals which is the convenient way to think of it for me.

here is my issue file wise, I can see these (or imagine what they are since the fields are blank on the ones that are hosed up.

I have these general cases:
1 EVEN
2 TYPE OBIT
2 SOUR @S2323@
3 PAGE 5, COL 1
2 NOTE yadda yadda yadda blah blah blah
^^^^excellent can get at them from query

1 EVEN
2 TYPE OBIT
2 SOUR Dickenson Press
3 PAGE 5, COL 1
2 NOTE yadda yadda yadda blah blah blah
^^^^ a problem that I need to find where I do not have these sources in my source pointers cannot get at them from query

1 EVEN
2 TYPE OBIT
2 NOTE Dickenson Press
3 PAGE 5, COL 1
2 NOTE yadda yadda yadda blah blah blah
^^^^ a problem that I need to find where I do not have these sources in my source pointers cannnot get at them from query


1 EVEN
2 TYPE OBIT
2 NOTE Dickenson Press --
3 CONT yadda yadda yadda blah blah blah
^^^^these I can find ok, and they are trivial can get at them from query

so is there a way?

[Jane]

You are making the whole process too complicated, Queries are not SQLs. Gedcom is a "tree" format like modern node datastorage, not table based databases. All your notes shown in the examples are against the Fact and not the source.

Just add an extra column from the Left panel for Note, the reference is %FACT.NOTE2[1]%

As Mike says make sure you have a Fact Query.

I have attached the one I wrote, does that work for you, try searching for Birth?

[tatewise]

Also checkout fhugdownloads:queriesfact|> Downloads and Links ~ Query Type: Fact most of which should give you ideas for what is needed on the Columns tab and the Rows tab.

You are correct that you cannot use %INDI% and %INDI.NAME% directly, but you can use the following:
=GetRecord(%FACT%) supplies the Individual or Family record holding the Fact
=FactOwner(%FACT%,1,MALES_FIRST) supplies Individual record or 1st Spouse.
=FieldText(FactOwner(%FACT%,1,MALES_FIRST),'INDI.NAME') ditto for Name.
etc...

[Ron Melby]

Thanks. It is not an optimal solution in that it wont take me directly to the event if it is faulty. But I can make that work.

[tatewise]

Ron, the Fact listed in the Result Set will take you directly to its entry in either the Facts tab or the All tab.

( I was simply responding to your comment about not being able to use %INDI% and %INDI.NAME% )

As an experiment, please run the View > Standard Queries > All Facts standard Query.
In the Result Set in the Event/Attribute column, right-click any item and choose one of the two options.
That will open the Property Box with the chosen Event/Attribute selected.

Your custom Query with similar columns will behave exactly the same way.
All you need is a column defined as %FACT%.