I have been trying to make this formula work in Select Expert:

 

If {?DateType} = True
Then {CustomerOrder.OrderDate} in {?StartDate} to {?EndDate}
Else {CustomerOrder.DueDate} in {?StartDate} to {?EndDate}
AND
(({EstAttributes.AttributeTypeKey} = 96 and {EstAttributes.Value} = 'Y' and {report_PendingOrdersByQuantity0.goodqty}<>{CustomerOrder.QuantityOrdered})
xor
(({report_PendingOrdersByQuantity0.goodqty} > ({CustomerOrder.QuantityOrdered}*1.1)) or
({report_PendingOrdersByQuantity0.goodqty} < ({CustomerOrder.QuantityOrdered}-({CustomerOrder.QuantityOrdered}*0.1)))))

 

However, I always get a ton of repeted records and the dates don't match the selected ?StartDate and ?EndDate chosen

 

I'm troubleshooting the formula and only got part of it to work properly:

 

but it doesn't give me the evaluation I need (first formula), I want the formula to evaluate both logics after ?DateType logic and select either one or the other possibility but not both. How can I do that? Does Crystal Reports Select Expert work with logical OR/XOR statements or just logical AND statement? If not, I wonder how I could work around it.

 

Thanks in advance for your help.

 

-Rafael

 

 

This does not make sense to me...my guess is what you are looking for is that one of these fields might be null and if that is the case then you want to look at the other field, correct?

You also need to parenth your entire 'if-then' to move on to your AND statement, otherwise it will stop as soon as it meets the first if then condition (which is all records) and ignore the rest of your statements...

 

(If isnull({CustomerOrder.OrderDate})=False Then {CustomerOrder.OrderDate} in {?StartDate} to {?EndDate}
Else {CustomerOrder.DueDate} in {?StartDate} to {?EndDate})

AND
(({report_PendingOrdersByQuantity0.goodqty} > ({CustomerOrder.QuantityOrdered}*1.1)) OR
({report_PendingOrdersByQuantity0.goodqty} < ({CustomerOrder.QuantityOrdered}-({CustomerOrder.QuantityOrdered}*0.1)))) 

Note I did really validate the AND part of this...

 

{?DateType} parameter is a boolean that will return 'True' if user chooses {CustomerOrder.OrderDate} and 'False' if user chooses {CustomerOrder.DueDate} .

 

First part of formula works well ({?DateType} IF-THEN-ELSE validation), I parenthed it properly now, thanks.

 

Formula also works well combined with second part or first logical AND statement ({report_PendingOrdersByQuantity... )  alone.

 

Problem arrives when second part of the formula gets more complicated and needs to compare two different situations/expressions that cannot be both true. After a value of 'True' is returned from the second part of the formula, then it will be compared to first part of formula for a final 'True' value that will actually display the record.

 

Now, the only way for the second part of formula evaluation to work is to add a logical XOR statement provided that

T T = F

T F = T

F T = T

F F = F

 

The first formula I wrote is the one I want to work:

 

(If {?DateType} = True
Then {CustomerOrder.OrderDate} in {?StartDate} to {?EndDate}
Else {CustomerOrder.DueDate} in {?StartDate} to {?EndDate})
and
(({EstAttributes.AttributeTypeKey} = 96 and {EstAttributes.Value} = 'Y' and {report_PendingOrdersByQuantity0.goodqty}<>{CustomerOrder.QuantityOrdered})
xor
(({report_PendingOrdersByQuantity0.goodqty} > ({CustomerOrder.QuantityOrdered}*1.1)) or
({report_PendingOrdersByQuantity0.goodqty} < ({CustomerOrder.QuantityOrdered}-({CustomerOrder.QuantityOrdered}*0.1)))))

 

Hope this makes sense, thanks again.

Formula1 as:

({EstAttributes.AttributeTypeKey} = 96 and {EstAttributes.Value} = 'Y' and {report_PendingOrdersByQuantity0.goodqty}<>{CustomerOrder.QuantityOrdered})

Formula2 as:

(({report_PendingOrdersByQuantity0.goodqty} > ({CustomerOrder.QuantityOrdered}*1.1)) or
({report_PendingOrdersByQuantity0.goodqty} < ({CustomerOrder.QuantityOrdered}-({CustomerOrder.QuantityOrdered}*0.1))))

Place each formula on your details in the report and tweak them independently until you get them giving you back the values correctly and you are confident with them as seperate parts, then add the formulas back into your select statement... probably would be something like:

(If {?DateType} = True
Then {CustomerOrder.OrderDate} in {?StartDate} to {?EndDate}
Else {CustomerOrder.DueDate} in {?StartDate} to {?EndDate})
and
( mailto:%7b@formula1 - {@formula1 }

xor
mailto:%7b@formula2 - {@formula2 })