Crystal Reports - Null values - formula for checking if several values are null

[Former Member]

Hi there

Can anyone help me?

I need to write a formula to check if some of my fields are null, but I'm not sure how to do this.

There are 4 items contained within one field which I need to check to see if they are null / blank and then mark these as 'None'

I've tried the formula below, but now I'm finding that everything is showing as 'None' as it's only checking if they are all filled in.

if {VR_ACCESS_Broker.ACCID} <> 17 and {VR_ACCESS_CHB2B.ACCID} <> 11 and {VR_ACCESS_Fleet.ACCID} <> 9

and {VR_ACCESS_Prefs.ACCID} <> 10 then 'None'

What I really need is to individually check if one or more of the 4 ACCID's are filled in and if not then mark the field as "None".  Obviously there are loads of other ACCID's which are not filled in, so it's not a matter of stating if it doesn't = 9,10,11,17 then mark as 'None'.

I need to find a way to write an 'and / or' command but I don't know how to.

I would be grateful if you could please help.

Many thanks

Louise

[abhilash_kumar]

Hi Louise,

Try:

If (Isnull({VR_ACCESS_Broker.ACCID}) OR {VR_ACCESS_Broker.ACCID} = '')

AND

(IsNull({VR_ACCESS_CHB2B.ACCID})  OR {VR_ACCESS_CHB2B.ACCID} = '')

AND

(IsNull({VR_ACCESS_Fleet.ACCID}) OR {VR_ACCESS_Fleet.ACCID} = '')

AND

(IsNull({VR_ACCESS_Prefs.ACCID}) OR {VR_ACCESS_Prefs.ACCID} = '')

     Then 'None'

-Abhilash

[Former Member]

Hi Abhilash

It’s stating that the   =  to? must be a number.

I’ve also tried changing this to a zero and to “ “  to see if it would have the same affect but the zero returns incorrect results and the “ “ still states it needs a number .

I would appreciate any further advise.

Many thanks

Louise

[abhilash_kumar]

I'm sorry I didn't realize that the field is a number.

Get rid of the OR condition after each of the isNull conditions. E.g:

If Isnull({VR_ACCESS_Broker.ACCID})

AND

.

.

Then 'None'

-Abhilash

[Former Member]

Hi Abhilash

I'm still having problems ... I think it is because some of the ACCIDs are not null i.e. they have been selected / ticked on the quote.

I really need the 'None' value only to appear when none of the 4 have been selected.

So :-  if ACCID 1 has been selected but ACCIDs 2,3 & 4 haven't then I want to show ACCID 1's name

Else if ACCID 2 has been selected but ACCIDs 1,3 & 4 haven't, then I want to show ACCID 2's name

and so on

i.e. if none of ACCID 1, 2, 3 & 4 have been selected then I want that to show the name as 'None'

Many thanks

Louise

[DellSC]

I would try something like this (Since ACCID values appear to be numbers, you'll need to convert them to strings:

If not IsNull({VR_ACCESS_Broker.ACCID}} and {VR_ACCESS_Broker.ACCID} = 17 then

  ToText({VR_ACCESS_Broker.ACCID}, 0, '')

else if not IsNull({VR_ACCESS_CHB2B.ACCID}) and {VR_ACCESS_CHB2B.ACCID} = 11 then

  ToText({VR_ACCESS_CHB2B.ACCID}), 0, '')

else if not IsNull({VR_ACCESS_Fleet.ACCID}) and {VR_ACCESS_Fleet.ACCID} = 9 then

  ToText({VR_ACCESS_Fleet.ACCID}, 0, '')

else if not IsNull({VR_ACCESS_Prefs.ACCID}) and {VR_ACCESS_Prefs.ACCID} = 10 then

  ToText({VR_ACCESS_Prefs.ACCID}, 0, '')

else 'None'

-Dell

[Former Member]

Hi there

Thanks very much, that works to a certain extent, but not fully.

Basically, the result I'm getting is :-

Quote ID     Result

48088          None

48088          9

48090          10

48090          None

48091          None

48092          None

48094          9

48094          None

As you can see in some instances (Quote ID : 48094) there are 2 lines for this quote, whereas what I need the report to state is that, if there are any instances of 9, 10, 11 or 17, then just state 9, 10, 11 or 17, otherwise show 'None'.

So I want my results to look like this:-

