I have two list fields in my Database: Tags and [Project Tags]. (Tags is an x:many relation and [Project Tags] is a lookup of an x:many field.) I want to combine them into a single list field [All Tags], so if (eg) Tags = (foo, bar) and [Project Tags] = (baz, bam) then [All Tags] = (foo, bar, baz, bam). Is this possible? I can’t figure out how to do it with a formula or a lookup.
Extra complication: ultimately I actually want the unique set of all tags, so if (eg) Tags = (foo, bar) and [Project Tags] = (foo, baz) then [All Tags] = (foo, bar, baz). I can do the unique in a second formula if necessary/possible.
(Updated) I believe these two fields are Collections: they currently contain (multiple) references to other entities (rows or other DataBases). Specifically:
[Tags] is a (many:many) relation
[Project Tags] is a lookup to a relation ([Tags], which is itself a many:may relation) on another Database.
Perhaps better expressed like this:
Tasks.Tags is a many:many relation
Projects.Tags is a many:many relation
Tasks.[Project Tags] is a lookup from Project.Tags
I want to form a (unique) union of Tasks.Tags and Tasks.[Project Tags]
I think you would need some scripting to do this. The Projects and Tasks DB’s (types) would each need their own Tags collection. Additionally, the Tasks DB will need a separate AllTags collection to represent the union, which would be maintained by scripts.
When the Tasks.Tags field is modified, a Rule is triggered to update Tasks.AllTags.
When the Projects.Tags field is modified, a separate Rule is triggered to update Tasks.AllTags for each related Task.
The script would collect the Tags in the Task and related ProjectTags collections, remove any duplicates, and set the result as the new contents of the Task's AllTags field (which would be read-only for users).
Sounds good: I already have Tasks.Tags and Projects.Tags collections implemented and working. Is there a reference I could consult for help in creating the two scripts? I have no idea how to add my own scripts to fibery.io at this point, but it sounds extremely useful! Update: I think I got it: I have to create new Rules, and then chose “execute a script”…?
Good question! Yes it is. I’m attaching Tags to Projects, Epics and Tasks. Those three have a natural relationship: Project → Epic → Task. At the Epic level I want to see Epic.Tags and Unique(Union(Project.Tags,Epics.Tags)), and the Task level I want see Task.Tags and Unique(Union(Project.Tags,Epic.Tags,Task.tags)).
What is the ultimate goal of having a union of all tags for Tasks or Epics?
Out of curiosity, why is it meaningful for an Epic to be assigned Tags which are not assigned to any of the Epic’s Tasks?
As @Matt_Blais suggested, you can use scripts to automate the combination (union) of these collections and copy the result into an ‘All Tags’ field, but knowing the specific use case might help determine the optimal method.
For example, will the script need to be triggered by people removing tags? or do tags never get removed?
I want to be able to assign tags to tasks or their parents (epics, projects), such that lower-level entities inherit tags from high-level (but not vice-versa). So eg. if I tag a project with ‘foo’ and one of its tasks with ‘bar’ then the project will show up with the ‘foo’ tag, but the task will show up with ‘foo’ and ‘bar’ tags.
The union is designed to implement this inheritance model: each (lower) level calculates the (unique) union of its own tasks and its parent’s tasks.
Yes, tags would be added and removed freely: each time a tag is added or removed it should be added (or removed) from its childrens’ union-tags.
So in general, when the tags of any “parent” type entity are changed at all, it should force all its children to recalculate their tags… and this would cascade down the hierarchy of all child entities.
Yes, but why is it important that all tags (child tags + parent tags + grandparent tags…) are collected together in one separate field?
How do you plan to use this field?
Should this field be read-only (it will contain inherited tags that a user might not have permissions to edit)?
Would it be acceptable instead that each entity (at any level) has a single ‘Tags’ field that can be both manually edited, but also inherits tags from parents automatically? (with a risk that a user deletes a tag that was added from a parent…)
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!
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
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.
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).
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
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]), “+”)).
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.
