Dyce & Sons Ltd.

Helping IT since 1993

FileMaker 19 Fun

Thursday 21st May, 2020

If you’re looking for some cheap thrills with FileMaker 19’s new Javascript integrations, might I interest you in this…

Use Case: FileMaker has a great portal feature that lets you see child records in a scrolling list. Sometimes it sucks.

English translation: Suppose you have an invoice, and you want to show the list of items on the invoice - in FileMaker you could (should!) use two tables; one to store the details for the invoice itself (customer, date, taxes, total, etc.); one to store the details for items on invoices (item code, name, quantity, unit cost, subtotal, tax, etc. ). You need a table for each type of thing you’re modelling - an invoice, and an invoice item.

An example of a multi-source, multi column portal
An example of a multi-source, multi column portal

But there are times when you want to show a list of more than one type of thing. Suppose you need to display a diary, but the diary consists of meetings, reminders, national holidays, staff holidays, renewal notices etc. You get the idea. FileMaker doesn’t have the idea of inheritance per se, so you end up having to build a table that models all of those things at once. It’s always a compromise, and there’s lots of extra ‘optional’ stuff in your table that requires extra logic to handle.

A better approach would be to keep a separate table for each type of thing you’re modelling, and instead find some way to pull out just the shared bits you need.

Let’s try and model that diary. Here’s the sort of things we need to store about each of our different types:

Meaning Meeting Task Reminder National Holiday Staff Holiday Renewal Notice
id ID ID ID ID ID ID
date being Meeting Date Date Due Date Due Holiday Date From Due Date
date end Until
from From
until Until Staff ID
staff ids Attendees Staff ID Staff ID
title Title Detail Title
completed Done Done
specific #1 Job ID Is Staff Holiday Customer ID
specific #2 Detail Renewal Type
specific #3
Key
Column Headings
FileMaker table names
Row Headings
Field intention
Grid
Actual FileMaker field names

Hopefully, you can see that this is not a massively contrived example. Each of these things should appear on the calendar, for a specific individual say, but each of them is a very different kettle of fish, except for a very few common details.

Let’s use three different technologies that FileMaker 19 supports, two of which are brand new, to make this happen. Before we start, to make the exercise slightly simpler, let’s focus on the basic task: we just want to display a single day’s calendar for a particular member of staff. (Once we’ve done this, replicating it for a week, or for all staff members is pretty straightforward, as hopefully you’ll see.)

We begin by identifying the common details.

Each type has some notion of date, usually called ‘Date’ as well - we’ll need to figure out how to handle the Staff Holiday exception, which uses ‘From’ and ‘Until’ to specify a span of Dates.

Most types also have a Staff ID that determines who the item is for, unless it’s a generic item (National Holidays), in which case it applies to everyone.

The meeting type alone has From and Until details. The other types don’t have this explicitly, but we can decide that if there’s no From or Until then they are “00:00”, i.e. they appear at the start of the day.

All types have some sort of ID, Name, Title, or Detail element, which we will also need.

Let’s agree then that we need 5 things from each type of item; ID, Date, From, Until, and Title, and that we will figure out what items to show based on two things: the Date, and if appropriate the Staff ID.

OK, promise you won’t stop reading?

There are some words which some FileMaker users shy away from, and perhaps the worst example is the dreaded “SQL”. This is a shame, because it’s not difficult to master the basics, and it makes some tasks in FileMaker either easier, or in fact possible! So, read to the end of the next section, and then decide if SQL is as scary as some people make out.

For some time, FileMaker has supported the ExecuteSQL function: it lets you retrieve data from FileMaker tables using SQL query statements. So, the first technology we’ll use is SQL. An SQL query statement looks like this:

SELECT fieldname FROM tablename WHERE otherfieldname = matchingvalue

The SELECT keyword is another way of saying “find”, or “fetch”, or “get”, and the rest of the query statement translates as:

Look in the tablename table in FileMaker, and get all the values in the fieldname field whenever the otherfieldname value is equal to matchingvalue

A concrete example: Suppose we have a FileMaker table called staff that has three fields: forename, surname, and email. Our query might be:

SELECT forename, surname FROM staff WHERE email = 'dd@example.com'

Here then we’ve asked for the forename, surname - two columns - for any record whose email is ‘dd@example.com’. If there are matches, then FileMaker returns them, and if not then the result of the query is just an empty string.

So far, so simple. The reason we need to use SQL is that we want to ask a similar question from several different tables, and SQL lets us do one important thing: stick the results of different queries together. The queries have to result in the same number of columns, and the columns have to have the same names, but, and this is the important but, SQL lets you rename the columns on the fly, and lets you fill in the blanks where a particular table doesn’t have a specific column.

What does this look like?

Suppose that our meetings table has the following columns (or fields):

ID
Meeting Date
From
Until
Attendees
Title

Our simplest query might be like this:

SELECT "ID", "Meeting Date", "From", "Until", "Title" FROM "meetings" WHERE Attendees LIKE "%rd%"

