Using References in a formula

Get Help
1

Has anybody used References in a formula field? I’m experimenting to see if there’s any way I can create a field that returns all the items of a specific type that refer to the entity.
For example, entity #1 of type A mentions entity #2, and entity #3 of type B also mentions entity #2. Can I write a formula for entity #2 that returns only those referring entities that are of type A (so it would only show entity #1)?

1 Like
2

To be honest, we haven’t paid much attention to using References in formulas.

Our own main use case is prioritzing Features based on the number of latest use cases:

References.Filter(FromEntityType.Name = "Customer Success/Conversation" and [Creation Date] >= Today() - Days(90)).Count()

At the moment, there is no way to extract a list of entities of a certain Type from the References but we will consider this. Meanwhile, the best you can do is to narrow References down to entities of a particular Type:

References.Filter(FromEntityType.Name = "Test/Task")

Here’s an example:

filter-references
filter-references1101×677 43.6 KB

If you stumble upon new scenarios of using References, please do not hesitate to share them — we watch closely and take notes :writing_hand:

2 Likes
3

Thanks for that.
I have a use case whereby I have created standard operating procedures (‘SOP’ entities) that contain a rich text field with instructions/rules for activities and which roles are responsible for them, e.g. The R&D_Manager shall keep a record of all employees vacation
[R&D_Manager is a #reference to a ‘Role’ entity].

When users are assigned to a specific role, I want to make it totally transparent to them what they are responsible for. If I can add a field ‘Relevant_SOPs’ to the ‘Role’ type which contains a list of references, filtered to only show those of the type ‘SOP’, I can then add a field for users which is a lookup to Role.Relevant_SOPs. Every user gets a customised list of SOPs they need to read.

1 Like
4

Thanks for the use case!

Back-references are ideal for unexpected connections — which is not the case here. From the data modeling perspective, it seems like a Rule/Instruction could be a separate Type:
image

Not sure about the UX though — have a couple of questions:

  1. How long are the instructions usually?
  2. Are there people who read instructions for all roles for a particular SOP?
1 Like
5

Thanks for the suggestion. It had crossed my mind to do that, but I had decided against it for a couple of reasons:

  • I want the person who has a specific role to understand the expectations of their role in the greater context of the procedure. In other words, it’s important that the person reads the entire contents of the SOP, not just the specific instructions that they should follow.
  • The ability to write instructions with #Role references is really useful for the author of the SOP. It’s so much more intuitive that writing an instruction and then remembering to create a link to a role entity.
1 Like
6

Got you, that makes a ton of sense. There is no nice solution at the moment but we’ll watch closely for the similar use cases and will try to come up with a decent solution :slight_smile:

1 Like
7

Just out of interest, would it be possible to write an action button script to extract all references of a specific type, and add them to a relationship field collection?

1 Like
8

Yep, you can do it right now with some coding — nice idea!

1 Like
9

Hi @antoniokov
I know it’s a while since this thread was alive, but I have a follow up question: when I use a formula to extract references of a specific type (like you suggested) I can see that this formula field shows up in Integromat as a data field for the entity :slight_smile: Unfortunately, it’s not clear what the data type is, and so I don’t know how I can use it.
How can I make use of a formula field (that returns an array of references) using Integromat?

1 Like
10

When you are extracting parent Entities out of references via Integromat (or any other automation), you don’t need a formula Field.

Here is an example: Features are mentioned in Interviews and we’d like to extract Interviews into a full-blown relation to use it in Formulas and Views:

  1. Add a many-to-many relation from Interviews to Features.
  2. Track new/deleted references and add/remove Entities to this relation.

The second step is awkward and we’re still thinking of a better out-of-the-box solution.