Quote ID     Result                        Removed (example - I don't need to see this)

                                                     48088          None

48088          9

48090          10

                                                     48090          None

48091          None

48092          None

48094          9

                                                     48094          None

i.e. :--

Quote ID     Result

48088          9

48090          10

48091          None

48092          None

48094          9

So that I get 5 quote ID's and can count 2 for 'None', 2 for '9' and 1 for '10'.

Can you please help?

Many thanks

Louise

[DellSC]

Ok, what you're going to have to do is Group on Quote ID and put the data in the group footer instead of in the details.  Then you'll use some formulas with variables to do the calculation.  It might look something like this:

{@InitVars}

StringVar broker := "";

StringVar chb2b:= "";

StringVar fleet := "";

StringVar prefs := "";

Put this formula in the Quote ID group header.

{@SetVars}

StringVar broker;

StringVar chb2b;

StringVar fleet;

StringVar prefs;

If not IsNull({VR_ACCESS_Broker.ACCID}} and {VR_ACCESS_Broker.ACCID} = 17 then

   broker := ToText({VR_ACCESS_Broker.ACCID}, 0, '');

if not IsNull({VR_ACCESS_CHB2B.ACCID}) and {VR_ACCESS_CHB2B.ACCID} = 11 then

  chb2b := ToText({VR_ACCESS_CHB2B.ACCID}), 0, '');

if not IsNull({VR_ACCESS_Fleet.ACCID}) and {VR_ACCESS_Fleet.ACCID} = 9 then

  fleet := ToText({VR_ACCESS_Fleet.ACCID}, 0, '');

if not IsNull({VR_ACCESS_Prefs.ACCID}) and {VR_ACCESS_Prefs.ACCID} = 10 then

  prefs := ToText({VR_ACCESS_Prefs.ACCID}, 0, '');

Put this in the details section.  It will run even though the section is suppressed.

{@DisplayVars}

StringVar broker;

StringVar chb2b;

StringVar fleet;

StringVar prefs;

if broker <> "" then broker

else if chb2b <> "" then chb2b

else if fleet <> "" then fleet

else if prefs <> "" then prefs

else 'None'

Put this in the appropriate place in the  Quote ID group footer.

-Dell

[Former Member]

Hi Dell

Thanks very much for the information.

I've tried your suggestion above, but am now getting 'None' for every quote ID.  I think it's because, in the majority of cases, at least one of the four (broker, chb2b, fleet or prefs) are being selected, so when the formula is ' else if ' for each one there's always going to be one not selected.

I don't know how to write a formula to say the following for  {@DisplayVars}

if broker <> "" then broker

OR   if chb2b <> "" then chb2b

OR   if fleet <> "" then fleet

OR   if prefs <> "" then prefs

else 'None'

The above formula shows a 'needs a Boolean' error at the ' then fleet' section on the 3rd row.

I basically need to check if the quote has broker or chb2b or fleet or prefs then put broker, chb2b, fleet or prefs down, otherwise tell me that none of these 4 have been selected by showing 'none'.

Sorry, I'm maybe explaining this all wrong.

Thanks so much for helping.

Louise

[DellSC]

The Or is not really going to get what you want - I think the problem is somewhere further up in the processing.

I would create a set of 4 formulas for testing/debug purposes.  Each formula will give you the value of one of the variables and they'll be in this format:

{@brokerValue}

StringVar broker

Temporarily create a second details section under the one where the {SetVars} formula is located.  In it put the following across the section:

{VR_ACCESS_Broker.ACCID}} {@brokerValue}

{VR_ACCESS_CHB2B.ACCID} {@chb2bValue}

{VR_ACCESS_Fleet.ACCID} {@fleetValue}

{VR_ACCESS_Prefs.ACCID} {@prefsValue}

This will allow you to see what the actual values are that are coming through in your data and then verify what should be shown in {@DisplayVars}.

Make sure that {InitVars} is in the QuoteID group header, {SetVars} is in a details section,  and DisplayVars is in the QuoteID group footer - they have to be in the right order in order for this to work correctly.  You may also have to add "WhilePrintingRecords;" to the top of each formula to get it to work.

-Dell

[Former Member]

Hi Dell

I've got lots of variables on each 2nd details line e.g.

Quote No     Broker     CHb2b     Fleet     Prefs

48118          4               4               9          14

48113          10             10             4          15

48111          4               4               4           4

48112          4               2               2           2

So the result I'd like to see from this is

48118 = Fleet

48113 = Prefs

48111 = None

48112 = None

I took a small sample of quotes (6) and I think my real problem is that, on looking through the data for those 6 quotes, there are almost 9000 records and every line on the 2nd details line are different for the same quote.

I'm really not sure how to fix this problem ... and I've another 4 choices to add and compare on top of these 4.

I had thought about using sub-reports for each option, but then I'll still have the problem of showing what quotes don't have any of the options selected.

Sorry Dell, but can you help me any further?

Thanks for the time you've already taken.

Louise

[DellSC]

Can you save the report with data and attach it here so that I can look at it?

You'll have to go to the "Advanced Editor" to attach the file and you'll also have to change the ".rpt" extension on the file name to ".txt" in order to update it.

Thanks!

-Dell

[Former Member]

Hi Dell

I've saved off the file for you giving just 6 quotes as an example, but I've had to e-mail you the file as it wouldn't allow me to send it here.

Thanks very much

Louise

[DellSC]

I have not received the file at either of the email addresses that are in my profile.

-Dell