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
- 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 - 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 - 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. - So just to recap, in this setup you have a unique list of emails in this 'Emails' table - In
The relationship is such that Emails have many leads
In your Leads Table - the reference field is the email itself.
From here -
1) if you haven't already, create a summary field in the parent table 'Emails' that counts the number of leads. This will count how many leads correspond to that one email. Call this field [# of Leads]
2) Test this field - your email records should have a count of at least one - because that email should have occurred at least once in your "leads' to have been loaded in the emails table
3) Create a lookup back into 'Leads' of your summary field [# of Leads]. This will help identify if this is the only lead, or if there are others.
So if for example: You have a 2 leads for this email: example@gmail.com
Example@gmail.com becomes a single 'Email' record in your email table. It can only occur once - because [Email] should be the key field
From the above - in your 'Emails' table - the value of [# of Leads] should show a 2 then. If you create a now create a lookup of that same field, via the relationship between Emails and Leads, then each of those records with example@gmail will populate/show with a value of '2' based on how the relationship is set up. This way - you know that this email has been seen more than once now - and is a repeat customer
Chayce Duncan | Director of Strategic Solutions
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base
