14 Replies Latest reply on Jan 6, 2011 5:08 AM by RitteT

    Insert data from mail

    RitteT

      Title

      Insert data from mail

      Post

      I receive new customers via mail. They send a form where they write name, number etc. in the field.

      Right now, we copy and paste every new form.  

      I want to automate the procces. Every new mail should get into the filemaker database.

      Example. I want "Given name" entered in the form-field in the mail, to be imported to "given name" in filemaker.

      "Number" to "number" etc. 

      Can you give me ideas on how to make it?

        • 1. Re: Insert data from mail
          philmodjunk

          You might want to investigate email plug ins to see what options they may offer.

          Using just native FileMaker, you can probably save the email as a text file and then use Import Records to import the data. Depending on file formats selected, you'll likely have to include some processing to extract the customer data from this imported file and store the data in fields of a new record in your main customer info table.

          • 2. Re: Insert data from mail
            RitteT

            The thing is that I don't know how to extract the costumer data from the mail/text.

            Do you have any suggestion?

            • 3. Re: Insert data from mail
              philmodjunk

              Not without knowing what the data in this text file looks like. FileMaker has a number of text functions that can do the job, but exactly how you'd use them depends on the format of this text file.

              • 4. Re: Insert data from mail
                RitteT

                I can save the mail as formatted text or plain text, from the mail app.

                If you want I can upload the text file? (If so, which one is preferred?) 

                Thanks!

                • 5. Re: Insert data from mail
                  philmodjunk

                  Plain text. The key is to be able to identify text in the file that immediately precedes the data you want to extract. Then you can use the position of that text to extract the next section of text as the value you want to store in a field. Check out the Position, left, and right text functions in Filemaker Help to learn more.

                  • 6. Re: Insert data from mail
                    RitteT

                    Here is the file:

                    http://www.megaupload.com/?d=15ZONEZ1

                    (I don't know if it's aloud to post extern links, but I can't attach to this post.)

                    I manage to import the file to filemaker, but it as 22 new records.

                    If I want to do what you suggest then every field should be in the same record?

                    Edit: I'm using swedish filemaker. Sadly I'm not able to look for position or text functions. Probably the translation is a lot different in filemaker, but I'll try to find it.

                    Edit2: Google found it.

                    Is there more chapters I can read about regarding this concern?

                    • 7. Re: Insert data from mail
                      philmodjunk

                      No problem with the external links. We do it all the time. The only limitation FileMaker Inc asks of people is not to post unrelated links--this then is seen as unsolicited spam and the FileMaker will then take action to remove the links.

                      When importing a text file each line of text will import into a different record. You'll have to import into a dedicated table for this and then use a script to extract the needed data into fields that are part of your main table.

                      I can't read the Swedish, so that makes it difficult to identify the exact text to use to extract the desired data, but it looks like you could use Go To Record ( 8 ) to go to the record where the person's first name is found.

                      After your script pulls all the needed data into your main table, you can choose whether to keep this data for future reference or delete all 22 recorrds.

                      • 8. Re: Insert data from mail
                        RitteT

                        I got it now, thanks! The left-function is pretty sweet!

                        One last question and I think that I've solved everything. How do I combine left and right functions?

                        I'm thinking of combining rightwords with leftwords-functions to get the value between. Right now I'm able to extraxt "Name*: John".

                        Using "MiddleWords ( plainmail ; Förnamn ; 2 )" gives me "Name*: John".

                        All I have to do is to narrow it to John, then I'm done. :)

                        • 9. Re: Insert data from mail
                          philmodjunk

                          Hmmm, MiddleWords uses these parameters: textexpression; starting word, number of words. I thus, don't know what value you have in Förnamn.

                          To answer your question, it depends on the number of possible patterns your text may take. Will there always be one and only one word here after the label, such as "John" in your example?

                          If so right words ( plainmail ; 1 ) would return the single rightmost word in the Plainmail field.

                          I'd be inclinded to use:

                          Let ( L = length ( plainmail ) ; trim ( Right ( plainmail ; L - Position ( plainmail ; ":"; 1 ; 1 ) ) ) )

                          This returns all text to the right of the semi colon (:) but with any leading and trailing spaces trimmed (deleted).

                          • 10. Re: Insert data from mail
                            RitteT

                            The problem is that I have to extract address and there may be 2,3 or 4 words. The person may also have a double namne, so I don't think that the "1" will work.

                            The only thing that is "constant" is the name of the fields in the form.

                            Example, I've "name" and "surname", between them is always the typed name by customer. 

                            With middlewords, I'm able to choose where to begin. But I can't decide where to end it.

                            So right now I can extract "John Surname:" from "Name: John Surname", because I tell it to start after john but I can't tell it where to stop.

                            I'll try your function. Thanks.

                            • 11. Re: Insert data from mail
                              RitteT

                              PhilModJunk, your last function was great. I'm able to take everything after the "*". But the problem is I can't decide for it wen to stop.

                              Is there a way to tell it to stop, so that it does not include next field. I've tried to trim it from the other way but it doesn't work.

                              Thanks for you help

                              • 12. Re: Insert data from mail
                                philmodjunk

                                Trim removes leading and trailing spaces only.

                                You'll need first to identify text that marks the position of the next field. Then you can use position to identify the start of that section and can then use Middle to extract just the text between the two field labels.

                                • 13. Re: Insert data from mail
                                  Sorbsbuster

                                  Can I pop in an idea? If you can have the customers use a standard e-mail template that you provide (as in 'fill in this form, please') in the format, for example:

                                  First_Name: <type your first name here>

                                  Family_Name: <type your family name here>

                                  Date_Of_Birth: <type your DOB here>

                                  etc,

                                  where 'First_Name' is the field name you have used in your database, and the customer simply replaces the '<type your first name here>' text, then I remember seeing a really neat trick (by Matt Petrowsky, I think).  It allowed you to have the list of field names in any order in the text (!) and the script would automatically extract the right data and insert it in the right field.  It was really flexible - you could change the order of the data collection, or even add more fields to the form at any time, and the script would cope with it.  I hope my memory serves me correctly.

                                  It was a long time ago, but if this would be useful for you post back and I'll try and find it.

                                  • 14. Re: Insert data from mail
                                    RitteT

                                    I've sorted it out. 

                                    Thanks for the help! :)