9 Replies Latest reply on Nov 9, 2009 12:58 PM by mastroiani

    Script for importing and sorting data

    mastroiani

      Title

      Script for importing and sorting data

      Post

      Hello, my department gets about 100 tickets per week for various support issues (IT and Maintenance, etc).  Tickets come in a form of regular text/html emails and they are always formatted the same and have same keywords, e.g. date, issue, priority, comments, owner etc etc.  It's very tiring to enter them manually into our databse.  I was wondering if there any way (or if a script could be written) which would just import this information from emails directly into our Filemaker Pro databse?  Unfortunately, we can not allow users to enter this information into our database - that's not an option for at this moment.

       

      Thank you very much in advance for your advice.

        • 1. Re: Script for importing and sorting data
          david_lalonde@d-cogit.ca
             Easiest thing that comes to mind, not knowing the software you use, is to copy the email content into a global field. Then, have a script parse the text.
          • 2. Re: Script for importing and sorting data
            philmodjunk
              

            You might be able to import from email saved as text.

             

            I don't know of one off the top of my head that does this, but you might want to research plug ins that work with email. One of them might offer some tools that make this easier.

            • 3. Re: Script for importing and sorting data
              mastroiani
                

              Thank you very much.  I'm not sure what you mean by software, but we have FilaMaker Pro 10 (regular, not Server or advanced version). 

               

              I should mention that I'm a very technically challenged person when it comes to scripts etc.  Please be patient with me and simpler with me :) 

               

              What does copying into "global content" mean and then parsing?

               

               

              • 4. Re: Script for importing and sorting data
                mastroiani
                  

                PhilModJunk,

                 

                If I import directly from email, how would it know which fields to populate unless there is a script?

                • 5. Re: Script for importing and sorting data
                  philmodjunk
                    

                  I'm assuming you'd use a script to parse the data after importing into a text field. It's just a possible small improvement over copying and pasting your email body text one email at a time as David described.

                   

                  Instead, you could import multiple emails in a single batch job and then kick off your script to parse each of them into respective data fields.

                  • 6. Re: Script for importing and sorting data
                    david_lalonde@d-cogit.ca
                      

                    I understand.

                     

                    In a FileMaker File, there are tables, records and field. A table is a grouping of records. A record is a grouping of fields. You define these when you create your database.

                     

                    A field has one of two storage types: global; or not global (let's call it local). A locally stored field means each record has a field value that is specific to the record. A globally stored field means every record shares the same field value in the file.

                     

                    I recommend you read FileMaker's documentation (user guide, help file, etc.) It will explain a lot of the technical language used in our microcosm.

                     

                    When I said I did not know what software you were using, I meant I do not know which email application your are using and on what operating system. If you used Apple's Mail in Mac OS X, for example, then I could recommend you use AppleScript.

                     

                    Explaining how to write your script is not possible at this time. I would need to know exactly how the email is formatted and some other details. 

                    • 7. Re: Script for importing and sorting data
                      mastroiani
                        

                      Thank you. 

                       

                      Email that comes in is copied to several people:  one person uses Eudora Mail on a PC and I use AppleMail on a MAC.  Both of us could enter the new information.  

                       

                      Below is a sample email that comes in.   Middle part is what we need imported (starting form the WORK ORDER field - until/including BROKEN THERMOSTAT) bypassing all other info.

                       

                       

                       

                       

                       

                      X-Sieve: CMU Sieve 2.3
                      X-Sieve: CMU Sieve 2.3
                      thread-index: AcpMOxToyLIiOM4FQ6yTu6KGjVk23A==
                      Thread-Topic: Status for Facilities Request 607821, HAVEMEYER/2/216
                      From: "Facilities Services Center" <xxxx@cuf.columbia.edu>
                      To: <xxxx@columbia.edu>
                      Cc:
                      Subject: Status for Facilities Request 607821, HAVEMEYER/2/216
                      Date: Tue, 13 Oct 2009 15:26:37 -0400
                      X-Mailer: Microsoft CDO for Exchange 2000
                      Priority: normal
                      X-OriginalArrivalTime: 13 Oct 2009 19:26:36.0845 (UTC) FILETIME=[14C0EDD0:01CA4C3B]
                      X-Spam-Score: 0.001 () HTML_MESSAGE
                      X-Scanned-By: MIMEDefang 2.65 on 128.59.28.174

                      Work order #607821 has been assigned to your request, described below. Please retain this number as a reference for any follow up questions on this matter. If you have any questions at any time, please call x4-2222.

                      Work order #: 607821
                      Status: "Ready" (Pending)
                      Request Date/Time: 10/13/2009 2:05:27 PM
                      Tenant: CHEMISTRY
                      Name: BILL|KEME
                      Email: xxxx@columbia.edu
                      Phone: 4x160
                      Location: HAVEMEYER/2/216
                      Description: HAVEMEYER-ROOM 216
                      BROKEN THERMOSTAT...EN2190 10/13/09


                      Thank you,
                      Columbia University Facilities Services Center
                      B230 East Campus
                      212-854-xxxx















                      • 8. Re: Script for importing and sorting data
                        philmodjunk
                          

                        Filemaker comes with a lot of text functions that you can use to extract chunks of text from your email body and then move that data into dedicated fields. I suggest looking up "text functions" in filemaker's help system to learn more.

                         

                        Here's a few examples to get you started:

                         

                        Assuming you've imported/pasted the example you gave into a text field...

                         

                        Position (EmailTextField; "Work order #:"; 1; 1) will return the start of your formatted section of the email.

                         

                        Set Field [YourTable::YourWorkOrderField; Let (Start = Position (EmailTextField; "Work order #:"; 1; 1); Trim(Middle (EmailTextField; start + 14; Position(emailTextField; Start; "¶", 1) - start + 14)))]

                         

                        is a script step that should copy the work order number from the email into a data field for you.

                        • 9. Re: Script for importing and sorting data
                          mastroiani
                             Thank you so much.  Will try this tomorrow.