Skip to Content
author's profile photo Former Member
Former Member

Disappearing report lines

Post Author: blagdonman

CA Forum: Formula

Please help while I have some hair left!

I have a report which operates on two linked tables named Events and Matches. The report creates lines composed from several fields in Events and one field in Matches, lets call it "Title". It was working fine while every record in Events had a valid link to Matches. Things changed, as they do, and the link between the tables became optional. So now, where no match is expected a zero value occurs in Events where the link to Matches would usually be.

I replaced the required Matches field in the report with a formula that said (in pseudo code): if the link in Events is null or zero valued then return the string "No match exists" else return the value of the Title field from Matches.

Much to my amazement this didn't work - the whole report line was blank, even the fields from Events didn't show up!

If I re-jig the "else" part of the formula to output the string "A match exists." instead of the Title field then I see the whole line.

As soon as I introduce the Title field back into the formula I get blank lines again even though, when the link in Events is zero, I'm not processing that part of the formula that uses the field name! I don't get it! CR seems to look into the formula, see the reference to the field in Events and assume that I'm going to process it.

Advice is welcomed, and necessary, to preserve my sanity.

Thanks,

J.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 09, 2008 at 11:11 PM

    Post Author: Jagan

    CA Forum: Formula

    I assume you've got a join like:

    Select *From events eInner Join matches m On m.id = e.id

    and your events with no matches have a 0 value in the events.id field.Either create a dummy 0 id record in the matches table so that these events always match something, or change your Crystal link to be a left join from events to matches.If you do the latter, then everywhere you use a field in the matches table you'll need to check it with IsNull() first.It sounds like each event can have multiple matches (pun intended) in the matches table, so if you use a left join then you'd better not try to use the matches table in the record selection formula.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 10, 2008 at 01:58 AM

    Post Author: blagdonman

    CA Forum: Formula

    Thanks for your reply - here's a few comments:

    - I had only used the CR Database Expert and the Select Expert to set up table, record and field selection and to make sure the links were the right way round.

    - The relationship from Events to Matches is many to one (which is the opposite to what you thought).

    - I originally had an inner join from Events to Matches.

    - I used the CR Database Expert to change to a Left Outer join (you had given the hint) and it worked. The whole thing now turns out exactly as I want it. The only remaining problem is that I'm not sure why!

    Thanks for your interest and help - we did it!!!!

    Regards,

    J.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.