Skip to main content
Solved

Is there any way to show an embedded report with all the records related through a summary table

  • October 10, 2024
  • 0 replies
  • 387 views
Forum|alt.badge.img+1

There are three relevant tables in this scenario, Projects, Groups, and a summary table called Project Groups. There is a one to many relationship from Projects and from Groups to the Project Groups table. This is to allow a project to be a member of multiple groups, and a group can contain multiple projects. 

I would like to show an embedded report in the projects form that shows all of the other projects (with some other relevant fields) that share a group with the project I am viewing, and I plan to group that report by Group. Is there a way to accomplish this? 

Best answer by DonLarson

This should do it

 

In the Project table make a Summary Field to get the Related Group from the Project Groups Table

Add a Report Link Field to the Projects table

Configure the Report Link field so that the target table is Project Groups

Set the Summary Field Related Group is equal to Related Group

Then you can create a report in the Project Groups table to use as the Embed for the Report Link field.

View original
This topic has been closed for replies.

Forum|alt.badge.img+19
  • DonLarson
  • Qrew Cadet
  • Answer
  • October 10, 2024

This should do it

 

In the Project table make a Summary Field to get the Related Group from the Project Groups Table

Add a Report Link Field to the Projects table

Configure the Report Link field so that the target table is Project Groups

Set the Summary Field Related Group is equal to Related Group

Then you can create a report in the Project Groups table to use as the Embed for the Report Link field.

    Forum|alt.badge.img+1

    This is working for me when my project is only in one group, but when its in 2 or more groups it doesn't seem to find any matches. Maybe I am doing something incorrectly. Originally I tried to do a combined text with the 'related group' field for my summary field but I assume because they are numeric I couldn't do a combined text. So instead I did a combined text on the the group name field. I was hoping it would try and individually match the items in the combined text field but it seems to do a match against the entire thing. 

    Forum|alt.badge.img+1

    Oh of course! Thank you both so much for your help

    Forum|alt.badge.img+19
    • DonLarson
    • Qrew Cadet
    • October 13, 2024

    RobertHarrison 

    I have an 80% solution for you.   This will assumes that you have a fixed, stable number of Groups.

     

    [Project Groups] create a Formula Text field using the Related Group

    [Projects]  create a Combined Text field in relationship of the Related Group Text field from the Project Groups Table.

    Now you need pairs of Report Links and Formula Text boxes in Projects

    The Formula Text evaluated the Combined Text field.

    If (Contains([Combined Text Related Group Text], "1", "")

    This determines if the Project is in Group One

    Then the Report Link uses that value to give you a Report of the other Projects in Group One

    Repeat for your other Groups.

    Projects will have an embedded report for each Group but only the ones that match your Project will be populated.   It is not as elegant a one report where you group them on the name of the Group.   

     

     

     

      Terms & ConditionsAccessibility statement