Advanced Text Calculation

[innodat]

Hi,

I'm trying to extract purchasing information from a paypal email, and got stuck. The goal is to read out the amount, name and email address of the buyer. Here's the beginning of the email in plain text:

Dear Rheinport Media Inc.,

This email confirms that you have received a payment of $149.00 USD from *buyer name* ([email protected]).

I'm using the following variables: $price, $name, $email, and a function MiddleWords ( Messages::Body Show; xx; z ).

This works fine to get the price, but since a customer can have a first-last name, or first-middle-last, or other combinations, extraction of the name and following email address fails.

Does anyone have an idea how to accomplish this? A known element, for example is the structure of the email address: "([email protected])". Is there a way to go backwards from that element?

Thank you so much for your input!

Michael

[fabriceN]

It seems that the amount is just after a "$" and before a " USD", the name is between a "USD from " and a " (", and the e-mail address between " (" and ")".

Try this custom function, it makes translating this into FileMaker much easier : http://www.briandunning.com/cf/674

HTH

[innodat]

Hi Fabrice,

thank you for the tip. I must not be applying it right, because my variable remains empty...

$amount = BetweenNext ( Messages::Body Show; "$"; 1 ; 0 ; "USD" ; 1 ; 0 )

I set "BetweenNext" to be the custom function's name, and parameters: occurrence1, occurrence2, searchString1, searchString2, include1_1, include2_1 and text

What am I doing wrong?

[innodat]

For anyone reading this, here's the suggested custom function:

Let ([

_occurrence1 = Case ( occurrence1 = 0 ; 1 ; occurrence1 )

; _occurrence2 = Case ( occurrence2 = 0 ; 1 ; occurrence2 )

; _lenStr1 = Length ( searchString1 )

; _p1= Position ( text ; searchString1 ; Case ( _occurrence1 < 0 ; Length ( text ) ; 1 ) ; _occurrence1 )

; _p1 = Case ( not include1_1 and _p1 ; _p1 + Length ( searchString1 ) ; _p1 )

; _rightText = Replace ( text ; 1 ; _p1 - 1 ; "" )

; _rightTextWithoutStr1 = Right ( _rightText ; Length ( _rightText ) - _lenStr1 )

; _p2= Position ( _rightTextWithoutStr1 ; searchString2 ; Case ( _occurrence2 < 0 ; Length ( _rightTextWithoutStr1 ) ; 1 ) ; _occurrence2 ) -1 + _lenStr1

; _p2 = _p2 + Case ( include2_1 ; Length ( searchString2 ))

]

;

Case ( _p1 and _p2 > _lenStr1 ; Left ( _rightText ; _p2 ))

)

BetweenNext ( text ; searchString1 ; occurrence1 ; include1_1 ; searchString2 ; occurrence2 ; include2_1 )

Edited May 1, 2008 by Guest

[comment]

Surely this could be simpler?

Let ( [

start = Position ( text ; prefix ; 1 ; 1 ) + Length ( prefix ) ;

end = Position ( text ; suffix ; start ; 1 )

] ;

Middle ( text ; start ; end - start )

)

For example, to extract the price in your e-mail, make prefix = "$" and suffix " USD".

You could add a Case() statement to protect from returning a result when prefix/suffix are not found.

Edited May 1, 2008 by Guest

[innodat]

That totally worked, thank you!!