Linking from a list 1

[DuncanSutcliffe]

Couldn't even think of a better subject for this one!

I have a table named {events}, which contains a memo field {events.providers}. The field is populated with a comma seperated list of numbers, each of which relate to an individual record in {providers}. The list may contain any number of individual, comma seperated items.

Can anyone think of a way of linking the two, so that I can include things like {providers.name}? Doesn't matter how complex - I just can't see my way through the problem at the moment.

I'm using CR 8.5, connecting to a SQL Server back end. I have no control of the db at all, it's outsourced to a different consultancy, so I can't create views or alter the way the app stores the list.

TIA
Duncan

 

[Reebo99]

Ok, this solution is proven in version 9, and should be the same for version 8.5.

Create a report based on {events} only.
Create a subreport based on providers. Link the 2 reports by {events.providers} to {providers.record}
In the subreport, change the record selection formula from something like :
{providers.record} = {?Pm-events.providers}

To:

{providers.record} in Split({?Pm-events.providers},",&quot

Let me know how you get on....


Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[DuncanSutcliffe]

Thanks - this is a good solution that I probably wouldn't have come up with. However I think 8.5 handles arrays in a way that prevents it working.

I added in a quick ToNumber function to make the field data types agree, but I then get an error along the lines of "This array must be subscripted". I'll keep fiddling and see if I get a way round this.

Duncan.

 

[Reebo99]

It would be easier if you converted the number to text like :

ToText({providers.record},0,"&quot in Split({?Pm-events.providers},",&quot

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[DuncanSutcliffe]

Brilliant. Give yourself a shiny.

I had missed out the arguments after the ToText({providers.record}) when I tried it that way.

Duncan

 

[Reebo99]

Thank you for recognition of my greatness. I will continue my day on an elevated level..

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.