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

  • 0
  • 1
  • Question
  • Updated 3 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 3 years ago

  • 0
  • 1
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.
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.
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.