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

Question

Hello everyone,Please see attached ERD section for reference.

The goal is as follows:1. I need to be able to generate a report for USERS listing all ELEMENTs that are being used in the PROJECTS that the user belongs to. I need to be able to display each element only once for each user, regardless of how many projects they are working on.2. I also need to generate a report for ELEMENTS listing all USERS that are using that element via the PROJECTS that the user belongs to. I need to be able to display each user only once for each element, regardless of how many projects they are working on.As you can see in the ERD, these tables are indirectly related through M:M relationships.Is there a way to accomplish this?Note: I have modified the ERD as it was previously showing an incorrect relationship.Thanks in advance,J.

markshnier__you · Answer

Is it a requirement that this be a report or would it be good enough to be able to click a button on a user and see an embedded report of all the elements and conversely set on an element record and click a button to see all of the users involved?

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

juansolorio1 · Answer

Hi Mark,The button option would definitely suffice.J.------------------------------Juan Solorio------------------------------

markshnier__you · Answer

Can you tell me what the Key Field is of users. And also what is the Key field of elements?------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

juansolorio1 · Answer

Record ID# in both instances.------------------------------Juan Solorio------------------------------

markshnier__you · Answer

The basic idea is to put Focus on a User.&nbsp; 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.&nbsp; Make the table and create Two numeric fields.[Record ID# of Focus User][Record ID# of Focus Project]Make one record.&nbsp; Then block all Roles including the Admin Role from adding any other records.&nbsp; 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.&nbsp;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&nbsp;IF([Record ID#] =&nbsp; [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.&nbsp; Call it [Project is in Focus for Focus User (=1)]Lookup up [Project is in Focus for Focus User (=1)] down to Project Elements.&nbsp; 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)]&nbsp; the formula will be 1.You already have a field on Users called [Link to User Focus (=1)]&nbsp;Make a Report Link field on Users with the [Link to User Focus (=1)]&nbsp; on the left and on the right&nbsp; [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&nbsp;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].&nbsp; 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() &amp; "db/" &amp; [_DBID_USER_FOCUS] &amp; "?act=API_EditRecord&amp;rid=1"&amp; "&amp;_fid_6=" &amp; [Record ID#]; // assuming field ID 6 is the [Record ID# of the Focus User] field&nbsp;var text RefreshPage = URLRoot() &amp; "db/" &amp; Dbid() &amp; "?a=doredirect&amp;z=" &amp; Rurl();$URL&amp; "&amp;rdr=" &amp; 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------------------------------

Forum Discussion

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