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 of the other tables but don't have to be related to all of the other tables.
I was hoping that I could for example in the Activities table have a field named Market and it be a multi select field. Then on the Market form, I could show all of the Activities that have that market selected. I don't know how to filter that report to only show the activity records that have the market name.
I appreciate any assistance.
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".
