filemaker iterate (Loop) parsing text

[Devin]

I'm in the process of seeing if it's posable to parse emails orders?

We get the emails that are very standard format. Getting them into filemaker was simple with 360works.

Parsing the text above "1    SKU....." is simple.

My issue  is I need to create a loop and be able to break out each Line Number for it's own record.. In this example text there are two line numbers.

Trying to grab  SKU #, Item Name, Item Description, Item Cost, File URL

Any help would be greatly appreciated!!

 

 

PURCHASE ORDER
PO # :    2974822-1
Date :    2017-06-09

From:
Company
1111 SomeWhere
Middletown, IL 11121

Ship Account Number :    3333329
1    SKU # CC-34QA-78489
     
Item Name    Large Poster
Item Description    3'x4' Poster
Item Cost    $18.75
File URL    https://somewhere.com/poster_large.pdf
Shipping Method    Fedex 2nd Day
Shipping Address    
Companystore XYZ
ATTN : ME
Bigstreet
Smalltown , CA - 22112
US
      
2    SKU # CD-88KS-457894
     
Item Name    Small Poster
Item Description    1'x2' Poster
Qty    1
Item Cost    $18.75
File URL    https://somewhere.com/poster_small.pdf
Shipping Method    Fedex 2nd Day
Shipping Address    
Companystore XYZ
ATTN : ME
Bigstreet
Smalltown , CA - 22112
US

[comment]

You can extract the value of the n-th SKU using:

Let ( [
start = Position ( $e_mail ; "SKU # " ; 1 ; $n ) + 6 ;
end = Position ( $e_mail ; ¶ ; start ; 1 ) 
] ;
Middle ( $e_mail ; start ; end - start )
)

where $n is a variable you increase within the loop until:

$n > PatternCount ( $e_mail  ;  "SKU # " )

 

Use the same formula to extract the other components - just change the start and end search strings.

 

 

 

Edited June 10, 2017 by comment

[Devin]

Thanks Comment..

I was able to extract SKU info after making sure that Return Characters, Tabs and Spacing were correct.

But I've been unable to grab anything else.

I'm supplying a screen shot to show returns and tabs.

Let ( [
start = Position ( $e_mail ; "¶File URL	" ; 1 ; $n ) + 6 ;
end = Position ( $e_mail ; "¶Shipping " ; 1 ; $n ) 
] ;
Middle ( $e_mail ; start ; end - start )
)

Edited June 11, 2017 by Devin

[comment]

It seems to work just fine for me - even better if I change the + 6 to +10 (the length of the start search string). Please post a zipped text file instead of a screenshot.

[Devin]

I have no issues grabbing the first when I keep $n = 1.. But if I want the 2nd one.. $n = 2 it fails to work.

Here is my test fmp file that I'm playing with.

Testemailparse.fmp12.zip

Edited June 11, 2017 by Devin

[Devin]

Found the issue..

The end position was my problem.. 

Let ( [
start = Position ( $e_mail ; "¶File URL" ; 1 ; $n ) + 10 ;
end = Position ( $e_mail ; "¶Shipping Method	" ; 1 ; $n ) 
] ;
Middle ( $e_mail ; start ; end - start )
)

What is the need for putting a length value? Just trying to better understand the logic.

[bruceR]

The 10 takes into account the entire length of the start phrase.

But here's a file with a few script mods to consider, including a global field to determine which occurrence you are searching for.

 

TestEmailParse_MODBFR.fmp12.zip

Edited June 11, 2017 by BruceR

[Devin]

12 minutes ago, BruceR said:

The 10 takes into account the entire length of the start phrase.

But here's a file with a few script mods to consider, including a global field to determine which occurrence you are searching for.

TestEmailParse_MODBFR.fmp12.zip

Thanks Bruce.. I should have played around with the reasoning for the 10.. I was just assuming that it was starting at the end of the search string. Good info.

Thanks for your mod.. Did not know I could use Char(9) to replace the hidden tab. Makes is much simpler to debug for visual.

Diving deeper into your mod. 

I like the + Length( $startString) 

Trim( Middle ( $e_mail ; $start ; $end - $start ))  Very cool way of cleaning up the text just incase.

 

[Devin]

OK.. another issue is parsing out the shipping address info.

In the example it's 5 lines. But looking thru orders I see that they may include 2 street lines for a suite or something.They also provide a phone number under the Country at times.

Is there away to script this when it's not the same?

 

 

[comment]

We only have one small example of the input and no clear formulation of the rules. Do you know what the numbers just before the string "SKU #" represent?

[Devin]

13 minutes ago, comment said:

We only have one small example of the input and no clear formulation of the rules. Do you know what the numbers just before the string "SKU #" represent?

sorry. should have explained that part more...The shipping address it the part below the Shipping Method for each SKU  (Line Item)

Example 1

Shipping Method    Fedex 2nd Day
Shipping Address    
Companystore XYZ
ATTN : ME
Bigstreet
Smalltown , CA - 22112
US

 

Example 2

Shipping Method    Fedex 2nd Day
Shipping Address    
Companystore XYZ
ATTN : ME
Bigstreet
Smalltown , CA - 22112
US
333-111-8888

Example 3

Shipping Method    Fedex 2nd Day
Shipping Address    
Companystore XYZ
ATTN : ME
Bigstreet
Suite 1A 
Smalltown , CA - 22112
US
333-111-8888

Example 4

Shipping Method    Fedex 2nd Day
Shipping Address    
Companystore XYZ
ATTN : ME
Bigstreet
Suite 1A 
Smalltown , CA - 22112
US

 

[comment]

My question was about these:

 

Another way to look at the problem is to ask what comes after the address. Going by your example file, there is always a carriage return, followed by a space, a tab, and another space.

 

 

Edited June 12, 2017 by comment

[Devin]

I do not need to loop between the each SKU for the shipping address. They will always be the same.. I'm told.

My issue is trying to figure out how to take an address info and split it as it varies order to order with the amount of data they supply.

[comment]

Are you asking how to split the address into individual elements? I don't know - unless there is something constant you can grab onto for orientation. Examples are pretty useless for this, because we don't know what's constant and what's just an example.

[Devin]

I can capture the address as a single text field by using one of the techniques above..

So yes that is my dilemma in breaking them up in to individual elements...

from what I can tell their is not away to capture number of Lines? Was thinking if their was then it might be possible.

 

[comment]

1 hour ago, Devin said:

from what I can tell their is not away to capture number of Lines?

You can determine the number of lines by using the ValueCount() function. But I don't see how this will help you.

[Devin]

19 minutes ago, comment said:

You can determine the number of lines by using the ValueCount() function. But I don't see how this will help you.

I was just thinking about trying pattern count for ¶. 

From what I can tell based on my scan of emails they have 4 different ways they are suppling the address. 1 - 7line version, 2 - 6 line version and 1 -5 line version. 

By doing an if statement for them based on the pattern count.. For the 2 - 6 lines version will need to see if only numbers are the last line. then based on that I would know what version  of the 6 line it is.

Just me thinking out loud..