[Also posted to the Infor CRM forum]

We have several groups with local joins on the USERSECURITY table. These joins give us a couple management levels for Opportunity records. The operative part of this is:

FROM OPPORTUNITY OP
 INNER JOIN USERSECURITY MGR1 ON (OP.ACCOUNTMANAGERID=MGR1.USERID)
 INNER JOIN USERSECURITY MGR2 ON (MGR1.MANAGERID=MGR2.USERID)

The two additional joins (MGR1 and MGR2) came from creating local joins.

--- opportunity.accountmanagerid to usersecurity.userid
--- usersecurity.managerid to usersecurity.userid

This gave us the desired data.

We recently extended the application by exposing the OPPORTUNITY_PRODUCT table as an object. It now shows in the Web client navigation bar as “Opp Product”. It is possible to make groups for Opp Product. We need to create groups that list opportunity-products for the opportunity manager, their boss, and their boss’s boss. This should work the same way.

The OPPORTUNITY table appears in the relationship tree of the Query Builder under OPPORTUNITY_PRODUCT as:

Opportunity (OPPORTUNITYID -> OPPORTUNITYID)[inner]

We can pick the Accountmanager Id property and create a local join to Usersecurity.Userid (as an inner join). Under Opportunity we get:

USERSECURITY (ACCOUNTMANAGERID -> USEID)[inner]

Then, under that we should be able to create a local join on User Id to Usersecurity.Managerrid. However, when we do, this second relationship doesn’t show in the relationship tree.

Does anyone else have experience where a local join doesn’t show in the tree after it is created?

Also, we have groups that extend this an additional two levels. The first three levels are fine. However, after adding the third level it is hard to distinguish which of the relationships is at what level. Is there a workaround for this?

Thanks for any help!