Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Auto update a field when record is added

3072 0
cancel
Showing results for 
Search instead for 
Did you mean: 
KVC
5 - Automation Enthusiast
5 - Automation Enthusiast

I have a table of people who could potentially be used as presenters. When a new record is added to the table (usually via a form), I want the field “Status” to update to “Potential”. As a completely separate step, when an event is connected to that person’s record I want the Status to update to “Active”. Any advice? I’ve used zapier for a few things in Airtable, but haven’t been able to figure this out. Thanks in advance.

13 Replies 13

Hi @KVC - it sounds like “Potential” is the default status. If so, I would do something like this:

Screenshot 2019-06-07 at 19.41.37.png

Then your status field is:

IF(Event, 'Active', 'Potential')

So status shows as “potential” for all records, but if an event is linked, then it changes to “active”.

Is this what you are after?

JB

KVC
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi @JonathanBowen. Thanks! Yes, but sometimes a third status comes into play that is updated manually…so not sure how that fits in?

What is the 3rd status and what triggers it? Or more specifically what real-world circumstances mean the 3rd status is used?

KVC
5 - Automation Enthusiast
5 - Automation Enthusiast

@JonathanBowen the third status is “Inactive” and I manually change Potential or Active to Inactive if after speaking we someone we determine they are not currently a good fit for what we are looking for.

OK, what about this:

Screenshot 2019-06-07 at 20.01.10.png

Add a checkbox (or could equally be a “notes”/text field) to “reject” the presenter, then the status formula is:

IF(
  AND({Event}, {Rejected}),
  'Error!',
  IF(
    {Event},
    'Active',
    IF(
      NOT({Rejected}),
      'Potential',
      'Inactive'
    )
  )
)

So this captures the 4 possible combinations:

  • Event and rejected (presumably this shouldn’t happen, hence “error”)
  • Event and not rejected - active
  • No event and not rejected - potential
  • No event and rejected - inactive

Any good?

JB

Oh, hang on, you can have an event and rejected, yes?

So that would need this for the status formula:

IF(
  {Rejected},
  'Inactive',
  IF(
    {Event},
    'Active',
    'Potential'
  )
)
KVC
5 - Automation Enthusiast
5 - Automation Enthusiast

Yes, this works, thank you so much! My only other wish list item would be if we could color code the words like a single select :winking_face:

Haha - can’t help with that one I’m afraid :slightly_smiling_face: