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
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
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