Jump to content
RichGags

Can FM create an ICS file to be attached to an email?

Recommended Posts

RichGags

I want to create an .ics file to be attached to an email so when I email my clients an appointment confirmation, they can run that file to add it to their calendar. I can create the contents of the file with a calc field ...

 

BEGIN:VCALENDAR

VERSION:1.0

BEGIN:VEVENT

DTSTART:20110125T090000

DTEND:20110125T100000

SUMMARY:Test

LOCATION:New York

DESCRIPTION:This is a test.

PRIORITY:3

END:VEVENT

END:VCALENDAR

 

How can I save or export this as appointment.ics so I can attach it?

 

Thanks,

Rich

Share this post


Link to post
Share on other sites
AHunter3

If you were to export field contents, the resulting text file would be UTF-16 which may not work properly. ikf you were to isolate a single record and export records as text (this one field ONLY), the apparent hard returns would be exported as the vertical tab character that FileMaker uses for hard returns within fields and that probably won't work either.

 

I see you as having two main options:

 

a) create a table that has no other purpose other than generating this file; you need to create a new record for each line of the .ics file, setting a text field to the value of each line one line at a time, and then export that set of records as tab delimited text (just that one field); or

 

b) export one record containing the entire .ics file's info in a single field using a MacOS computer to a specified filepath and then use a calculated AppleScript to tell TextWrangler to open that file and replace the vertical tab character with a true hard return character, replacing all, then saving results, then closing file.

Share this post


Link to post
Share on other sites
RichGags

I found this on Anvil's blog.

 

Script:

 

Set Field [Event::g_Data ; "BEGIN:VCALENDAR¶VERSION:2.0 ¶PRODID:-//Microsoft Corporation//Outlook 12.0 MIMEDIR//EN¶BEGIN:VEVENT¶UID:¶DTSTAMP:" & Year(Event::start) & Month(Event::start) & Day(Event::start) & ¶ &"DTSTART:" & Year(Event::start) & Month(Event::start) & Day(Event::start) & ¶ & "DTEND:" & Year(Event::end) & Month(Event::end) & Day(Event::end) & ¶ & "SUMMARY:" & Event::Title & "¶END:VEVENT ¶END:VCALENDAR"]

Set Variable [$path ; Get(TemporaryPath) & “event.ics”]

Export Field Contents [Event::g_Data ; “$path” ; Automatically Open]

 

Are you saying these returns wont work? Could this be the reason the ics file I am creating is not importing into the calendar? It appears like it tries to open however it is not working.

Share this post


Link to post
Share on other sites
AHunter3

That's what I'm saying. Or guessing and you seem to be confirming my guess. Those returns, when exported, will not be returns, they will be vertical tabs.

 

I'd not be at all astonished to find that calendar apps can't make heads nor tails of them.

 

Either of my alternative recommended approaches should work for generating an .ics file that has real returns in it.

Share this post


Link to post
Share on other sites
Josh Ormond

I had a working sample at one point...I'll dig around and see if I can find it. If not, I try to throw together a sample. I think I started with Anvil's code as well.

Share this post


Link to post
Share on other sites
Jack Rodgers

Troi's File plugin will create a text file and you can add the ics ending.

Share this post


Link to post
Share on other sites
Josh Ormond

Jack - You don't need a plugin.

 

RichGags - the code you show above should work. A couple things to keep in mind:

1. You need to put it into a field. The ¶'s will translate into line breaks. Then you export the field contents.

2. On the more obvious side, you will need to make adjustments to match your solution. Test it. At least on windows when I open the file it opens in my default desktop calendar program (which for me is Outlook). Then I can just save it.

 

