Skip to main content
Question

Set Value Of One Field If Another 2 Match

I'm trying to set a value in a field, if an email address matches an email address in a different field. For example, I have a field called Email and a report link called Merge: Existing Email. If the email address matches the value of Email, I wanted to set a status in a separate field called Existing Patient Record. I tried using a conditional like this, but it just returns 0 for all records. Any other ideas?


If([Email]=[Merge: Existing Email], 1, 0)
    This topic has been closed for replies.

    So based on the formula - your are comparing [Email] to your report link as they are different field types. 

    Can you elaborate more on what the email you are trying to match [Email] to is? From the description - it sounds like you want to compare the email from a parent record to the email in a child - which is displaying via report link? Is that correct? 

    Chayce Duncan | Director of Strategic Solutions
    (720) 739-1406 | chayceduncan@quandarycg.com
    Quandary Knowledge Base
    • QuincyAdam
    • Author
    • Registered
    • May 28, 2019
    Hi Chayce, I just realized that my report link will show a match regardless. So there are 2 issues I'm trying to solve for:
    Issue #1: I have a table for incoming leads called "Leads". I have another table that contains existing records from an external system we use. If the field "Email" in the Leads table matches "Email" of the  other table, I wanted to create some type of flag. I thought report link would show this, but it appears the email address from the "Leads" table will display in the report link regardless if one exists in the other table. Any thoughts on solving for this?
    Issue #2: If the record already exists, I wanted to create a status column; something that would note that it's an existing record. Thoughts?
    Thanks!
    One solution is to generate a third table that combines a full list of unique emails across both tables. For concept - this table would be called 'Emails'. Basically the idea would be to use this table - and make the key field the 'Email' - and be a complete list of all unique emails combined from both tables. Then - make this email table parent relationship to both your leads - and this external source data referencing - and use the existing email fields as references.

    The reason I'd suggest this method is the following - if set up properly you can:
    Summarize the # of external records with that email
    Summarize the # of Leads with that email

    You can then pass that data back to Leads as a lookup - and in practice - if the # of External records using that email is greater than 0 or if the # of leads is greater than 0 - then your lead for example is for someone you've seen before

    At the end of the day - what you're looking for is to identify each email as a unique entity - so you can summarize the different tables into it and play with the data that way.


    Chayce Duncan | Director of Strategic Solutions
    (720) 739-1406 | chayceduncan@quandarycg.com
    Quandary Knowledge Base
    Terms & ConditionsAccessibility statement