Input validation, MsAccess versus Outsystems

Hello, I am creating a small app to explore the possibilities for OS in my organization, but experience some difficulties creating a validation.

The validation performs a final-numbercheck:

It concerns the input of an 18 character (EAN-)code, for example 871687110003012500.

First digit must be multiplied by 3, second by 1, third by 3, etc. until the 17th digit.

All those 17 results must be summed. From this result, the most right number must be equal to the most right number of the EAN-code.

In MsAccess I created the functionality as follows:

IIf(IsNull([Asset_EAN]);"";IIf(Len([Asset_EAN])=18;

IIf(Right((Left([Asset_EAN];1)*3)+(Right(Left([Asset_EAN];2);1)*1)+(Right(Left([Asset_EAN];3);1)*3)+(Right(Left([Asset_EAN];4);1)*1)+(Right(Left([Asset_EAN];5);1)*3)+(Right(Left([Asset_EAN];6);1)*1)+(Right(Left([Asset_EAN];7);1)*3)+(Right(Left([Asset_EAN];8);1)*1)+(Right(Left([Asset_EAN];9);1)*3)+(Right(Left([Asset_EAN];10);1)*1)+(Right(Left([Asset_EAN];11);1)*3)+(Right(Left([Asset_EAN];12);1)*1)+(Right(Left([Asset_EAN];13);1)*3)+(Right(Left([Asset_EAN];14);1)*1)+(Right(Left([Asset_EAN];15);1)*3)+(Right(Left([Asset_EAN];16);1)*1)+(Right(Left([Asset_EAN];17);1)*3);1)=Right(10-Right([Asset_EAN];1);1);"";"Not valid");

"Length"))

Now I tried it in OS, but got stuck.. (see post below)

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,17,17)))=

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,0,0))*3)

+

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,1,1))*1)

+

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,2,2))*3)

+

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,3,3))*1)

+

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,4,4))*3)

+

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,5,5))*1)

+

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,6,6))*3)

+

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,7,7))*1)

+

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,8,8))*3)

+

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,9,9))*1)

+

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,10,10))*3)

+

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,11,11))*1)

+

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,12,12))*3)

+

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,13,13))*1)

+

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,14,14))*3)

+

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,15,15))*1)

+

(TextToInteger(Substr(AssetForm.Record.Asset.EAN,16,16))*3)

The code above does not work, for at least 2 reasons:

Reason 1: The first (most left) number can’t be retrieved by TextToInteger(Substr(AssetForm.Record.Asset.EAN,0,0))

Which is strange as the other numbers seem to be retrieved well. So that’s my first question: how do I retrieve the most left number?

Another thing is that I don’t get the most right number of the result of the sum.

Imagine 871687110003012500 as the EAN

Than:

8*3=24

7*1=7

1*3=3

6*1=6

8*3=24

7*1=7

1*3=3

1*1=1

0*3=0

0*1=0

0*3=0

3*1=3

0*3=0

1*1=1

2*3=6

5*1=5

0*3=0

Total sum: 90

Most right digit 0. So the last number of the EAN must be 0. This EAN is valid.

 How do I get the most right number of the total sum? As the sum can consist of 2 or 3 digits.


Can someone help me out?

Solution

You're using substr wrongly. 

It expects text, start index, number of characters to retrieve. 

You are assuming the last one is ending index. 

I think this should solve the problem... 

Also, I am assuming your code is in an IF condition, as the = sign will be a comparison. Always... 

Cheers 

Solution

To get the last number, use the length function, to get the size of the text and set the substr to retrieve starting on this value - 1,with 1 as the number of characters. 

Eduardo Jauch wrote:

You're using substr wrongly. 

It expects text, start index, number of characters to retrieve. 

You are assuming the last one is ending index. 

I think this should solve the problem... 

Also, I am assuming your code is in an IF condition, as the = sign will be a comparison. Always... 

Cheers 


ah, I'll start with fixing this, and puzzle further on. Thanks!

Done!!!! @Eduardo Jauch, thank you...


SyntaxEditor Code Snippet

TextToInteger(Substr(AssetForm.Record.Asset.EAN,17,1))
<>
Substr((TextToInteger(Substr(AssetForm.Record.Asset.EAN,0,1))*3)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,1,1))*1)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,2,1))*3)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,3,1))*1)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,4,1))*3)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,5,1))*1)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,6,1))*3)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,7,1))*1)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,8,1))*3)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,9,1))*1)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,10,1))*3)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,11,1))*1)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,12,1))*3)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,13,1))*1)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,14,1))*3)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,15,1))*1)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,16,1))*3)

,Length((TextToInteger(Substr(AssetForm.Record.Asset.EAN,0,1))*3)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,1,1))*1)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,2,1))*3)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,3,1))*1)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,4,1))*3)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,5,1))*1)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,6,1))*3)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,7,1))*1)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,8,1))*3)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,9,1))*1)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,10,1))*3)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,11,1))*1)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,12,1))*3)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,13,1))*1)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,14,1))*3)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,15,1))*1)
+
(TextToInteger(Substr(AssetForm.Record.Asset.EAN,16,1))*3))-1,1)