HR – tracking member’s movement

Dear all, I have a couple of challenges that I have tried to overcome, looking for your advice. Million thanks in advance.

I have a list of 100 members in a list who joined a medical program from 01Jan21 to 31Dec21. Information collected are private & confidential. Are there any solutions I can use for the following tasks that I need to perform throughout the 12 months period?

1. At the start of the year – HR will provide me the list of members that have enrolled in the medical program, of which I will create a dedicated page to the HR to track any movement during this period.

2. During the 12 months period, HR will inform me of any new member/s coming on board or an existing member/s had left the program.

a. Task 1: I must inform and update my provider of such movement with all the personal information provided by HR.

i. Question: How can I update the existing row of information without creating a new row of the same information – but only update the existing row with the new status of the membership.

ii. Question: How to ensure the new member added is not a repeated record

iii. Question: How to ensure we deleted the correct member from the program 

iv. Question: How to set automation to email out the row of updated information to the selected provider – but the email needs to be encrypted because the email's content has PDPA information.

b. Task 2: At the end of the 12 months period, I will need the HR to verify the member list that all record is correct and updated. Then the whole circle repeat itself for Yr2022

Thank you again for your time, cheers


  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @Patrick Tan ,

    All of this is very doable in Smartsheets. I don't know if the Smatsheet notification email meets your encryption requirements.

    Building your tool is too complicated to explain in detail here. Notifications can be done with basic automation. You'll find several posts about finding duplicates in the Community. Easiest if you have unique identifier for each patient. I wouldn't delete members. I'd mark them as inactive so you maintain the record.

    Happy to continue helping.


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.