If/then/else in Formulas 1

[Creakinator]

This is a very basic issue but I can't seem to get it to work. First off - CRXI with SQL server 2005.

I have two formulas that I need someone to point out my mistake in:

1. First formula
//==============
if {CUSTOMER.T_CUSTOMER_TYPE} = 'SO' then
{CUSTOMER.ADDRESS1}
else
{CUSTOMER.ADDRESS1} & ' - ' & {CUSTOMER.COMPANY_NAME}
//=====================
Nothing shows in the formula in the report. The current record I am looking at has the {CUSTOMER.T_CUSTOMER_TYPE} = "RSO" so the formula should give me the "Blythe - RSO" in the formula field. I've tried SO in double quotes ("SO"). I can't see what the issue is.

2. The second formula is:
//=========================
numbervar LenAgencyNum;
stringvar firstPart;
stringvar endPart;

LenAgencyNum := len({V_PARENTCASE_INFO.T_POLICE_REF});
firstPart := left({V_PARENTCASE_INFO.T_POLICE_REF}, LenAgencyNum - 2);
endPart := right({V_PARENTCASE_INFO.T_POLICE_REF}, 2);

if {CUSTOMER.T_CUSTOMER_TYPE} = "SO" then
firstPart + "-" + endPart
else
{V_PARENTCASE_INFO.T_POLICE_REF}
//================
So that the V_PARENTCASE_INFO.T_POLICE_REF is split into two parts (Ex: 12121213 will become 121212-13" if the agency {CUSTOMER.T_CUSTOMER_TYPE} = "SO", if not the V_PARENTCASE_INFO.T_POLICE_REF will stay the same. As above, I get no answer in the formula field.

For both formulas, if I put in the original field on the report, it shows data. The formula editor shows no errors. All other fields are fine. I have another if/then in a formula that works perfectly but it is an If isnull(Field Name) then ....

Here's the SQL for the table, it you need that:
SELECT V_CASE_AGENT_CONTACT.LAST_NAME
, V_CASE_AGENT_CONTACT.FIRST_NAME
, LU_PRIORITY_SETTER.FULL_NAME
, X_PRIORITY.REPORT_SENT_ON
, V_PARENTCASE_INFO.T_POLICE_REF
, X_PRIORITY.PRIORITY_REASON
, LE_PRIORITY_SETTER.value
, LU_ASSIGNED_NAME.FULL_NAME
, X_APPROVAL.ASSIGNED_DATE
, X_APPROVAL.DUE_DATE
, LE_EXAM_ASSIGNED.value
, CASES.NAME
, LE_PRIORITY.value
, V_CASE_AGENT_CONTACT.X_EMPLOYEE_ID
, X_PRIORITY.DATE_CHANGED
, LE_PRIORITY_REASON.value
, CUSTOMER.COMPANY_NAME
, CUSTOMER.ADDRESS1
, CUSTOMER.T_CUSTOMER_TYPE
, LU_PRIORITY_SETTER.EMAIL_ADDR
, LE_INCIDENT_TYPE.value
FROM
((((((((((((LIMS_SBCSD.dbo.X_PRIORITY X_PRIORITY
LEFT OUTER JOIN LIMS_SBCSD.dbo.CASES CASES_PRIORITY
ON X_PRIORITY.CASES = CASES_PRIORITY.NAME)
LEFT OUTER JOIN LIMS_SBCSD.dbo.LIST_ENTRY LE_PRIORITY
ON X_PRIORITY.PRIORITY = LE_PRIORITY.NAME)
LEFT OUTER JOIN LIMS_SBCSD.dbo.LIMS_USERS LU_PRIORITY_SETTER
ON X_PRIORITY.USER_NAME = LU_PRIORITY_SETTER.USER_NAME)
LEFT OUTER JOIN LIMS_SBCSD.dbo.LIST_ENTRY LE_PRIORITY_REASON
ON X_PRIORITY.PRIORITY_LIST = LE_PRIORITY_REASON.NAME)
LEFT OUTER JOIN LIMS_SBCSD.dbo.CASES CASES
ON CASES_PRIORITY.NAME = CASES.NAME)
LEFT OUTER JOIN LIMS_SBCSD.dbo.X_APPROVAL X_APPROVAL
ON CASES.NAME = X_APPROVAL.CASES)
LEFT OUTER JOIN LIMS_SBCSD.dbo.V_PARENTCASE_INFO V_PARENTCASE_INFO
ON CASES.PARENT_CASE = V_PARENTCASE_INFO.NAME)
LEFT OUTER JOIN LIMS_SBCSD.dbo.LIST_ENTRY LE_EXAM_ASSIGNED
ON CASES.X_TYPE_OF_SUBCASE = LE_EXAM_ASSIGNED.NAME)
LEFT OUTER JOIN LIMS_SBCSD.dbo.LIST_ENTRY LE_INCIDENT_TYPE
ON CASES.INCIDENT_TYPE = LE_INCIDENT_TYPE.NAME)
LEFT OUTER JOIN LIMS_SBCSD.dbo.LIMS_USERS LU_ASSIGNED_NAME
ON X_APPROVAL.USER_NAME = LU_ASSIGNED_NAME.USER_NAME)
LEFT OUTER JOIN LIMS_SBCSD.dbo.V_CASE_AGENT_CONTACT V_CASE_AGENT_CONTACT
ON V_PARENTCASE_INFO.NAME = V_CASE_AGENT_CONTACT.CASE_NAME)
LEFT OUTER JOIN LIMS_SBCSD.dbo.CUSTOMER CUSTOMER
ON V_PARENTCASE_INFO.T_POLICE_DPT = CUSTOMER.NAME)
LEFT OUTER JOIN LIMS_SBCSD.dbo.LIST_ENTRY LE_PRIORITY_SETTER
ON LU_PRIORITY_SETTER.X_USER_POSITION = LE_PRIORITY_SETTER.NAME
WHERE
CASES.NAME = '11-01871-D-02' and X_APPROVAL.APPROVAL_TYPE = 'ANALYST'
//==================

Thanks for help.

Christy

 

[hilfy]

To try to debug this, I would create a formula that looks like this:

{CUSTOMER.T_CUSTOMER_TYPE} = "SO"

Put this on the report. It should evaluate to true or false (or null if the field is null.) See what value this is returning - this will help you determine where in the formula the error is occurring.

Also, if either {CUSTOMER.ADDRESS1} or {CUSTOMER.COMPANY_NAME} is null, the formula will evaluate to null if the customer type is not SO. In that case you'll either need to account for nulls in your formula or create a separate formula for each field to translate null fields into a blank string and use those formulas instead of the fields.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Creakinator]

Checking for the NULL in the bottom section worked.

First Formula is:
if {CUSTOMER.T_CUSTOMER_TYPE} = 'SO' then
{CUSTOMER.ADDRESS1}
else
if isnull({CUSTOMER.ADDRESS1}) then
{CUSTOMER.COMPANY_NAME}
else
{CUSTOMER.ADDRESS1} & ' - ' & {CUSTOMER.COMPANY_NAME}

Formula 2 is OK now too.

Thanks for your help!!