Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

Substr Function

Avatar

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile
DKinsley
Level 3

23-06-2015

I have a script that "walks" through all the records in an Access database table to find the largest file ID number. This script has been working correctly. Now I need to add a prefix to all the ID numbers. This requires me to modify my script so it ignores the one character prefix (prefix is either a "T" or a "Y"). I was trying to use the Substr function to accomplish this.

This is the original script that works:


while(!oDB.isEOF()){


if(xfa.record.DataConnection1.FileName.value > nMaxID)


  nMaxID = Number(xfa.record.DataConnection1.FileName.value);


oDB.next();


}


Below is how I tried to modify the script above to ignore the prefix but I get an error that says Substr is not defined. What have I got wrong?


while(!oDB.isEOF()){


if(xfa.record.DataConnection1.FileName.value > nMaxID)


  nMaxID = Number(Substr(xfa.record.DataConnection1.FileName.value,2,12);


oDB.next();


}


Accepted Solutions (0)

Answers (16)

Answers (16)

Avatar

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile
DKinsley
Level 3

26-06-2015

Thank you. I will give it a try. My concern is that the vValue will not find the largest number in the database column when each value starts with text ("T" or "F"). Example T1012 or F1013

Avatar

Avatar
Coach
MVP
radzmar
MVP

Likes

395 likes

Total Posts

2,686 posts

Correct Reply

491 solutions
Top badges earned
Coach
Ignite 3
Ignite 1
Validate 1
Give Back 50
View profile

Avatar
Coach
MVP
radzmar
MVP

Likes

395 likes

Total Posts

2,686 posts

Correct Reply

491 solutions
Top badges earned
Coach
Ignite 3
Ignite 1
Validate 1
Give Back 50
View profile
radzmar
MVP

26-06-2015

Here'e the complete script I was thinking of.


while (!oDB.isEOF()) {  


  if (xfa.record.DataConnection1.FileName.value > nMaxID) {


       var vValue = xfa.record.DataConnection1.FileName.value;


       nMaxID = Number(vValue.substr(1,12));


  }


  oDB.next();


}


Avatar

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile
DKinsley
Level 3

25-06-2015

Thanks for your help. When you can, please post what your script should look like.

Thank you

Avatar

Avatar
Coach
MVP
radzmar
MVP

Likes

395 likes

Total Posts

2,686 posts

Correct Reply

491 solutions
Top badges earned
Coach
Ignite 3
Ignite 1
Validate 1
Give Back 50
View profile

Avatar
Coach
MVP
radzmar
MVP

Likes

395 likes

Total Posts

2,686 posts

Correct Reply

491 solutions
Top badges earned
Coach
Ignite 3
Ignite 1
Validate 1
Give Back 50
View profile
radzmar
MVP

25-06-2015

Yes, the code got a bit flawed by copy and paste at  my mobile I'm afraid 😮

Avatar

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile
DKinsley
Level 3

25-06-2015

I am mistaken. The script above is still not working correctly.

radzmar: your script does not include "substr" - is it missing or can I use Number(vValue, 2, 5) the same way as substr ?

Avatar

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile
DKinsley
Level 3

24-06-2015

This is now working. Thank you to all the responders for helping me solve this issue.

This works:


while(!oDB.isEOF()){


if(xfa.record.DataConnection1.FileName.value > nMaxID)


  nMaxID = Number(xfa.record.DataConnection1.FileName.value.substr(1,12));


  Test.rawValue = nMaxID;


oDB.next();


}


Thank you!

Avatar

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile
DKinsley
Level 3

24-06-2015

radzmar,


Your script does not have substr in it? Is it missing?

The script is going out to the database and looking for the largest number. The numbers start with either "T" or "Y" so I need to have the script ignore the first text character and only look at the remaining numeric digits. I would thing the substr would need to be run before the value is saved in a variable. 

Avatar

Avatar
Coach
MVP
radzmar
MVP

Likes

395 likes

Total Posts

2,686 posts

Correct Reply

491 solutions
Top badges earned
Coach
Ignite 3
Ignite 1
Validate 1
Give Back 50
View profile

Avatar
Coach
MVP
radzmar
MVP

Likes

395 likes

Total Posts

2,686 posts

Correct Reply

491 solutions
Top badges earned
Coach
Ignite 3
Ignite 1
Validate 1
Give Back 50
View profile
radzmar
MVP

23-06-2015

I'm just quessing because I cannot create a similar scenario at my end right now, but you should try to store the records value in a variable before you apply the substr() method.


while(!oDB.isEOF()){ 


if(xfa.record.DataConnection1.FileName.value > nMaxID)


var vValue = xfa.record.DataConnection1.FileName.value;


nMaxID = Number(vValue, 2,12); 


oDB.next();


}



Avatar

Avatar
Validate 1
Level 4
jasotastic81
Level 4

Likes

35 likes

Total Posts

329 posts

Correct Reply

43 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 25
Boost 10
View profile

Avatar
Validate 1
Level 4
jasotastic81
Level 4

Likes

35 likes

Total Posts

329 posts

Correct Reply

43 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 25
Boost 10
View profile
jasotastic81
Level 4

23-06-2015

No, that's just where I put mine for that example.

Avatar

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile
DKinsley
Level 3

23-06-2015

Does substr have to be used in the calculate event? I have all my script in the exit event.

Avatar

Avatar
Springboard
MVP
Magus069
MVP

Likes

143 likes

Total Posts

1,077 posts

Correct Reply

155 solutions
Top badges earned
Springboard
Validate 1
Ignite 3
Ignite 1
Establish
View profile

Avatar
Springboard
MVP
Magus069
MVP

Likes

143 likes

Total Posts

1,077 posts

Correct Reply

155 solutions
Top badges earned
Springboard
Validate 1
Ignite 3
Ignite 1
Establish
View profile
Magus069
MVP

23-06-2015

Yes it can be used in JavaScript... Just one last thing, make sure that your value is a String before using substr.. so try using value.toString().substr(2,5)

Avatar

Avatar
Validate 1
Level 4
jasotastic81
Level 4

Likes

35 likes

Total Posts

329 posts

Correct Reply

43 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 25
Boost 10
View profile

Avatar
Validate 1
Level 4
jasotastic81
Level 4

Likes

35 likes

Total Posts

329 posts

Correct Reply

43 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 25
Boost 10
View profile
jasotastic81
Level 4

23-06-2015

Here's the method working using JavaScript.

779793_pastedImage_0.png

779794_pastedImage_1.png

Maybe the problem is with how the method works. The first parameter is the starting location of the substring, and the second is the number of characters to take. So, from your example, you start with character 2 and take 12 characters. If you only want to ignore the first character, try substr(1,12). That starts at the first character in the string. (Keep in mind that characters in the string are numbered starting from 0.)

Avatar

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile
DKinsley
Level 3

23-06-2015

substr appears to be a Formcalc method only - not JavaScript. Your suggestion stopped the error but it does not appear to be doing what i expected. Are you sure it can alos be used in JavaScript?

Avatar

Avatar
Validate 1
Level 4
jasotastic81
Level 4

Likes

35 likes

Total Posts

329 posts

Correct Reply

43 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 25
Boost 10
View profile

Avatar
Validate 1
Level 4
jasotastic81
Level 4

Likes

35 likes

Total Posts

329 posts

Correct Reply

43 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 25
Boost 10
View profile
jasotastic81
Level 4

23-06-2015

substr is a method in JavaScript. Should you use xfa.record.DataConnection.FileName.value.substr(2,12) ?

Avatar

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile

Avatar
Validate 100
Level 3
DKinsley
Level 3

Likes

9 likes

Total Posts

545 posts

Correct Reply

5 solutions
Top badges earned
Validate 100
Validate 50
Validate 25
Validate 10
Validate 1
View profile
DKinsley
Level 3

23-06-2015

Unfortunately, with the lower case "s" I am still getting the substr undefined error.

Avatar

Avatar
Springboard
MVP
Magus069
MVP

Likes

143 likes

Total Posts

1,077 posts

Correct Reply

155 solutions
Top badges earned
Springboard
Validate 1
Ignite 3
Ignite 1
Establish
View profile

Avatar
Springboard
MVP
Magus069
MVP

Likes

143 likes

Total Posts

1,077 posts

Correct Reply

155 solutions
Top badges earned
Springboard
Validate 1
Ignite 3
Ignite 1
Establish
View profile
Magus069
MVP

23-06-2015

Hi there,

If I'm right, the substr function does not have a capital S...

while(!oDB.isEOF()){ 

     if(xfa.record.DataConnection1.FileName.value > nMaxID) 

          nMaxID = Number(substr(xfa.record.DataConnection1.FileName.value,2,12); 

     oDB.next();