How to generate report of records related in many-to-many relationshipts

The basic idea is to put Focus on a User.  That User will "light up" and shine a light down to the many to many join table and then up to Projects and then down to the other join table and then up to Elements.

Then we will show an embedded report of lit up Elements on Users.

We will need a helper table called User Focus.  Make the table and create Two numeric fields.

[Record ID# of Focus User]
[Record ID# of Focus Project]

Make one record.  Then block all Roles including the Admin Role from adding any other records.  The Record will be Record ID# = 1.

Make a Relationship from User Focus to Users based on a a reference field called [Link to User Focus (=1)] with the formula of 1. 

Lookup the [Record ID# of Focus User] down to Users.
Make a numeric formula field on Users called [User is in Focus (=1)] with a formula field of 

IF([Record ID#] =  [Record ID# of Focus User], 1,0) // ie light up the Focus User.

Lookup [User is in Focus (=1)] down to Project Users.

Make a summary field of the Maximum of [User is in Focus (=1)] up to Projects.  Call it [Project is in Focus for Focus User (=1)]

Lookup up [Project is in Focus for Focus User (=1)] down to Project Elements. 

Summarize Maximum of [Project is in Focus for Focus User (=1)] up to Elements.

Make a report of Elements where [Project is in Focus for Focus User (=1)] equals 1.

Now, make a field on Elements called [Link to all Users (=1)]  the formula will be 1.

You already have a field on Users called [Link to User Focus (=1)] 

Make a Report Link field on Users with the [Link to User Focus (=1)]  on the left and on the right  [Link to all Users (=1)] from the Elements table.

Set the form properties of the form for that Report Link field to use the filtered report of Elements where [Project is in Focus for Focus User (=1)] equals 1.

Test that it all works by manually editing the User Focus record with the value of the [Record ID#] of a user and see that the Elements show up in the embedded report.

Make a form rule to only expose the Report Link field when the [Record ID#] = [Record ID# of Focus User].  I suggest making a formula checkbox field for that test and using it for the form rule.

Now we just need to make a Formula URL button to set the focus and redisplay the User record.

var text URL = URLRoot() & "db/" & [_DBID_USER_FOCUS] & "?act=API_EditRecord&rid=1"
& "&_fid_6=" & [Record ID#]; // assuming field ID 6 is the [Record ID# of the Focus User] field 

var text RefreshPage = URLRoot() & "db/" & Dbid() & "?a=doredirect&z=" & Rurl();

$URL
& "&rdr=" & URLEncode($RefreshPage)

Let me know how you make out,

Once that is working do basically the same thing from the other end to set the Focus Element.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------