Count with len()

[bhavvibudagam]

Hi Experts,

Can any one please help me on this.

In have to count the ID is not in the specified format.

The ID should contain the 9 digits. First 2 digits is letters,next 6 digits is numbers,last 1 digit is letter.

ID

BC123456Y

HC456789Z

YU345679D

D15656Y23

12485H6KL

In the above ID D15656Y23 and 12485H6KL are not on the specified format so need to get the count as 2.

[sunny_talwar]

May be this

Count({<ID = {"=Len(ID) = 9 and IsText(Left(ID, 2) and IsNum(Mid(ID, 3, 6)) and IsText(Right(ID, 1))"}>}ID)

[sunny_talwar · Accepted solution]

There is a typo above, try this

Count({<ID = {"=Len(ID) = 9 and IsText(Left(ID, 2)) and IsNum(Mid(ID, 3, 6)) and IsText(Right(ID, 1))"}>}ID)

[zhadrakas]

What about

in script:

if(len(trim(ID))=9 AND isText(right(ID,1)) AND isText(left(ID,2)) AND isNum(mid(ID,3,6)), 0,1) as CountWrongID

in Frontend

sum(if(len(trim(ID))=9 AND isText(right(ID,1)) AND isText(left(ID,2)) AND isNum(mid(ID,3,6)), 0,1))

[sunny_talwar]

I guess the requirement is to count the ID which don't meet the criteria... in the case, just add not() around the whole check

=Count({<ID = {"=not(Len(ID) = 9 and IsText(Left(ID, 2)) and IsNum(Mid(ID, 3, 6)) and IsText(Right(ID, 1)))"}>}ID)

[its_anandrjs]

Load another filed on model this ways

Replace(if(IsText(Left(ID,2)),1,0) and if(IsText(Right(ID,1)),1,0),-1,1) as Correct

Then count with SET =Count( {<Correct = {1} >}  ID)