Filtering Duplicate Child Values for Reverse Lookups

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have an app with this table structure:

Parent 1 < Child > Parent 2 > Gparent > GGparent

In some instances the GGparent doesn't exist, just the Gparent.

In the Parent 1 tables, we want a summary fields which list all unique values for Children, but many children come from the same GGparent or Gparent.

Here's what I mean: We want a summary field that lists which of our Consultants work with clients that a contact works with, without listing the same Consultants twice. In this instance:

Contacts < Trust Roles > Clients < Client Assignments > Consultants

Another example, we want a field which lists the Provider-type Trust Roles that a Client has, without listing the same Provider-type Company twice:

Clients < (Provider) Trust Roles > Contacts > Locations > (Provider) Companies

I can establish the reverse relationships and summary fields, but I'm not sure how to make sure the summary fields filter for unique values. It'd be great if there was a way to filter the uniqueness of the values such that we'd need less reverse-relationships to accommodate them.

Thank you!

Photo of Amber

Amber

  • 590 Points 500 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
No problem.
Get child value 1 up to the Parent 1 using a reverse relationship.

Look that up down to Children.

Make your summary field for the next Minimum Record ID# but instead of filtering it where it is > than the previous Minumum Record ID#, instead filter is where child value <> Child Value 1.

Once again, get Child value 2 up to the Parent and look up back down to Children.

Make your summary field for the next Minimum Record ID# but instead of filtering it where  where child value <> Child Value 1
And
where child value <> Child Value 2

Repeat but for the next one filter on

where child value <> Child Value 1
and
where child value <> Child Value 2
and
where child value <> Child Value 3


Just keep going until you run out of gas.