Jump to content

filemaker iterate (Loop) parsing text


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

Recommended Posts

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

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

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

Screen Shot 2017-06-10 at 8.01.39 PM.png

Edited by Devin
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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?

 

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

My question was about these:

h.png.af8ef19cb8264ae0422c14b943ccbd4e.png

 

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 by comment
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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