There are a few subtle additions to that query versus the last one. First there are lots of double quotes. SQL doesn’t like spaces in field names, and it objects to field names that are like SQL syntax, e.g., ‘From’. Unless we’re prepared to change our FileMaker fieldnames to be more suitable for SQL queries, it’s best to consider wrapping all field and table names in double quotes as just good manners. Next, the test we’re using is LIKE, and the value we’re testing against has some strange percentage symbols in there. LIKE lets us perform a fuzzy search, because, well in this case our attendees field will contain a whole list of different staff ids. We just care if one of them is ‘rd’. The percentage signs are just saying “anything” as in anything then "rd" then anything And yes, simple values should be wrapped in quotes to.

In FileMaker the ExecuteSQL function actually takes whole selection of parameters:

ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )

In fact, our function above would look like this:

ExecuteSQL ( "SELECT \"ID\", \"Meeting Date\", \"From\", \"Until\", \"Title\" FROM \"meetings\" WHERE \"Attendees\" LIKE ?" ; "" ; "" ; "%rd%" )

Our query is obviously this bit:

"SELECT \"ID\", \"Meeting Date\", \"From\", \"Until\", \"Title\" FROM \"meetings\" WHERE \"Attendees\" LIKE ?"

and the lunatic looking \" throughout are because that’s how you have to write a quote mark inside another quote mark. The two:

"" ; "" ;

are the fieldSeparator and rowSeparator. If you leave them empty, then the defaults are used - a simple comma for between the fields, and a return for the end of a row.

The question mark is a placeholder. It says, in this query when you see a ? replace it with the appropriate parameter. We only have one question mark, so we need only one parameter here. When we have more question marks, we’ll need more parameters to match.

Let’s add that second parameter. As well as checking the attendees we also need to pick the right date. Let’s try to use some variables now, instead of hard-coded parameters. Our queries are going to start to get a bit longer, so let’s space them out a little. That would look like this:

ExecuteSQL (
  "SELECT
    \"ID\",
    \"Meeting Date\",
    \"From\",
    \"Until\",
    \"Title\"
  FROM \"meetings\"
  WHERE \"Meeting Date\" = ? AND \"Attendees\" LIKE ?" ;
  
  "" ; "" ;
  
  $date ;
  "%"&$staffID&"%"
)

Hopefully you can see that by adding WHERE \"Meeting Date\" = ? AND \"Attendees\" LIKE ? we’re using two parameters, which we’ll need to supply, and that the order of them needs to be $date ; "%"&$staffID&"%" in order to match the placeholder question marks. The $date is just a FileMaker variable containing the date we’re looking for, and staffID is human-readable tag of the staff member.

Assuming that we’ve set the variable $date to “2/6/2020”, and $staffID to “rd”, this gives something like this, if we were to evaluate our function, we might get something back like this:

07BD4B2E-10B6-4B18-AFB4-72AF31EB90AB,2020-06-02,09:30:00,11:00:00,Allocations
B0ABC1F6-D212-481F-B4F5-57F145BAA63A,2020-06-02,10:00:00,11:00:00,Update meeting
94BC1CB1-2CA7-4563-B310-9BE610316B6A,2020-06-02,09:45:00,10:00:00,Scrum
A16A8722-F968-499C-86DC-38D005D02AA1,2020-06-02,14:00:00,15:00:00,Sprint 25 Retrospective

To paraphrase Eric Morecombe “that’s all the right meetings, not necessarily in the right order”. Sorting is easy to fix in SQL:

ExecuteSQL ( "SELECT \"ID\", \"Meeting Date\", \"From\", \"Until\", \"Title\" FROM \"meetings\" WHERE \"Meeting Date\" = ? AND \"Attendees\" LIKE ? ORDER BY \"Meeting Date\", \"From\", \"Until\" " ; "" ; "" ; $date ; "%"&$staffID&"%" )

Adding the ORDER BY clause makes all the difference:

07BD4B2E-10B6-4B18-AFB4-72AF31EB90AB,2020-06-02,09:30:00,11:00:00,Allocations
94BC1CB1-2CA7-4563-B310-9BE610316B6A,2020-06-02,09:45:00,10:00:00,Scrum
B0ABC1F6-D212-481F-B4F5-57F145BAA63A,2020-06-02,10:00:00,11:00:00,Update meeting
A16A8722-F968-499C-86DC-38D005D02AA1,2020-06-02,14:00:00,15:00:00,Sprint 25 Retrospective

Now let’s look at doing the same thing for ‘Tasks’ table. It has the following columns (or fields):

ID      
Date Due
Staff ID
Job ID  
Detail  
Done

Our query might look something like this:

ExecuteSQL (
  "SELECT
    \"ID\",
    \"Date Due\",
    \"Detail\"
  FROM \"tasks\"
  WHERE \"Date Due\" = ? AND \"Staff ID\" = ?
  ORDER BY \"Date Due\" " ; 
  
  "" ; "" ; 
  
  $date ; 
  $staffID
)

which, again assuming that $date and $staffID are set as before, results in something like this:

F3749F53-E27F-433A-ABE7-BEAD333B6EBA,2020-06-02,Prepare quote for Jim
69AABBF8-0A3E-4AE7-9B12-5E5432FD6AAE,2020-06-02,Check signoff
07610DCF-A5D8-42CF-88B7-FBD46B59DA90,2020-06-02,Call marketing

