How to delete duplicated employee records due to movement




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!


Best Answer

  • kevin.cho
    Answer ✓

    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 NameFormulaFormatNote
    Employee IDEmployees List.Employee IDTextAssuming the "Employee ID" is stored in a Text field in the list as a property. 
    Employee ID ItemFINDITEM(Employees List, Employee ID)Employees ListConverts the Employee ID value into the item we want to retrieve. 
    Employee List ItemITEM(Employees List)Employees ListReturns self Employee item
    Exists in Other?'True Flag'[ANY: Employee ID Item] AND Employee ID Item <> Employee List ItemBooleanChecks if the Employee ID is already the code of any item, excluding itself
    True FlagTRUEBooleanHelper 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).