I have a two tables that have many to many relationships between them using an assignment table as such:
Records < Country-Record Assignments > Countries
I want a text field in the Records table that will list all of the Countries it has been assigned. So for example, if a Record was assigned to United States, Canada, China, and India, the value of the field in the Record table could be "Canada, China, India, United States".
Any help in this would be greatly appreciated.
- If your goal is to enable users to see or report on the countries assigned to a record, then create an embedded table of assignments within the records form. Sometimes, when I've done this for users, they are more than satisfied. If they want, they can use the links in the header of the embedded table to pop it out as a separate report and add more columns.
- Alternatively, if you are trying to extract the list of assignments for a country and use it somewhere, then you might try getting the expanded query for a report on the assignments table, and using that.
The way to do this is with as many "reverse relationships" as you guess will be enough to cover off 99% if the situation.
One Record Job has many CR Assignments. Make a summary field on that relationship of the Minimum of the Record ID# of CR Assignments. Call it [Record ID# of CR Assignments 1].
Then do a lookup of that down to the child CR Assignments table as we will need it later.
Then do a new Relationship where one CR Assignments has many Records. (yes that sounds backwards, hence its called a Reverse Relationship)
For the reference
field (the field on the right side), use that new Summary field called [Record
ID of CR Assignments 1]. Get rid of the extra fields that get
created on the left side of the relationship (Add Record and that report link
field). Do a lookup from the CR Assignments down to Records and
bring in the Country Name and call it Country 1.
Wonderful, the Record now knows the first CR Assignment.
Now, make a duplicate of that summary field, but in the duplicate add the extra condition that the [record ID#] is greater than the [Record ID# of CR Assignments 1]. Call it [Record ID# of Operator 2]. Do a lookup of that field back down to the Child Operator table as we will need it later.
Now, in making that duplicate, conveniently, you will find that it duplicated the reverse relationship so now do a lookup in that relationship of the Country Name and call it [Country 2]. Wonderful again, we now have Country #2's name on the Record.
Then make another duplicate of that summary field and just keep going, each time changing the filter so that the record ID# is greater than the previously highest record ID of Operator.
In the end say you collect the names of the first 4 Countries on a Record, so then just string them together in a formula like
and put that concatenated field list on your report.
If you stop at say 8 countries and want to allow for the possibility of more, then you would need to create a formula field to detect that the # of CA Assignments was more than 8 (ie a summary count was > 8, and then include some text on the result text string to say
& IF(# CA Assignments] >8 . “.. and more....”
Having said this, I still recommend looking very hard at using an embedded table of countries within your form. You can print the form with standard tools to get the equivalent of a printed report showing the relevant countries related to a particular record, for example, and you can email the record as well.
- 825 Conversations
- 43 Followers
- 1395 Conversations
- 48 Followers
- 2805 Conversations
- 64 Followers
- 2551 Conversations
- 39 Followers
- 322 Conversations
- 25 Followers
- 911 Conversations
- 63 Followers