Solved: Valid IF giving different responses - Page 2

1minManager · 08-16-2023 08:21 AM

So I have a Pushlisher App and a Core App that share a table. The both do a valid if on a UK National Insurance Number which is in the format AB123456C (2 letters, 6 numbers, 1 letter). This is the valid if I use:

OR(
   ISBLANK([NI Number]),
AND(
   ISNOTBLANK([NI Number]),
   LEN([NI Number])=9,
   ISBLANK(NUMBER(MID([NI Number],1,1))),
   ISBLANK(NUMBER(MID([NI Number],2,1))),
   NUMBER(MID([NI Number],3,1))>=0,
   NUMBER(MID([NI Number],4,1))>=0,
   NUMBER(MID([NI Number],5,1))>=0,
   NUMBER(MID([NI Number],6,1))>=0,
   NUMBER(MID([NI Number],7,1))>=0,
   NUMBER(MID([NI Number],8,1))>=0,
   ISBLANK(NUMBER(MID([NI Number],9,1)))
))

So today we got the following:

Added a record with a NI Number = WORKS
Added a record with a different NI Number = FAIL
Added a record with a different NI Number = FAIL
Added a record with a the same NI Number as the first = FAIL

No idea why. The error was actually seen in the Bot log. The bot tried to copy a record, but the valid if on [NI Number] in the target table made the copy fail. Got around it by using CONTEXT("Host")="Server" in the OR() so it ignores it, But anyone any ideas?

SkrOYC

@1minManager wrote:

SWITCH(NUMBER(MID([NI Number],1,1)),"A",TRUE,"B",TRUE,...FALSE)

I'd use CONTAINS() instead.

CONTAINS(
  "ABCDEFGHIJKLMNOPQRSTUVWXYZ",
  MID([NI NUMBER], 1, 1)
)

CONTAINS() - AppSheet Help

View solution in original post