Displaying data from value list from a field

[Wing]

I have a problem that's come up at the end of several steps. Here goes:

Table One is called "topics". This table has two fields "topic ID" and "topic Name".

Table Two is called "locations". This table has a field with a check box style value list. The value list is generated from the other table's "topics ID" and "topic Name".

This all works great. It displays all the records in "topics" table with the id and name showing.

So, what I want to do now, is display the checked values on a report. If I put the check box field on the report as a standard edit box, it only displays the data under "topic ID". I'm assuming this is because that's how the relationship between the two tables is set up.

I'd like the field to display just the "topic name" or even both, but the id number doesn't really tell much.

Any suggestions would be much appreciated. Thanks.

[LaRetta]

Create a relationship from this Locations table to Topics on the TopicID. Then place the related Topics Description from the Topics table directly on your current Locations layout.

But ... do you have multiple items checked in those checkboxes in Locations?

Edited June 1, 2009 by Guest

[LaRetta]

To display what is in the checkbox field (if more than one is checked), You must have a relationship from Topics::TopicID = Locations::TopicID.

Then in Locations, you need a calculation (result is TEXT) with:

Substitute ( List ( topics::Desc ) ; ¶ ; ", " )

... keep in mind that you will not be able to generate a sub-summary report by individually checked item.

[Wing]

Yes, multiple items are checked.

Is this a portal solution? Which I'm not very familiar with.

[LaRetta]

This is not a portal solution except in the way that there must be a relationship between the two tables. Once you have related them on TopicsID using equal, then the calculation in Locations table will produce the multiple topic descriptions that are checked. :wink2:

[Wing]

That solution worked great. Thank you!

I actually used in the calc field:

List (topics::Desc)

I still needed it as a list but didn't know about this before.