We have two objects "Project" and "Project Members". Project is child of some other object with master-child relationship. So, we can't have "Master-Detail" relationship between "Project" and "Project Member" object and we are using "Lookup Relationship".
There is a requirement on "Project" object to show diffrent details of "Project Member" who has role called "Project Manager". So, we have added a hidden lookup of "Project Member" on "Project" object. Which we are setting by trigger when a "Project Member" record is created with role called "Project Manager". Also, there are some cross object formula on "Project" which takes details of "Project Manager" records.
With this implementation, functionality is working fine. But, it is a circular reference of lookups between "Project Member" and "Project". By this hidden lookup, One "Project" can have one or more "Project Members" in "Project Member" related list. But "Project" will have always one "Project Manager" value in "Project Manager" lookup.
Let me know, if it is ok to use circular lookup reference in such scenario? Because it gives us below advantage :
- Not any affect on reports
- With cross-object fomula, we can populate more detail of "Project Manager" in future. (Less Code needed)
- No need of trigger to populate "Project Manager" details on "Project" record.