Recursive Custom Function

[pctechtv]

In my solution that is based on this type of relationship (pic) I have a custom function called Infobase(). What it does is let me deduce the parent of a particular Primary Key. I use it to do things like link Information Topics back to found sets.

http://pctechtv.com/show/cus.png

So I am imagining that I keep feeding the result of Infobase() back into itself that I can come up with the parent of all parents in a particular topic. I was looking into to recursion in Custom Function and saw some info that said a Custom Function cannot iterate but it can recursively call itself. Assuming that is true I would think that this can be done with my function Infobase(). Eventually the “empty” of the result could insure we are at the highest parent. In my attempt to create a recursive Custom Function I must be doing something wrong?

[LaRetta]

... I must be doing something wrong?

 

Well, considering that you have a string of threads started on this subject that you have not even responded on after you have been assisted, I would say yes, you are doing something wrong.  

 

If you do not follow up with suggestions then you are wasting our time as well as yours.  :-)

[comment]

Well, I already wasted my time with this, so I am going to post it anyway...

1. A function - recursive or otherwise - can only calculate a result on the basis of input; it cannot change its context mid-calculation.

2. You are showing a recursive many-to-many relationship between topics(?). In such arrangement there is no "parent of all parents". Each topic may have more than one parent - and you could even have a circular reference where a topic is an ancestor of itself.

3. Do a search for "lineage" to see previous threads on this subject.

4. What LaRetta said.

[Lee Smith]

[pctechtv]

Excuse me for not being on top of the previously posted topics, I am all over the place devloping and being the lead hear at work on a bunch of open projects. I was under the thinking that related to or not, I should make post separate for the time that they were being dealt with. I see now that here I should probably include them and be more diligent in updating the info. The people on this forum have graciously responded to my questions and did their best for me. I say that because I should do my best to meet the requirement of the forum. I will do better. Thanks

[Lee Smith]

...snip.. I am all over the place devloping and being the lead hear at work on a bunch of open projects. I was under the thinking that related to or not, I should make post separate for the time that they were being dealt with. 

Related questions should be in one thread. I is hard sometimes to see that from the git go. However, once the question is being answered, it will often take a few zigs an zags as it progresses from beginning to end. It this happens, then we ask that you just keep in one place so that it is easier to follow and see what has been asked and answered.

 

 

 

I see now that here I should probably include them and be more diligent in updating the info. ... snip...

Exactly.

 

Good post

 

Lee

[LaRetta]

No worries and I hope I didn't come across harsh; I wasn't feeling harsh. :-)

 

You are being helped by (mostly) seasoned developers who face these problems every day in their daily work (and have for more than a decade). The issue isn't (usually) that we don't know how to solve the problem but rather how to gather requirements to provide the best solution. If you leave the thread, we never get to that point and thus the thread remains unresolved (leading many folks who are searching for same answer down a dead-end street) and you are left heading in other directions (wasting time).

 

If what we suggest doesn't work (or fit your situation) then tell us what you tried and how it didn't work.  If you don't understand something, please ask. If questions are asked, answer them … our questions have valid reasons.  And if your needs change in the thread (maybe we show that a Find would work better than portal) that is fine also; no need to post all over again in the other forum.  The goal is to solve your problem using the best method no matter the technique used and Lee will graciously move a thread to another forum if it ends up being more appropriate.

 

So what dictates a separate thread?  A separate problem.  Others may have different takes on this; it is just my opinion (which is the only opinion I have).

[pctechtv]

I received some help that that showed me what I was doing wrong and not understanding. It explained the Let function better so I see why my declaration and reference were getting me no where. This worked

Let ( id = Infobase ( anid ) ;
        If ( IsEmpty ( id ) ; anid ;
                                    DuParent ( id )
           ) // if
       )// let

The Let variables only keep there value for as long as the Let Evaluates is what I learned and it is a great help.

[pctechtv]

2. You are showing a recursive many-to-many relationship between topics(?). In such arrangement there is no "parent of all parents". Each topic may have more than one parent - and you could even have a circular reference where a topic is an ancestor of itself.

 

This database is a collection of Information for the purpose of solving problems. For example Subject -< Problem -< Information. When we get to the information part it breaks into the relationship that is pictured. This was suggested to me, and when I understood how it would work I implemented. It is so simple but can seem so complex some of the time. Simple because it is just like your saying to a record "who do want to be your parent?" and we just give it the ID of that in the join table. New to FileMaker (about 4 months in) but not to programing it took me a while to wrap my head around it. You could also look at it like who do you want to be your child. When Information is created this relationships gives us the ability to have as many Topics and Subtopics(Topics of Topics) as we want. Now I am not doubting what you are saying, but the way we are designing our database is like this. When a new record is created it is the Information, to create a Topic for that Information we have the mechanism via Scripting and other ways that creates a record in the TOPICLIST table which deems it Information with a parent. Now this is a Topic, the only records in our INFORMATION table that are not Topics are the (and I need a good word for this) root Information records. So this question, the Custom Function and the need for recursive ExecuteSQL help us always determine who is the root of our Topics. This is useful for many things, one being so that we can make sure a Topic can always be part of a found set that is its "Subject Matter" respectively. You point is a good one because understanding the type of relationship I am working with I have wondered if a dual parent could accidentally be created and throw things off. I would imagine yes, and say that there is a level of proficiency that need to be achieved with FileMaker that can be the sentinel for his not occurring. So I will consider your discussion good advice as well. Thanks

[comment]

A join table is required only if the relationship is a many-to-many.- meaning that a topic can have many children AND many parents. If that's not true in your situation (and I think that is what you are saying), then the join table is doing nothing except complicate things. You should link a child topic directly to its parent (or root) in a self-join:

 

Topics::TopicID = ChildTopics::ParentID

 

I repeat my recommendation #3 above.

[pctechtv]

If that is the case and I am only complicating things I will change immediately. Will this be same as

Information-----<Information_Topic>-----Topics------<Topic_SubTopic>--------SubTopics? TO's in green all have the same data source table, Topics. TO's in Blue all have the same join table as a data source table. Will this allow for unlimited Subtopics for a Topic? This will mean revamping a lot but if better I will get to immediately. Thanks

[comment]

It's a one-to-many relationship: it allows a topic to have an unlimited number of subtopics, but no more than one parent topic. In terms of practical implementation, you will want to have:

ParentTopic -< Topics -< Subtopics

where Topics is the "main" TO of the table* (i.e. the one which has a layout).


Once again, I recommend you read previous threads on the subject. You are not the first one doing this and you can take advantage of previous efforts instead of sailing uncharted waters.


---
(*) "Topics" should also be the name of your table. It is customary to name a table for what a record in a table represents, e.g. Invoices, Employees, Apartments, etc.