Send a Calendar Entry from Ninox
A while ago, a client asked me whether it was possible to send a calendar entry from their bookings table to create calendar entries for the members (external) booking onto an event. This resulted in a Blog post called Sending a Calendar Entry from Airtable with Integromat. The same issue came up with a client earlier today, but with Ninox as the database. My first reaction was to recommend the same Integromat approach - and then it occurred to me that this would be able to be done entirely within Ninox - hence this post which I hope will be useful...
The Ninox Database
I've added an Events and a Bookings table to my Test database for the purposes of this post:
If you're intending to follow this article, then you should set up tables with these or similar fields. The Confirm Booking button is where the code to send the confirmation and .ics file will be.
iCalendar (.ics) Files
.iCal files are just text files with an .ics extension - and the contents are fairly simple. There is a great deal of information available on the web about the format but none is terribly clear for the non-techie and it isn't easy to find out what the minimum content would be. I decided to experiment a little and have come up with the following content for this scenario:
BEGIN:VCALENDAR
VERSION:2.0
CALSCALE:GREGORIAN
BEGIN:VEVENT
UID:event-4.ics
SUMMARY:Ninox Training
DTSTART;TZID=Europe/London:20190810T120000
DTEND;TZID=Europe/London:20190810T173000
LOCATION:Faversham
DESCRIPTION:This is a course to introduce you to Ninox
STATUS:CONFIRMED
END:VEVENT
END:VCALENDAR
The values marked in green are where variable information is included from the database. Note in particular the required format for the Date/Time values.
The UID value is a unique identifier for the calendar entry (which is calculated in this case as "event-" followed by the ID of the booking record and .ics on the end) and is the same as the file name of the file we are going to create/send. Including a UID (which is optional) means that if you send an update to the calendar entry it will edit the existing entry rather than creating a new one (as long as you use the same UID).
Creating the iCal file and Sending the Email
Ninox's incredible language fortunately gives us an option to create a text file from a 'string' created within the code (a 'string' is just some text). This means that we just have to work out how to insert relevant variable information (the green text in the example above) into the overall text. In Ninox this is pretty simple to do:
"Begin by creating the .ics file using createTextFile...";
"--------------------------------------------------------";
createTextFile(this, "BEGIN:VCALENDAR
VERSION:2.0
CALSCALE:GREGORIAN
BEGIN:VEVENT
UID:event-" + Id + ".ics
SUMMARY:" + Events.'Event Name' + "
DTSTART;TZID=Europe/London:" + format(Events.'Start Date/Time', "YYYYMMDDTHHmmss") + "
DTEND;TZID=Europe/London:" + format(Events.'End Date/Time', "YYYYMMDDTHHmmss") + "
LOCATION:" + Events.Location + "
DESCRIPTION:" + Events.Description + "
STATUS:CONFIRMED
END:VEVENT
END:VCALENDAR", "event-" + Id + ".ics");
Apart from the annotations at the beginning this is really just a single function - all I did was take the sample text earlier in the article and paste it into the Ninox code window for the 'On Click' event of the button and then edit it by adding the createTextFile(this, ...... ,"event-" + ".ics"); and replaced the variable information with relevant field references from the database. The latter can be a little tricky in terms of getting all the ""'s etc in the right place!
So, what this does is create a file attached to the current record ('this') with the correct .ics file content. The next step is then simply to send this new file to the person booked onto the event. However, I discovered that if I did that as the next code step, the file hadn't yet been saved and so it failed. The solution to this was simply to ask the user to confirm that the email should be sent with a dialog box:
"Ask the user to confirm sending of confirmation";
"(this allows time for the file to be created)";
"-----------------------------------------------";
let result := dialog("Send Booking", "Would you like to send this calendar entry?", ["Yes", "No"]);
if result = "Yes" then
And then, if the result is Yes then send a suitable email:
let filetosend := file(this, "event-" + Id + ".ics");
let emailText := "Dear " + Contacts.Name + ",<br><br>This is a confirmation of your booking for:<br><br>Event: <b>" + Events.'Event Name' + "</b><br>Starting: <b>" + Events.'Start Date/Time' + "</b><br>Ending: <b>" + Events.'End Date/Time' + "</b><br><br>" + Events.Description + "<br><br><br>";
sendEmail({
from: "julian@kirkness.com",
to: Contacts.Email,
subject: "Event Booking",
text: text(emailText),
html: emailText,
attachments: filetosend
})
end;
Note here that I have built the email using HTML to control the layout and that this can be as comprehensive as you like. In this relatively simple layout, the attendee should receive a message like this:
And the content of the .ics file would be:
BEGIN:VCALENDAR
VERSION:2.0
CALSCALE:GREGORIAN
BEGIN:VEVENT
UID:event-1.ics
SUMMARY:Ninox 1
DTSTART;TZID=Europe/London:20200104T141000
DTEND;TZID=Europe/London:20200104T212000
LOCATION:London Office
DESCRIPTION:This is a seminar about Ninox’s first seminar sessions
STATUS:CONFIRMED
END:VEVENT
END:VCALENDAR
(Note that you can open one of these files in TextEdit (Mac) or similar text editor)
You can find out more information about Ninox here
And the following articles about .ics files may be useful:
https://en.wikipedia.org/wiki/ICalendar