Search the whole column

[MHann]

I'm trying to compare one field on one line against fields in a column on the same report. Similar to vlookup in excel. From below: If the formula is able to find an employee who is also a supervisor it will give a certain output.
Is there a way to do this?

Employee Supervisor @formula
1 2 ?
2 2
3 3
4 3
5 3

Thank you in advance.

 

[lbass]

You could try:

if {table.employee} = {table.supervisor} then //rest of formula

You could also consider adding the table a second time and rename table_1 table_sup. Then link {table_sup.employee} to {table.supervisor} field.

Then insert a group on {table_sup.employee} and a second group on {table.employee} to show all the employees under the supervisor.

-LB

 

[MHann]

if {table.employee} = {table.supervisor} then //rest of formula

Is there a way to do it when both fields are in the same table?

 

[lbass]

This formula IS referencing the same table.

-LB

 

[MHann]

Ok. Thank you. I'll give it a try.

 

[MHann]

When I use that formula it only compares the fields that are on the same row. I'd like to compare Employee 1's supervisor to the entire Employee column.

I think I stated this incorrectly I also wrote my example wrong it should have been:

Employee Supervisor @formula
1 2 ?
2 2 ?
3 2 ?
4 1 ?
5 1 ?

 

[lbass]

What is the formula supposed to show as a result? What are you trying to do? If you want to organize data according to supervisor, then try my second suggestion above, and add the table a second time.

-LB

 

[MHann]

I'm trying to create a field that says "Supervisor" if the employee supervises another employee in the report.

 

[lbass]

You need to add the table a second time in order to write this formula. Let me restate what I said earlier, with a change or two. Add the employee table a second time, renaming the alias table employee_sup. The use a left join FROM employee.supervisor TO employee_sup.employee.

Then create a formula like this for the detail section:

if isnull({employee_sup.supervisor}) then
"Supervisor" else
"No"

-LB

 

[MHann]

Thank you! Worked perfectly!