Valid IF giving different responses

AppSheet AppSheet Q&A
1

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?

2

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
1 Like
3

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 :tired_face:

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

4

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

5

I’d use CONTAINS() instead.

CONTAINS(
  "ABCDEFGHIJKLMNOPQRSTUVWXYZ",
  MID([NI NUMBER], 1, 1)
)
CONTAINS() - AppSheet Help
1 Like
6

Good call. I’ll try that out :smiley:

2 Likes