There are couple of things wrong with this as far as our ultimate solution is concerned.

Firstly, the number of columns doesn’t match - what happened to From and Until? Secondly, the column names, which we don’t see in the result, but will matter to SQL shortly, are different. And thirdly, and less seriously, we don’t really need to sort these by date. We’ll leave the date sort in for now, but we do need to fix the names and add those “00:00:00” dummy values I mentioned earlier.

SQL lets you ‘alias’ columns using the AS keyword. We can rewrite our two queries like this:

ExecuteSQL (
  "SELECT
    \"ID\",
    \"Meeting Date\" AS due,
    \"From\" AS begins,
    \"Until\" AS ends,
    \"Title\" AS title
  FROM \"meetings\"
  WHERE \"Meeting Date\" = ? AND \"Attendees\" LIKE ?
  
  ORDER BY due, begins, ends " ;
  
  "" ; "" ;
  
  $date ;
  "%"&$staffID&"%"
)

which gives exactly the same result as before, and

ExecuteSQL (
  "SELECT
    \"ID\",
    \"Date Due\" AS due,
    \"00:00:00\" AS begins,
    \"00:00:00\" AS ends,
    \"Detail\" AS title FROM \"tasks\"
  WHERE \"Date Due\" = ? AND \"Staff ID\" = ?
  
  ORDER BY due " ;
  
  "" ; "" ;
  
  $date ; 
  $staffID
)

which adds the two ‘fake’ columns, with that result now looking like:

F3749F53-E27F-433A-ABE7-BEAD333B6EBA,2020-06-02,00:00:00,00:00:00,Prepare quote for Jim
69AABBF8-0A3E-4AE7-9B12-5E5432FD6AAE,2020-06-02,00:00:00,00:00:00,Check signoff
07610DCF-A5D8-42CF-88B7-FBD46B59DA90,2020-06-02,00:00:00,00:00:00,Call marketing

Two things to note: (a) I’ve chosen aliases here that don’t need to be put in quotes (due, begins, and ends) and (b) I can use the aliases instead of the original column names when it comes to specifying the sort order.

It is at this point; we get to see why we’ve been using SQL. SQL has one more useful trick up its sleeve, as far as we’re concerned, the UNION keyword. We can use it to add our two sets of data together and treat them as a single list of items. It works by tying the two SELECT statements together, and then sorting at the end:

ExecuteSQL (
  "SELECT \"ID\",
    \"Meeting Date\" AS due,
    \"From\" AS begins,
    \"Until\" AS ends,
    \"Title\" AS title
  FROM \"meetings\"
  WHERE \"Meeting Date\" = ? AND \"Attendees\" LIKE ? 

  UNION
  
  SELECT
    \"ID\",
    \"Date Due\" AS due,
    CAST('00:00:00' AS TIME) AS begins,
    CAST('00:00:00' AS TIME) AS ends,
    \"Detail\" AS title FROM \"tasks\"
  WHERE \"Date Due\" = ? AND \"Staff ID\" = ?
  
  ORDER BY due, begins, ends " ; 
  
  "" ; "" ; 
  
  $date ; 
  "%"&$staffID&"%"; 
  $date ; 
  $staffID
)

Did you notice the slight wrinkle in there?

CAST('00:00:00' AS TIME) AS begins, CAST('00:00:00' AS TIME) AS ends

To you or me, 00:00:00 in the previous results look exactly like a time. SQL however knows they are a string and tells you that you can put strings and times into the same column in your results. The CAST() function turns our string into something that SQL knows is a time and is now happy to have share with the values in meetings::From and meetings::Until retrieved from the FileMaker meetings table.

The result might then look something like this:

07610DCF-A5D8-42CF-88B7-FBD46B59DA90,2020-06-02,00:00:00,00:00:00,Call marketing
69AABBF8-0A3E-4AE7-9B12-5E5432FD6AAE,2020-06-02,00:00:00,00:00:00,Check signoff
F3749F53-E27F-433A-ABE7-BEAD333B6EBA,2020-06-02,00:00:00,00:00:00,Prepare quote for Jim
07BD4B2E-10B6-4B18-AFB4-72AF31EB90AB,2020-06-02,09:30:00,11:00:00,Allocations
94BC1CB1-2CA7-4563-B310-9BE610316B6A,2020-06-02,09:45:00,10:00:00,Scrum
B0ABC1F6-D212-481F-B4F5-57F145BAA63A,2020-06-02,10:00:00,11:00:00,Update meeting
A16A8722-F968-499C-86DC-38D005D02AA1,2020-06-02,14:00:00,15:00:00,Sprint 25 Retrospective

We’ve now reached the point where you might think that some smart Alek says, “here’s one I made earlier”, i.e. we need to repeat the process for the other tables in our FileMaker solution.

