Allow to return an empty value from a formula

Ideas & Features
1

Sometimes, when using a formula field that returns a single entity, the user might want to return an empty value (meaning no entity).

For example, if (X is true return entity Y, else return nothing)

There is a workaround for this, which is taking a collection of entities of the type returned and applying to it Collection.Filter(true = false).Sort(Some Field).First() which returns the first entity in an empty list (which is an empty value), but this is rather awkward.

2

Related:

4 Likes
3

And related to that :

I think this is a prety important feature I come by quite often and must use workarounds.

4 Likes
4

Yes, it’s indeed unconventional that Fibery doesn’t allow a direct way to set a field to null in a formula.
In my case, I want to create a button with a formula that functions as a toggle to populate and clear a second Project relationship field (Active Project), which is also a to-one relation.
The suggested workaround does not work for to one relation fields.

I use the following script as workaround:

const fibery = context.getService('fibery');
for (const entity of args.currentEntities) {
    const currentActiveProjectId = entity['Active Project'] && entity['Active Project'].Id;
    const projectId = entity['Project'] && entity['Project'].Id;
    const newActiveProjectId = currentActiveProjectId ? null : projectId;
    if (newActiveProjectId !== currentActiveProjectId) {
        await fibery.updateEntity(entity.type, entity.id, { 'Active Project': newActiveProjectId });
    }
}

Anyhow, this feature is still needed a lot.

5

The workaround should be fine for to-one relations:

Projects.Filter(true = false).Sort().First()

6

I may not fully understand, but In my case I don’t have a collection field ‘Projects’. I have a Page entity with Project and Active Project fields which are both to-one relations.

So using ‘Projects’ results in ‘Reference to undefined variable Projects’
And using the filter on the Project field results in ‘Cant call ‘Filter’ method’.

7

If you are updating a field via an automation (button or rule) then all workspace dbs are accessible in formulas.
In my example above, Projects is the name of the database, not a relation field name.

8

Can you please help write it out? I’m not getting it…
I have:

If(
    IsEmpty([Step 1 Page].[Active Project]),
    [Step 1 Page].[Project],
    Projects.Filter(true = false).Sort().First()
)

Reference to undefined variable Projects

Case:
That formula needs to result in the Project entity linked in the Active Project field, if the Active Project field is empty. If it is not empty, it should make the field empty.

9

What is the name of the database containing the Projects?
Note: if there is more than one db in the workspace with that name, you’ll need to qualify it with a space name.
Try typing the first few letters and let the auto complete make suggestions, and you should see what you need.

1 Like
10

Thank you!

Now it works. Indeed the space name needed to be added, and it was listed when typing as you said.

If(
  IsEmpty([Step 1 Page].[Active Project]),
  [Step 1 Page].Project,
  [Projects (Dev)]
    .Filter(true = false)
    .Sort()
    .First()
)
11

Is there a way to get a null value for a number field?

If(
A > B,
A - B,
null
)

It only works if I substitute null for 0, which I don’t want to do.

12

Yeah, it’s an ugly workaround, but you create a number field called “Blank” or something, hide it to keep it more out of the way and make sure you leave it empty, then you point to that field whenever you need a null value. It will break everything if someone updates it though, so keep it secret, keep it safe.

4 Likes
13

Awesome workaround, thanks!

14

That’s awesome :sweat_smile: I have the same problem with dates that sometimes need to stay empty. Thanks for sharing!