Import email Text to Filemaker?

[john12pm]

I am trying to create a method to import text from email orders we receive into Filemaker.

Below is an example of the email text we receive. I would like to capture everything right of the ":" to a related field in Filemaker. I would like to let the user cut and paste the text from the email into a text box in Filemaker. The user would then hit an 'review data' button to move the data to related fields so they can double check the accuracy. Once they confirm the accuracy, they could then hit an Export button to move the data into the Filemaker Orders Database.

USER/EMPLOYEE DETAILS:

Name :Tim Burton

BU Name :Southern California BU

MU Name :Las Vegas MU

Location :LAVNV

Department :010100

Title :Delivery Driver - Bu

Phone Number :555-444-7000

Email :[email protected]

End User Name :Tim Burton

REQUESTED SERVICE DETAILS:

Service Type :CellPhone

Provider Name :Nextel

Request Type :Replace

Equipment Type :I-530

Quantity :1

Preferred Area Code/Exchange:/

Cell Phone # ::??

Pager Number :

Pin Number for Pager :

Preffered ZipCode for Pager:

Card Number :

Direct Connect#:100*12711*01

Push to Talk# :

Reason :I-550 won't hold charge

Accessories Needed :

Addl Info. :

REQUESTER DETAILS:

Phone Number :555-444-7000

Email :[email protected]

MANAGER DETAILS:

Phone Number :

Email :

Approval Status:

SHIPPING INFO:

Attention :Jim

Address :1234 W. Taylor

City :Las Vegas

State :NV

Zip :89102

Hoping someone can lend some advice?! Thanks in advance for any input.

Regards,

John

[sbg2]

One way would be to use set field steps with the following calculation:

Let(

[Line = MiddleValues(Parse::g_EmailText;2;1);

Pos = Position ( Line ; ":" ; 1 ; 1 );

Text = Right(Line; Length(Line) - Pos);

RemCR = Substitute (Text; ¶ ; "")

];

RemCR)

As in attached example. Just change the xxx in MiddleValues(g_EmailText; xxx ; 1) to the corresponding line of the email text. For example Name: is line 2 and Service Type is line 13.

Parse.zip

[john12pm]

many thanks sbg2!! Your script worked perfect!

[Steveo the Devo]

I must add my thanks for the script too - it is brilliant. However, I would like to complicate things a little further if possible?

I am working with a patient database at a large hospital and we want to quickly import patient data that come to us as text files (see below). I have used the script above to pull out the relevant data for a single patient but we often get a list of 30 patients in a single file and copy and pasting (and then using the transfer script) 30+ times can be rather time consuming.

Is there a way, I can adapt the above script (or is there another way) to fill in the details of more than one patient (1 record per patient). Luckily the data comes across with the same number of paragraph returns between each patient.

Thanks in advance.

Format for text file

Bed number

Reference number

Gender

Name Date of Birth

Admission date admission time

Procedure

(blank)

Dietcode1

Dietcode2

Dietcode3

Dietcode4

(blank)

(blank) eg....

201

0267474

F

JOAN CITIZEN 27-06-1912

18-05-2006 1900

DOCTORB

HEMICOLECTOMY

SOFT

HPHE

ENTFL

202

0499373

F

KATHY JONES 23-11-1945

11-04-2006 1430

DOCTORJ

SUB DURAL HAEMORRHAGE

SOFT

ENTFL

203

0247708

F

JANE DOE 27-01-1931

11-06-2006 1635

DOCTORJ

RIGHT CEREBROVASCULAR ACCIDENT

DIAB

------------------------

[sbg2]

Without a sample of the actual text you are getting from your emails its hard to say. I have attached another example of one way you could do this. There is a slight change in the Set Field calc which now uses a global counter field, a new counter field and the script now contains a loop.

Let(

[Line = MiddleValues(Parse::g_EmailText;[color:red](Parse::g_Counter * 6) + 2;1);

Pos = Position ( Line ; ":" ; 1 ; 1 );

Text = Right(Line; Length(Line) - Pos);

RemCR = Substitute (Text; ¶ ; "")

];

RemCR)

A few notes:

You will have to change the "* 6" to the actual number of returns for each group. You may want to change the "* 6" part of (Parse::g_Counter * 6) to a more dynamic value. Meaning at some point the number of returns in each group may be increased (for example, because two new fields were added). If you use a global field to specify the number of returns per record then you wont have to change every calculation when a new field is added, you just need to change the global field with a Set Field step in your script.

for example (Parse::g_Counter * NoOfReturnsPer) where NoOfReturnsPer would equal how many fields are in each group. Make sense?

Parse_v2.zip

[Steveo the Devo]

Thanks for the reply - I will try out the new script later today, see what comes up and report back.

The text example (in my post above) has been copied and pasted (with only name/identifiers changed - to protect the innocent) from the email - so the number of paragraph returns/spaces/etc will stay the same. The only thing that will change will be the number of patients in each email (anywhere from 1 to 45) however, formatting should stay the same.

With some minor modifications to your original script I have been able to extract each part of the text into all the separate fields I want. However, I did not use a global setting for the text container field, is this an important omission on my behalf? What are the implications for not using a global field?

[sbg2]

With some minor modifications to your original script I have been able to extract each part of the text into all the separate fields I want. However, I did

not use a global setting for the text container field, is this an important omission on my behalf? What are the implications for not using a global field?

Please look up "global" in the help file. But in short the implications are file size. The sample group you provided in your first email is approximately 1kb of data. Times that by

how many records you might import, the numbers add up quickly. Also, even if you clear this particular data from this field at a later time the record size will remain the same.

*** Its not as bad as it might sound though. if you are curious see my note at the bottom of this post and examine the files I have attached.

I think the real issue here is you may need something more dynamic than I have provided. Start asking yourself "what if?". For example what if a new field is added to the group somewhere in the middle as such:

USER/EMPLOYEE DETAILS:

Name :Tim Burton

BU Name :Southern California BU

MU Name :Las Vegas MU

SOME_NEW_FIELD: SOME NEW DATA

Location :LAVNV

Department :010100

The solution I have provided for you would require you to rewrite every Set Field step after Set Field[MU Name; blah blah blah]. Not very versatile. I know FileMaker 8 has some new features, like variables, but I'm not sure if this will allow you to use Set Field with a variable as the field to set. If you could this would make your solution much more versatile if and when things change. Unforutanely it is beyond me since I am stuck in the world of FM7.

--------------------------------------

***

I have attached two sample files for your review. There are two scripts in each file

1) "Loop and Paste..." - which will create 1000 records with some data (either approx. 1kb or 1.5kb of data depending which file you are in)

2) "Clear All" - which loops through the records and sets each field to "" (ie. nothing).

Run the Loop and Paste script on the file then close the file and look at the file size. Open the file back up run the Clear All script and again close the file and look at the file

size. You will notice the 1kb file stays the same size while the 1.5kb file is much smaller after the Clear All. As far as I can tell somewhere between 1 & 1.5kb FileMaker will

reset a fields length.

1kb.zip

1.5kb.zip

[Steveo the Devo]

Thank you once again, the new script works brilliantly! I haven't managed to get my head around how to implement the variable data stuff (still swimming in newbie code) so for the time being it will be a busy day if the formatting changes. It would certainly be a worthwhile thing to implement. As a workaround I could set line, position and paragraph return data for the email text 'fields' in a related table, then just use those fields in the calculation?

I figured the global field's issue was primarily size related - I have now made that field global. My only other concern is that this will be a shared database: can two (or more) different users both access and replace the global field with different text strings at the same time?

Edited June 16, 2006 by Guest