Ignoring an 'IF'!

[DeviousDi]

Hi all,

I have a report that is currently driving me a bit potty!

I have a formula field that SHOULD give an invoice amount if certain criteria are met, or not.

Here's the formula:

if isnull({query_status.id}) then {invoices.amount_open} else
if {@DaysOutstanding} >= 90 and
{@InternalDispute} = 'No' and
{query_status.id} <> 2
then {invoices.amount_open} else 0

The problem being that its ignoring the @DaysOutstanding rule. Any ideas?

Here are my links:

SELECT "invoices"."invoice_number", "debtor_freefields"."ff4", "invoices"."amount_open", "debtors"."debtor_number", "debtor_freefields"."ff3", "queries"."query_date", "query_status"."id", "invoices"."date_due", "standard_queries"."code"
FROM ((("OnGuard"."dbo"."debtor_freefields" "debtor_freefields" INNER JOIN ("OnGuard"."dbo"."invoices" "invoices" INNER JOIN "OnGuard"."dbo"."debtors" "debtors" ON "invoices"."debtor_id"="debtors"."id") ON "debtor_freefields"."debtor_id"="debtors"."id") LEFT OUTER JOIN "OnGuard"."dbo"."queries" "queries" ON "debtors"."id"="queries"."debtor_id") LEFT OUTER JOIN "OnGuard"."dbo"."standard_queries" "standard_queries" ON "queries"."standard_query_id"="standard_queries"."id") LEFT OUTER JOIN "OnGuard"."dbo"."query_status" "query_status" ON "queries"."query_status_id"="query_status"."id"


I'm sorry if this is an easy one to solve, I seem to be having one of those days!

Thanks in advance

Di

 

[MCuthill]

DeviousDi,

Nothing jumps off the page as a concern. It "could" be bracketting, but I don't think it should matter when you have all "AND" clauses. In case it is the contents of the individual formulas, could you provide the group with the contents of your {@DaysOutstanding} and {@InternalDispute} formula's?

That being said, it could be that I too am having one of those days and it is something obvious. haha. (definitely can't rule it out)

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[DeviousDi]

Hi Mike,

Here's the formulas:

@DaysOutstanding:

datediff("d", {invoices.date_due}, CurrentDate)

@InternalDispute:

if {standard_queries.code} in ["Q08", "Q10", "10", "CC02", "CC03"] then 'Yes' else 'No'

One good thing is, both those seem to be working fine! Lol

Di

 

[MCuthill]

Thanks DeviousDi!

Definitely nothing in those that I would think would conflict or not allow to be used in a selection critiera. Is it possible for either {invoices.date_due} or {standard_queries.code} to be Null?

If they can be null, you may/will likely need to add catches for Nulls into those formulas. If they cannot be null... might as well rule out the obvious -- please give the following a try, just adding some paranthesis to your Invoice Amount formula.

[blue]IF isNull[/blue]({query_status.id}) [blue]THEN[/blue] {invoices.amount_open} [blue]ELSE[/blue]
[blue]IF[/blue] [red]([/red]
     {@DaysOutstanding} >= 90 [blue]AND[/blue] 
     {@InternalDispute} = 'No' [blue]AND[/blue]
     {query_status.id} <> 2
[red])[/red]
[blue]THEN[/blue] {invoices.amount_open} [blue]ELSE[/blue] 0

Worth a try anyways, please advise as to if it makes any difference. Failing that, perhaps try putting just the portion in red paranthesis into it's own True or False IF and see what it returns?

Cheers!



Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

erm.... " or not allow to be used in a selection critiera" << should be "used in a formula (or selection criteria)".



Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

Try:

if isnull({query_status.id}) or
(
{@DaysOutstanding} >= 90 and
{@InternalDispute} = 'No' and
{query_status.id} <> 2
) then
{invoices.amount_open} else 0

-LB

 

[pmax9999]

Hi Di

Based on your earlier post, it seems that {standard_queries.code} can be NULL, and therefore your {@InternalDispute} formula needs to test for them. Try amending that formula to:

If 	Isnull({standard_queries.code})
Then	'No'
Else
If	{standard_queries.code} in ["Q08", "Q10", "10", "CC02", "CC03"] 
Then 	'Yes' 
Else 	'No'

Cheers
Pete

 

[DeviousDi]

Hi All,

Thanks for replying. I tried all of the above suggestions, and its still not working!

I think I'm going loopy! Lol

Di

 

[pmax9999]

I still think it likely the problem is related to Nulls. The only field I can see that isn't tested (assuming all the suggestions have been applied) is {invoices.date_due}. Is it possible that this field can be Null?

To be sure, I would include a test, by changing {@DaysOutstanding} to:

If 	IsNull({invoices.date_due})
Then 	0
Else	Datediff("d", {invoices.date_due}, CurrentDate)

Cheers
Pete