Jump to content

Help Parse Text Calculation


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

Recommended Posts

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

Link to comment
Share on other sites

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

)

 

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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 )

 

Link to comment
Share on other sites

ok, you make a good point,

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

:beer:

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

  • Like 1
Link to comment
Share on other sites

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..

Link to comment
Share on other sites

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

 

 

  • Like 1
Link to comment
Share on other sites

This topic is 2248 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.