Generate a MAILTO from a list of query results (with one field - "email") -not user fields.

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • In Progress
Hi there,   Imagine I have a list of Employee records, which includes fields "email" and "Manager?"  (Y/N).  On my home page, I'd like a button "Email all managers" which would filter on Y/N for manager, and then generate a MAILTO link for the resultant data set....  

Guidance much appreciated.  Thank you!
Photo of Jessica Tobiason

Jessica Tobiason

  • 40 Points

Posted 3 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
Here is a solution if you have 25 or fewer managers.  Basically we will make use of the new Combined Text summary fields to roll up all the manager emails into one field and then parse them out into a semi colon delimited field.

  1. New table called All managers. Create 1 record.  It will be [Record ID#] of 1.  Lock down so no one, not even Admins can add another record.

  2. Make a numeric formula field on the Employees table as a formula value of 1 called [Link to All Managers]

  3. Make a relationship to All managers based on that field.

  4. Make a Combined Text summary field where [Manager] = "Y"

  5. Convert to a formula text field type
var text value = [my combined text summary field];

List(";",
Trim(Part($value,1,";")),
Trim(Part($value,2,";")),
Trim(Part($value,3,";")),
Trim(Part($value,4,";")),
Trim(Part($value,5,";")),
Trim(Part($value,6,";")),
Trim(Part($value,7,";")),
Trim(Part($value,8,";")),
Trim(Part($value,9,";")),
Trim(Part($value,10,";")),
Trim(Part($value,11,";")),
Trim(Part($value,12,";")),
Trim(Part($value,13,";")),
Trim(Part($value,14,";")),
Trim(Part($value,15,";")),
Trim(Part($value,16,";")),
Trim(Part($value,17,";")),
Trim(Part($value,18,";")),
Trim(Part($value,19,";")),
Trim(Part($value,20,";"))
Trim(Part($value,21,";")),
Trim(Part($value,22,";")),
Trim(Part($value,23,";")),
Trim(Part($value,24,";")),
Trim(Part($value,25,";"))
)

use that in your mailto: formula

Put that report (the 1 record report) on your dashboard with just a button to mailto:
Photo of Jessica Tobiason

Jessica Tobiason

  • 40 Points
Thank you!   I'm partway there, but don't see how to change the combined text lookup field (from the relationship) to a formula text field...   I feel like I'm close.     Thank you so much for your help!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
no problem,

You are not changing the Combined Text Summary field.  You are making a new formula text field called [All Manager emails in Mailto: format]. 
Photo of Jessica Tobiason

Jessica Tobiason

  • 40 Points
Ok, so after step 4, the first field (the combined text field) should show all the managers' emails  in the Employees table, and then step 5 should get them in mail-able format.

I'm not seeing any values in the combined text field after creating the relationship, I'm afraid. 

Here's the relationship (employee = people in my app)

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
From your screen shot it appears that you did not follow my steps.
Did you do steps 1, 2 and 3?