Formula Help - Only show one line of data

[starrynight99]

I have a report that shows all employees (active and termed) within two companies.  Due to recent company transfers, the report is now showing two lines, one line showing terminated in their old company and one line showing active in their new company.

We need the report to show only the active line if the employee has a two lines.  Does anyone know a formula that can do this?  We do have a status code in the report showing if the line is active or termed.

[starrynight99]

Even if the formula would be something like if "John Doe" and "Company A" then do not include that would work

[bus_pass_man]

The pattern you are dealing with seems to be somewhat akin to a slowly changing dimension.  How you filter out the non-active records depends on what metadata/data is available.  Some indication somewhere in the record that the employee has been terminated in one company would probably be necessary. You speak of "one line showing terminated in their old company".  Depending on what that is could be what you would be looking for.

What thought has been given to tracking the historic data in the old company of the employees who were transferred to the new company?

This really is a modelling problem, not a report author problem, which would suggest that it cannot be ruled out that other situations could exist.

[MFGF]

Even if the formula would be something like if "John Doe" and "Company A" then do not include that would work

Taking into account what bus_pass_man said (which is the most robust way to do this), in the short term you could perhaps count the number of statuses per employee in your report, and set up a filter that brings back all the employees with a count of 1, and also those employees with a count of 2 and a status of 'Active'. eg:

count([status code] for [Employee no]) = 1 OR (count([status code] for [Employee no]) = 2 AND [status code] = 'Active')

Let us know if this gets you any closer...

Cheers!

MF.