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...
Forum Discussion
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
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
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.
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.
