Skip to Content
0
Former Member
Aug 01, 2007 at 03:20 PM

Splitting a comma delimited field and then linking to a subreport

19 Views

Post Author: JCM

CA Forum: General

Hello, Hopefully I can explain what I need to do. I have a table that has a field which contains a comma delimited list of id numbers which are links to another table. The list is variable length. It can be as little as just one number to as many as 20 and it will show up like so: 1,2,5,10 or 45,32,78,99,100 and so on. Let's call that Table A. The first column (A.1 let's say) is a name and then the second column is the above field. I need to take that list apart and link each number to Table B in which the first column is the id number and the second is let's say pets. So basically the data can be like so: Table AName............ID'sJohn............ 1, 2, 3, 4Mary............2, 5Bill...............4and then Table BID.............Name1..............cat2..............dog3..............fish4..............horse5.............birdI need my report to produce this:Name..........PetsJohn............cat ..................dog..................fish...................horseMary...........dog..................birdBill............horse How can I accomplish this? So far I've tried having my main report be the names. I then try to create a subreport which takes the comma delimited list of pets and then uses those to retrieve the pet names. However I can't exactly get it to work. The best I was able to get was to only retrieve the first pet name in each list. Ideas?Thanks. Oh, I'm using Crystal XI by the way.