Disappearing report lines

[Former Member]

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.

[Former Member]

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.

[Former Member]

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.