However, there’s almost always a gotcha. Remember, our aim is to produce a diary displayed in FileMaker using version 19’s new Javascript features. Specifically, we want to be able to click on an item in the diary and be taken to the corresponding entry. Pretend we’ve got everything working. How will our web viewer know which entry to take us to? We have an id certainly - but we also need to know which table it belongs to. We therefore have to add one more ‘synthetic’ column to our results, source, which will contain the name of the table that the item came from. We can add it easily, in a similar fashion to the fake “00:00:00” times:

ExecuteSQL (
  "SELECT
    'meetings' as source,
    \"ID\",
    \"Meeting Date\" AS due,
    \"From\" AS begins,
    \"Until\" AS ends,
    \"Title\" AS title
    
  FROM \"meetings\"
  WHERE \"Meeting Date\" = ? AND \"Attendees\" LIKE ? 

  UNION

  SELECT
    'tasks' as source,
    \"ID\",
    \"Date Due\" AS due,
    CAST('00:00:00' AS TIME) AS begins,
    CAST('00:00:00' AS TIME) AS ends,
    \"Detail\" AS title
    
  FROM \"tasks\"
  WHERE \"Date Due\" = ? AND \"Staff ID\" = ?
  
  ORDER BY due, begins, ends " ;
  
  "" ; "" ;
  
  $date ;
  "%"&$staffID&"%" ;
  $date ;
  $staffID
)

The results might look like this:

tasks,07610DCF-A5D8-42CF-88B7-FBD46B59DA90,2020-06-02,00:00:00,00:00:00,Call marketing
tasks,69AABBF8-0A3E-4AE7-9B12-5E5432FD6AAE,2020-06-02,00:00:00,00:00:00,Check signoff
tasks,F3749F53-E27F-433A-ABE7-BEAD333B6EBA,2020-06-02,00:00:00,00:00:00,Prepare quote for Jim
meetings,07BD4B2E-10B6-4B18-AFB4-72AF31EB90AB,2020-06-02,09:30:00,11:00:00,Allocations
meetings,94BC1CB1-2CA7-4563-B310-9BE610316B6A,2020-06-02,09:45:00,10:00:00,Scrum
meetings,B0ABC1F6-D212-481F-B4F5-57F145BAA63A,2020-06-02,10:00:00,11:00:00,Update meeting
meetings,A16A8722-F968-499C-86DC-38D005D02AA1,2020-06-02,14:00:00,15:00:00,Sprint 25 Retrospective

Another thing to watch: whenever you’re using the LIKE operator, you need to make sure that your test value is not empty. Any empty string is LIKE any value, so we just need to make sure that we don’t hand one in. Again, a quick and dirty fix is to change the parameter we pass just for the Staff ID in the meetings section:

"%" & If ( IsEmpty( $staffID ) ; "XXX" ; $staffID ) & "%"

We use “XXX” because we’re sure that won’t be used as a Staff tag.

With all the bits in place, now we can fast forward to the first stab at a complete calculation:

ExecuteSQL ( 
    "SELECT 
      'national_holidays' as source,  
      \"ID\", 
      \"Holiday Date\" AS due, 
      CAST('00:00:00' AS TIME) AS begins, 
      CAST('00:00:00' AS TIME) AS ends, 
      \"Title\" AS title 
    FROM \"national_holidays\"
    WHERE \"Holiday Date\" = ? 

    UNION

    SELECT 
      'renewals' as source,  
      \"ID\", 
      \"Date Due\" AS due, 
      CAST('00:00:00' AS TIME) AS begins, 
      CAST('00:00:00' AS TIME) AS ends, 
      \"Renewal Type\" || ' ' || \"Customer ID\" AS title 
    FROM \"renewals\" 
    WHERE \"Date Due\" = ? 

    UNION

    SELECT
      'meetings' as source, 
      \"ID\", 
      \"Meeting Date\" AS due, 
      \"From\" AS begins, \"Until\" AS ends, 
      \"Title\" AS title
    FROM \"meetings\" 
    WHERE \"Meeting Date\" = ? AND \"Attendees\" LIKE ? 

    UNION

    SELECT
      'reminders' as source,  
      \"ID\", 
      \"Date Due\" AS due, CAST('00:00:00' AS TIME) AS begins, CAST('00:00:00' AS TIME) AS ends, \"Detail\" AS title
    FROM \"reminders\"
    WHERE \"Date Due\" = ? AND \"Staff ID\" = ? 

    UNION

    SELECT
      'tasks' as source,
      \"ID\", \"Date Due\" AS due,
      CAST('00:00:00' AS TIME) AS begins,
      CAST('00:00:00' AS TIME) AS ends,
      \"Detail\" AS title 
    FROM \"tasks\"
    WHERE \"Date Due\" = ? AND \"Staff ID\" = ?
    
    ORDER BY due, begins, ends " ; 
    
    "" ; "" ; // fieldSeparator & rowSeparator

    $date ; // national_holidays

    $date ; // renewals

    $date ; "%" & If ( IsEmpty( $staffID ) ; "XXX" ; $staffID ) & "%" ;  // meetings

    $date ; $staffID;  // reminders

    $date ; $staffID // tasks
)

Oh, wait, where are the staff holidays?

Just checking to see if you were paying attention.

