7 Replies Latest reply on Feb 4, 2010 4:36 AM by HugoLidia

    Importing data on a regular basis

    user14360

      Title

      Importing data on a regular basis

      Post

      Hi,

       

      If you regularly have a report coming to you in an excel spreadsheet (or csv file) that needs to be imported weekly (possibly even daily) into a database, is there a way to set it up to be fully automated?

       

      At the moment, the user receives the report via email, saves it to their hard drive and then runs a script in the database which:

      - goes to the right table

      - shows all records

      - opens the import records dialog box at which point the user selects the file they saved to their hard drive

      - imports the records (probably by using the 'last order' setting, so it should be relatively easy)

       

      Ideally however, I'd like it if the report could come in via email, be placed in a shared folder somewhere and then for the import script to run automatically at a certain time each week.

       

      Does anyone have any advice re this process?  Are there naming conventions I would need to use?  Filepath variables?  Ways to avoid errors if the person responsible for putting the file in the shared folder hasn't done so on time? 

       

      Thanks.

      Filemaker Pro 10 Advanced, Mac OS X 

        • 1. Re: Importing data on a regular basis
          hiatts
            

          easy... applescript.

           

          applescript fmp dictionary has functions to call filemaker scripts, in addition to many other features.

           

          1. setup a mail rule to run applescript

          2. applescript to detach attachment and save to a folder

          3. applescript then updates a global field (single record) with the file name and path, play with the directory naming etc until you get it correct for fmp to understand 

          4. applescript then calls a fmp script, that then takes the value in the global field and imports the file contents

           

          note this method dsnt care when the attachment comes in...

           

          alternatively with a pure shared folder... you have applescript fire up via ical... to look for a file, or even have fmp fire up and look for a file (start up file option), setting again ical to trigger fmp to open.

           

          we use all of the above every day in the best company in the world

           

          stuart 

           

          • 2. Re: Importing data on a regular basis
            user14360
              

            Brilliant.  I haven't played with AppleScript yet, but I'll give it a go.

             

            A couple of q's:

            1) When the email comes in, does the user do something with the email to fire the applescript?  (Like move it to a particular mail folder?)  Or do we have a naming convention for the subject which triggers the apple script?  Or can it be either?

            2) When you suggest having iCal to trigger filemaker to open and look for the file, is there a reason why you would do it that way rather than have a server side script do it?

             

            Thanks. 

             

            (I'd love to know what the best company in the world is!  And how you get to work for them... )  :  ) 

            • 3. Re: Importing data on a regular basis
              user14360
                

              So, having had a bit of a look at AppleScript, I suspect it will take me a little while to get up to speed and write one that works for me.  So I'm wondering:

               

              1) Does anyone have a mail plugin they would recommend that can automate this task?

              2) Does anyone have an AppleScript sample that would automate this task? (or that would point me in the right direction of how to write one.)

               

              Thanks. 

              • 4. Re: Importing data on a regular basis
                hiatts
                  

                1. if you set up a mail rule, it will exe the applescript if it meets certain conditions, ie from particular address, subject line has certain text, etc etc

                2. fms (as opp to fmp - client) has limitations on server scripts, file must be in a certain directory, etc etc. I am suggesting using fmp client to get the data into fms hosted db. 

                • 5. Re: Importing data on a regular basis
                  hiatts
                    
                  this is code for a droplet... however, you can also modify it to be called from a mail rule... 
                   
                  in this ver a file dropped onto the icon will take the filepath, insert it into an fmp field, the run an fmp script to cleanup the file path so that fmp can use it, then calls an fmp script to import the actually file. 
                   
                  (*
                  To add a method to check that this applescript is permitted to run ARC.

                  *)


                  property fDebug : false

                  property AppName : "fmp app name"
                  property AppFile : "fmp filename.fp7"
                  property AppURL : "fmp7://xxx.xxx.xxx.xxx/"

                  my OpenFMPApp()

                  on open dropped_items
                  --my debug("open")
                  set Counter to 0 as number
                  set StartTime to current date
                  repeat with currentItem in dropped_items
                  set FileName to get POSIX path of currentItem
                  with timeout of (10 * 60) seconds
                  my ImportFile(FileName)
                  if result = true then set Counter to Counter + 1
                  end timeout
                  end repeat
                  if Counter > 0 then
                  my PostImport()
                  set EndTime to current date
                  set TimeTaken to EndTime - StartTime
                  set msg to (Counter as string) & " file"
                  if Counter > 1 then set msg to msg & "s"
                  set msg to msg & " imported in " & (TimeTaken as string) & " second"
                  if TimeTaken > 1 then set msg to msg & "s"
                  set msg to msg & "."
                  activate
                  display dialog msg with title "RMA Automation" buttons {"OK"} default button 1 with icon note giving up after 3
                  end if
                  end open

                  on OpenFMPApp()
                  set FileOpen to false
                  tell application "FileMaker Pro Advanced"
                  get getURL AppURL & AppFile
                  set FileOpen to document AppName exists
                  end tell
                  return FileOpen
                  end OpenFMPApp

                  on ImportFile(FileItem)
                  set fImported to false as boolean
                  my debug("ImportFile")
                  tell application "FileMaker Pro Advanced"
                  set FileOpen to my OpenFMPApp()
                  if FileOpen then
                  tell document AppName
                  set CurrentLayout to get name of current layout
                  tell table "main"
                  tell first record
                  my debug("first")
                  set cell "fDownloadZV11" to 0 as integer
                  set cell "TmpFilePathAndName" to FileItem
                  end tell
                  end tell
                  my debug("clean")
                  do script "Cleanup RMA Import File Reference"
                  my debug("process")
                  do script "Process RMA File  - for folder action"
                  set fImported to true
                  end tell
                  end if
                  end tell
                  return fImported
                  end ImportFile

                  on PostImport()
                  tell application "FileMaker Pro Advanced"
                  set FileOpen to my OpenFMPApp()
                  if FileOpen then
                  tell document AppName
                  set CurrLay to get name of current layout
                  if CurrLay is "RMA Requests" then
                  my debug("filter")
                  tell current table to show (every record whose cell "!Show" = "0")
                  my debug("sort")
                  tell current layout to sort by field "Imported" in order descending
                  end if
                  end tell
                  end if
                  end tell
                  end PostImport

                  on debug(strMsg)
                  if fDebug then display dialog strMsg
                  end debug

                  • 6. Re: Importing data on a regular basis
                    user14360
                       Hiatts - thanks for going to the trouble of putting your applescript there.  I've decided it's too much for me to take on learning applescript at the moment, and am going to wait and see if I can get someone to write one up for me where I work.  
                    • 7. Re: Importing data on a regular basis
                      HugoLidia
                         Don't forget, when importing the file, you'd need to verify that it is indeed a new file and not a duplicate, otherwise you'll duplicate your existing records!