Counting descendant tags

[morosentity]

I was wondering whether someone might be able to help me come up with a script, a calculation, a complex function, or whatever is necessary to summarize the number of descendant tags for each record in my tags table?

 

I’m relatively new to FMP, and I’ve been using it to organize my notes for historical research. The basic tables in my DB are a notes table, a sources table, and a tags tables.

 

The tags table has a title field, an id field, and a parent_tag id field, and there is a parent_tag table occurrence based on the tags table and related to it via its id (tags::parent_id = parent_tags::id).

 

I have already created a simple calculation that Counts the children (first generation) of each tag. However, I can’t figure out how to calculate the total number of descendants (all generations, such as grandchild tags, and great-grandchild tags) for a given tag record.

 

It may be important to point out that I also have a calculation for ordering the genealogy of related tags, which I’ve called “c.ancestors”

 

If ( not IsEmpty(parent_id) ; parent_tags::c.ancestors ) & "|" & title & "|"

 

This has proven useful for sorting my tags first by ancestor tags and then by descendant tags and also for devising other scripts, such as a script for collapsing a series of related tags (i.e. omitting those tag records that have a common parent tag).

 

Here’s a sample run (comma delimited) of tags along with the values for the c.ancestors calculation:

 

Tag title, id, parent_id, parent title, # of children (1^st generation) tags, c.ancestors

…

Ted James, 2134, 1031, individual, 0, |agents and subjects||individual||Ted James|

 

Test Parent 1, 5505, 1031, individual, 2, |agents and subjects||individual||Test Parent 1|

 

Test Child 01, 5506, 5505, Test Parent 1, 2, |agents and subjects||individual||Test Parent 1||Test Child 01

 

Test Grandchild 1, 5508, 5506, Test Child 01, 1, |agents and subjects||individual||Test Parent 1||Test Child 01||Test Grandchild 1|

 

Test Great-grandchild 1, 5509, 5508, Test Grandchild 1, 0, Test Grandchild 1, 5508, 5506, Test Child 01, 1, |agents and subjects||individual||Test Parent 1|Test Child 01||Test Grandchild 1||Test Great-grandchild 1

 

Test Grandchild 2, 5514, 5506, Test Child 01, 0, |agents and subjects||individual||Test Parent 1||Test Child 01||Test Grandchild 2

 

Test Child 02, 5507, 5505, Test Parent 1, |agents and subjects||individual||Test Parent 1||Test Child 02

 

Test Grandchild 03, 5511, 5507, Test Child 02, 1, |agents and subjects||individual||Test Parent 1||Test Child 02||Test Grandchild 03

 

Tonya Doe, 4336, 1031, individual, 0, |agents and subjects||individual||Tonya Doe|

 

Any advice or suggestions would be much appreciated!

[comment]

Why don't you do the same thing in the opposite direction, i.e. given a self-join relationship where:

 

Tags::TagID = ChildTags::ParentID

 

define a calculation field cCountDescendants (result is Number) =

Count ( ChildTags::ParentID ) + Sum ( ChildTags::cCountDescendants )

(evaluate from the context of Tags)

 

 

Note that cascading calculations like these are likely to eventually break when the number of levels exceeds some limit. I have never tested what that limit is.

[morosentity]

I'm sorry for taking so long to respond. This appears to have worked like a charm! Thank you very much.

 

I guess I still don't entirely understand how Sum works (I read through the FMP Help description of it ...), which explains why I didn't think to come up with this simple yet elegant solution.