Field Parsing Question: Been away too long...

[agraham999]

Well it's been years since I touched Filemaker, as it just never came up, but now I'm working in it again and I am finding myself slowly getting back in. I have a problem with parsing data out of a field and I hope someone can help me out, as it may be a simple calculation...or it may not be.

Anyway...I've got a field from a distributor that I believe is using SAP and they export this field in a garbage in/out format which comes into Filemaker as:

GROCERYSTORENAME # 6345 - LOS ANGELES, CA

sometimes it doesn't have the store number:

GROCERY STORE NAME - BOSTON, MA

What I want to do is extract the store name, city, and state...and of course I've got some fun characters occasionally in there like the pound and the dash. Can anyone help me in getting my brain around this stuff again?

[LaRetta]

Hi Alan,

# and - are word separators so they are actually easy to ignore. Your example shows a dash between the store and the city. Is that always the case? Or is that dash always (at least) the LAST dash in the string? And is the state always two character word at the end?

If dash is always what separates, you can use this. I suggest you create the calc and observe the results before using it in script:

Trim ( GetValue ( Substitute ( text ; "-" ; ¶ ) ; 1 ) )

However, pinning down the rules a bit more would be in order to be sure we address all possibilities. :^)

ooops - wrap with Trim() to remove leading and trailing spaces

Edited April 12, 2012 by LaRetta

[agraham999]

Yes that "-" dash is always there and yes the State is always 2 characters at the end. Thanks...I'll try this and let you know how it turns out.

[agraham999]

That worked great...however...any thought as to getting the city out since some have one word and some have two? BTW...I'm enjoying being back in FMP...and 12 is fantastic.

[Russell Barlow]

any thought as to getting the city out since some have one word and some have two?

Let ( [

start = Position (YourField ; "-" ; 1 ; 1 ) + Length ( "-" ) ;

end = Position ( YourField ; "," ; start ; 1 )

] ;

Trim( Middle ( YourField ; start ; end - start ) )

); ""

)

Edited April 12, 2012 by Russell Barlow

[LaRetta]

I am sorry ... I was just responding when I realized that, if that company name accidentally has a dash, your calculation will break. So I suggest a different calc approach which looks for the last dash.

Company Name

Let ( [

num = PatternCount ( text ; "-" ) ;

pos = Position ( text ; "-" ; 1  ; num )

] ;

Trim ( Left ( text ; pos - 1 ) )

)

City

Let ( [

num = PatternCount ( text ; "-" ) ;

pos = Position ( text ; "-" ; 1  ; num ) ;

line = Right  ( text ; Length ( text ) - pos )

] ;

Trim ( LeftWords ( line ; WordCount ( line ) - 1 ) )

)

[agraham999]

Wow...thanks so much...that was perfect.

I'm in the process of consolidating all the data from multiple sources...distributors, the SAP database, etc...

I was working in the SAP system here and was shocked at how painful it was compared to FMP...but I've been out of FM so long now I'm still getting my brain back into writing calcs...hopefully I can move us over to FMP12 and out of SAP as some point.

Thanks again!

[Lee Smith]

Let ( [

start = Position (YourField ; "-" ; 1 ; 1 ) + Length ( "-" ) ;

end = Position ( YourField ; "," ; start ; 1 )

] ;

Trim( Middle ( YourField ; start ; end - start ) )

); ""

)

Shouldn't this be

Let ( [

St = Position ( Text ; "-" ; 1 ; 1 )+ Length ( "-" ) ;

end = Position ( Text ; "," ; st ; 1 )

] ;

Trim (Middle ( Text ; st ; end - st ) )

)

[Lee Smith]

I am sorry ... I was just responding when I realized that, if that company name accidentally has a dash,...

I don't see that in what they posted? Of course these eyes are old and miss a lot of things.

Here is what I was going to post.

Trim ( Let ( [

  prefix = "-";

  suffic = "," ;

  start = Position ( Text ; prefix ; 1 ; 1 ) +

  Length ( prefix ) ;

  end = Position ( Text ; suffic ; start ; 1 )

] ;

Middle ( Text ; start ; end - start )

)

)

[LaRetta]

Nice one, Lee!!! But it breaks if there is another dash, such as in the middle of the company name "Smith Hardware - South Division" etc. which is why I ended up changing mine, LOL.

Edited April 12, 2012 by LaRetta

[Lee Smith]

Yep. and as you know, it is better for everyone to see actual data, or the source, because there is often other keys that we can use, or hurdles to be allowed for such as your point about the company.

[LaRetta]

True, I looked at dash (and then final dash), comma, space between city and state...

Mine will break if there is a dash in the city so let's hope a city never names itself South-Side, LOL.

[Russell Barlow]

Shouldn't this be

Yes. Bad C/P job, was pulled out of a case statement.