>>Hi all,
>>
>>I have run into a tough problem that perhaps someone else has run into or where conceptually they have done something similar.
>>
>>The table is of insurance company names. From the beginning the client said that there would be head offices and then branch offices. And the branch offices can also have branches etc. And the companies can be named differently (different operating companies). And the client would like to just see all the companies in one table for browsing. So I set up the table to have a pointer back to a parent company. It has worked well over the years (and no arguing over the design at this point - it has worked...although I might be open to a new design in the future if they move to a new backend).
>>
>>Here is the table example:
>>
>>Company_ID ParentCoID CompanyName City etc
>>
>>7684 0 Intact Insurance Toronto
>>7685 7684 Intact Insurance Waterloo
>>etc.
>>
>>So the branch points back to the parent via ParentCoID; the topmost company does not have a ParentCoID.
>>
>>There have been a LOT of mergers and buyouts in Canada for insurance companies so the users needed a tool to reassign branches to head offices or to other branch offices etc. They did this by having a dialog that allowed them to go to the parent and choose branches from a list. This has worked well for the most part.
>>
>>Had this problem recently though where the user ended up creating an endless loop.
>>
>>Original setup:
>>
>>Certas Insurance [Aurora, ON branch], Company_ID = 7204, ParentCoID = 4778 [Desjardin Insurance Co.]
>>Certas Insurance [Mississauga, ON branch], Company_ID = 7682, ParentCoID = 7204
>>
>>- see also the image which shows the tree data after running through a procedure that works up a cursor of the relationship starting at the parent and working down.
>>
>>In other words, Certas Mississauga was a branch office under Certas Aurora and Certas Aurora was a branch under Desjardin (which happens to be the Head office).
>>
>>Because of a reorganization, Mississauga was now supposed to be elevated as the "regional office" and Aurora was to be "demoted" to be a branch under Mississauga.
>>
>>So the user went in, went to Mississauga and tried to make Aurora a branch (child) of Mississauga. My code had nothing to check this so they were able to save the change.
>>
>>This resulted in the following in the table:
>>
>>City Company_ID ParentCoID
>>Aurora 7204 7682
>>Mississ. 7682 7204
>>
>>- basically an endless loop. When my "related companies processor" ran, it got into the loop and crashed after 99 iterations (it has a "level" field to indicate levels deep in the tree, N(2)).
>>
>>So I modified the "related companies processor" to stop after 25 levels down (as no company currently in the system has more than 6 levels). This at least traps the error so the user does not just crash out.
>>
>>I have tried to think of a way to programmatically catch the problem to block the user from making a change that would cause an endless loop. Trapping the error as I did above helps but it would be better to prevent the problem. I have worked in a simple check to catch if the user is setting up a loop between 2 offices (a points to b and they are suggesting b points to a) but the reality is that they could link an office in such a way that the change could cause the loop anywhere in the tree e.g. a points to b which points to c but then c points back to a.
>>
>>The only way I can think of to cover off all possibilities is to process the tree from the top and see if at any node an endless loop has been set up - a lot of cpu work given that some of the larger insurance companies can have hundreds of branches under them.
>
>One way to structure lists like this is to have top-level entries that have no parents. If you've done that you could "walk" through the parents of the candidate node (CN) that is about to be changed. If you eventually hit an empty parent, then OK. If you hit CN, then you have a loop and you disallow the change.

Gut guess puts money on assigning each parent level via SQL as speed king, as there is only one line of optimized SQL (ok, with optimized sub-select) and another one for exit check to call for each level, after init with root = 0 and all other levels -1. Self referencing tuples/errors will not be assigned a parent,marking "wrong" structure

something like

update Level with -1 from (xxx) 
update Level with  0 from (xxx) where ParentCoID = 0 && set root(s)
assert _tally>0
for lnLevel = 1 to 1000000
   update Level with m.lnLevel from (xxx) where ParentCoID  in (select Company_ID from (xxx) where Level=m.lnLevel-1)
   if _tally = 0
      exit
   endif
next

and check on all records with nParent still -1