Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 2237 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

Hey everyone!

I am trying to pull out multiple alphanumeric serial numbers out of a giant text field. The serial number could show up in any number of places, but has a fixed format. I need the calculation to pull all occurrences of the serial number and basically put them in a list, to which I will then be able to create a join field from. Honestly, I have no idea where to even start.

The serial number is always going to be 2 Letters and 3 numbers - example: AB123

Source (Text Field):

Big and boisertious rock track, but time selling point, Track is a version of AC101, KD205, GR771. Also check FE454/GG123.

Result:

AC101

KD205

GR771

FE454

GG123

Thanks in advance.

d

Posted (edited)
2 hours ago, dztrucktion said:

I need the calculation to pull all occurrences of the serial number

Since there can be any number of such occurrences, you will need either a custom recursive function or a looping script to extract them one by one. To install a custom function, you will need the Advanced version of FMP. Which is why I will stop here until you update your profile.

 

 

Edited by comment
Posted

Understood. I have updated my profile to show that I use FileMaker Pro 16 Advanced and I do have some experience with custom functions.

Thank you!

Posted (edited)

Good. There are several ways to look at this. I think the easiest approach would be to go through the text word-by-word and check if the current word fits the desired pattern of letters and numbers. If it does, send it to the output. Then, if there are more words to check, continue the recursion with the remaining text.

ExtractSerials ( text ) =

Let ( [
countWords = WordCount ( text ) ;
firstWord = LeftWords ( text ; 1 ) ;
mask = Substitute ( firstWord ;  [ "0" ; "#" ] ; [ "1" ; "#" ] ; [ "2" ; "#" ] ; [ "3" ; "#" ] ; [ "4" ; "#" ] ; [ "5" ; "#" ] ; [ "6" ; "#" ] ; [ "7" ; "#" ] ; [ "8" ; "#" ] ; [ "9" ; "#" ] ; 
[ "A" ; "@" ] ; [ "B" ; "@" ] ; [ "C" ; "@" ] ; [ "D" ; "@" ] ; [ "E" ; "@" ] ; [ "F" ; "@" ] ; [ "G" ; "@" ] ; [ "H" ; "@" ] ; [ "I" ; "@" ] ; [ "J" ; "@" ] ; [ "K" ; "@" ] ; [ "L" ; "@" ] ; [ "M" ; "@" ] ; [ "N" ; "@" ] ; [ "O" ; "@" ] ; [ "P" ; "@" ] ; [ "Q" ; "@" ] ; [ "R" ; "@" ] ; [ "S" ; "@" ] ; [ "T" ; "@" ] ; [ "U" ; "@" ] ; [ "V" ; "@" ] ; [ "W" ; "@" ] ; [ "X" ; "@" ] ; [ "Y" ; "@" ] ; [ "Z" ; "@" ]  )
] ;
If ( mask = "@@###" ; firstWord & ¶ )
&
If ( countWords > 1 ; ExtractSerials ( RightWords ( text ; countWords - 1 ) ) )
)

 

 

Edited by comment
Oops. It needs to be countWords > 1.
  • Like 2
Posted (edited)

Note: In the example text you have pasted here, there are invisible characters ( ZERO WIDTH NO-BREAK SPACE) surrounding the last period. This will break the suggested process, because the character is not a word delimiter.

I am not sure if this is a forum glitch or a problem with your data caused by copy/pasting. If the latter, you will need to clean it up.

Edited by comment
  • Like 1
Posted
10 minutes ago, comment said:

Note: In the example text you have pasted here, there are invisible characters ( ZERO WIDTH NO-BREAK SPACE) surrounding the last period. This will break the suggested process, because the character is not a word delimiter.

This would explain the removal of any value with a period directly after it. I would assume it was from my imported data. I fixed it by using the replace command to refresh the fields.

47 minutes ago, comment said:

ExtractSerials ( text ) =



 

Worked like a charm thank you so much. I did have to include a Substitute line in the calculation field to remove an extra carriage return, but this solution was a stoke of genius! Thank you!

 

Posted
1 minute ago, dztrucktion said:

I did have to include a Substitute line in the calculation field to remove an extra carriage return

You said you wanted to use this as a multi-key match field, so the extra carriage return should pose no problem. Several Filemaker's native functions leave a trailing carriage return, e.g. LeftValues(). 

  • Like 1
Posted
26 minutes ago, comment said:

You said you wanted to use this as a multi-key match field, so the extra carriage return should pose no problem. Several Filemaker's native functions leave a trailing carriage return, e.g. LeftValues(). 

Just a little bit of an OCD thing for me, Thanks again!

This topic is 2237 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.