Relationship Field for Custom Reports

I would like to have a field to choose on my custom reports that shows the relationship to the root person (or other selected person, for that matter).

I have designated a group of family members that are buried in a particular cemetery. I made a nice custom report to take with me to the cemetery that includes the burial place description which gives me the plot location.

At first, I selected only the closest relatives for this group. However, after visiting the cemetery, I noticed many distant relatives nearby and wanted to include those names in my group as well. Without having my database handy during a cemetery visit, It would be really helpful to include a field in my report that shows my relationship to each name in the group.

Roots Magic has relationship information, but there seems to be no way to access it for my report. Or, is there? Any ideas for me?

1 Like

You are correct that there is no way from within RM to access the relationship information except to eyeball one person at a time on the RM screen. Relationship is not a field in Custom Reports nor on People List View. Relationship is not available for searching or for making groups or for color coding. Unless these sort of things appear as future enhancements, the only real possibility for what you need is to produce reports with SQLite.

For example, I have recently written an SQLite query for my own use that color codes everyone in my database depending on certain relationships. My particular query is more generic than what you need. For example, everyone in my database to whom I’m related is color coded red and you need more detail than that. For example, you need to know about second cousins once removed vs. granduncles. But the concept is the same in that you need better access to relationship information in RM.

I go on to color code spouses of red people as green if they are not already color coded, spouses of green people as blue if they are not already color coded, parents of green people as purple if they are not already color coded, children of purple people as brown if they are not already color coded, and spouses of purple people as aqua if they are not already color coded. Everybody else is color coded yellow. It may sound like an overly convoluted scheme, but it meets my needs very well. And notice how important to my query is the condition “if they are not already color coded”. If that condition is not included at every step, the color codes step on each other.

I would love to be able to accomplish this color coding from within RM, but it’s simply not possible. In addition to not being able to test for relationship like third cousin twice removed from within RM, it’s also not possible to test for spouse of, parent of, or child of from within RM. It’s pretty easy to test for all these things from SQLite.

I’m not at the RM computer but the Kinship List report may give you the relationships to the reference person in Set Relationships and filtered for a named group.

Thanks for verifying there is no field that will help me, Jerry. Using SQLite is a bit beyond me at the moment. I’ll have to make of study of it soon.

Tom, I was ever so hopeful that an additional report would be just the thing. Unfortunately, I cannot figure out how to filter for a group. Printing everyone in the database is just overkill.

Am I missing something?

Right. I’d forgotten that it is one of the List reports that offers no Select people dialog. Other than SQLite or exporting to some other software that can give you the info you want, a workaround with RM might be to export your group (or supergroup) to another RM database and run the Kinship List from it. You could combine it with your custom report in Excel or other spreadsheet software if you have structured the Name fields in both the same way and with the RIN option. On your custom report sheet, add a column for Relationship and use the VLOOKUP() function to populate it from the Kinship sheet.

Thanks, Tom. I just started working on the Excel option. Fingers crossed.

I am in the process of moving programs and interestingly enough this query took about 2 minutes to write. About another minute or so and I could tweak it to allow me to pick a specific cemetery and probably restrict how deep the cousinships go…and it can’t be done in RM.

It is my opinion that bit likes this would have gained far more favor with their users than screwing with the UI and making it a pain to use.

1 Like

Oh dear. In combining my Kinship Report in Excel with an Excel list of everyone in my tree, I have hit a big problem.

I have an unequal number of “everyone” on the Kinship Report (954) and “everyone” on my custom report (1010). When I do a “Count Trees”, I have only one tree and the number of records is 1010 like on my custom report.

Why the difference? What is the Kinship Report not picking up?

Did you eyeball the two lists to see who is missing? Knowing that might allow you to figure out why. For example, are some of them missing persons actually second spouses? If so, they really aren’t kin. Do you maybe have some people such as parents of a spouse that would not be kin? For example, my wife’s parents wouldn’t show up.

This may be a lot of work for some , and certainly redundant in some cases. But for those that are more experienced in RM than I, I ask the question: Would it be worthwhile to create a new fact, such as “relationship” then slowly add the relationship (third cousin" as an example) in the desired individual(s)’ field. Then this fact would be available to select to “Add” in a custom report?? What is the groups thoughts? Worth it?

I’m a sample size of one, and I wouldn’t find it to be worth it. But if it’s important to you, I would say go for it.

It certainly it would work just fine. In addition to custom reports, it would work for People List View, it would work for color coding, and it would work for making groups. For example, you could color code all your second cousins as purple or you could make a group of all your third cousins. I would store the relationship in the Description field.

I actually do have a similar but more limited relationship fact called a Parents fact which simply lists a person’s parents. I suppose you might ask why you would need such a fact because it’s so easy to see a person’s parents in RM. There are two reasons.

  1. It seems to me that there isn’t a good place in RM to attach a citation that documents the identity of a person’s parents. There already is a Parents line in RM’s Edit Person screen and there is a slot there where citations to document a person’s parents could be attached. But that approach doesn’t really work in practice. Citations attached at that point aren’t actually associated with the person at all. They are only associated with the parents and only as a part of the couple record for the parents. It’s like documentation for the couple’s marriage or divorce, not documentation for the identity of the couple’s children.
  2. It allows parents to be listed in Narrative reports. Parents are listed in other reports, including Individual Summary reports and Family Group Sheets. But they are not listed in Narrative reports.

I actually enter the parents information into my Parents fact twice. First, I enter the names of the parents into the Description field of the Parents fact. Then, my sentence template for the Parents fact does not include the Description field. Instead, I share the Parents fact with each of the parents in turn using Parents as the role that is shared. My sentence for the Parents fact then references the [Parents] variable. The sentence could look exactly the same in reports whether the sentence is created with the [Desc] variable for the Description field or is created with the [Parents] variable. But text from the [Desc] variable is just text and does not create index entries for the Person Index at the end of the report. The [Parents] variable does create index entries for the Person Index at the end of the report.

Given that I am sharing the Parents fact in this manner, the reason I even bother putting the parents’ names into the Description field is so the information will not be lost if my data is transferred to software that does not support RM’s shared facts.

1 Like