Forum Discussion

RobertHarrison's avatar
RobertHarrison
Qrew Trainee
2 months ago

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

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? 

reports
  • DonLarson's avatar
    DonLarson
    2 months ago

    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.

  • DonLarson's avatar
    DonLarson
    Qrew Elite
    2 months ago

    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.

  • DonLarson's avatar
    DonLarson
    Qrew Elite
    2 months ago

    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.   

     

     

     

  • RobertHarrison's avatar
    RobertHarrison
    Qrew Trainee
    2 months ago

    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. 

    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      2 months ago

      I'm traveling now in an opposite time zone, so not full access to my computer, but this might help. 

      A report link field can be set to have a non exact match in field settings  

       

      Report Link non exact matching means that the field on the "Parent" (left) must be contained within the field on the right "Child".

      SET NON EXACT MATCH!!!

    • DonLarson's avatar
      DonLarson
      Qrew Elite
      2 months ago

      I tested my solution.  Even with Marks advice I am having the same issue you brought up.  It only works if the Project is in a single Project Group.

      I have a half baked idea I am working on.    For the sake of realism, how many Project Groups could you really have for a single Project?   If it is two or three I might have a solution.   If it is dozens than I am in trouble.