Hi John,

First of all, I'm trying to fit this into the Stonefield (that's the program they are trying to use to create reports and I'm trying to help).

If I would design this by myself, I would do few left joins (right now I'm trying to do it in one select by using subtables- I assume this is how Doug is doing it internally - BTW, it would be nice to see the Select statement which is generated to produce a report).

So, I would produce the final result as a cursor and the report will plainly print this.

My question to Doug - is it possible to somehow create a cursor in Report's DE or add some initial script and use the cursor for the report?

I may also try to take the tables home, design reports manually (though I only have VFP8 at home), but then the question becomes how to plag them in either Exceed or Stonefield.

BTW, in LS how did you eliminate blanks? I guess I would have to dig your utility somewhere...

>Hi Naomi,
>
>Do a LEFT JOIN with Phones and People, in that order, to create a table with ID, names, phone, and phone type for each phone row - the idea being that the result set is atomic to each phone number. In the report detail band, use a Print When to determine under which column to print a phone number, also, to determine if you've already printed the name in for a prior number.
>
>I was faced with a similar issue when I wrote my LS utility. Take a look at the report that is in the utility and you'll see.
>
>
>>Hi Doug & everybody,
>>
>>I've sent an e-mail to the development team with this question, but I also want to ask here in case anyone had the same problem.
>>
>>We have a People table and a Phones table where we have Phone and Type fields.
>>
>>Now, in the report I'd like to show
>>

>>Last Name First Name Home Phone(s) Office Phone(s) Cell Phone(s) E-Mail(s)

>>
>>I tried to approach this problem in two ways.
>>
>>1. Using new fields in the People table with custom scripts - this approach seems to be too slow - is there a way to speed it up?
>>
>>2. By Creating 5 different subtables for the different type of Phones, adding complex join (left join) relations to the People table
>>
>>Sample of the relation: LEFT JOIN
>>
>>

>>People.CID = Phones_Office.CID and Phones_Office.Type = 'Office' and Phones_Office.PHONE <> space(60)
>>
>>Many - Phones_Office
>>One - People

>>
>>Somehow it looks like using this approach behaves as an Inner JOIN. E.g. if I include only Home Phone I see ~768 records, but adding Cell Phone reduces the number of records to ~200.
>>
>>Do you know how should I solve this problem?
>>
>>Thanks a lot in advance.