Conditional Formula Bombs Crystal Report

0
votes

I have an issue with one of my crystal reports and it's conditional formula. The formula is shown below. I have two tables that hold different customer info that link to my invhdr table. when the account code is cash I need to retrieve a field from one table and visa versa.. The trouble is crystal seems to ignore the conditional formula as written below and seems to require that a link to the field exists even though the formula should prevent the requirement to retrieve the field..

can anyone explain a possible workaround. maybe a conditional table link if it's possible? Thanks ND

if Len("" + {invhdr.ACCT}) > 0 then if {invhdr.ACCT} = 'CASH' then {CashCust.CUSTOM1}
else if {invhdr.ACCT} <> 'CASH' then {Lookup.VATREGNO} else "";

so say if invhdr.ACCT ='test' it seems to still need a link to {CashCust.CUSTOM1} all my tables are linked as inner join , not enforced

1
crystal-reportscrystal-reports-2008

1 Answers

1
votes

I suggest changing the inner joins to left outer joins (from the invhdr table to the CashCust and Lookup tables, so that invhdr is on the inside of the joins). This is unrelated to the formula - if you have inner joined all three tables, then corresponding records must exist on all three tables for rows to be returned; this is the definition of an inner join.

You may then encounter issues with the formula if invhdr.ACCT is null - a quirk of older versions of Crystal was that if any part of a formula evaluated to null, then the whole formula evaluated to null. This behaviour may have been amended in more recent versions of Crystal, but if not then "" + {invhdr.ACCT} may evaluate as null - try checking the value with the IsNull function instead.