Jump to content

Recursive field (Number of children)

This topic is 2739 days old. Please don't post here. Open a new topic instead.

Recommended Posts



Let say I want to create a blog in which user can add comments. Each comment can also be commented.


I want to create a field that calculates the total number of comments a blog or a comment has.


Here is the tables structure


Blogs (id, article, c_nbComments...)

Comments (id, id_blogid_parentComment, comment, c_nbComments, ...)


Let say I have two groups of Anchor-buoy



CMT__COMMENTS --< cmt_COMMENTS__children


So how can I calculate the total number of comments for each blog or comments?





Link to post
Share on other sites
  • Replies 6
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

In FM 12, you'd be able to whip up a SQL query in a case statement and have at it fairly easily. In FM 11, if your calculation is being stored in c_nbComments, do this:


In Blogs:

Count(bl_COMMENTS::id) + Sum(bl_COMMENTS::c_nbComments)


In Comments (evaluate this from the context of CMT_COMMENTS): 

Count(cmt_COMMENTS__children::id) + Sum(cmt_COMMENTS__children::c_nbComments)


That will give you the totals you want, though it might be a little slow if you're talking about very large numbers of nested comments

Link to post
Share on other sites

D'uh... using the FM11 method I used sum twice instead of a count and a sum.


I am still looking at how to do it using SQL.


For the comments, I have tried something similar to this, but FM doesn't seem to be able to parse it.

WITH    q AS
	SELECT  id, id_parentComment
	SELECT  p.id, p.id_parentComment
	FROM    q
	ON      p.id = q.id_parentComment
FROM    q
Link to post
Share on other sites

This topic is 2739 days old. Please don't post here. Open a new topic instead.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Similar Content

    • By mikedr
      I think I'm constructing a Rube Goldberg-esque solution to my problem.  Here's the application domain.  I have a table of records that correspond to patent applications, which are commonly referred to as matters.  A matter can claim (but does not have to) immediate priority to one and only one other matter.  However, more than one matter can claim priority to the same matter, and a matter that has matter(s) which claim priority to it can itself claim priority to a matter.  When a matter claims priority to another matter, this means that the former matter is of lower priority than the latter matter.
      I realize this is confusing, so here's a concrete example that I've been using for testing purposes.  Say there are seven matters A, B, C, D, E, F, G.  B and C claim priority to A, and are thus of lower priority than A.  D and E claim priority to B, and are thus of lower priority than B and A.  F and G claim priority to E, and are thus of lower priority than E, B, and A.
      For what it's worth, I created another table called priority, which lists two matters, the matter claiming priority, and the matter to which it claims priority.  So, in the example, there are six records in this new table, one for each priority relationship.  In hindsight, this is perhaps not needed, since in my main matter table, I could simply have a matter refer to another matter to which it claims priority.
      Now, the problem.  Per the concrete example listed above, the result of matters claiming priority to other matters effectively results in a hierarchical tree.  I want to list all the matters that are in the same tree -- that is, all the matters in any priority chain.  So, regardless if I start at matter A, B, C, D, E, F, or G, I end up with the same list of matters.
      Here's how I've done this.  I have a first script that finds the highest priority matter in the tree.  So, if you started at A, you're OK, because A is the highest priority.  But if you start from E, say, it would find B as being of higher priority, and then finally find A as being of highest priority.  That is, regardless of whether you start at A, B, C, D, E, F, or G, you always end up at A.
      Then, this script calls a second script that recursively calls itself.  The second script has a parameter that is a current matter.  It locates any matters that claim priority to the current matter.  If there are any such matters, for each of these matters the script calls itself again, to determine whether there are any matters that claim priority to these matters.  This recursion continues until it locates all matters that do not have any other matter claiming priority to them.
      This indeed works as expected.  Once we get to A in the first script, the call to the second script passing A finds B and C.  For each of B and C, the second script calls itself.  When the second script is operating on B as the current matter, it finds D and E, and when operating on E as the current matter, it finds F and G.
      What I don't like about this approach is that there is a *LOT* of searching going on.  But it does work. 
      The idea is that once I get all the matters in a family -- i.e., all of A, B, C, D, E, F, and G -- I can list these applications in a layout, and long-term, call some (external) tree building tool appropriately to create a visual representation.
      Critically, though, a user is just going to specify at most one priority relationship for any given matter, that the given matter claims priority to (and thus is of lower priority than) another matter.  From these priority relationships, then, I need to figure out the family of matters.
    • By mikedr
      I have a question regarding how FM persists local variables ("$") across iterations of a script that's called recursively.

      Say you have a script "dosomething". And in this script there's a loop:

      set variable $counter=1
      perform script dosomething
      exit loop if condition-is-met
      end loop

      Obviously this is pseudo code, but I hope you get the gist. My question is, say the first time the script calls itself. You have the original $counter, and the "new" $counter. Does the latter affect the former? That is, I know that "$" variables are local to a script, but do they persist "into" another script (such as a recursive call into the same script)?

      Perhaps an easier question is script A calling script B. Say script A is:
      set variable $counter=1
      perform script B

      In script B -- called from script A -- can the variable $counter (defined in script A) be "seen"? I'm hoping not.
    • By cat traveller
      Hello all,

      still I did not fully understand how to implement recursion....

      admittedly I am lost- and very much so:

      There is a list of values = list
      By script, I am looping through another list with keys.

      Now I would like to check if the key is in the list.

      First I was thinking to get the value with Position ( list ; key; 1; 1).
      The problem is that in list, there might be 4651 and key might be 6, which would make Position true.
      However 4651 is not 6.

      Hence, I thought to loop through list by recursion and to check with the "=" but I dont know how to build this:

      Let (
      list = thelist;
      k = thekey
      counter = 1
      c = valuecount (thelist)
      Case ( counter > c ; "";
      // this is my exit

      GetValue ( list; 1) = k ; 1;
      and here is am not getting the calc to recurse...

      My second thought was to turn 6 into 0006 and then use Position. However the Length of the keys might change and
      it seems not the right approach.

      Who could help a dummy, please....

  • Who Viewed the Topic

    6 members have viewed this topic:
    LaRetta  bcooney  Steve Martino  comment  jeffamm  Ocean West 

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.