13 Replies Latest reply on Jul 29, 2015 10:10 AM by beverly

    Applescript - FileMaker to Calendar

    themactech

      Hi everyone,

       

      I had a great time at Devcon this year. I look forward to the next one.

       

      If ANYONE would be kind enough to give me a hand you would be a huge hero to me.

       

      I've been working on what should be a simple Applescript to take data from a FM record and create a calendar event. But I've been struggling. I thought maybe it would be easier just to have FM send email to a remote computer and create a simple Mail Rule with Applescript to create the event.. but still... it alludes me. The one reason I'm considering using Mail was that there seemed to be a long pause while FM was running the Applescript and thought it might confuse the user. But any advice would be GREATLY appreciate.

       

      I have three simple text strings.. and this is what's being sent to the email

       

      "July 18, 2015 12:00:00 AM"

      "July 21, 2015 12:00:00 PM"

      "CL-Vacation"


      The first being the start date, end date and then a short description.. easy right? Well the Applescript is driving me crazy as I can't nail down the exact format. I can parts of it to work but then as a whole it just doesn't do it.


      Below is the Mail Rule Script I've created. Am I missing something... if I use the Test Strings.. the Rule works but of course I want to use my variables. THANKS IN ADVANCE.


      using terms from application "Mail"

        on perform mail action with messages msgs for rule theRule

        tell application "Mail"

       

        repeat with msg in msgs

        try

        set msgcontent to content of msg

        set msgid to message id of msg

        set starttime to paragraph 1 of msgcontent

        set endtime to paragraph 2 of msgcontent

        set mytitle to paragraph 3 of msgcontent

        set starttime to date starttime

        set endtime to date endtime

       

       

       

        end try

        end repeat

        end tell

        end perform mail action with messages

      end using terms from

       

       

      --TEST STRINGS

      --set starttime to "July 18, 2015 12:00:00 AM"

      --set endtime to "July 21, 2015 12:00:00 PM"

      --set mytitle to "CL-VACATION"

       

      -- Create Calendar event

      tell application "Calendar" to tell calendar "Work"

        make new event with properties {start date:starttime, end date:endtime, summary:mytitle, allday event:true}

      end tell

       



        • 1. Re: Applescript - FileMaker to Calendar
          beverly

          calendar events (in email) are pretty precise. take a look at references to iCalendar and/or .ics (https://en.wikipedia.org/wiki/ICalendar).

           

          On the other hand... modern email clients are typically smart enough to know things like phone number, URLs, dates, address, etc.

           

               CL-Vacation, July 18, 2015 12:00:00 AM - July 21, 2015 12:00:00 PM

           

          (for example, may yield a "link" that allows you to add the date(s) to your calendar right from your email client.

           

          On Jul 28, 2015, at 3:50 PM, themactech

          • 2. Re: Applescript - FileMaker to Calendar
            themactech

            Thanks for the reply Beverly.

             

            I should have mentioned that the concept for this came about when a supervisor was approving a leave request for the dates you saw and wanted to know if FM could automatically add the even to the shared calendar rather than him having to type it in all the time.  So by the act of approving the request, FM would generate the email that a "server" checking Mail on that specific email address and then run the rule / script when it sees an email like this come in. Hope that makes sense.

            • 3. Re: Applescript - FileMaker to Calendar
              electon

              Technically you should be able to script creating a new event in Calendar without the need of your mail client trick.

               

              Here's an example that creates a new event starting at current date, ending two hours later:

               

              tell application "Calendar"

                tell calendar "My Calendar"

                set start to current date

                make new event at end with properties {description:"Event Description",

                summary:"Event Name", location:"Event Location", start date:start,

                end date:start + 120 * minutes}

                end tell

              end tell

               

              You can set alarms and a lot of stuff, only not your own uuid. this will be generated when you create the event.

               

               

              Most probable is that it's failing when you try to set the dates.

               

              In applescript you can set your dates when they are as string this way:

               

              set start to date ( "28-07-2015 10:00:00" )

              Are you familiar with how to write ( quote escape ) calculated applescript in FM?

              This way you can get your data into the script.

              • 4. Re: Applescript - FileMaker to Calendar
                themactech

                Thanks Electon

                 

                Thanks for the reply!!

                 

                I have experimented with using AppleScript within FM and I did get it work (I think). But there was a long pause (spinning beachball) that I thought might confuse the users so that's why I turned to the Mail trick.

                 

                Clay

                • 5. Re: Applescript - FileMaker to Calendar
                  electon

                  The something might be wrong with your script.

                  I don't get the beach balls here. I'll post an example just need to adjust some things.

                  • 6. Re: Applescript - FileMaker to Calendar
                    themactech

                    I appreciate it. Thank SO much.

                     

                    Clay

                    • 7. Re: Applescript - FileMaker to Calendar
                      Benjamin Fehr

                      I can't find the relevant thread at the moment. This one describes the import procedure:

                      Re: How can I import a Google .ICS calendar file into FileMaker?

                       

                      Anyway, what I do is to create a .ICS-File and open it. This will prompt iCal on Mac or Outlook Calendar on Windows to import the datas. The thread above gives you the structure about .ICS-Files.

                       

                      The ICS-content is formula-based text which I write to temporary_path by TROI File PlugIn.

                      I also have a routine to use the ICS-File to attache to emailed invitations.

                       

                      The "UUID" in ICS is a unique ID-N°. Though a ICS-record with same UUID will edit previous record in iCal/Outlook.

                      An ICS-File can contain 1 or many records.

                      Unfortunately, the Calendar-Category (Privat, Work, etc.) is not part of the ICS-Standard. You get prompted to assign your records to the corresponding category when ICS-File gets imported into Calendar.

                      • 8. Re: Applescript - FileMaker to Calendar
                        electon

                        Here's an example file. You need to set the global field on the layout to the name of the target calendar.

                        You may need to refresh the calendar once the script is run. I'm trying to work out why it not always shows right away.

                        Maybe it's only on my machine.

                        • 9. Re: Applescript - FileMaker to Calendar
                          themactech

                          Hey thanks so much. I will experiment with this method!!

                          • 10. Re: Applescript - FileMaker to Calendar
                            themactech

                            Hi Electon

                             

                            AWESOME. It works for me but I had to tweak it a little. For some reason the date format was making Calendar gripe so I added a few more lines to the script.


                            Set Variable [ $day; Value:Day ( Calendar::DateStart ) ]

                            Set Variable [ $month; Value:Month ( Calendar::DateStart ) ]
                            Set Variable [ $start; Value:Quote ($month & "/" & $day & "/2015 " & Calendar::TimeStart) ] Set Variable [ $day; Value:Day ( Calendar::DateEnd ) ]
                            Set Variable [ $month; Value:Month ( Calendar::DateEnd ) ]
                            Set Variable [ $end; Value:Quote($month & "/" & $day & "/2015 " & Calendar::TimeEnd) ]


                            Setting a local calendar like Work or Home it works fast. (a little bit of the refresh issue). But I realized that when I set it to our company calendar (caldav) FM decides to spin for 20 secs. That part is still a mystery to me.


                            THANKS AGAIN!!

                             

                            • 11. Re: Applescript - FileMaker to Calendar
                              electon

                              The fact that dates are giving an error might be due to system format / region settings.

                              I've never had to test it extensively for all possible cases.

                               

                              The refresh issues are annoying, same with CalDAV. I've read some people are complaining too.

                               

                              CalDav:     could it be that AS waits for the whole sync process?

                              When creating events manually the sync might be just happening in background.

                               

                              Refresh issues, I don't know.

                              With iCloud calendars they immediately show on mobile devices, desktop seems to lag behind. Sometimes the only show after quitting and reopening the Calendar app, enabling disabling the calendar or searching for that event.

                              Possibly with all the sandboxing apple is doing it becomes an issue.

                               

                              Anyway, the example was just to prove the way to do it directly in apple script without workarounds.

                               

                              I think what efficientbizz is doing with the .ICS files might be a better route.

                              This way you could also send calendar events via mail for people to put them in their own agenda.

                              • 12. Re: Applescript - FileMaker to Calendar
                                Benjamin Fehr

                                I've found one*) of my source of inspiration

                                HomeBase Software | Search Results

                                 

                                Similar technique was used by FMI for FM DevCon App for scheduled sessions listed

                                 

                                Home Base Solution is based on a script which fills-up a Vars, inserts Vars in a field for export.

                                There are others that use Virtual-List technique (as I do) or TROI File PlugIn for export

                                • 13. Re: Applescript - FileMaker to Calendar
                                  beverly

                                  Yes! the format of the event(s) is important. See the link I posted or research iCalendar for proper formats.

                                  The export with Virtual-list (or plug-in) ensures the proper line-endings. Applescript may be able to assist with the export as well and maintain the line-endings.

                                   

                                  On Jul 29, 2015, at 1:01 PM, efficientbizz