Mechanism for Allowing One Ref Item to Marked as Primary

[mykrobinson]

I'm working on a new iteration of an employee directory with a few other items baked in for ease of access. One things I want to add is emergency contacts.

I have an Employees table and an Emergency Contacts table. The primary key in the employees table is employe_id, and there is a ref in the Emergency Contacts table.

there is a column in the Emergency Contacts table called [Is Primary] and its a yes/no data type. what i would like is the ability to one have one primary contact per employee. So they can have unlimited emergency contacts, but only one marked as primary. 

I need help thinking through a formula to be able to create an action that will, upon adding a new row to emergency contacts, will check and see if there are other contacts for the employee, and if the newly added or edited row has been checkmarked as primary, AppSheet will set another row that may already be marked as primary to "False," if that makes sense.

Essentially, i want to ensure that each employee may only have a single contact flagged as the primary.

Thank you

[Marc_Dillon]

To check if there is already a primary contact or not:

ISBLANK( FILTER(
  emergency contacts ,
  AND(
    [employee] = [_THISROW].[employee] ,
    [primary]
  )
) )

To allow a new record to overwrite a previous primary contact, run a form save action, or a Bot, that runs a reference action on the old primary record that sets it to not-primary.

I've done basically the exact same thing before. I added in an [overwrite?] column, that's yes/no, that shows up when the user selects their new contact as primary, but there already is a primary. Just to be sure.