COUNT() expression returns different values to same column dependent on regular edit VS quick edit

[Doors]

Hi all,

I’ve got an expression setup to count a bunch of values and show the result:

COUNT(SELECT(Angelfish[Dinner], IN([Dinner], {“Meat”, “Veg”, “Special”, “No Dinner required”})))

When adding a new row via a regular edit on the form the first result is 0, then 1,2,3 etc.

However using quick edit the first result is 1, then 2,3,4 etc.

Is this expected bahaviour? Its gong to mess up other expressions that reference this count dependent on how the users enter the data.

It gets even worse if you create new rows with one input method then the other, the count is all over the place.

Thanks.

[Steve]

Short answer: this sounds like correct behavior.

The SELECT() expression scans the table and counts values from the matching rows it finds there. When you’re in a form to add a new row, that new row doesn’t yet exist in the table, so it won’t be found by the SELECT(). Once the form is saved, the row will be in the table and the SELECT() will find it.

A Quick Edit column is only available for columns in rows that already exist in the table, so the SELECT() can already see the row to count it.

Note, too, that how you store the count matters: is it in a normal column, or a virtual column?

[Doors]

I get it, thanks.

So what expression could I use to avoid the issue?

Alternatively disable quick edit but in this instanace it’s really helpful given the number of rows I have. Is there a way of just using quick edit and not having the option to regular edit?

[Steve]

Doors:

So what expression could I use to avoid the issue?

Is the count stored in a normal column, or a virtual column?

Doors:

Is there a way of just using quick edit and not having the option to regular edit?

Yes. You could modify the system-provided Edit action and set its prominence to Do not display:

This may have undesirable side-effects, though. Is so, there are other options.

[Doors]

Hi Steve,

Thanks for getting back to me. The count is stored in a regular column.

J.

[Steve]

A regular column value is only updated when the row is modified by a form or an action. As a result, the calculated count is “frozen” to the value computed when the row as last updated. an will remain that way until updated again through a form or by an action. The easiest fix here is to replace the normal column with a virtual column. The downside is a virtual column is not saved in your spreadsheet.