There are just a few rules that you need to adhere to, which you found linked to in that Anvil blog article. When you use the script step, "Export Field Contents", you just need to make sure that the file name (included in the file path in Anvil's example) ends with .ics.

Share this post


Link to post
Share on other sites
AHunter3

OK cool... I thought there was a good chance that the calendar program would not like the (default format) UTF-16 of FileMaker's export field contents textfiles.

 

Sounds like it's not an issue.

Share this post


Link to post
Share on other sites
Josh Ormond
OK cool... I thought there was a good chance that the calendar program would not like the (default format) UTF-16 of FileMaker's export field contents textfiles.

 

Sounds like it's not an issue.

As long as it has the basic data in the correct form, the encoding matters little. (Although it will have some effect under certain encodings that the opposing OS may not recognize...but UTF-8 or -16 should be fine across all OS's)

Share this post


Link to post
Share on other sites
RichGags

I have tried this. Here's what my ics file contains when I export it (when opened with TextEdit)...

 

BEGIN:VCALENDAR
VERSION:2.0
METHOD:PUBLISH
BEGIN:VEVENT
ORGANIZER:MAILTO:support@me.com
DTSTART:20110201T190000Z
DTEND:20110201T210000Z
LOCATION:181 My Address
UID:20110125T132238Z@me.com
DTSTAMP:20110125T132238Z
SUMMARY:Your appointment
DESCRIPTION:Rich Gags
PRIORITY:5
CLASS:PUBLIC
END:VEVENT
END:VCALENDAR

 

When I double click the ics file, I get the message...

 

iCal can not read this calendar file. No events have been added...

 

Any ideas? Thanks.

Share this post


Link to post
Share on other sites
RichGags

Update....

 

I tried to open this .ics file with BBEdit. Without modifying it at all, I saved it as a new file but at save time, specified "as Stationary", "line breaks - Unix (LF)", "unicode (UTF-8, no BOM)".

 

It worked perfectly and imported into the calendar. Now what should I do?

Share this post


Link to post
Share on other sites
AHunter3

You can AppleScript BBEdit to do that, from within FileMaker. It's even AppleScript-recordable (most apps are not but BBEdit is.)

Share this post


Link to post
Share on other sites
RichGags

Ok. Thanks... But how do I do it on the other machines on my network? Currently only 2 of the 10 workstations are Macs.

Share this post


Link to post
Share on other sites
Josh Ormond

Did you test it on a Windows machine? When I tested it with Outlook, it worked perfectly without any changes.

Share this post


Link to post
Share on other sites
RichGags

Ok. Almost there. This works...

 

 

tell application "BBEdit"

activate

make new text document

open {file "Macintosh HD:Users:richgags:Desktop:Testappointment.ics"} with LF translation

set properties of text document 1 to {line breaks:Unix, encoding:"Unicode (UTF-8, no BOM)"}

save text document 1 to file "Macintosh HD:Users:richgags:Desktop:Testappointment.ics" without saving as stationery

quit

end tell

 

The only problem is I need to be able to specify the path in the actionscript with a variable from the filemaker script. For example, I would like it to say...

 

 

 

tell application "BBEdit"

activate

make new text document

open {file $$Path } with LF translation

set properties of text document 1 to {line breaks:Unix, encoding:"Unicode (UTF-8, no BOM)"}

save text document 1 to file $$Path without saving as stationery

quit

end tell

 

... Where $$Path is set in the Filemaker script as ...

 

Set Variable [$$Path; Value:If (Get(SystemPlatform)=1;

"filemac:"& Get ( DesktopPath ) & $Client&"appointment.ics";

"filewin:/"& Get ( DesktopPath ) & $Client&"appointment.ics" )

 

However when I try to use that I get a "unknown token" error.

Share this post


Link to post
Share on other sites
AHunter3

You need to use a calculated applescript.

 

Let( Qmk = """";

 

"tell application " & Qmk & "BBEdit" & Qmk & "¶" &

"activate"

 

(etc)

 

...

"open " & $$Path & " with LF translation" & "¶" &

"set properties of text document 1 "..... (etc)

Share this post


Link to post
Share on other sites
RichGags

Thanks but it is not working for me. It opens BBEdit and starts a new untitled text document but does not open the file on my desktop. Here's what I have...

 

Let( Qmk = "\"";

"tell application " & Qmk & "BBEdit" & Qmk & "¶" &

"activate" & "¶" &

"make new text document" &"¶" &

"open " & $$Path & " with LF translation" & "¶" &

"end tell")

 

as per your instructions...

The four quotes get converted to the "\"".

 

I think the problem lies in the "open " & $$Path & " with LF translation" & "¶" &.

I also tried "open {file " & $$Path & "} with LF translation" & "¶" & as well as "open {file " & Qmk & $$Path & Qmk & "} with LF translation" & "¶" & however that did not work either.

Share this post


Link to post
Share on other sites
AHunter3

Copy the calculation code for "Perform AppleScript" and stick it into a Set Field and set a global text field to that result and then copy the calculated AppleScript out of the global. Look at it. Anything different from what you had (your post 15 above that you said "works")? Must be some difference!

Share this post


Link to post
Share on other sites
RichGags

Thank you. That did the trick. I was able to see the differences. First of all, BBEdit does not need "filemac:" so I had to get rid of that. Second, the first slash / in the Get (DesktopPath) needs to be removed and the rest of them converted to a colon : for the applescript.

 

Now I need to work on the DTSTART and the DTEND. The dates need to be in the format 20110201T190000. (ie YYYYMMDD Problem is Month (ApptDate) returns 2 instead of 02. Any way to force FM to use a 2 digit number for months less than 2 digits?

 

Thanks.

Share this post


Link to post
Share on other sites
RichGags

Update. Got the date part...

 

"DTSTART:"& Year(ActivityHistory::Date)& Right("00" & Month(ActivityHistory::Date);2) & Right("00" & Day(ActivityHistory::Date);2)

 

Now onto the time. Is there an easy way to convert regular time to military time? Thanks.

Share this post


Link to post
Share on other sites
AHunter3

Time will automatically be "military" time. The difference between 3:00 PM and 15:00 is simply a matter of display. if you assess either of those with Hour (TheTime) it will return 15.

Share this post


Link to post
Share on other sites
Jack Rodgers

Year( Backup Drives::Use As Backup Date ) &"." &

Right ( "00"& Month ( Backup Drives::Use As Backup Date ) ; 2 ) & "." &

Right ( "00" & Day (Backup Drives::Use As Backup Date ) ;2 ) & "." &

Right ( "00" & Hour(Get ( CurrentTime ) ) ; 2 ) & "." &

Right ( "00"& Minute ( Get ( CurrentTime ) ) ;2 ) &

".fp7"

 

This script creates 2011.01.05.04.03.fp7 which is appended to my file name to make a backup copy using Save a Copy.

 

Adjusted to your question:

 

Year( Backup Drives::Use As Backup Date ) &

Right ( "00"& Month ( Backup Drives::Use As Backup Date ) ; 2 ) &

Right ( "00" & Day (Backup Drives::Use As Backup Date ) ;2 ) &

Right ( "00" & Hour(Get ( CurrentTime ) ) ; 2 ) &

Right ( "00"& Minute ( Get ( CurrentTime ) ) ;2 )

Share this post


Link to post
Share on other sites
RichGags

Thank you. I thought I had the .ics file created. On Mac it works fine with the BBEdit Applescript trick.

 

@Josh.... If the script is run and the ics file is created on a Windows box, the ics file does not work when I try to open it on a Mac. It needs to be opened with Wordpad and saved as unicode text for it to work. Any ideas on what I can do for my windows users? I will not know which OS my customers have. I do not have Outlook to see if my ics file will open with it but if one of my Windows users sends the ics file to a mac customer, it wont work.

 

Is there such a vehicle like Applescript to open a file on a windows box and save it as Unicode?

Share this post


Link to post
Share on other sites
RichGags

Update... The file windows FM creates will open with outlook. I sent it to a friend who has outlook. It will not open in iCal.

Share this post


Link to post
Share on other sites
Josh Ormond

You can test for the platform. Use Get(SystemPlatform).

 

It returns:

 

• -1 if the current platform is PowerPC-based Macs

• 1 if the current platform is Intel-based Macs

• -2 if the platform is Windows XP or Windows Vista

 

http://www.filemaker.com/help/html/func_ref2.32.72.html

 

Then you can use an If() statement to decide which set of steps (or which script) to execute.

 

 

The most similar to AppleScript on Windows is Visual Basic (VB). I'll test a few things and see if I can find an easy way to do what you need. Send Event () is typically how you fire off OS level stuff on Windows. You can only see that step as an option if you are on a windows OS.

Share this post


Link to post
Share on other sites
Jack Rodgers

To eliminate needless and futile worrying do the following. It will either produce results and you can then emulate the files or where it fails will provide a clue.

 

The first place to start is to export an ical file from outlook and from iCal.

 

Try importing a into b and b into a just to make sure they work and just to be sure a into a and b into b.

 

If either can import the others file form you only need to export one file. If not, then you export two files and let the user choose which to use.

 

 

Assuming one or both work, open them in a text editor that does not format or style the text. Do not save the file.

 

Create an export that matches the text file. You can select and copy and paste portions into your script or into a field.

Share this post


Link to post
Share on other sites
Jack Rodgers

Note that if you are using someone else's suggested script (of of a blog or website), it might not work (as scripts posted here always do). This is common and has been true for more than 25 years and in all programs and platforms. Magazines that published scripts were a nightmare for accuracy and dependability.

 

This works, copy the text of a working ics file. Note that it varies with one or multiple records.

 

Now, select the we will put this into a field for safe keeping and replace certain portions with {IDNAME} with IDName being something descriptive.

 

Now when you want to send you just put the field into something you can use and substitute:

 

substitute(TemplateFieldText; ["{IDNAME}";yourfield];["{EMAIL}";youremailaddress]....and so on)

 

I used this idea to copy the HTML of my web pages and save it to a field and then when I wanted to update those pages to new data, I would run it through this substitution and thjen save it as filename.html and replace it on my site.

Share this post


Link to post
Share on other sites
Josh Ormond

Got it figured out...and this is an 11 on the coolness meter. No plug-in, no AppleScript, no VB... It uses the Virtual List technique that Bruce Robertson has been using for some really powerful stuff.

 

  1. Create a table in your file called VirtualList
  2. In the VirtualList table, create a serial number field...

    FieldName: Serial

    Type: Number

    Options: Auto-enter Serial (starting with 1)


  3. Also create a calculation field...

    FieldName: VirtualList

    Type: Calculation (text result)

    Options: Unstored, =GetValue ($$VirtualList; Serial)


  4. Create more records than you will ever use in the VirtualList table. If you are doing just single events, you don't really need more than a 100 or so. Doesn't matter, you can add more later.
  5. Create a script to capture the return delimited list of text that has the data for the event. In this case you have everything stored in a single text field.
    Set Variable [$$VirtualList; EventDetailsFieldName]
    Set Variable [$VLCount; ValueCount ( $$VirtualList )]
    Go to Layout ["VirtualList" (VirtualList)]
    Enter Find Mode
    Set Field [VirtualList::Serial; "≤ " & $VLCount]
    Perform Find
    Export Records [No dialog; "FileName.ics"; Unicode (UTF-8)] //Only export the VirtualList field and set encoding to UTF-8.
    Set Variable [$$VirtualList; ""]
    Go to Layout [original layout]

 

That should give you an ics that works for any calendar program that reads ics (including Outlook, iCal, Google Calendar, etc).

Share this post


Link to post
Share on other sites
Josh Ormond
Note that if you are using someone else's suggested script (of of a blog or website), it might not work (as scripts posted here always do). This is common and has been true for more than 25 years and in all programs and platforms. Magazines that published scripts were a nightmare for accuracy and dependability.

 

This works, copy the text of a working ics file. Note that it varies with one or multiple records.

 

Now, select the we will put this into a field for safe keeping and replace certain portions with {IDNAME} with IDName being something descriptive.

 

Now when you want to send you just put the field into something you can use and substitute:

 

substitute(TemplateFieldText; ["{IDNAME}";yourfield];["{EMAIL}";youremailaddress]....and so on)

 

I used this idea to copy the HTML of my web pages and save it to a field and then when I wanted to update those pages to new data, I would run it through this substitution and thjen save it as filename.html and replace it on my site.

 

Jack,

The issue here is strictly the encoding that FileMaker uses when you use the Export Field Contents. It encodes it as UTF-16, according to FileMaker.com...although I am actually seeing something different, but that could be a Windows thing.

 

iCal can read UTF-8 but not UTF-16. Microsoft Outlook can actually read both, along with a few others.

Share this post


Link to post
Share on other sites
Jack Rodgers

http://en.wikipedia.org/wiki/ICalendar

 

There is much more to the ical format than Shakespeare told Hamlet...

 

Readup on it here, as a beginning. Looks kinda interesting.

 

Google calendar also works with this so I might investigate it further. After I finish automating a download of my daily backup on WorldCloud.

 

The possibilities of transmitting various data looks worthy of its own table to hold all of those bits of information.

 

That script is interesting...

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use