[Logic] Formulas - Chained/Multi-Hop *-to-Many Relation Fields with Distinct() Function

Ideas & Features
1

Add a Distinct() or Unique() collection function to formulas to return only unique entities from an entity collection, i.e. *-to-many relation field.

While direct relation fields are already unique, duplicates can appear when using one of Fibery’s strengths: allowing access to fields of the entities in a *-to-many relation field.

With this new formula function, examples below could return unique results with Unique() or Distinct():

  • Contact Related Orgs: For a contact, get all of its relationships’ organisations.
  • Project Task Workers: For a project, get all of its tasks’ assignees.
  • Company Deal Owners: For a company, get all of its deals’ owners.
  • Feature Bug Sprints: For a feature, get all of its bugs’ sprints.
2

For the use cases listed, isn’t this exactly what lookups do already?

3

Hi @Chr1sG, yes, I agree: in the straightforward cases where there is no filtering on the intermediary/junction table needed.

But when a Formula is needed to create a “filtered lookup” then the lookup field does not serve the purpose. And we cannot move the filtering to the post-lookup values, since of course that does not have the context of the junction entities/rows.

I have added the filtering concept in bold for each of the previous cases where the lookup field would not work:

  • Contact Related Orgs: For a contact, get all of its active relationships’ organisations.
  • Project Task Workers: For a project, get all of its unfinished tasks’ assignees.
  • Company Deal Owners: For a company, get all of its high-value deals’ owners.
  • Feature Bug Sprints: For a feature, get all of its in-progress bugs’ sprints.
4

I’m still confused. Whether it is a lookup or a formula with a filter function, it is not possible for an entity to appear twice in the resultant collection, so I don’t know what this is asking for

Can you share an example (screenshot) where you are seeing duplicates.
As far as I can tell, your most recent use case examples might require a two step approach, e.g. a formula followed by a lookup (or vice versa), but I’m not sure why duplicates would ever occur.

5

Hi @Chr1sG. Thanks for that clarification, and sorry for not having explained it better. Based on your answer, I realised that I had framed my request completely incorrectly. Below is the process to correct the framing, using an example with fictitious, descriptive field names.

Clarifying my request

My Incorrect framing

I had completely mis-framed my original request, by asking for Entities.Distinct() to return distinct Entities, as you are correct that Entities is already distinct without duplication.

Corrected framing

I should have framed Distinct() or Unique() as a flattening aggregation function to return something that right now is not possible, i.e. a field from *-to-many relations e.g. Entities.Distinct([State]).

With this, the 3-step approach above would turn into:

Tasks.filter(Lower(State.Type) != "finished").Distinct(Assignee).Join(Name, ",")

The issue

The issue lies in multi-hop relation formulas, where the following would return the same Assignee.Name multiple times, if the Project has different Tasks with the same Assignee

Tasks.filter(Lower(State.Type) != "finished").join(Assignee.Name, ",")

Current 3-Field Workaround

Right now, we can only solve this using three fields as follows.

1. Filtering Formula on Tasks

An Assignee (State of Unfinished) formula field on Tasks with a formula to move the filtering here:

if(Lower(State.Type) != "finished",
Assignee,
Assignee.[AnyCollection].Filter(true = false).Sort().First().Assignee
)

Note: the final argument is used to return an empty/null of the same Type as Assignee. AnyCollection can be any to-many relation field (e.g. Tasks, Holidays, etc.) on the Type/Database of Assignees (e.g. Users).

2. Rollup #1’s values up on Projects via a Lookup

To deduplicate the Assignees, an Assignees (from Tasks with State of Unfinished) lookup field on Projects

3. And finally the modified original formula for the Join

To access the deduplicated Assignees, in case we wanted to Join their Names for a label, aggregate some field from them, etc.

[Assignees (from Tasks with State of Unfinished)].join(Name, ",")
6

If you just need a list of (unique) Assignees from the unfinished Tasks of a Project, I would suggest that the easy way of doing this is to

  • add a formula (on the Project DB) called ‘Unfinished Tasks’
    Tasks.Filter(State.Type != "Finished")
  • add a lookup (on the Project db)
    Unfinished Tasks → Assignees

And then, if getting the names as a concatenated string is essential, you can add a .Join formula field:

  • [Unfinished Tasks Assignees].Join(Name, ", ")

So if I understand correctly, the primary use case is being able to write a formula that combines a filtered collection followed by a lookup.
This is indeed something that possibly a few other people would also like, and if/when it gets implemented, being able to append a .Join function would presumably work out of the box.

7

Thanks @Chr1sG for the alterative, more elegant solution. I tend to put the logic for qualifying an entity, on the entity’s own database (e.g. Tasks/Unfinished, versus Projects/Unfinished Tasks).

Exactly.

*-to-one hopping

Basically right now in Fibery Formulas unlike in Airtable Formulas, we can hop *-to-one relations such as Parent.Budget.Factor.Handlers (singular Factor). This is already a mega-feature in comparison with Airtable, for example.

*-to-many hopping not allowed now

Where, justifiably, the hopping stops, is with *-to-many relations, as they only offer aggregation: we cannot do Parent.Budget.Factors.Handlers (plural Factors), but we can do Parent.Budget.Factors.Aggregation_Function().

*-to-many hopping partial solution with Distinct Lookup

So the suggestion would allow hopping *-to-many relations in certain situations (where a unique/distinct list is desired) without the risk of having duplicate entities in a Collection and breaking the entire Formula system, allowing something like: Parent.Budget.Factors.Distinct(Handlers) and since it would return a Collection, presumably it would allow further hopping e.g. Parent.Budget.Factors.Distinct(Handlers).Filter(Lower(Status.Name) = "active").Distinct(Type) meaning “The Types of Active Handlers of Factors …”

1 Like