Linking an array to a seperate database

[enitial]

Hello

This is my first time using this site, so apologies for any ignorance.

I have been set the task of updating a report that links to an sql database. The table i link to has a 'comments' field, that has to hold an array of 'comment IDs'

I need to link this array of 'comment IDs' to a table of 150 corresponding 'comment descriptions'

Example:

SAMPLE TABLE
Sample ID CH4 CO2 O2 N2 Comments
1 0.3 0.2 20.1 79.4 4,7,67
2 0 0 20.8 79.2 1,2

COMMENT TABLE
Comment ID Comment Description
1 Sample Point blocked
2 Needs New Valve
3 Odour detected
4 Leaking

RESULT in REPORT
.
.
.
Sample Point 2 - Sample point blocked
Needs new valve

i know the database sould have been designed to handle this, but as it happens we are stuck with the need for a quick fix. So the 'comment table' MUST be an Excel spreadsheet, that the array links to inside CR, and looks-up.

Thanks

 

[lbass]

I'm not sure what the datatype is for {Comment.CommentID}, but I'll assume it is a number and that {Sample.Comments} is a string.

Insert a subreport that uses the Comment table only, and on the Links tab, choose {Sample.Comments} as the linking field. Then in the subreport, go into report->selection formula->record and change the selection formula to:

totext({Comment.CommentID},0,"") in split({?pm-Sample.Comments},",")

Then place the description field in the detail section of the subreport. The subreport should be placed in the section containing the {Comment.CommentID} (details?), or if what we are seeing in your example above is a group section where the comment IDs have been accumulated into a string, then the sub belongs in the group section.

-LB