Re: Showing distinct records in labels

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thanks again for the reply Iain,

> Then you are at a position I call, "I wouldn't start from here" <G>

Thats what I thought for a long time.

Thanks for all the suggestions you have thrown at me but I have managed to
sort this out a different way (really easy when you know how). It turns out
that you can make group headers/footers have multiple columns... the option,
when you are in section expert is not when you select the group header! you
have to select the details section, format that with multiple columns and
then if you look in layout tab there is an option to 'format groups with
multiple columns'. If I do this I can just put the fields in the group
header and suppress the details section and, as though by magic, I get
exactly what I am looking for... after all that!

Thanks again for your help.

Gav

"Iain" <iains@xxxxxxxxxxxx> wrote in message
news:436f6733$0$28008$ed2619ec@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Then you are at a position I call, "I wouldn't start from here" <G>
>
> Is it possible to redesign the interface with the .NET application to pass
> the data as parameters rather than recordselectionformula? There is
> standard code for doing this (which I can't give you as I don't use .NET)
> for setting these parameters. As such, you then 'simply' add these to the
> command SQL and the returned data is pre-filtered.
>
> Alternatively, instead of building recordselectionformula and setting it,
> can you build the command SQL and set that? Again, I believe there's a
> standard interface for doing this. This would allow you to include the
> record selection formula in the original SQL query and have it acted on
> before the returned data is selected.
>
> As long as the selection is in the record selection formula, you have to
> pass that data back from the database, and this is going to mess with the
> 'distinct' part of your query.
>
> Regards,
>
> Iain
>
> "Gav" <spam@xxxxxxxx> wrote in message
> news:esOlc844FHA.2600@xxxxxxxxxxxxxxxxxxxxxxx
>> Thanks again for replying to my post
>>
>> Sorry for my late reply, I haven't checked on here for a while, however
>> my problem is still on going.
>>
>> the values are not hard coded, the recordselectionformula is created by a
>> .NET application. So I cannot put them into the command.
>>
>> Although I'm sure I did try that just to see if it worked and I still got
>> an invalid field error.
>>
>> Gav
>>
>> "Iain" <iains@xxxxxxxxxxxx> wrote in message
>> news:436b2dff$0$63069$ed2e19e4@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>>> If the values 1 & 2 are hard coded, then they don't need to be in the
>>> recordselection formula, add them to the command et voila!
>>>
>>> If they are parameters, you can add those parameters to the command and
>>> also voila.
>>>
>>> Iain
>>>
>>> "Gav" <spam@xxxxxxxx> wrote in message
>>> news:OVB65UJ4FHA.2364@xxxxxxxxxxxxxxxxxxxxxxx
>>>> Thanks again for the reply but still no good... when I do this it
>>>> complains that the field in the recordselectionformula is invalid.
>>>>
>>>>
>>>> "Iain" <iains@xxxxxxxxxxxx> wrote in message
>>>> news:43688948$0$82629$ed2619ec@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>>>>> The other suggestion is to use a command rather than linking in the
>>>>> tables directly.
>>>>> Then you can put in whatever SQL you like and crystal only sees the
>>>>> result-set.
>>>>>
>>>>> Iain
>>>>>
>>>>> "Gav" <spam@xxxxxxxx> wrote in message
>>>>> news:ehXkSmv3FHA.2364@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>> Thanks for the reply Iain,
>>>>>>
>>>>>> I tried this and it didn't work for me. I have 2 tables with a one to
>>>>>> many relationship i.e.
>>>>>>
>>>>>> Customers 1-M Companies
>>>>>>
>>>>>> Customers
>>>>>> ID NAME ADDRESS
>>>>>> 1 SMITH MARS
>>>>>>
>>>>>> Companies
>>>>>> ID CustID NAME ADDRESS
>>>>>> 1 1 JOHN VENUS
>>>>>> 2 1 FRED PLUTO
>>>>>>
>>>>>> I want to pull out the customers depending on which companies I
>>>>>> select so...
>>>>>>
>>>>>> SQL query:
>>>>>>
>>>>>> SELECT
>>>>>> Customers.NAME, Customers.ADDRESS FROM Customers LEFT OUTER JOIN
>>>>>> Companies on Customers.ID = Companies.CustID
>>>>>> WHERE
>>>>>> Companies.ID = 1 or Companies.ID=2
>>>>>>
>>>>>> When you add the select distinct records option in crystal reports it
>>>>>> automatically adds the WHERE field into the select section:
>>>>>>
>>>>>> SELECT
>>>>>> DISTINCT Customers.NAME, Customers.ADDRESS, !!CR puts this
>>>>>> in!!Companies.ID!!automatically!! FROM Customers LEFT OUTER JOIN
>>>>>> Companies on Customers.ID = Companies.CustID
>>>>>> WHERE
>>>>>> Companies.ID = 1 or Companies.ID=2
>>>>>>
>>>>>> So this query returns
>>>>>>
>>>>>> SMITH MARS 1
>>>>>> SMITH MARS 2
>>>>>>
>>>>>> which are different as far as the query is concerned.
>>>>>>
>>>>>> If I could just get it to put the DISTINCT in but not add the WHERE
>>>>>> fields into the SELECT part of the statement it would work great.
>>>>>>
>>>>>> Any clues?
>>>>>>
>>>>>> Thanks again
>>>>>> Gav
>>>>>>
>>>>>> "Iain" <iains@xxxxxxxxxxxx> wrote in message
>>>>>> news:43673464$0$63054$ed2e19e4@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>>>>>>>I think,
>>>>>>> Database-> Select distinct records
>>>>>>> may well do it for you.
>>>>>>>
>>>>>>> Regards,
>>>>>>>
>>>>>>> Iain
>>>>>>>
>>>>>>>
>>>>>>> "Gav" <spam@xxxxxxxx> wrote in message
>>>>>>> news:umTHXtm3FHA.2532@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>>>> Hi all,
>>>>>>>>
>>>>>>>> I am trying to create a report that is addressing labels so the
>>>>>>>> details is Formated with multiple columns. Sometimes the query for
>>>>>>>> the report can return the same address several times. Is there some
>>>>>>>> sort of trick I can do with the design of the report so that if an
>>>>>>>> address is returned more than once it only shows it once?
>>>>>>>>
>>>>>>>> Thanks
>>>>>>>> Gav
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: Showing distinct records in labels
    ... you then 'simply' add these to the command SQL ... Alternatively, instead of building recordselectionformula and setting it, ... standard interface for doing this. ... As long as the selection is in the record selection formula, ...
    (microsoft.public.vb.crystal)
  • Re: Showing distinct records in labels
    ... So I cannot put them into the command. ... add them to the command et voila! ... >> complains that the field in the recordselectionformula is invalid. ... >>> Iain ...
    (microsoft.public.vb.crystal)