We need to change the WHERE criteria for that part of the query, as a holiday has a start and an end date, so we need to check if the date we’re looking at is in between these two dates, inclusively:

ExecuteSQL (
  "SELECT
    'staff_holidays' as source,
    \"ID\",
    CAST('" & $sqlDate & "' AS DATE) AS due,
    CAST('00:00:00' AS TIME) AS begins,
    CAST('00:00:00' AS TIME) AS ends,
    'Holiday' AS title
  FROM \"staff_holidays\"
  WHERE \"From\" <= ? AND \"Until\" >= ? AND \"Staff ID\" = ?" ;
  
   "" ; "" ;
   
   $date ;
   $date ;
   $staffID
)

And yes, there’s yet another wrinkle in there. We don’t have a specific date to pull from the staff_holidays table, so we need to make one up, i.e. use the search $date. It also needs to be an SQL style date to match the results in the column from other tables. We therefore need to calculate some an $sqlDate that’s formatted correctly, so that it SQL can CAST() it. A custom function would be best, but a quick and (very) dirty solution would be:

$sqlDate = Year($date) & "-" & Month($date) & "-" & Day($date)

The result of our latest query might be:

staff_holidays,9D6AB90B-1305-488B-BFC0-354DC1604EE7,2020-05-14,00:00:00,00:00:00,Holiday

Putting the calculation all together, and placing into a FileMaker script, gets us this:

Set Variable [ $date ; Value: "2/6/2020" ] 
Set Variable [ $staffID ; Value: "rd" ] 
Set Variable [ $sqlDate ; Value: Year($date) & "-" & Month($date) & "-" & Day($date) ] 
Set Variable [ $entries ; Value: ExecuteSQL ( 
    "SELECT 
      'national_holidays' as source,  
      \"ID\", 
      \"Holiday Date\" AS due, 
      CAST('00:00:00' AS TIME) AS begins, 
      CAST('00:00:00' AS TIME) AS ends, 
      \"Title\" AS title 
    FROM \"national_holidays\"
    WHERE \"Holiday Date\" = ? 

    UNION

    SELECT 
      'renewals' as source,  
      \"ID\", 
      \"Date Due\" AS due, 
      CAST('00:00:00' AS TIME) AS begins, 
      CAST('00:00:00' AS TIME) AS ends, 
      \"Renewal Type\" || ' ' || \"Customer ID\" AS title 
    FROM \"renewals\" 
    WHERE \"Date Due\" = ? 

    UNION

    SELECT
      'meetings' as source, 
      \"ID\", 
      \"Meeting Date\" AS due, 
      \"From\" AS begins, \"Until\" AS ends, 
      \"Title\" AS title FROM \"meetings\" 
    WHERE \"Meeting Date\" = ? AND \"Attendees\" LIKE ? 

    UNION

    SELECT
      'reminders' as source,  
      \"ID\", 
      \"Date Due\" AS due, CAST('00:00:00' AS TIME) AS begins, CAST('00:00:00' AS TIME) AS ends, \"Detail\" AS title FROM \"reminders\" WHERE \"Date Due\" = ? AND \"Staff ID\" = ? 

    UNION

    SELECT
      'tasks' as source,
      \"ID\", \"Date Due\" AS due,
      CAST('00:00:00' AS TIME) AS begins,
      CAST('00:00:00' AS TIME) AS ends,
      \"Detail\" AS title 
    FROM \"tasks\" WHERE \"Date Due\" = ? AND \"Staff ID\" = ?
    
    UNION

    SELECT 
      'staff_holidays' as source, 
      \"ID\", 
      CAST('" & $sqlDate & "' AS DATE) AS due, 
      CAST('00:00:00' AS TIME) AS begins, 
      CAST('00:00:00' AS TIME) AS ends, 
      'Holiday' AS title FROM \"staff_holidays\" 
    WHERE \"From\" <= ? AND \"Until\" >= ? AND \"Staff ID\" = ?
 
    ORDER BY due, begins, ends " ; 
    
    "" ; "" ; // fieldSeparator & rowSeparator

    $date ; // national_holidays

    $date ; // renewals

    $date ; "%" & If ( IsEmpty( $staffID ) ; "XXX" ; $staffID ) & "%" ;  // meetings

    $date ; $staffID;  // reminders

    $date ; $staffID  ; // tasks

    $date ; $date ; $staffID // staff holidays
  )] 
Show Custom Dialog [ "Diary Entries" ; $entries ]

with a result that looks like this:

