Valid IF giving different responses

[1minManager]

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:

ISBLANK(NUMBER(MID([NI Number],1,1))),
ISBLANK(NUMBER(MID([NI Number],2,1))),

---

What is your expected outputs in this case? Because NUMBER() will return 0 if it doesn't recognize a number, instead of blank

NUMBER() - AppSheet Help

[1minManager]

Hi @SkrOYC so I've tried it both with ISBLANK() and =0 and got errors with both.  Almost thinking of doing SWITCH(NUMBER(MID([NI Number],1,1)),"A",TRUE,"B",TRUE,...FALSE) for each letter 😫

The issue might be that NUMBER() can return the number, zero or blank depending on how it interprets the character.

[SkrOYC]

---

@1minManager wrote:

The issue might be that NUMBER() can return the number, zero or blank depending on how it interprets the character.

---

You could report it as a bug then if NUMBER() is not returning 0 for your letters

[SkrOYC · Accepted solution]

---

@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

[1minManager]

Good call.  I'll try that out 😃