Forum Discussion

QuincyAdam's avatar
QuincyAdam
Qrew Cadet
5 years ago

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)
App builders
Formulas and functions

7 Replies

Sort By
  • ChayceDuncan2's avatar
    ChayceDuncan2
    Qrew Cadet
    5 years ago
    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's avatar
    QuincyAdam
    Qrew Cadet
    5 years ago
    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!
  • ChayceDuncan2's avatar
    ChayceDuncan2
    Qrew Cadet
    5 years ago
    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
    • QuincyAdam's avatar
      QuincyAdam
      Qrew Cadet
      5 years ago
      Hi Chayce,

      I think I understand the logic behind this, but I'm having some trouble putting it into practice. 
      I created a table called "Emails" and set "Email" as the key. I tried doing a table-to-table relationship with "Leads" with "Email" as the parent table. I select "Email" as the reference field. The lookup fields don't display for me on the next screen. I'm just seeing QuickBase Date Modified, Record Owner, etc... fields. I'm not sure the association was made correctly. I'm likely missing a step, but am not able to work through the summary part to understand that yet.
      Thanks,
      Quincy
    • ChayceDuncan2's avatar
      ChayceDuncan2
      Qrew Cadet
      5 years ago
      So from the sound of it - your new table only has 1 field yes (not including the built-in fields like date created)? Email?

      If yes - there won't really be any other lookup fields to reference since you don't have any other fields except those built in ones. My recommendation is to create a summary field instead first. Your test to see if its working right then - it to find an email you know exists in your Leads table - and enter that in your 'Emails' table. 

      So for example - lets say my email appears in 4 different lead records. If you create me as a single record in 'Emails' - then the field [# of Emails] should read as 4. Thats a quick way to test if your relationship is set up correctly.

      With that - you can then do a lookup of that new field - essentially you're bring it up - to then bring it right back down as a lookup. With it - you can write a formula that says that if this field is greater than 1 - then I've already seen this person before, since I now have 2 or more leads associated with them. 

      Chayce Duncan | Director of Strategic Solutions
      (720) 739-1406 | chayceduncan@quandarycg.com
      Quandary Knowledge Base
    • QuincyAdam's avatar
      QuincyAdam
      Qrew Cadet
      5 years ago
      I got the relationship setup -- thank you for walking me through that! How do I get through the next part:
      With that - you can then do a lookup of that new field - essentially you're bring it up - to then bring it right back down as a lookup. With it - you can write a formula that says that if this field is greater than 1 - then I've already seen this person before, since I now have 2 or more leads associated with them. 

      The count is currently in my "Emails" table, but I want it to be in my "Leads" table, so I can create a report for all leads that are 0.