Formula Order

[Former Member]

Hello,

I'm using 2 formulas below;

{Interaction.Date_Created} in YearToDate and

{Interaction.Call_Sub_Type} = "FC539 Letter Details" and

{Employee_1.Full_Name} = "Joe Bloggs" and

isnull({Interaction.Closure_Code}) or

{Interaction.Date_Created} in YearToDate and

{Interaction.Closure_Code} in ["Returned to CC to Investigate", "Feedback Only"] and

{Employee_1.Full_Name} = "Joe Bloggs"

As you can see I'm using 'or' but when I swap the 2 formulas round the report pulls back different results. Is there any reason why the order 2 formulas are put in to the formula expert make a difference?

Many thanks,

Alex O

[Former Member · Accepted solution]

Cliff and V361-V361, you are both correct, but the original issue is

>

> Hello,
> 
> I'm using 2 formulas below;
> 
> {Interaction.Date_Created} in YearToDate and
> {Interaction.Call_Sub_Type} = "FC539 Letter Details" and
> {Employee_1.Full_Name} = "Joe Bloggs" and
> isnull({Interaction.Closure_Code}) or
> 
> {Interaction.Date_Created} in YearToDate and
> {Interaction.Closure_Code} in ["Returned to CC to Investigate", "Feedback Only"] and
> {Employee_1.Full_Name} = "Joe Bloggs" 
> 
> As you can see I'm using 'or' but when I swap the 2 formulas round the report pulls back different results.  Is there any reason why the order 2 formulas are put in to the formula expert make a difference?
> 
> Many thanks,
> 
> Alex O

I believe it is becuase the null test is missing from the second formula..

[Former Member]

alex, cr will stop evaluating this type of formula once it comes across the first statement that can be evaluated as true.

[Former Member]

When in doubt, wrap it in parentheses

(
{Interaction.Date_Created} in YearToDate and
{Interaction.Call_Sub_Type} = "FC539 Letter Details" and
{Employee_1.Full_Name} = "Joe Bloggs" and
isnull({Interaction.Closure_Code}) 
)
or
(
{Interaction.Date_Created} in YearToDate and
{Interaction.Closure_Code} in \"Returned to CC to Investigate\", \"Feedback Only\" and
{Employee_1.Full_Name} = "Joe Bloggs" 
)

[Former Member]

{Interaction.Date_Created} in YearToDate and
{Interaction.Call_Sub_Type} = "FC539 Letter Details" and
{Employee_1.Full_Name} = "Joe Bloggs" and
isnull({Interaction.Closure_Code}) or

{Interaction.Date_Created} in YearToDate and
{Interaction.Closure_Code} in ["Returned to CC to Investigate", "Feedback Only"] and
{Employee_1.Full_Name} = "Joe Bloggs"

Is this one formula or two seperate formulas? I do not understand the OR .

Debi

[Former Member]

IF {Interaction.Date_Created} in YearToDate and

{Interaction.Call_Sub_Type} = "FC539 Letter Details" and

{Employee_1.Full_Name} = "Joe Bloggs" and

isnull({Interaction.Closure_Code}) THEN TRUE

ELSE

IF {Interaction.Date_Created} in YearToDate and

{Interaction.Closure_Code} in \"Returned to CC to Investigate\", \"Feedback Only\" and

{Employee_1.Full_Name} = "Joe Bloggs" THEN TRUE

IF this is being used as a record selection

[Former Member]

>

> IF {Interaction.Date_Created} in YearToDate and
> {Interaction.Call_Sub_Type} = "FC539 Letter Details" and
> {Employee_1.Full_Name} = "Joe Bloggs" and
> isnull({Interaction.Closure_Code}) THEN TRUE 
> 
>  ELSE
> IF {Interaction.Date_Created} in YearToDate and
> {Interaction.Closure_Code} in \"Returned to CC to Investigate\", \"Feedback Only\" and
> {Employee_1.Full_Name} = "Joe Bloggs"  THEN TRUE
> 
> IF this is being used as a record selection

Sharon,

That is what I would expect to see which except for needing [] around the IN data. This is why I am confused by what he wrote. I have seen a big formula someone posted that had OR statements like his formula.

Royal, Is your original formula part of a SQL statement?

The order that you place tests in a formula will always make a difference to the data returned.

Part of the difference here may be that you do not test for NULL in the second part of the formula. If you could have a null and you do not test for it, the formula stops when it hits the null. and the null or not null test must ALWAYS be before the value test for the same field.

Try modifying this line:

 {Interaction.Closure_Code} in ["Returned to CC to Investigate", "Feedback Only"] and

to

 (Not (IsNull( {Interaction.Closure_Code} ) and  
    {Interaction.Closure_Code} in 
         ["Returned to CC to Investigate", "Feedback Only"]) and 

Debi