CODEWITHSUNDEEP
filtercharacternumericfilemakerletter
Ken
Ken

Reputation: 3

How can I extract the numeric portion before the first letter in a FileMaker calculation?

I have a field called Serial Number in FileMaker, and I need to extract the numeric portion of the string that appears before the first letter (A-Z), after removing the first 6 characters.

Here's the behavior I expect:

  1. Remove the first 6 characters from the Serial Number.
  2. Extract the numeric part before the first occurrence of a letter (A-Z).
  3. Convert the extracted portion into a number.

For example:

I've tried using a Let function with Position and Min to find the first letter and extract the digits, but I’m encountering issues where the calculation does not work as expected.

Here's what I thought would work initially (I don't think "[A-Z]" is a valid input for the Position function):

Middle ( Serial Number; 7; Position ( Serial Number; "[A-Z]" ; 7 ; 1 )- 7)

Here’s my most recent attempt:

GetAsNumber (
    Let (
        [
            stripped = Right( Serial Number ; Length( Serial Number ) - 6 ) ;
            firstLetterPos = Min( 
                Position( stripped ; "A" ; 1 ; 1 ) ;
                Position( stripped ; "B" ; 1 ; 1 ) ;
                Position( stripped ; "C" ; 1 ; 1 ) ;
                ...
            ) ;
            firstLetterPos = If( firstLetterPos = 0 ; Length( stripped ) + 1 ; firstLetterPos ) ;
            numericPart = Left( stripped ; firstLetterPos - 1 )
        ] ;
        GetAsNumber( numericPart )
    )
)

Problem:

What I'm Looking For:

Upvotes: 0

Views: 59

Answers (3)

Sylvain Parent
Sylvain Parent

Reputation: 9

Here's a custom function that extracts from the collected string the series of consecutive numeric characters found from the starting position. Paste this custom function into your solution and call it like this

tText_00ExtractConsecutiveNumbers ( Serial Number ; 7 )

/**
tText_00ExtractConsecutiveNumbers ( stringToTreat ; startingPosition ) 
Version : 1.0
**/


While ([ 

startingPosition = Abs ( Int ( GetAsNumber ( startingPosition ) ) ) ;
stringLength = Length ( stringToTreat ) ;
maxLoop = If ( startingPosition ≤ 1 ; stringLength ; stringLength - (startingPosition - 1 ) )  ;
isTheEnd = False ;
ctr = startingPosition 

]; 

not isTheEnd

;[

isANumber = not IsEmpty ( GetAsNumber ( Middle ( stringToTreat ; ctr ; 1 ) ) ) ;
isTheEnd = ( ctr > stringLength ) or ( not isANumber ) ;
resultToReturn = Middle ( stringToTreat ; startingPosition ; ctr - startingPosition ) ;
ctr = ctr + 1 
    
];

resultToReturn

)

Upvotes: -2

michael.hor257k
michael.hor257k

Reputation: 117102

Filemaker has no native support for RegEx, so the term [A-Z] is meaningless.

I would suggest something like:

Let ( [
stripped = Replace ( Serial Number ; 1 ; 6 ; "" ) ;
letters = Filter ( stripped ; KanjiNumeral ( stripped ) ) ;
pos = Position ( stripped ; Left ( letters ; 1 ) ; 1 ; 1 ) 
] ;
Replace ( stripped ; pos ; Length ( stripped ) ; "" )
)

Alternatively you could install the (free) BaseElements plugin and do:

BE_RegularExpression ( Serial Number ; ".{6}(\d+).+" ; "" ; "$1"  )

Upvotes: 1

bruderdog
bruderdog

Reputation: 136

Does this work for you?

Let ([
    // test input. Put your real input here:
    ~serial = "12345P382M45" ;

    ~stripped = Right ( ~serial ; Length ( ~serial ) - 6 ) ;
    ~letter = Left ( Filter ( ~stripped ; "qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM" ) ; 1 ) ;
    ~pos = Position ( ~stripped ; ~letter ; 1 ; 1 ) ;
    // I'm not sure what you want to happen if there are no letters
    // but this will return EVERYTHING after the first 6 digits in
    // the absense of letters(~pos == 0)
    ~res = If (
        ~pos > 0 ;
        Left ( ~stripped ; ~pos - 1 ) ;
        ~stripped
    )
];
    GetAsNumber ( ~res )
)

Upvotes: 0

Related Questions