formula help

[devnaren]

Hi,

I am using CRXI. I have a table like:

Department:
DeptNo DeptName
1 Finance
2 Stock
3 Sharing

Employee:
EmpID DeptID EmpName EmpDescription DeptName
1 1 Sam Finance Works Finance
2 1 John Working for Finance Finance
3 1 Robert Doing Financial Stuff Finance
4 2 Chris Stock Finance Stock
5 3 Ken sharing stuff Sharing


Now in the report i am using fields

EmployeeID EmployeeName

I created three parameters
1.DepartmentName which is dynamic
2.EmpID which is static
3.EmpDescription which is static

Now the record selection formula should be written as:

1.if the user selects only DeptName as Finance from LOV then he should get all the employees who are in Finance Department.

2.if the user selects DeptName as Finance and EmpID as 3 then
it will return the record to the report if that emp id is present in that department. otherwise it will be blank on the report.

3.if the user selects DeptName as Finance and EmpDescription
entered just as Finance then it should display the records from the DeptName Finance who have the EmplDescription like
Finance.

for this i written as:

if length({?DeptName}) >= 1 then
{DeptName}={?DeptName}

else if length({?DeptName}) >= 1 and length({?EmpID}) >= 1 then
{EmpID}={?EmplID}

else if length({?DeptName}) >= 1 and length({?EmpDescription}) >= 1 then
{EmpDescription} like '%{?EmpDescription}%'

when i am passing the parameters as for DeptName as Finance and Description as 'Finance' then
its getting all records from the table whose description is like 'Finance'.

I want to write that formula to return only Description like 'Finance' from the DeptName 'Finance'


Can anyone help me out how to solve this.

 

[smcnulty2000]

I would rewrite it this way:

(if length({?DeptName}) >= 1 
 then {DeptName}={?DeptName}
else 1=1) 

and 

(if length({?EmpID}) >= 1 
then {EmpID}={?EmplID}
else 1=1)

and 

(if length({?EmpDescription}) >= 1
then {EmpDescription} like '%{?EmpDescription}%'
else 1=1 )

Note the parentheses around each section.

This separates the logic into little modules so you don't do something too convoluted to yourself in a complex if-then tree.