Discussions

  • 1.  How to generate report of records related in many-to-many relationshipts

    Posted 08-29-2021 20:21
    Edited by Juan Solorio 08-29-2021 23:16
      |   view attached
    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.


  • 2.  RE: How to generate report of records related in many-to-many relationshipts

    Posted 08-29-2021 21:26
    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
    ------------------------------



  • 3.  RE: How to generate report of records related in many-to-many relationshipts

    Posted 08-29-2021 21:32
    Hi Mark,

    The button option would definitely suffice.

    J.

    ------------------------------
    Juan Solorio
    ------------------------------



  • 4.  RE: How to generate report of records related in many-to-many relationshipts

    Posted 08-29-2021 21:44
    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
    ------------------------------



  • 5.  RE: How to generate report of records related in many-to-many relationshipts

    Posted 08-29-2021 21:47
    Record ID# in both instances.

    ------------------------------
    Juan Solorio
    ------------------------------



  • 6.  RE: How to generate report of records related in many-to-many relationshipts

    Posted 08-29-2021 23:14
    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
    ------------------------------



  • 7.  RE: How to generate report of records related in many-to-many relationshipts

    Posted 08-30-2021 18:42
    Thanks, Mark!

    We had implemented a similar approach with an intermediary table. However, the roadblock we came across was that only one User (or Element) could be viewed at a time. If multiple people were attempting to view these reports simultaneously, they would not be able to because the single record in the intermediary table keeps getting overwritten every time they click the button. Nevertheless, it is the closest we have come to what we are looking for, and it definitely does what we need it to do.

    Thanks again!

    J

    ------------------------------
    Juan Solorio
    ------------------------------



  • 8.  RE: How to generate report of records related in many-to-many relationshipts

    Posted 08-30-2021 21:38
    I do have another approach which I can implement, where the User Focus is stored in a self maintaining table of Users where the Key field is the Userid. But I don't think it's needed, because the click only has to last long enough to refresh the record and see the result. 


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