Return field values for records in Child table that meet multiple criteria

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have a parent table (Clients) and a child table (Contacts). I would like to return a list of values (all of the email addresses from the Contacts.[Email 1] field) from the Contacts table where the records in the Contacts table meet 2 conditions:

  •  Contacts.[Client name] = Client.[Client name], and 
  •  Contacts.[IsBilling] contains "TRUE"
So of the 900+ Contact records I have 10 where Contacts.[Client name] = "ABC Company" and there are 3 records of that 10 where Clients.[IsBilling] = "TRUE". 

Similar to the email formula field feature which allows one to add a link/image that appears in the total row of an email column (see screen shot attached), I would like to take that list of email addresses and build a URL that includes a subject line and CC's and BCC's some other internal email addresses.

I have tried various angles at this including...

  • various table relationship options
  • using the API_DoQuery 
Nothing has worked yet and I am getting frustrated. Am I on the right track? Is this possible?

Photo of Owen

Owen

  • 0 Points

Posted 2 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Is there a reasonable practical limit on the number of qualifying children which the solution would need to handle.  You can do say up to around 5 or maybe 10 with reverse relationships.
Photo of Owen

Owen

  • 0 Points
I really only need 3-4 email contacts' addresses and could try to limit to that. Currently Contacts.[IsBilling] is a formula which looks for "Billing" in a multi-select field Contacts.[Role] which includes options for "Accounting/Billing", "Billing - Primary contact" and "Billing - Secondary contact", so with that structure  nothing prevents users from assigning any of those options to more than one contact but it should be rare. I looked at a couple of Q/A on reverse relationships. Does this require a third intermediary table between Clients and Contacts? Any additional detail you can provide on the reverse relationship would be appreciated.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
The basic setup of a reverse relationship is this.

Summary field of the minimum record ID of the Contact which meets the filter condition.  call it [Record ID# 1 of Billing Contacts]

Then make a reverse relationship where 1 Contact has Many Clients and use that Summary field [Record ID# 1 of Billing Contacts] on the right side of the relationship.  Lookup the Billing contact to the Client and call it [Billing Contact 1].

Back on the regular relationship lookup that [Record ID# 1 of Billing Contacts] field down to Contacts.

Duplicate that summary field and call it [Record ID# 2 of Billing Contacts]. Edit the filer such that the [Record ID#] > the value in the field [Record ID# 1 of Billing Contacts].  So this will be the 2nd-most Minimum Record ID#.

Conveniently, when you duplicated the summary field it will have duplicated the reverse relationship for you.  Lookup the Billing contact and call it [Billing Contact 2]

Just keep up the pattern until you have enough loops and enough [Billing Contacts] floated up to the Client.
Photo of Owen

Owen

  • 0 Points
Great, I can do that. I already have one contact, the primary floated up, but once I get my 3-5 contacts floated up, I am also trying to pull all of those the email addresses into a link/formula that allows a use to click and automatically pop an email (using default email client), and *hopefully* prepopulate the subject line and include some internal email addresses in the CC or BCC.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Se this recent post for the mailto command.  You would list the email addresses like List(";",[Billing Contact 1],[Billing Contact 2] ...[Billing contact 5]).  They should be able to be populated into the TO box with semicolons as the delimiter.