Sharepoint online: catch index errors when using @currentField.x

0
votes

I am trying to validate the input for a column. The inputs must be of the pattern 123-45.678.901ab.

The closest I got was by using JSON formatting:

{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "style": { "background-color": "=if(@currentField.length>13,if((@currentField.0>='0')&&(@currentField.0<='9')&&(@currentField.1>='0')&&(@currentField.1<='9')&&(@currentField.2>='0')&&(@currentField.2<='9')&&(@currentField.3=='-')&&(@currentField.4>='0')&&(@currentField.4<='9')&&(@currentField.5>='0')&&(@currentField.5<='9')&&(@currentField.6=='.')&&(@currentField.7>='0')&&(@currentField.7<='9')&&(@currentField.8>='0')&&(@currentField.8<='9')&&(@currentField.9>='0')&&(@currentField.9<='9')&&(@currentField.10=='.')&&(@currentField.11>='0')&&(@currentField.11<='9')&&(@currentField.12>='0')&&(@currentField.12<='9')&&(@currentField.13>='0')&&(@currentField.13<='9'), if(@currentField.length>14,if((@currentField.14>='a')&&(@currentField.14<='z')||(@currentField.14>='A')&&(@currentField.14<='Z'), if(@currentField.length>15,if((@currentField.15>='a')&&(@currentField.15<='z')||(@currentField.15>='A')&&(@currentField.15<='Z'),'green', 'red'), 'red'),'red'),'red'),'red'),'red')" } }

enter image description here

The problem is, that my whole formula crashes (neither green nor red) if the input is shorter than 16 chars. I was expecting to prevent that by calling if(@currentField.length>15) before accessing the @currentField.15. Is there a proper way to use the @currentField.X without crashing if the input is too short?

1
jsonsharepointformatting

1 Answers

0
votes

You could try the below, use substring(@currentField, 15, 16) instead of @currentField.15

if(@currentField.length>15,if((substring(@currentField, 15, 16)>='a')&&(substring(@currentField, 15, 16)<='z')||(substring(@currentField, 15, 16)>='A')&&(substring(@currentField, 15, 16)<='Z')