Discussions

Expand all | Collapse all

Formula to determine if report link found a a match

  • 1.  Formula to determine if report link found a a match

    Posted 05-11-2017 17:50
    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.


  • 2.  RE: Formula to determine if report link found a a match

    Posted 05-11-2017 18:59
    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.


  • 3.  RE: Formula to determine if report link found a a match

    Posted 05-11-2017 19:32
    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".


  • 4.  RE: Formula to determine if report link found a a match

    Posted 05-11-2017 19:49
    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.







     


  • 5.  RE: Formula to determine if report link found a a match

    Posted 05-11-2017 19:58
    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


  • 6.  RE: Formula to determine if report link found a a match

    Posted 05-11-2017 20:09
    John,
    That is not going to work.