Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Recursive query


nhjelmberg

Recommended Posts

What is the Filemaker 7 way of handling recursive queries? Assume that I have a table with two fields, employee_id and manager_id, and the following posts:

 

1 0

2 1

3 1

4 2

 

Employee 1 is manager of employee 2 and 3. Employee 2 is manager of employee 4 so employee 1 is indirectly manager of employee 4. How do I display all employees subordinate to employee 1 (in this case employee 2, 3 and 4)?

 

In Oracle SQL I could use the CONNECT BY clause. Is there anything similar in Filemaker or should I use another method (portal, self-join, script, another table structure)?

Link to comment
Share on other sites

Unfortunately, you can only see one level down through each relationship. You could setup a chain of relationships to see each sub-subordinate level, but you would only see the records at that particular level.

 

A better way of dealing with this type of structure is by using multi-keys to hold either all the subordinate IDs for each employee, or the other way around--hold all the supervisor IDs for each employee. Then the relationship matches between each supervisor and all the people they supervise, directly or indirectly.

 

You might check my demo of one technique for this here:

 

http://www.fmforums.com/forum/showtopic.php?tid/135595/post/148595

 

The advantage of this is it requires no additional scripts and no join table (though scripted techniques are fine too.)

Link to comment
Share on other sites

  • 2 weeks later...

It looks like you have to be registered to get at the Sample Files forum.

 

Here's my demo from that thread. Although there was some other stuff in that thread that may be helpful.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.



×
×
  • Create New...

Important Information

Terms of Use