When using self-relations in a type, the uni-directional type is often a great fit for the relation (that is - not for situations where I’d want the basic / undirected / bi-directed siblings), where the concept of “parent” entities and “child” entities exists, but they really are all the same type.
In such scenarios, it’s often nice to see automatic sibling links, through a lookup: using the children of parents. The problem is that this results in including the entity itself as one of its own siblings.
Is there a way to include these auto-siblings while filtering the self? This could be doable through the API but seems like the lookup version is already so close.
I think this will eventually be possible by using a formula field, when the option to refer to self in a formula becomes possible (it was requested here at some point but I can’t find it at the moment).
So for example, a type with a relationship to itself (parent-children) can have a lookup field (Siblings) which is:
Siblings: Parent -> Children
which as you say, returns all children including itself.
Would love to see this feature as well. I have tried to set up a “Task/Subtask” arrangement within the same Type, and this seems like it could help with that.
Is there an update on implementation of this in formulas?
I didn’t want to hijack this old thread but I thought it might be the best place to get @antoniokov 's attention.
I have a few use cases that would also benefit from this, e.g.:
tracking how names of places change over time while also automatically being able to use the latest name for the entity (name record with the latest/maximum date)
tracking position history of employees while also automatically being able to populate their current position (position in the positions collection with the latest/maximum date)
Keeping a record of the changes is easy: in both of these case, we can create entities (e.g. Place Name or Positions) that have start and end dates and relate them to the parent entity to keep the history of changes.
However, to get the latest name or position, then the only way I can think of is to create a formula field in the parent entity that finds the last change date:
[Last Name Change] = [Place Names].Max([Start Date])
[Last Position Change] = [Positions].Max([Start Date])
And then create another formula field that filters all records in the history to find the latest item (where we need this):
[Current Name] = [Place Names].Filter([Start Date] = this.[Last Name Change]
[Current Position] = [Positions].Filter([Start Date] = this.[Last Position Change])
I’m not sure if there is actually a way of doing this currently.
If I’ve understood your problem, then I think there is a way to achieve it:
Use a max formula to find the latest start date (as you’ve described)
Use a formula in the position (or place name) type to check if its start date = person’s max start date (returns a boolean with the name ‘Current’)
Use a formula in the person type which is positions.filter(Current = true)
This should return a single position (assuming there is always only one with the highest start date)
This is fantastic, although, I feel this concept proposed above would simplify this, given that you can’t hide any of these fields.
There is one issue left in my case: filter still returns a collection object with one item rather than that item. So it is not possible to for example use the last/current name of a place as the name of the entity through “Generate Name through Formula”. Any way to resolve this?
I actually think the formula engine could use a few more functions that allow you to work with collections, e.g. sort them, extract first/last/nth/every nth item. For this example, I think being able to sort the collection on the [Start Date] in descending order and then extract the first item would be easiest solution (eliminates the need for all these other fields).
).