Help

Re: Run automation only if cell is empty

Solved
Jump to Solution
2320 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Pjero_Kusijanov
7 - App Architect
7 - App Architect

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

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

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 %}.

See Solution in Thread

19 Replies 19

Hi @Pjero_Kusijanovic

Unfortunately, the “find” action was build only to use in email actions. It doesn’t work when it finds multiple record (ID’s).

Please send an email to support@airtable.com requesting this feature and let’s hope they wake up :slightly_smiling_face:

Not necessarily. The output can also be sent to a script action which can operate on the group of found records. That’s what I would recommend in this case.

After making the script action, add an input variable that contains the list of record IDs from the “Find” action:

Screen Shot 2021-03-07 at 8.50.26 AM

Here’s a sample script to update the found records:

// Setup
const config = input.config();
const table = base.getTable("Sample");

// Build an update array
let updates = config.foundRecords.map(id => {
    return {
        id: id,
        fields: {
            // In this example, I'm updating a checkbox field
            // Change as needed based on the field(s) you wish to update,
            // and the appropriate date type for the field(s)
            "Output": true
        }
    }
});

// Update the records
while (updates.length > 0) {
    await table.updateRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}

Well, this is what Airtable support said to me…

Thanks so much for your patience here, I’ve just heard back from the engineering team regarding your previous email and it looks like the way we’ve designed this feature to work is intended for email.

So it may be possible, but not the intention I get from this :man_shrugging:

Pjero_Kusijanov
7 - App Architect
7 - App Architect

Thank you both :slightly_smiling_face:

Interesting, because the documentation says the opposite:

Afterwards you can add another action to determine what step to take (as long as at least one record was found using find records ). One recommendation is to use the update record action in order to update one set of records using data from the records found using the “find records” action outlined in this article.

@Pjero_Kusijanovic : could you please share some screenshots showing what you tried? The documentation says that your process should work, but perhaps it wasn’t set up correctly.

Pjero_Kusijanov
7 - App Architect
7 - App Architect

Sure, here I will explain in more details.

So, the trigger is updated {Down %} field…
Screenshot 2021-03-07 at 23.15.08

Next is the action find record, looking if the {Cancel %} field (the field that I want to update in next step) from the record in the previous step is empty.
Screenshot 2021-03-07 at 23.15.48

This should update {Cancel %} with a value from {Down %} only if the {Cancel %} is empty.
Screenshot 2021-03-07 at 23.18.05

Now, if the {Cancel %} is empty everything works fine and the field is updated, but if the field {Cancel %} is not empty I get “Failed to run” error. Not sure why the error appears when, there are 0 results in second field with {Cancel %} not being empty?

Hope this explains well.

That (that part that I highlighted in bold) is precisely why the error appears: the Find action isn’t returning any records, so the Update action has nothing to do. In essence, you’re trying to implement a conditional update, but automations are not yet designed for conditional behavior. They only work when every step is executed successfully. When one step fails, the whole thing fails. In this case, zero results in the Find step causes the Update step to fail because there’s nothing to update.

In the end, the problem isn’t with passing the results from the Find action to the Update action, because that works when a record is found. The real problem is with the overall design of the automation. A more appropriate design would process the conditional test before triggering the automation, so that the automation runs only when needed instead of running every time that {% Down} field is changed.

Before I recommend an alternative approach, I want to make sure that I understand your process clearly. As I’m reading it, you’re entering values manually into {% Down}. The first time that this happens, you want that number copied into the {Cancel %} field. If {% Down} is changed later, you want {Cancel %} to remain as it was. Is that correct?

Some other questions…

  • Is a value ever manually entered into {Cancel %} before something is put into {% Down}?
  • Is the value in {Cancel %} ever removed?

@Justin_Barrett, thank you for putting effort in solving my case.

You understood the process correctly.

To answer your questions;

  • Is a value ever manually entered into {Cancel %} before something is put into {% Down} ?
  • No, {% Down} is always entered first.
  • Is the value in {Cancel %} ever removed?
    {Cancel %} is Percent field and it can be modified but there should always be a value, unless removed by mistake. Change in {Cancel %} field should not influence any other field.

And yet again, a message from Support:

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.

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.

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
18 - Pluto
18 - Pluto

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
7 - App Architect
7 - App Architect

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

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:

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.)

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.

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.

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

Odd…that used to work.

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

{Cancel %} & "" = BLANK()