How to union two list fields in the same Database?

Sorry: I guess bringing up Reduce() and Map() was a bit off-topic, but yes: Join() is a specific example of a Map() + Reduce(), where the entities are mapped to strings and then reduced with string concatenation. So if you exposed Map() and Reduce() functions to us directly then we could implement our own Join(), eg: as Reduce(Map([Tags], ToText), ‘+’) but we could also implement any (supported) Map() function or any supported Reduce() function, or any combination, which would be very powerful (as long as you support more than ToText() in Map(), and more than ‘+’ in Reduce()).

So I guess collections are sets, in which case yes they’ll be unique by definition, and you would just need to supply (off the top of my head) Union(), Intersect(), Diff() functions: and I guess also Complement() if the universe for that particular collection was finite and enumerated.

1 Like

My gut feeling is that union is the most useful. I’m not sure how many users would need intersect, diff or complement.
Perhaps community members will come up with use cases :slightly_smiling_face:

Yes, 100%. Union() would solve my immediate problem: I just figured if you’re going to add that then you might consider adding (at least) Intersect() and Diff() at the same time, because together those operations would enable a lot more use cases than Union() alone.

Can you provide some example use cases that need intersect or diff. It’s always interesting to hear what people want to achieve.

Thanks! What are References? I can’t find an explanation of them in the UI or docs; just lots of people commenting on how much they like them!

“References” would be links to entities in a Rich Text field.
In an entity that has been linked, all the links to it are shown in a collection in the entity view:

image

Or possibly another name for Relation fields / collections?

Does this help?

1 Like

Yes, thanks: I get it now: although I guess Reference isn’t surfaced as an official Collection in the Database (or any other) view of a referenced Database?

See here for a backdoor API to programmatically access references

Thanks! Very useful.

Hey Chris! I would definitely need this feature (allowing combining entities from the same type). Is this already on your roadmap? Thanks in advance!

2 Likes

+1 on this union() feature; it really could be a game changer (especially on roadmap/PM topics) to merge same entity type in a single place even when feeded from several sources.
Automation can be used as workaround, but it requires to limit/restrict the way to use it to a strict workflow (e.g. relations between entities to be set in a certain order) that may not be always be followed.

I have a working demo. The only real downside is that the Child’s “Collected Tags” collection, which includes both the Parent’s and Child’s tags, is not read-only - it is a regular many-to-many relation that is maintained by Automation.

This is a UI issue, in that it would be confusing for people who make changes directly to this collection instead of changing the Child’s or Parent’s “My Tags” collections directly.


Here are very simple Parent and Child DBs. Each one has its own “My Tags” collection (linked to the same Tags DB):

The Child also has a Lookup of its Parent’s “My Tags” collection (“Parent Tags”).

There is a separate automation for each possible change (link or unlink) to the Child’s “My Tags” or “Parent Tags” collection, and these four automations all do exactly the same thing: First unlink everything from the "Collected Tags’ collection, then rebuild it by linking everything from the Child’s “My Tags” collection and the “Parent Tags” collection:

There is no issue with duplication of Tags/links - that can’t happen because each Tag entity is unique, and if it is linked in to a collection twice, it is still just a single link to a unique entity.

Now that it is possible to hide fields, there is a workaround for this: add a formula field which is simply the name of the other field (i.e. [Collected Tags] in your case) and hide the original field.
The original field can still be updated by automations, but users see only the (read-only) formula field.

2 Likes

To whoever may be interesting, I’ve got inspired by @Matt_Blais (thanks for sharing your solution btw :pray:) to automatize a similar (but not exactly the same) mechanism (roadmap orientated).

Our setup is the following:

  • We have 2 databases for tasks and studies
  • For each Task/Study, we have a collection of Involved teams (e.g. which team is contributing to which Task/Study) which is updated depending on Task/Study assignee.s.
  • Several Task & Study are part of an Internal Goal
  • Several Internal Goal are constituting a Milestone

Our main challenge is that a team involved on a Task/Study may evolve, list of Task/Study linked to an Internal Goal may evolve and list of Internal Goal linked to a Milestone may evolve.
Target is to get a unified field listing all Involved teams in each Milestone and Internal Goal.

We did implement the following solution to replace the union() feature without any human intervention:

  1. in each Internal Goal, we have 2 Lookup fields
    1.1. Lookup of all Team.s from linked Task - field is hidden from card view
    1.2. Lookup of all Team.s from linked Study - field is hidden from card view
  2. in each Internal Goal, we have a many-to-many relation field with the Team items
  3. we have 4 automation rules allowing to update this many-to-many field (all running on the Internal Goal layer):
    3.1. when Task is linked to the Internal Goal (e.g. new Task created), link added in the 2. many-to-many relation, based on 1.1 Lookup field

    3.2. when Study is linked to the Internal Goal (e.g. new Study created), link added in the 2. many-to-many relation, based on 1.2 Lookup field

    3.3. when Team is unlinked from the 1.1 Lookup field (e.g. when a Task is assign to someone from another team or deleted and no other Task are involving the Team), the 2. many-to-many relation is cleared and updated based on 1.1 & 1.2 Lookup fields

    3.4 when Team is unlinked from the 1.2 Lookup field (e.g. when a Study is assign to someone from another team or deleted and no other Study are involving the Team), the 2. many-to-many relation is cleared and updated based on 1.1 & 1.2 Lookup fields
  4. add a Lookup field in the Milestone checking the 2. many-to-many relation from all linked Internal Goal.

As per Chris suggestion right above, you can add a last step to transform the 2. many-to-many relation into a read-only field and hide the rewritable one.
Checked so far: removing/reassigning/creating seem to work pretty fine :crossed_fingers:.

Let me know if you need any further clarifications, I’ll be glad to share some screenshots/details if it can help.

@Chr1sG this workaround does not replace the union() feature need as I’m quite sure it won’t adapt to all setups/way of working :slight_smile:

3 Likes

Having Union() would be great!

As long as I can use Union() to ensure that a collection only contains each value once, then I don’t need a Unique() function before using Join().

Having Intersect() and Diff() feels like natural and quick additions when creating Union(). I mean it’d be like providing plus but no minus or multiply… :speak_no_evil:

Fibery collections cannot contain the same entity twice, per definition, so if/when we introduce union, there will be no issue with duplicates.

1 Like

I would assume this to happen on assignment, not sure how it would behave during processing “in memory” in a formula. If that’s baked in, then great!