How to delete duplicated employee records due to movement

heron

Hi,

 

I have a scenario where we have created gap process to hire new resources, essentially adding new lines in our employee list.

 

However one problem coming up is sometimes we created a gap and then an existing/internal team member applied and got the job. Not sure if there is a way we can somehow delete the original record based on the employee ID (that's the code for our employee list)? Since the newly created gap has some hiring data attached to it, we will like to keep the new line.

 

In below example, essentially we need to delete line 1 first and then update line 2 current code with employee ID. 

 

Appreciate the help!

Heron

 

kevin.cho

I'm interpreting this as:

 

"1. We want to delete records which has the same value for the "Code" as the "Employee ID" property in another record

2. We want to then update the codes with the employee ID, such that the two are aligned".

 

Is this correct?

 

If so:

To solve for 1., create a module with 5 line items, dimensioned against "Employees" (or the appropriately named list). 

Line Item Name	Formula	Format	Note
Employee ID	Employees List.Employee ID	Text	Assuming the "Employee ID" is stored in a Text field in the list as a property.
Employee ID Item	FINDITEM(Employees List, Employee ID)	Employees List	Converts the Employee ID value into the item we want to retrieve.
Employee List Item	ITEM(Employees List)	Employees List	Returns self Employee item
Exists in Other?	'True Flag'[ANY: Employee ID Item] AND Employee ID Item <> Employee List Item	Boolean	Checks if the Employee ID is already the code of any item, excluding itself
True Flag	TRUE	Boolean	Helper line item

 

You can then create a "Delete from List Using Selection" action using the "Exists in Other?" boolean formatted line item. This will mark any record that has its Code as the same value as "Employee ID" in any other record. 

 

Example - where B is the new record that contains the Employee ID of A. 

 

2. To update the code, 

Create two line items: 

Code (if not already existing in your system module) = CODE(ITEM(Employees List))

Code mismatch? = Employee ID <> Code

 

Create a saved view with columns showing "Employee ID", and filter on "Code Mismatch?" = TRUE 

Create an import matching on Name, and updating the Code value mapped to "Employee ID". 

 

If you can ensure that this runs in a process so that step 1 (deletion) occurs before step 2, then you can stop here; otherwise, better to include some more checks in Step 2 to ensure you don't try to create records with duplicate codes (as the action will error out and cause a poor user experience).