staff_holidays,9D6AB90B-1305-488B-BFC0-354DC1604EE7,2020-05-14,00:00:00,00:00:00,Holiday
national_holidays,27080940-9901-4856-8ADE-588F61DB2580,2020-06-02,00:00:00,00:00:00,Swiss Holiday
reminders,4F0086A9-9714-4AEA-86ED-93A503BFB60C,2020-06-02,00:00:00,00:00:00,Post brochures
renewals,4176B2FB-2547-473C-8326-5AD75AEF3615,2020-06-02,00:00:00,00:00:00,Monthly SmithJ/0312
renewals,C110857C-64B2-4FB1-8EF3-40CB6764A839,2020-06-02,00:00:00,00:00:00,Monthly WilliamsM/0312
tasks,07610DCF-A5D8-42CF-88B7-FBD46B59DA90,2020-06-02,00:00:00,00:00:00,Call marketing
tasks,69AABBF8-0A3E-4AE7-9B12-5E5432FD6AAE,2020-06-02,00:00:00,00:00:00,Check signoff
tasks,F3749F53-E27F-433A-ABE7-BEAD333B6EBA,2020-06-02,00:00:00,00:00:00,Prepare quote for Jim
meetings,07BD4B2E-10B6-4B18-AFB4-72AF31EB90AB,2020-06-02,09:30:00,11:00:00,Allocations
meetings,94BC1CB1-2CA7-4563-B310-9BE610316B6A,2020-06-02,09:45:00,10:00:00,Scrum
meetings,B0ABC1F6-D212-481F-B4F5-57F145BAA63A,2020-06-02,10:00:00,11:00:00,Update meeting
meetings,A16A8722-F968-499C-86DC-38D005D02AA1,2020-06-02,14:00:00,15:00:00,Sprint 25 Retrospective

At this point, we’d be finished as far as old-school FileMaker was concerned. We would stick our result into a global variable, throw together some sort of virtual list and then display it in a portal. But it would be slow, and the UI might be awkward because portals are rubbish at handling variable data, and variable height rows. So, if that last paragraph sounds a bit geeky and frightening, GREAT! You can forget I said it.

Instead we’re going to use a FileMaker web viewer and some new version 19 Javascript tricks to produce something a little slicker.

First, we’re going to be using Javascript, so let’s make sure that the result of our SQL calculation is in the right format, by which I mean JSON. This is relatively straightforward. We want the result as a JSON array of arrays. This looks like this:

[ [ "2020-05-21","2","Fred" ], ["2020-04-01","1","Jo"], ["2020-06-23","3","Bob"] ]

This is almost like our data except that each row of our data is wrapped in square brackets, and the values in our rows are in quotes. Actually, you could say that the values were separated by quoted commas (","), and hopefully you can see that all we really need to do is change the fieldSeparator and rowSeparator values, and add some square brackets to the front and back of the result. Et voila:

"[&para;[\"" // prepend the start of our new array of arrays
&
  ExecuteSQL ( 
    "SELECT 
      'national_holidays' as source,  
      
      .... blah, blah, blah ...
 
    ORDER BY due, begins, ends " ; 
    
    "\",\"" ; "\"],&para;[\"" ; // fieldSeparator & rowSeparator

    $date ; // national_holidays
    
    .... blah, blah, blah ...

    $date ; $date ; $staffID // staff holidays
  )
& "\"]&para;]" // postpend the end of our new array of arrays

)

All of which gets us this result:

[
["staff_holidays","9D6AB90B-1305-488B-BFC0-354DC1604EE7","2020-05-14","00:00:00","00:00:00","Holiday"],
["national_holidays","27080940-9901-4856-8ADE-588F61DB2580","2020-06-02","00:00:00","00:00:00","Swiss Holiday"],
["reminders","4F0086A9-9714-4AEA-86ED-93A503BFB60C","2020-06-02","00:00:00","00:00:00","Post brochures"],
["renewal","4176B2FB-2547-473C-8326-5AD75AEF3615","2020-06-02","00:00:00","00:00:00","Monthly SmithJ/0312"],
["renewal","C110857C-64B2-4FB1-8EF3-40CB6764A839","2020-06-02","00:00:00","00:00:00","Monthly WilliamsM/0312"],
["tasks","07610DCF-A5D8-42CF-88B7-FBD46B59DA90","2020-06-02","00:00:00","00:00:00","Call marketing"],
["tasks","69AABBF8-0A3E-4AE7-9B12-5E5432FD6AAE","2020-06-02","00:00:00","00:00:00","Check signoff"],
["tasks","F3749F53-E27F-433A-ABE7-BEAD333B6EBA","2020-06-02","00:00:00","00:00:00","Prepare quote for Jim"],
["meetings","07BD4B2E-10B6-4B18-AFB4-72AF31EB90AB","2020-06-02","09:30:00","11:00:00","Allocations"],
["meetings","94BC1CB1-2CA7-4563-B310-9BE610316B6A","2020-06-02","09:45:00","10:00:00","Scrum"],
["meetings","B0ABC1F6-D212-481F-B4F5-57F145BAA63A","2020-06-02","10:00:00","11:00:00","Update meeting"],
["meetings","A16A8722-F968-499C-86DC-38D005D02AA1","2020-06-02","14:00:00","15:00:00","Sprint 25 Retrospective"]
]

Now we’re cooking.

Time to look at some HTML and Javascript.

We need to start with a very, very simple HTML page:

"<html>
  <head>
    <title>Diary</title>
    <style type=\"text/css\"></style>
  </head>
  <body>
    <div class=\"diary\"></div>
    <script type=\"text/javascript\"></script>
  </body>
</html>"

The backslashes are there, so that we can paste this code directly into a web viewer element.

