Formula to detect a new cell in the create or update automation

Community Discussions
,
1

Hi :slight_smile:!

How can I detect if a cell is new when I use the Create or Update automation?

I would like to create a simple tracker app with a single e.g.

Tracker page

---------------------
| Track new event |
---------------------

When I press the button I'd like to create a row on the 'Count' sheet and fill the count column with 1 if there's no record for Today() or update the existing value with Count[count] + 1

To do that I created a 'Create or update' automation with this condition:
Screenshot_20200705_104128

To update the Count column I tried different variants of this formula in the automation, without any luck:

  1. =IF(ISBLANK(Count[Count]),1,Count[Count]+1) This sets it as '#VALUE!' when a new row is created but then I get '#value!' on update.
  2. =IF(ISERR(Count[Count]),1,Count[Count]+1) This seemed to work for a new row but it was keeping the value as 1 on updates
  3. =IF(Count[Count] = "",1,Count[Count]+1) This was only work on updates, incrementing count but on insert was returning #value!

Depending on formula i use I either get a #value! value on a new day or the data is set to 1.

I made sure the value is saved as a 'value' and not as a formula, and the 'write to' field has = [Count]

I know I can use a filtered formula with an extra sheet to handle this problem but I want to explore this solution first.

5

Hi @KenA-01ee,
Thanks for your question.
An expression that returns the appropriate value to write to the Count column is:
=IF(ISERROR(FINDROW(Count,"Count[Date]=TODAY()")), 1, FINDROW(Count,"Count[Date]=TODAY()")[Count]+1)
To break it down:
We first check if there is an existing row for today in the Count table.

This is the critical part that wasn't handled in the expressions you tried. Since there is no context row, the expression must find the relevant table row and then get the current value in the Count column

If yes, we add 1 to the value in the Count Column in Today's row. Else we simply return 1. This value is written to Count column by the automation.

As you noted, you can accomplish this with a new table with a filtered formula as well.