Formula field using 5 y/n fields

[jrhessey]

I'm connecting to a MAS90 DB and using the following formula in CR 8.5.

if {AR_90_UDF_ARCustomer.Credit_Hold} = "Y" then "CH"
else
if {AR_90_UDF_ARCustomer.Cash_In_Advance} = "Y" then "CIA"
else
if {AR_90_UDF_ARCustomer.Cod} = "Y" then "COD"
else
if {AR_90_UDF_ARCustomer.Credit_Watch} ="Y" then "CW"
else
if {AR_90_UDF_ARCustomer.Do_Not_Sell} = "Y" then "DNS"

I've used a formula like this before for dates on a single field and it's always worked fine. I'm using multiple fields now, and it only returning the first field in the formula, Credit_Hold. If I switch it to COD then the cod text works. How can I get it to return fields that equal yes no matter where they are in the formula? Thanks for any ideas!

 

[synapsevampire]

I don't understand why your post states that it isn't returning the fields, it should only be returning the output, not the fields at all.

If you mean that it's only returning output based on one field and never analyzes the following fields, it may have someting to do with nulls being found, try:

whileprintingrecords;
if not(isnull({AR_90_UDF_ARCustomer.Credit_Hold}))
and
{AR_90_UDF_ARCustomer.Credit_Hold} = "Y" then "CH"
else
if not(isnull({AR_90_UDF_ARCustomer.Cash_In_Advance}))
and
{AR_90_UDF_ARCustomer.Cash_In_Advance} = "Y" then "CIA"
...etc...

You might also use the Report->Options->Convert nulls to default if you don't care about nulls in the report.

-k

 

[Turkbear]

Hi,
Your code:

if {AR_90_UDF_ARCustomer.Credit_Hold} = "Y" then "CH"
 else
 if {AR_90_UDF_ARCustomer.Cash_In_Advance} = "Y" then "CIA"
  else
  if {AR_90_UDF_ARCustomer.Cod} = "Y" then "COD"
   else 
   if {AR_90_UDF_ARCustomer.Credit_Watch} ="Y" then "CW"
    else
    if {AR_90_UDF_ARCustomer.Do_Not_Sell} = "Y" then "DNS"

Can ONLY have 1 result and that result will be whatever is FIRST evaluated to 'True'...An 'If..Then..Else..' test will stop when a true result is found.

Why not use a separate formula for each field and then use a formula to display all the Non-blank results..






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

 

[jrhessey]

Synapse -

You are correct in your assupmtion. I meant the text output based on the fields. Thanks. I will give that a try.

Turk -

I'm not saying you're incorrect but I have another report with this fomula and it works perfectly... The only difference is that it uses one field form the DB not 5.

if {SO_03SOHistoryHeader.OrderDate} = Date (2001,01,01) to Date (2001,12,31) then "2001"
else
if {SO_03SOHistoryHeader.OrderDate} = Date (2002,01,01) to Date (2002,12,31) then "2002"
else
if {SO_03SOHistoryHeader.OrderDate} = Date (2003,01,01) to Date (2003,12,31) then "2003"
else
if {SO_03SOHistoryHeader.OrderDate} = Date (2004,01,01) to Date (2004,12,31) then "2004"
else
if {SO_03SOHistoryHeader.OrderDate} = Date (2005,01,01) to Date (2005,12,31) then "2005"
else
if {SO_03SOHistoryHeader.OrderDate} = Date (2006,01,01) to Date (2006,12,31) then "2006

 

[jrhessey]

Synapse -

That works great, however, I can not create a group for the formula field now. I sthere anyway I could do that with this formula, or is there another way to do it? Thanks for your help!

 

[jrhessey]

NM Synapse. I figured it out! Thanks a lot for your help!