I am developing an Autolaunched flow to lookup a list of existing Salesforce Affiliation records (a child object) between an Account and a Contact. In my example, I am looking to check to see if a Contact has affiliations to a United States congressperson's account record (two senators and one congressperson based on the contact's residential congressional district). We're using the NPSP Affiliation custom object to establish this relationship. The ultimate goal is that contacts should have two senators and one congressperson.
First, in the Record Lookup step, I just add the Contact Id and assign that to a sObject variable, where I get the record Id, mailing state and a custom field called Federal District.
Then I do a fast lookup where I take the fields in the sObject variable to lookup an account that has the same federal district or is a senator in that state. This is under a field called Congressional Position.
Then another Fast Lookup where we locate affiliation records where the account Id from the account (lawmaker) fast lookup and contact id from the contact fast lookup matches. The goal now is to determine if a contact already has a record with a congressperson and if not, create a new one.
For affiliation records that exist, we update those records. For those that don't, we create a new record. At the end of the flow, a contact will now be affiliated with two senators and one congress member.
However, while the flow works great, it sometimes creates duplicates senate or congressional records instead of updating them. I have the flow running on a process that checks if a contact is new or has had the congressional district field updated. Are there any steps I can take or any clarity on how I can have the flow only create records for affiliations that don't exist and just update for those that do.