Strings and Selection Criteria 1

[donnarenia]

How can I write a formula that will do the following:

Show me all the invoices in a date range selected by a paramenter (?invoice date) however, some of the invoices have adjustments within the same date range....for those invoices I want to show the DATE RECEIVED DATE instead of the INVOICE DATE.

I wrote this formula:
if {AR.InvoiceDate}in {?Invoice Date} and {AR.Type}
= "I" and {CASH.Type} in ["A", "P"]
then {AR.InvoiceDate} else
if {CASH.DateReceived} in {?Invoice Date} and {AR.Type} = "I" and {CASH.Type}in ["A", "P"]
then {CASH.DateReceived}

but when I use it, it only show me the dates that have adjustments and the INVOICE DATE disappear.

Any Suggestions?

 

[Turkbear]

Hi,
try a slight rewrite to see if it helps:

If
(
(AR.Type} = "I" 
and 
{CASH.Type}in ["A", "P"]
)
AND
(
  if {AR.InvoiceDate}in {?Invoice Date} 
   then {AR.InvoiceDate} 
  Else
    If {CASH.DateReceived} in {?Invoice Date}
    then
     {CASH.DateReceived}
)

As long as 1 record cannot have BOTH
{AR.InvoiceDate}in {?Invoice Date}
and {CASH.DateReceived} in {?Invoice Date}
this should work.









To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[donnarenia]

I get the following error message:

The keyword 'then' is missing

 

[fisheromacse]

maybe this permutation:

If
({AR.Type} = "I"
and
{CASH.Type}in ["A", "P"]
AND
{AR.InvoiceDate}in {?Invoice Date})
then {AR.InvoiceDate}
Else
(If
({AR.Type} = "I"
and
{CASH.Type}in ["A", "P"]
AND
{CASH.DateReceived} in {?Invoice Date})
then
{CASH.DateReceived}
)

 

[lbass]

A slight change to Turkbear's solution:

If (AR.Type} = "I" and
{CASH.Type}in ["A", "P"] then
(
if {AR.InvoiceDate} in {?Invoice Date} then
{AR.InvoiceDate} Else
If {CASH.DateReceived} in {?Invoice Date} then
{CASH.DateReceived}
)

-LB