We’re going to have our Javascript build the diary div. Each entry will be a div on its own and containing just the title and from and until times if applicable. To add some styling, we’ll add the type of the entry as an additional classname to the div, e.g.

<div id="entry_1" class="entry meetings"><span>09:45 ~ 10:00</span> Scrum</div>

This makes styling the diary pretty straightforward. How about something dreadful untasteful like this?

.diary {
  width: 300px;
  display: grid;
  grid-template-columns: 150px 150px;
  column-gap: 10px;
  }

.entry {
  font-family: arial, helvetica;
  font-size: 16px;
  width: 145px;
  padding: 5px;
  min-height:30px;
  margin-bottom:5px;
  
  }

.entry span {
  font-size: 12px;
  float: left;
  color: #0079F2;
  }

.staff_holidays, .national_holidays {
  font-size: 11px;
  text-align: right;
  height: 11px;
  }
  

.national_holidays {
  color: white;
  background: green;
}

.staff_holidays {
  color: blue;
  background: #A8CEFF;
  grid-column: auto / span 2;
  }


.tasks {
  color: #8CA600;
  background: #DDFDD5;
  }

.reminders {
  color: #black;
  background: #FFE94B;
  }

.renewals {
  color: red;
  background: #FFACA9;
  font-size: 17px;
  font-family: courier, monospace;
  grid-column: auto / span 1;
  height: 60px
  }

.meetings {
grid-column: 1;
  color: #555;
  background: #f5f5f5;
  font-family: courier, monospace;
  text-align: left;
  border-bottom: 1px dotted #ccc;
  text-align: right;
  width: 300px;
  margin:0;
    }

The simplest way to try CSS out in FileMaker is to create a global text field, say ui::css, paste your css into it, and then update the web viewer calculation so that we’re bringing the CSS in directly:

<style type=\"text/css\">" & ui::css & "</style>

Then you can just stick the CSS into the new global field, and fiddle about until, well in my case, the design makes you physically sick. But I digress.

We can take the same approach with the Javascript at the end of the file. We can create a global text field, say ui::js, and update the web viewer calculation so that we’re bringing the JS in directly:

<script type=\"text/javascript\">" & ui::js & "</script>

We’ll need two more global fields, ui::searchDate and ui::searchStaffID to hold the calendar date and staff id we’re looking to display.

And with those created, all we have to do is to write some Javascript and tie it together with FileMaker’s new Javascript functions and a little light FileMaker scripting.

First let’s create our webviewer on the layout we want the diary to appear on, and give it an object name, say “diaryview”.

Next, we can actually write the script mentioned above, called “Diary Entries” so that it looks something like this:

Set Variable [ $date ; Value: ui::searchDate ] 
Set Variable [ $staffID ; Value: ui::searchStaffID ] 
Set Variable [ $sqlDate ; Value: Year($date) & "-" & Month($date) & "-" & Day($date) ] 
Set Variable [ $entries ; Value:  "[[\"" &  ... ] 
If [ $entries <> "[[\"\"]]" ] 
  Perform JavaScript in Web Viewer [ Object Name: "diaryview" ; Function Name: "showDiary" ; Parameters: $entries ] 
End If

where the $entries value is our SQL calculation from before.

What this script does is take our globals, use them to calculate the entries, and then hand the result assuming it’s not an empty array (JSON remember!) through to our WebViewer using the new Perform JavaScript in Web Viewer script step.

Perform JavaScript in Web Viewer requires the name of the WebViewer, the name of the Javascript function, and any parameters to hand in. Think of the Perform JavaScript in Web Viewer step as a transmitter to the WebViewer.

Let’s assume that we’ve written the Javascript. It will build our diary, and when we click on an entry, will take us to the correct FileMaker record in the correct table. To do that we need a FileMaker script for our Javascript to call. This is our receiver, that receives messages from the WebViewer. Let’s assume that the script is going to be called with the name of the table we need, and the id of the record to find, as a list of two values, i.e. separated by the traditional FileMaker ¶ character.

Let’s create a second script “Find Source Record” which looks like this:

Set Variable [ $tableName ; Value: GetValue ( Get (ScriptParameter) ; 1 ) ] 
Set Variable [ $id ; Value: GetValue ( Get (ScriptParameter) ; 2 ) ] 
Go to Layout [ $tableName ; Animation: None ]
Set Error Capture [ On ]
Enter Find Mode [ Pause: Off ] 
Set Field By Name [ $tablename&"::id" ; $id ] 
Perform Find []

Almost done. Just the Javascript left.

It is at this point that I’m guessing some people will expect some huge library or set of incomprehensible files that import from here there and everywhere. Sorry to disappoint. For this barebones solution we need just one object with two methods, and the function we call from the Perform JavaScript in Web Viewer script step.

Here’s the script in full first, and I’ll explain it bit by bit:

