Formula to determine if report link found a a match

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
After searching the available QB formulas and relationship uses, I'm not entirely sure this can be done.  It seems like a simple goal, but I cannot figure it out.

I need a way to determine if an email address in table A matches an email address in table B.  The Report Link field does this with relatively little hassle inside of a form.

However, I need a report that lists the clients in table A who do and do not have a matching email in table B.  The table/grid edit report just lists the report link field as the "value to match" from table A.   . 

Is there a formula I could write (or any mechanism I could use) to determine if a report link found a match?

I've been using the report link because I need this to function automatically and not be dependent on user selection via a dropdown menu.  I think this precludes the use of relationships (based on my admittedly limited understanding).  If there is a way to get relationships to automatically do this, it would be wonderful.
Photo of Kaisa Janzen

Kaisa Janzen

  • 250 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
All you need to do is to have a table of unique email addresses in table and be able to set that field as the Key field.  Can you tell me what you table A is? Can the Key field be the email address?  ie will every record have an email address which is unique?

I can explain the solution further once I understand that.
Photo of Kaisa Janzen

Kaisa Janzen

  • 250 Points 250 badge 2x thumb
Table A is a connected table synced with Gmail.  Most of the email addresses are harvested from the body via the very helpful formula you authored here: https://community.quickbase.com/quickbase/topics/formula-help-with-truncating-text

The report link plays very nicely with this formula field.  

Some of our submissions do come directly from the sender, in which case using the from address as the key field would be terrific.  

However, because a significant portion of our emails are forwarded via a third party, even though they are set up to display "useremail@xxx.xxx",  the sender is actually "formmailer@xxx.com" and QB syncs the sender or "from" field as the same.  Using the formula you created, I can still glean the requisite email address, but I'm afraid the from address would contain several repeated instances of "formmailer@xxx.com".
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
OK, well here is a low tech solution but maybe its good enough for you to start with.  make a new table called Unique email addresses or whatever they really represent in your app. Make a field called email of type email address.

Then on your gmail sync table, make a summary report based on the count of the email addresses.  That will give you a list of the unique email addresses.

Then while viewing the report, use the More ... button to export to another table and follow the steps to import the unique email addresses to the Unique email address table.

Then make a relationship where 1 Unique email address has Many Table Bs.

Make a  formula checkbox field in the unique email addresses table and the formula will be

true

It will always calculate to "checked".

Look that up down to your Table B and any record with a checkbox exists in the email address table.

So that will work, but then from time to time or every morning someone will need to manually do that same More button routine to copy the unique email addresses form gmail to the unique email address table.  The copy function will not change the existing email addresses, but it will add the new ones.

I do not know of a native way to automate that daily process.  It would have to be done with a script and that will take paying a developer to do that.







 
Photo of John Thomas

John Thomas

  • 1,062 Points 1k badge 2x thumb
Hi Kaisa,

As i am new to quick base i don't know exactly whats your requirement. From my knowledge i thought i can have a contribution on some logic. So you could have a hand on the situation.
 
Create Two Tables

Let it be
Parent , Child
For an example
Parent Fields:

id
name
email id

Child fields:

original id
original name
original email id 

Create a relationship between both tables like one Child record to many Parent records
[Give lookup of original email id]

So create the record from Child and and add child record for corresponding to Parent

So based on the relationship . The record on the  Parent will have the Child lookup original email id field

Now create  a formula  email field with the below formula:[Example Field Name: Match Email field]

If([Email Id]<>[Child - Original Email Id], "Not Matching","Matching")

Email id : Parent  field
Child - Original Email id  : Relationship lookup field

Now the records will check and give the status as matching or not matching

At Last create  a report: Table Report

On Filters Give the filter as

Match Email field is equal to the value Not Matching

Now you will get the report with mail id's that are not matching
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
John,
That is not going to work.