Sum function ignoring some records

[RevItUp]

I am using Ver.8. against an Oracle DB (Remedy ODBC) I have 2 fields defined to enable me to sum the qty of records based some conditions:
FieldName1: If FIELD-A = VALUE-1 or VALUE-2
or Field-B = VALUE-3 or VALUE-4 then 1 else 0.
FieldName2: If FieldName1 = 0 then 1 else 0.
The 2nd field (FieldName2) should count all the records that did not pass the test for FieldName1. At a summary level in the report I do a SUM of both fields. FieldName1 counts the records correctly but Fieldname2 does not. The records are in the report - I can drill down and see them. In every case the records not being counted have a blank in one of the fields(RECEIVER) that FieldName1 tests for. Those records that fail the test for FieldName1 but that do have a value in RECEIVER do get counted correctly.
I've tried variations for FieldName2 as well (just to see if I can find out what's in there):
FieldName2: If FieldName1 = 0 or RECEIVER = &quot; &quot; or RECEIVER = &quot;&quot; or length(RECEIVER) < 2 or isnull(RECEIVER)...
But the records are still not counted. However if I test for some other value 1st such as:
FieldName2: If RECEIVER = &quot;SOME VALUE&quot;
then the records do get counted. This of course is impractical because I can't predict every value that will occurr in the field. So it seems my problem is with this specific field. FieldName1 actually tests for values in 3 different fields. You may be thinking 'test the values in FieldName2 in reverse (<>) of FieldName1' but I can't unless I know what's in there - like a null or space or something. Is there some condition that can be tested in this &quot;blank&quot; field. Does anyone know what's in this field or what's happening here?
I hope I've explained this well enough. Thanks for your help.

 

[Ngolem]

try this formula

//@FieldName1

WhilePrintingRecords;

If {Table.FIELD-A} in [ VALUE-1, VALUE-2 ] or
{Table.Field-B} in [ VALUE-3, VALUE-4 ] then
1
else
0;


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[lyanch]

The test for null needs to come first or the evaluation won't work correctly.

extending Jims formula:

If (not isnull({Table.FIELD-A}) and {Table.FIELD-A} in [ VALUE-1, VALUE-2 ]) or
(not isnull({Table.Field-B}) and {Table.Field-B} in [ VALUE-3, VALUE-4 ]) then
1
else
0;

That should properly give a 0 where the {Table.Field-A} etc is null...

Lisa

 

[Ngolem]

Thanks Lisa...thought of that after posting

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[RevItUp]

Hi Jim, Lisa and thank you.
It's still not responding the way it should, here's the code maybe that'll help - it's actually on an isnull rather than not condiion. The receiver field is the only one that has nulls.
if isnull({HPD:HelpDesk-ShrPeople_Join.Receiver Group}) and
({HPD:HelpDesk-ShrPeople_Join.Assigned To Group+} in [&quot;US-ISSD-Windsor&quot;, &quot;US-ISSC-Windsor&quot;, &quot;US-ISSD-Tier2-Windsor&quot;,
&quot;US-ISSD-Quality of Service&quot;, &quot;US-GIA/SRA Acct Maint-Americas&quot;, &quot;US-Apps-Remedy Support-Windsor&quot;]) or
{HPD:HelpDesk-ShrPeople_Join.Receiver Group} in [&quot;US-ISSD-Windsor&quot;, &quot;US-ISSC-Windsor&quot;, &quot;US-ISSD-Tier2-Windsor&quot;,
&quot;US-ISSD-Quality of Service&quot;, &quot;US-GIA/SRA Acct Maint-Americas&quot;, &quot;US-Apps-Remedy Support-Windsor&quot;] or
{HPD:HelpDesk-ShrPeople_Join.Receiver} = &quot;WEBWEB&quot;
then 1
else 0
thanks,
George

 

[RevItUp]

FYI: Finally cracked it. I needed a test of the null/not null in every block of code that touches the null field - so not just the 1st block. I.E.:
if isnull({HPD:HelpDesk-ShrPeople_Join.Receiver Group}) and
({HPD:HelpDesk-ShrPeople_Join.Assigned To Group+} in [&quot;US-ISSD-Windsor&quot;, &quot;US-ISSC-Windsor&quot;, &quot;US-ISSD-Tier2-Windsor&quot;,
&quot;US-ISSD-Quality of Service&quot;, &quot;US-GIA/SRA Acct Maint-Americas&quot;, &quot;US-Apps-Remedy Support-Windsor&quot;])
or not isnull({HPD:HelpDesk-ShrPeople_Join.Receiver Group}) and
{HPD:HelpDesk-ShrPeople_Join.Receiver Group} in [&quot;US-ISSD-Windsor&quot;, &quot;US-ISSC-Windsor&quot;, &quot;US-ISSD-Tier2-Windsor&quot;,
&quot;US-ISSD-Quality of Service&quot;, &quot;US-GIA/SRA Acct Maint-Americas&quot;, &quot;US-Apps-Remedy Support-Windsor&quot;]
or not isnull({HPD:HelpDesk-ShrPeople_Join.Receiver Group}) and
{HPD:HelpDesk-ShrPeople_Join.Receiver} = &quot;WEBWEB&quot;
then 1
else 0