let Diary = {
  render: async (props) => {
    let posts = JSON.parse(props);
    let view = `${posts
        .map(
          (post, i) =>{
    const [ type, id, due, from, until, title] = post
    const timestamp = from != '00:00:00' ? [from, until].join('~') : '' 
    return `<div id="entry_${i}" class="entry ${type}"><span>${timestamp}</span> ${title}</div>`
    }
        )
        .join("\n ")}`;
    return view;
  },

  postRender: async (props) => {
    let posts = JSON.parse(props);
    posts.forEach((post, i) => {
  const [ type, id, due, from, until, title] = post
     document.getElementById(`entry_${i}`).addEventListener("click", () => {
        FileMaker.PerformScript ( 'Find Source Record', type+String.fromCharCode(13)+id );
      });
    });
  },
};

async function showDiary(data) {
  const diaryElem = document.querySelector('.diary');
  diaryElem.innerHTML = await Diary.render(data);
  await Diary.postRender(data);
}

Starting at the end, showDiary(data) is the function we call from FileMaker. The data parameter is our JSON array of arrays, and array of records to display.

First we get hold of the div into which we’re going to insert our entries:

const diaryElem = document.querySelector('.diary');

The next two lines render (create) the diary entry elements from our data, and then, once rendered, adds the “clickability”. These two calls are to the methods in the Diary object, and both functions are asynchronous. The await keyword lets us treat them like they are synchronous, which means we don’t have to worry about adding “clickability” to objects in our web page that may not exist yet.

The Diary object contains these two methods, render and postRender.

The render method takes our data, which is a JSON string and parses it into a real Javascript array. It then creates a variable called view, which is in essence the HTML we want to insert into the div. The fancy stuff happens here:

${posts
  .map(
    (post, i) =>{
      const [ type, id, due, from, until, title] = post
      const timestamp = from != '00:00:00' ? [from, until].join('~') : '' 
      return `<div id="entry_${i}" class="entry ${type}"><span>${timestamp}</span> ${title}</div>`
    }
  )
  .join("\n ")}

The .map() and join() are “chainable” functions; .map() is a function that iterates over the array it’s attached to, using the function it’s given to update each element of the array. Remember, each element of the array is really another Javascript array made up of the results of our SQL queries, e.g.

["tasks","F3749F53-E27F-433A-ABE7-BEAD333B6EBA","2020-06-02","00:00:00","00:00:00","Prepare quote for Jim"]

This line:

const [ type, id, due, from, until, title] = post

uses the new ES6 destructuring syntax. It takes our array (post) and then pulls out each part, in order into separate variables type, id, due, from, until, and title. All in one line.

We can then see if we have time values, and whether we need to collate them:

const timestamp = from != '00:00:00' ? [from, until].join('~') : ''

This uses the ternary operator ?:, a short if…then…else. If from isn’t “00:00:00”, then stick from and until together, otherwise use the empty string.

Having calculated all the parts, we simple assemble them using Javascript interpolation:

return `<div id="entry_${i}" class="entry ${type}"><span>${timestamp}</span> ${title}</div>`

In other words, every one of these in our array:

["tasks","F3749F53-E27F-433A-ABE7-BEAD333B6EBA","2020-06-02","00:00:00","00:00:00","Prepare quote for Jim"]

is turned into a string like this:

<div id="entry_1" class="entry tasks"><span></span> Prepare quote for Jim</div>

or this:

<div id="entry_4" class="entry meetings"><span>09:00~09:45</span>Scrum</div>

giving us an array of strings. We then join this up and return the result.

At this point we have our HTML inserted into the Diary div, and it should look fine. Now that it’s inserted we can add the finishing touch, by adding the interactivity.

The postRender function works along the same lines as the render function:

postRender: async (props) => {
    let posts = JSON.parse(props);
    posts.forEach((post, i) => {
  const [ type, id, due, from, until, title] = post
     document.getElementById(`entry_${i}`)
       .addEventListener("click", () => {
        FileMaker.PerformScript ( 'Find Source Record', type+String.fromCharCode(13)+id );
      });
    });
  },

It iterates over the array of entries, but this time it uses the .forEach function. Again, it takes a function as an argument, but this time the function doesn’t return a value. Instead it looks for each if the new divs in turn using the id we added in our HTML and adds an onClick event listener. When the user clicks on an entry, the function the listener has been given is called. In this case it’s the new FileMaker.PerformScript() function, that takes the name of the FileMaker script to run, and the parameter string to pass it. ‘Find Source Record’ is the name of the script, and our parameter string is just the table name (type) and the record id (id) joined together using carriage return.

That’s the last piece of the puzzle in place.

This is very much a toy solution, and definitely not a production-ready approach. There are lots of holes in here, but it does show the transmit/receive paring of the Perform JavaScript in Web Viewer script step and the FileMaker.PerformScript() Javascript function that FileMaker 19 has added to its arsenal.

If you fancy picking it apart, ping me ( fun19 at dyce dot com ), I’ll send you over a copy.

Update

Thanks to John Renfrew’s delay suggestion, the issue of updating the Web Viewer on record reload is solved. The trick is just to add a setTimeout() call at the end of the JS, and completely remove the layout script triggers. I was too worried about not being able to get a return value from FileMaker.PerformScript(), when none is actually needed ;-)

setTimeout(() => {FileMaker.PerformScript ( 'Diary Entries', "" )}, 100)

Thanks again to John Renfrew for the insight.