# Allow to return an empty value from a formula

**URL:** <https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983>\
**Category:** Ideas & Features\
**Tags:** formulas\
**Created:** [June 20, 2022, 9:22am UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983 "2022-06-20T09:22:46Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![evyatar\_gefen](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/evyatar_gefen/32/4069_2.png) [@evyatar\_gefen](https://community.fibery.io/u/evyatar_gefen)\
**Post date:** [June 20, 2022, 9:22am UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/1 "2022-06-20T09:22:46Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [June 20, 2022, 5:24pm UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/2 "2022-06-20T17:24:24Z")

</div>

**Related:**

> [@Button/Rule formula cannot set only half of an uninitialized Date Range](https://community.fibery.io/t/button-rule-formula-cannot-set-only-half-of-an-uninitialized-date-range/2791/3):
>
> For that matter it would be useful to have a `Null` constant in Formulas, so we can actually set something to null/empty.

---

<div class="post-metadata">

**Author:** ![Claude\_de\_Loupy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/claude_de_loupy/32/1637_2.png) [@Claude\_de\_Loupy](https://community.fibery.io/u/Claude_de_Loupy)\
**Post date:** [June 22, 2022, 7:37am UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/3 "2022-06-22T07:37:34Z")

</div>

And related to that :

> [@If condition: how to return nothing when the returned type is an entity?](https://community.fibery.io/t/if-condition-how-to-return-nothing-when-the-returned-type-is-an-entity/2452):
>
> Hi, I am missing something? (I searched in this forum but found nothing) The if function needs 3 arguments. So you cannot use: if(test, do this) You must write: if(test, do this, do that) If I want to do nothing, it’s easy when dealing with text: just return “” → `if(test, “test ok”, “”)´ But if the result of a test is an entity (say a Contact), what can I return? What is the equivalent of “” for an entity? if(test, a\_given\_contact, ????) Thanks

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

---

<div class="post-metadata">

**Author:** ![Yuri\_BC](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/yuri_bc/32/8803_2.png) [@Yuri\_BC](https://community.fibery.io/u/Yuri_BC)\
**Post date:** [August 10, 2024, 9:14pm UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/4 "2024-08-10T21:14:27Z")

</div>

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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [August 10, 2024, 10:52pm UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/5 "2024-08-10T22:52:21Z")

</div>

> [@Yuri\_BC](#):
>
> The suggested workaround does not work for to one relation fields

The workaround should be fine for to-one relations:

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

---

<div class="post-metadata">

**Author:** ![Yuri\_BC](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/yuri_bc/32/8803_2.png) [@Yuri\_BC](https://community.fibery.io/u/Yuri_BC)\
**Post date:** [August 11, 2024, 8:04am UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/6 "2024-08-11T08:04:59Z")

</div>

> [@Chr1sG](#):
>
> The workaround should be fine for to-one relations:
> 
> `Projects.Filter(true = false).Sort().First()`

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_’.

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [August 11, 2024, 8:21am UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/7 "2024-08-11T08:21:25Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Yuri\_BC](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/yuri_bc/32/8803_2.png) [@Yuri\_BC](https://community.fibery.io/u/Yuri_BC)\
**Post date:** [August 11, 2024, 12:44pm UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/8 "2024-08-11T12:44:28Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [August 11, 2024, 12:56pm UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/9 "2024-08-11T12:56:24Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Yuri\_BC](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/yuri_bc/32/8803_2.png) [@Yuri\_BC](https://community.fibery.io/u/Yuri_BC)\
**Post date:** [August 11, 2024, 1:07pm UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/10 "2024-08-11T13:07:35Z")

</div>

Thank you!

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

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

```

---

<div class="post-metadata">

**Author:** ![interr0bangr](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/interr0bangr/32/8089_2.png) [@interr0bangr](https://community.fibery.io/u/interr0bangr)\
**Post date:** [August 11, 2024, 4:03pm UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/11 "2024-08-11T16:03:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JackC](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/jackc/32/5420_2.png) [@JackC](https://community.fibery.io/u/JackC)\
**Post date:** [August 12, 2024, 5:43am UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/12 "2024-08-12T05:43:44Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![interr0bangr](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/interr0bangr/32/8089_2.png) [@interr0bangr](https://community.fibery.io/u/interr0bangr)\
**Post date:** [August 12, 2024, 12:07pm UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/13 "2024-08-12T12:07:01Z")

</div>

Awesome workaround, thanks!

---

<div class="post-metadata">

**Author:** ![YvetteLans](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/yvettelans/32/6101_2.png) [@YvetteLans](https://community.fibery.io/u/YvetteLans)\
**Post date:** [August 12, 2024, 12:30pm UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/14 "2024-08-12T12:30:47Z")

</div>

> [@JackC](#):
>
> Yeah, it’s an ugly workaround, but you create a number field called “Blank” or something

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

---

<div class="post-metadata">

**Author:** ![RonMakesSystems](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/ronmakessystems/32/14122_2.png) [@RonMakesSystems](https://community.fibery.io/u/RonMakesSystems)\
**Post date:** [May 12, 2025, 3:23pm UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/15 "2025-05-12T15:23:53Z")

</div>

We must employ lots of work arounds to simply clear a value. The ability to set a value to equal null in order to clear the value would be fantastic. Thanks!

This would then work for all values, dates, text, select, etc. (except for required ones)

---

<div class="post-metadata">

**Author:** ![interr0bangr](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/interr0bangr/32/8089_2.png) [@interr0bangr](https://community.fibery.io/u/interr0bangr)\
**Post date:** [May 12, 2025, 3:40pm UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/16 "2025-05-12T15:40:45Z")

</div>

While I agree that there should be a way to set a value to NULL without having to resort to workarounds, what’s wrong with the “Clear Value” option that exists?

 ![Sprints _ Fibery](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/c/c0e7518bfb9c2e14277d92be75f89e56a86925fb.png)

---

<div class="post-metadata">

**Author:** ![RonMakesSystems](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/ronmakessystems/32/14122_2.png) [@RonMakesSystems](https://community.fibery.io/u/RonMakesSystems)\
**Post date:** [May 12, 2025, 3:47pm UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/17 "2025-05-12T15:47:23Z")

</div>

I’m finding myself needing to either set a value, or set empty. Based on some condition. So I need to use the formula… ://

Some cases I could also use multiple rules, but that’s a bit less clean

---

<div class="post-metadata">

**Author:** ![interr0bangr](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/interr0bangr/32/8089_2.png) [@interr0bangr](https://community.fibery.io/u/interr0bangr)\
**Post date:** [May 12, 2025, 3:57pm UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/18 "2025-05-12T15:57:51Z")

</div>

Ahh, didn’t know you were trying to use a formula.

What I do in those cases (again, not ideal, but it’s better than other methods in my opinion) is create a field in the database called “NULL DATE” (or NULL NUMBER or NULL USER, etc, etc) and then just use that field, that is always empty, as the value in your formula.

Like:  
If(A + B = C, A + B, NULL NUMBER)

It’s especially helpful if you need to use null in several places in a single database.

---

<div class="post-metadata">

**Author:** ![RonMakesSystems](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/ronmakessystems/32/14122_2.png) [@RonMakesSystems](https://community.fibery.io/u/RonMakesSystems)\
**Post date:** [May 12, 2025, 5:53pm UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/19 "2025-05-12T17:53:11Z")

</div>

Yeah thats what i resorted to, explained here: [Setting an empty date in automations](https://community.fibery.io/t/setting-an-empty-date-in-automations/8771)

I think setting it up as empty formulas makes it less error prone it cant accidentally be filled anywhere.

---

<div class="post-metadata">

**Author:** ![tycecycle](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@tycecycle](https://community.fibery.io/u/tycecycle)\
**Post date:** [May 12, 2025, 8:48pm UTC](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/20 "2025-05-12T20:48:31Z")

</div>

This request seems like it’s covered in this long-running feature request perhaps. [Allow to return an empty value from a formula](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983/)

[Next page](https://community.fibery.io/t/allow-to-return-an-empty-value-from-a-formula/2983.md?page=2)