not Isnull command crystal reports with data before and after SAP upgrade

[vicky_williams]

Hi

I am trying to populate a field within crystal reports with data from one of 2 fields from the OIBT table (or text if both fields are blank):

1) - Cardcode (If available)

2) - IntrSerial (if Cardcode is not available)

3) - 'Undefined' (If Cardcode and IntrSerial are both not available)

I have the following formula

IF  (not isnull({OIBT.CardCode}))then {OIBT.CardCode}  else IF not isnull({OIBT.IntrSerial}) then {OIBT.IntrSerial} else 'Undefined'

It works in all cases except for Batches which have been transferred between depots prior to upgrading SAP from 2007 to 8.81

Data of this type shows nothing in the Cardcode field if it is displayed alone on the report - however it is still printing this field instead of the (like the Not Isnull command is not working for these items or there is something in the field which is not visible)

Is there a different command I should use instead of isnull? Or any other way around this problem?

Thanks

Vicky

[vicky_williams · Accepted solution]

@trimcardcode

{OIBT.CardCode} & "x1"

@Supplier2

if not isnull({@trimcardcode}) then IF {@trimcardcode} startswith "x" then IF not isnull({OIBT.IntrSerial}) then {OIBT.IntrSerial} else "Undefined" else {OIBT.CardCode} else IF not isnull({OIBT.IntrSerial}) then {OIBT.IntrSerial} else "Undefined"

[Former Member]

Hi Vicky

Have you tried open the batches as update under the Batch Management and check if anything is not filled in?

Kind regards

Peter Juby

[vicky_williams]

Hi Peter

I checked on the batches update for one of the items, but it does not display the CARDCODE field here, so I don't think I can fill this in

Thanks

Vicky

[Former Member]

Hi Vicky,

Try:

SELECT isnull({OIBT.CardCode},isnull({OIBT.IntrSerial},'Undefined')) FROM OIBT

Thanks,

Gordon

[vicky_williams]

Hi Gordon

I tried to put this in a new formula field but got "the remaining text does not appear to be part of the formula" (FROM OIBT was highlighted)

when I removed "FROM OIBT" the message read "Too many arguments have been given to this function" and 'Undefined' was highlighted

Thanks

Vicky