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.
And related to that :
I think this is a prety important feature I come by quite often and must use workarounds.
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.
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’.
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.
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.
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.
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()
)
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.
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.
Awesome workaround, thanks!
That’s awesome 
I have the same problem with dates that sometimes need to stay empty. Thanks for sharing!
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)
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?
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
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.
Yeah thats what i resorted to, explained here: Setting an empty date in automations
I think setting it up as empty formulas makes it less error prone it cant accidentally be filled anywhere.
This request seems like it’s covered in this long-running feature request perhaps. Allow to return an empty value from a formula
