Help Please, I am trying to figure out how to extract field contents via a calculation as follows:

My lookup field is ABCDEFG-01-12345.jpg

I want to automaticaly extract only 12345 to be placed into another field? I tried the Calculation Right ( Sorce field; 5 ) but that returns me 5.jpg

any adivise is most welcome.

Thank you

Try something like this. Replace the text on the first let variable with your field value.

Let 
( 
[

fileName = "ABCDEFG-01-12345.jpg" ; 

dashCount = PatternCount ( fileName ; "-" ) ; 
periodCount = PatternCount ( fileName ; "." ) ; 

pos1 = Position ( fileName ; "-" ; 1 ; dashCount ) + 1 ; 
pos2 = Position ( fileName ; "." ; 1 ; periodCount ) ; 

result = Middle ( fileName ; pos1 ; pos2 - pos1 )

] ; 

result

)

 

Or RightWords ( "ABCDEFG-01-12345.jpg" ; 2)-1

I've used that in the past. But there are some weird voodoo rules with what FM counts as a "word". I just avoid that completely if I can.

5 minutes ago, Lee Smith said:

Or RightWords ( "ABCDEFG-01-12345.jpg" ; 2)-1

Actually that calc results in: 12344

Using "- 0" instead also forces it to be a number and does work. Though, you can still get collisions if there are letters elsewhere before the breaking characters.

Is that part of the weird voodoo you were referring, nice catch JO, but I call it user error.

I even put the text into Data View to verify that there wouldn't be any "voodoo" problems.

Well, at least Beckham has another way to look at for a solution. 

Try this in the data viewer:

RightWords ( "ABCDEFG-0-12345.jpg" ; 1 )

 

Now add an "a" before the period.

RightWords ( "ABCDEFG-0-12345a.jpg" ; 1 )

 

Now add another "a" before the second dash.

RightWords ( "ABCDEFG-0a-12345a.jpg" ; 1 )

 

Now add a number after the "G".

RightWords ( "ABCDEFG1-0a-12345a.jpg" ; 1 )

 

ok, you make a good point,

Filter (RightWords ( "ABCDEFG1-0a-12345a.jpg" ; 1 ) ; 1234567890 ) 

I will never use the RightWords again, really different in the results. Ok, that is a fib, LOL

Always forget the GetValue Function.

GetValue ( Substitute ( "ABCDEFG-01-12345.jpg" ; ["-" ; ¶] ; ["." ; "¶" ]) ; 3 )

Lee

 

Beckham.fmp12

Hi Lee, Thank you for the sample file i am most grateful .. even lloking at the sample file i dont yet understand this witchcraft used in Calc fields but i am able progress my solution further with your generous help...

Thanks again..

Does the format of the file name ever change? It is it always the same as the example you showed?

Hi Beckham.

Closely verify that your data will parses using one of the calculations. If not, then post a copy of the one(s) that broke.

As Josh pointed out, the pattern of the data is critical.  The more sample data that is provided, the better the chances that our calculations won't brake, as Josh demonstrated with the RightWords Function. 

12 hours ago, beckham said:

i dont yet understand this witchcraft used in Calc fields

Do not feel alone. Feel free to ask us what you don't understand about these calculation. There are resources available that can demystify things such as the Let Function. 

Lee