Run automation only if cell is empty

[Pjero_Kusijanov]

Hi :slightly_smiling_face:

I have automation set to run when an record is updated. Thank I have “Find record” action to find records which should be updated. Last step is “Update record”, but I can not select record from “Find record” step. Anything I select it gives me error. :frowning:

Does any one know how to do this?
Thank you,
Pjero

[Pjero_Kusijanov]

Databaser:

Thank you for your feedback about using “Find Records”/“Update Records”. Unfortunately, if the Find Records step returns more than one record, the Update Records step will fail to run (due to the way it handles the inputs). I agree that having this functionality would be a game-changer for your workflow; my apologies that we’re not quite there yet. At the moment, there isn’t a solid timeline in place that I can pass along

In my case “Find record” can return only one or zero records since it applies only to the record ID that trigger the automation.

[Justin_Barrett]

Databaser:

And yet again, a message from Support:

It’s not my intention to start a conflict over this issue. The beginning of my reply above pretty accurately portrays how I view this situation: it’s interesting that the documentation would say one thing while someone from support says something else. I’m not trying to assert that the documentation is correct (there are lots of examples where it’s off in varying amounts). It’s just interesting/odd/strange/curious/insert-your-word-of-choice-here that there are mixed messages about how this feature is supposed to operate. That’s all that I’m trying to say.

@Pjero_Kusijanovic Thanks for the clarification. I’ve got an idea for how to set up the automation, but I don’t have time to outline it tonight. I’ll get back to this in the next couple of days depending on my schedule.

[Databaser]

Justin_Barrett:

Databaser:

And yet again, a message from Support:

It’s not my intention to start a conflict over this issue. The beginning of my reply above pretty accurately portrays how I view this situation: it’s interesting that the documentation would say one thing while someone from support says something else. I’m not trying to assert that the documentation is correct (there are lots of examples where it’s off in varying amounts). It’s just interesting/odd/strange/curious/insert-your-word-of-choice-here that there are mixed messages about how this feature is supposed to operate. That’s all that I’m trying to say.

Ofcourse @Justin_Barrett, absolutely not my intention to start a discussion here. Only some minor frustration that something like this isn’t crystal clear. I asked Support about it and they will change the support page. It shouldn’t be the case that we get a wrong impression what the possibilities/plans are via the support pages, that’s all :slightly_smiling_face:

Thanks for pointing this out - I’ll make this recommendation to the team so we can get this changed asap.

[Justin_Barrett · Accepted solution]

Sorry for the delay. Things have been crazy here.

I suggest making a new automation using the “When record matches conditions” trigger, but we’re going to use a formula value as the trigger value. Add a formula field. I’ll name this {% Down Copy Trigger}, but feel free to name it as you wish. The formula will be:

IF(
    {% Down} != {Cancel %},
    OR(
        NOT({Cancel %}),
        ABS(DATETIME_DIFF(LAST_MODIFIED_TIME({Cancel %}), LAST_MODIFIED_TIME({% Down}), "seconds")) < 10
    )
)

The initial test is to see if there’s a difference between {% Down} and {Cancel %}. If there is (implying that {% Down} has changed), the output will be either 1 or 0. It will be 1 if either:

• {Cancel %} is empty (i.e. the first update)
  OR
• {% Down} was updated within 10 seconds of the last change to {Cancel %}

Now set up a new automation using the “When record matches conditions” trigger, with the condition being that {% Down Copy Trigger} equals 1. The only action you need is the “Update record” action, set to copy the value from {% Down} into {Cancel %} in the triggering record.

When you first enter a value into {% Down}, it will copy instantly into {Cancel %} (it might actually take more than one automation run depending on how many digits you enter, but it will copy everything). If you need to tweak the number, you’ve got 10 seconds, and that time continues to refresh as you fiddle with {% Down}. Once 10 seconds has passed with no changes to {% Down}, further changes to {% Down} will have no effect on {Cancel %}.

[Pjero_Kusijanov]

Just a comment to keep the thread open. Will reply asap :slightly_smiling_face:

[Pjero_Kusijanov]

Justin thank you for the solution!

This works perfect, but there is one thing… When {Cancel %} is edited to “0” {% Down Copy Trigger} returns 1, instead of 0.

I have tried to achieve this by editing the formula as follow; NOT({Cancel %}&"")

Is there a better solution to this?

Thank you a lot :slightly_smiling_face:

[Pjero_Kusijanov]

This NOT({Cancel %}&"") seams not to be working after first entry “0” into {Cancel %}, but it does work after second entry. (I mean entering the value “0” for the second time.)

[Justin_Barrett]

Pjero_Kusijanovic:

This works perfect, but there is one thing… When {Cancel %} is edited to “0” {% Down Copy Trigger} returns 1, instead of 0.

That’s because 0 is equivalent to False, and when NOT({Cancel %}) is evaluated, it becomes NOT(0), which is 1, so the OR() function returns 1. It’s one of the hiccups of using that shortcut method for testing for a filled/empty field.

Pjero_Kusijanovic:

I have tried to achieve this by editing the formula as follow; NOT({Cancel %}&"")

Is there a better solution to this?

You might try changing that line into {Cancel %} = BLANK(). That’s the same thing that NOT({Cancel %}) was meant to to, but I wasn’t aware that you would ever enter 0 as a value, so I felt the shortcut technique was safe.

[Pjero_Kusijanov]

BLANK() also returns 0 as False, so I get same results as with NOT().

[Justin_Barrett]

Odd…that used to work.

I just ran a quick test, and I was successful with this:

{Cancel %} & "" = BLANK()