Search or Filter using the value of a field of a fact

RootsMagic
1

Hello,

I don’t find how to use the value of a field of a fact to dynamically filter a field of another fact?

I explain more: I don’t add an individual in my tree if I don’t have at least 1 official act containing him. I’m currently mainly working on Birth books from French Archives, but I also previously used Marriage and Death ones. Each act is added to the corresponding fact as a Citation with any parameters, including a normalized name containing the word Marriage_Archives or Death_Archives.
So now Birth facts, depending on the details I found in Marriage or Death acts (exact date or age), can have many sources. Because at least parents birth details are also often available in Birth acts of their children, I also link these children Birth acts to the Birth fact of their parents. Doing so, many individuals have now Birth_Archives citations associated to their Birth fact, but not their own Birth_Archives.

Now, I would like to isolate any individual who has not his own Birth act as a citation of his Birth fact, but I don’t find how to do this. For example, building a Group with criteria like ‘‘Birth source footnote contains Birth_Archives’’ + ‘‘Birth Source.Date is different from Birth.Date’’. The first criteria works perfectly and the second one would eliminate any individuals who already have their own Birth citation. But even the Date parameter is not accessible in Criteria (you find footnote, comment, name, ref#, but not date?). And even if it was available, how to pass Birth.Date as a dynamic value?

I asked many AI, including Microsoft Copilot or Google Gemini, but no useful answers despite their searches including this community, the wiki, etc.

Must I use SQLlite and a database analysis tool like Microsoft PowerBI Desktop? But the result will not be dynamic each time I will add a new Birth citation.

Many thanks in advance

Christian

2

@ClipOn I understand a lot of what you are saying BUT NOT sure I get

If you have not already attached a citation to it, why would the birth date and source date be different?

Any way I don’t know if this will help-- I attached a Birth-Archive Citation to 2 people–in one I have Ken born Jan 4 1834 in the citation/ source ( his birth fact says 1835 )— in the other I just have Barbie with no date in the citation/source but a full birth date in the fact…

When I use Birth source footnote contains Birth-Archives + Birth source footnote contains born-- Ken shows up on the list…
When I use Birth source footnote contains Birth-Archives + Birth source footnote
DOES NOT contains born- Barbie shows up on the list…

So I suggest maybe looking at someone who has been done and see if you used born-- if you used birth-- it won’t work --it will find both as you have Birth_Archives in there…

3

That kind of criterion is not supported in RM’s Advanced Search. It would be possible but quite difficult in SQLite because of the way source and citation data is stored. By “that kind of criterion”, I mean that using Advanced Search one database value can be compared with a constant value but not with another database value.

That being said, I’m not 100% sure what you are needing to accomplish, perhaps because I’m not familiar with the kinds of records which are called acts. But let me make a guess, using records with which I’m more familiar.

Suppose we have John Doe who is married to Jane Smith and suppose they have a child named William Doe. And suppose William has a birth certificate. The Birth fact for William should obviously have a citation for his birth certificate. His birth certificate will also have information about John and Jane. The birth certificates I’m familiar with would include John and Jane’s birth places but not their birth dates. Nevertheless, it’s likely that you would want to be sure that the citation for William’s Birth fact would also be linked to John’s Birth fact and to Jane’s birth fact because of the birth place information. Aside from the difference between a “certificate” and an “act”, is that what you are trying to check - that if there is a particular citation for a person’s Birth fact then the exact same Citation would also be used for the Birth fact for each parent?

Again, RM’s Advanced Search does not support this kind of query. It could be done with SQLite, and curiously this query would be considerably easier with SQLite than would be the comparison of the Birth Source Date with the Birth Date.

4

My English vocabulary is a little limited and you are right : act = certificate. I download these certificates from online governmental archives, read and describe this media in a citation of a source to the which I add the media and a webtag with the direct link to the online page.

In French certificates, there is very interesting data, including often in Birth ones the age of the parents. So I link the child’s Birth citation to the Birth parents with an estimated Birth year.

But Birth certificates contain also the exact time of the Birth of the individual. I enter this time in Description in English form that is including AM or PM . So this will be my filtering criteria: if Birth.Description doesn’t contain AM or PM, so the Birth certificate of THIS individual is not associated :grinning:

5

I’m American, but I have lived in Europe so I understand the frustration of dealing with a language which is not my first language.

It’s certainly possible in RM to filter on Description fields that include AM or PM. But I’m still not sure what your overall goal is with the filtering. If I understand correctly, you want to find children with a Birth fact which has a citation for a birth certificate and where the same citation for a birth certificate has NOT been used as a citation for the parents. Is that it?

6

No, it’s the opposite :grinning:

I want to find any people who has not his OWN Birth certificate (associated using a citation).

But this same person can already have some other Birth certificate(s) associated to his Birth fact because I already found birth date information in his children Birth certificates.

image
image1763×822 83.1 KB

In this example, I was able to estimate Birth date because I found the age of Jean Etienne in the 3 Birth certificates of his 3 children. (forget 3 first citations from FamilySearch that I’m cleaning).

No other detail is entered for his Birth fact, not even the Place because not sure for now.

1 Like
7

Now that I understand your process a little better, I think I can make some suggestions that only involve RootsMagic without needing to involve SQLite.

I’ll be using examples from my own database because I’m familiar with the data. Hopefully, it will be easy to translate the examples from my data to your data. In particular, I live in Tennessee and Tennessee had a series of birth certificates and death certificates that ran from 1908 to 1912. Prior to that, there were no statewide birth or death records of any kind, although there were local records in many places. These records are all online. So time to time, I work on a project to find all the 1908 through 1912 birth and death records for anybody in my database that might match.

But let’s start with a special case. Suppose I wanted to find everyone who was born and died in Tennessee in the same year between 1908 and 1912. A search criteria that you might want try might look something like the following.

Birth > Place > Contains > Tennessee
AND
Birth > Date > Is After > 1911
AND
Birth > Date > Is Before > 1913
AND
Death > Date > Equals > Birth Date

Except that won’t work because Death > Date > Equals has to be a date like 1912 or 1913, etc. It can’t be Birth Date. So we take a different approach.

Birth > Place > Contains > Tennessee
AND
Birth > Date > Equals > 1908
AND
Death > Date > Equals > 1908

That only gets us 1908, but we could then repeat for 1909, 1910, 1911, and 1912. It’s really no extra work to do it that way. The reason for doing it this way is that it provides a way to make the Birth date and the Death date the same without comparing them directly. This isn’t the same as your need to compare information from the fact without information from the citation. But it’s a very general concept.

Now we switch to our real problem, and as I said I have actually worked on this one for a while. Let’s make two groups.

----- Group #1, born in Tennessee in 1908.

Birth > Place > Contains > Tennessee
AND
Birth > Date > Equals > 1908

----- Group #2, has a citation from the 1908 birth records

Birth > Place > Contains > Tennessee
AND
Birth > Source > Footnote Contains > 1908

I need to say a little more about Footnote Contains > 1908. That would work in my database because any other citations I might have for the same birth fact would not include the string “1908” in the footnote sentence. You may really have to look at your footnotes to see what string would work. Here are additional comments.

  1. Footnote Contains is really the only parameter that will give you what you want. There is no way from within RM’s search screen to get at individual fields in the definition of the citation. And the Citation Name is not a searchable field and it should be. I think that surely this is just an oversight rather than a design flaw.
  2. You really can’t use Footnote Does Not Contain > 1908 at this point because the Birth fact might have more than one citation and if so that clause would pick up one of the other citations.
  3. You can’t really can’t have two different Footnote Contains lines in the search criteria to pick up additional data for the same reason that their might be more than one citation for the birth fact and additional Footnote Contains lines might pick up one of the other citations. You can certainly do a Footnote Contains on a longer string than just my 1908, but it needs to be a single contiguous string…

We are now ready to complete the search. We make one more group.

----- Group #3, born in Tennessee in 1908 and Footnote Does Not Contain 1908

Select > Group #1
Unselect > Group #2

I emphasize that I think this is only way to do it. I really don’t think there is a way to do this without making three groups in all because there is not really a way to do a direct search for the Footnote Does Not Contain > 1908 criterion because there could be more than one citation. I emphasize that you would need to examine your footnotes and come up with a better string than my Contains >1908 that fits with your data. I emphasize that you have to do one year at a time for the reasons stated above.

Make sure the groups involved are all Rules Groups and not Simple Groups.

I have tested this in my database with my data and it works. You will simply have to adapt it to your data. The only issue that might be tricky is the string to use with Footnote Contains.

Sometimes with this kind of search, a complete state such as Tennessee is too broad for what I’m trying to accomplish. If so, then I change Tennessee the search string to something like Jefferson County, Tennessee.

8

Many thanks for this very long answer and this hard work!

I already used your proposal of 2 groups and their differences, and so I was able to isolate any people who had no Birth citations at all. I’m working on such lists today.

The fact that we cannot do more detailed search criteria on Citations and that the search applies to ALL the footnotes of ALL citations of a fact is really a blocking point. And the best of all would be to be able to use a database value as criteria parameter. Dream…

When my tree will be clean, I will start using SQLlite with PowerBI Desktop. For now, I only have connected the RM database and browsed the data model.

9

You probably already found it, but be sure to check out SQLite Tools for RootsMagic And you probably already have found this issue as well. If you get very deep into using SQLite with RM you will discover that RM uses a proprietary collating sequence called RMNOCASE for any of its text columns that are indexed (or maybe for all its text columns, indexed or not, I can’t remember for sure right this second). Queries involving any of those columns will require downloading and installing a fake RMNOCASE collating sequence into your PowerBI Desktop environment. There is more information about that on the SQLite Tools for RootsMagic site. It’s best to post questions about using SQLite with RM on that site rather than on this forum.

10

One more time, so many thanks for all these answers and tips :ok_hand: