Valid If sometimes not working

AppSheet AppSheet Q&A
, ,
1

Hi everyone,

My client approach me once in few months back saying that the system is not working as expected in which they are able to enter a duplicate entry of some columns which is weird because when I check back in appsheet data tab, valid if and error should be showing. When I tested few times using mine and my colleague laptop, some weird things happen because my laptop could get the valid if error but when I tested with another laptop it couldnt, they could still enter data which were not supposed to be able to enter and should be showing error instead. Okay this is few months back and somehow the error is showing again but today they approach me saying same things is happening. I am confused cause the formula should be working fine.

Below are the valid if information and a snapshot of the database

Valid if Formula:
or(
NOT(IN(
[_THIS],
SELECT(
Pest Control 3 Monthly[Unit],
([Month] = MONTH(TODAY()))
))),
isnotblank([Status]))

Error Message: The data of Unit has been entered this month. Please change.

Snapshot of database: The highlighted line, the right one is unit and month is in another column but you could see on the left the month is same. Status also is in the form.

Nadhirah_1-1729736449140.png
Nadhirah_1-1729736449140.png408×192 4.29 KB

Supposed error shown in other places:

Nadhirah_0-1729736407100.png
Nadhirah_0-1729736407100.png293×298 10 KB

However in mentioned table form: No error shown despite checking all valid if formula

Nadhirah_2-1729736542474.png
Nadhirah_2-1729736542474.png300×324 8.36 KB

Below is the valid if formula for working valid if table if you want to compare:
or(
NOT(IN(
[_THIS],
SELECT(
Office Monthly[Unit],
([Month] = MONTH(TODAY()))
))),
isnotblank([Status]))

2

If two users have made records at the time or if the other user had stale data in his device, this could happen.

1 Like
3

Is that so? Thanks for the info. I will get back to my client if they did
this previously.

4

One way to improve this.. set the quick sync option ON so it will update all devices (needs to use the same version from your app) within few seconds in the background.

2 Likes
5

You are using MONTH() with your validation. In general.. it will read the same month, but from a different year. Is it possible that it will give you wrong result next year? To avoid this, it would be better to use EOMONTH() so you could be sure you are reading the data from a correct month.

2 Likes
6

What does EOMONTH() do? It gives current month and year? I used MONTH() to identify this month only

7

I checked with my client and also the database, the data inserted by user is on different day and one device so this should not happen.

8

https://support.google.com/appsheet/answer/10107332?hl=en

1 Like