Forum Discussion

CarolMcconnell's avatar
CarolMcconnell
Qrew Officer
3 months ago
Solved

Many to Many Relationships

I'm trying to do this the easiest way possible and could use some advise. I have 4 tables Market, Activites,Base Agreements, Admendments The activities table can be related to one or more of each ...
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    3 months ago

    If I understand your question correctly, you have a table of Markets, with a field called Market and you have a table of activities and you want to show an embedded report on the Markets record of all the activities for which the Market field matches any of the multi select markets on the Activity record. 

    I suggest that you make a field on Activities called [Markets for Report Link] of type formula text.  The formula will be 

    ToText([My multi- select markets field])

    If you look at that the data in that field, it will be a semi colon delimited limited list of the markets you selected in text format.

    Then on the Market table, create a Report link field and configure the left value to be the [Market] field and on the right side navigate to your Application (being sure to watch out for a popup blocker), and then the field in Activities for [Markets for Report Link].

    But then the magic is to deselect "Only include values if they match exactly in both fields" on that Report Link field.

    In my cheat notes to myself, non exact match means that 

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

     

MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
3 months ago

If I understand your question correctly, you have a table of Markets, with a field called Market and you have a table of activities and you want to show an embedded report on the Markets record of all the activities for which the Market field matches any of the multi select markets on the Activity record. 

I suggest that you make a field on Activities called [Markets for Report Link] of type formula text.  The formula will be 

ToText([My multi- select markets field])

If you look at that the data in that field, it will be a semi colon delimited limited list of the markets you selected in text format.

Then on the Market table, create a Report link field and configure the left value to be the [Market] field and on the right side navigate to your Application (being sure to watch out for a popup blocker), and then the field in Activities for [Markets for Report Link].

But then the magic is to deselect "Only include values if they match exactly in both fields" on that Report Link field.

In my cheat notes to myself, non exact match means that 

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

 

  • CarolMcconnell's avatar
    CarolMcconnell
    Qrew Officer
    3 months ago

    Thank you so much, that did the trick.  One other thing I had to in the report settings on the form to filter using report link settings but it is working great.