How to union two list fields in the same Database?

I want to use one field so that I can easily see at a glance (or test programmatically) whether a given task has a given tag. If I can’t create this union then anytime I ask the user to interact with tags at the task level I have to rely on the user looking at the the tag’s tasks and the tag’s epic’s tasks and the tag’s epic’s project’s tasks, and merge them in her head: or, in any views that I create I will have to (eg) select either the task’s tags or the task’s epic’s tags (etc.) rather than being able to select the pre-unioned tags field. So having the tasks automatically propagated into this union-task field will make everything nicer (UI wise).

The field should be read-only, yes: the individual tags field (task, epic, project) is user-assignable, but the union field should be read-only: only used for filtering, grouping, etc.

The last option could work, if the union solution is not feasible: it would be a lot better than nothing!

1 Like

There is currently no way to write a script that can populate a read-only field, sorry. An automation can only really do things that an Admin can do, and even an Admin is not allowed to change a read-only field.

For the latter proposal, I’ve thought some more about it and it’s not as easy as it first appears.
It would be possible to write scripts that append the Project’s Tags to the Epic’s Tags (and append the Epic’s Tags to the Task’s Tags) but you would need to think carefully about how to trigger them - for example, I presume an automation should be triggered if the Project gains a new Tag. I also guess it should be triggered when an Epic is connected to a Project for the first time?

But what should happen if an Project is disconnected from a Epic, or if an Epic has a Tag removed from it’s list?
How can the automation determine whether any Tags should be removed from the list(s)?

E.g. Project Tags = [foo, bar] Epic Tags = [foo, baz] Task tags = [goo, bar]
What should happen to the Task Tags union when ‘foo’ is manually removed from the Epic? Presumably nothing…
What if the Epic is switched from a Project with [foo, bar] to one with [goo, baz]?
Unfortunately, it’s actually quite tricky to define automations for all possible scenarios.

What you’re left with is needing to have a read-write field for the item’s own Tags, a read-only field for inherited Tags, and a read-write field for the union that is automatically maintained via scripting.
Then you would need at least 3 automations (per database) to be triggered when

  • own Tags field is updated
  • new item added to inherited Tags field
  • item removed from inherited Tags field
    Each of these would run a script that updates the union field.
    It’s possible, but ugly, and hard to maintain.

One final suggestion: what about using a formula to just concatenate the tags in a comma-separated string?
It risks duplication of tags, but the field would be read-only and searchable.
It has the disadvantage of not being helpful for creating views :slightly_frowning_face:

It might actually be simpler to implement this as an external script (webhook API), than adding all these rules in every DB/type - that sounds like a nightmare to maintain.

Related: Can we have a "code library" for a Workspace?

1 Like

I think it might be useful for us to think about the more general case of a function in formulas that allows combining collections/entities, i.e. not limited to the union of fields in the same database but joining the results of any two (or more) expressions that return either a collection of entities or a single entity (of the same type).

1 Like

If a Formula field could return a collection, and we had functions for collection operations like merge/union and intersection, that would be very cool :smiley:

I assume a collection would never contain multiple references to a single entity, so we wouldn’t need a “unique” function.

Speaking as an end-user, this would be ideal: I can already create two collections of entities from the same Database (eg. [Tags] and [Project Tags]), so all I’m missing is the ability to create the formula: Union([Tags], [Project Tags]).

Of course, if you’re going to extend the Collection interface inside formulae, then it would be great to also have Map() (as well as all the set operations + the existing Filter()). Also (and I guess this is blurring the lines between sets and lists), Reduce() would be really handy. (At that point Join([Tags]) would effectively be: Reduce(Map(ToText, [Tags]), “+”)).

1 Like

I don’t get what you mean with the Reduce example.
Are you talking about something similar to Tags.join(Name," ")
which is available in formulas already

Formulas can return collections, and collections never contain the same reference multiple times.
I guess this means that merge is not applicable, and unique is implied.

1 Like

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!

1 Like