FileMaker Data API and Portal Filters

We’re currently working on a project where we are exposing the FileMaker Data API to some web technologies. The team working on the Web side of things have asked for the portal data passed back by the Data API to be filtered between a date range.

To do this with a relationship would mean having the current date in every parent record and we’d need to update this daily. Not the smartest way but it would work.

However we wondered if portal filters would work and sure enough the JSON passed back by the Data API was filtered based on the portal filter we placed on the layout.

tldr; You can use portal filters on a FileMaker layout and this will translate over to the FileMaker Data API response.

We also discovered that if you give your portal an object name, this too will be reflected in the Data API response object

{
 "response": {
  "data": [
   {
    "fieldData": {...},
    "portalData": {
     portalLayoutObjectName: [...],
    },
    ...
   },
   {...}
  ]
 },
 "messages": {...}
 "fieldData": "...",
 "portalData": 
 ...
}

fm-csv2json

A CSV Parser that converts CSV into JSON on the FileMaker Pro Platform.

I was browsing over at the FileMaker forums and came across a question about parsing CSV data.

This caught our interest and made us ask the question.

Could we take some CSV data and transform it into a valid JSON Object?

The answer was yes and with that we present fm-csv2json.

A FileMaker script which when passed a valid CSV string, will covert this string into a JSON object.

Download free over on Github.

We also have a similar parser that turns XML into JSON too.

fm-xml2json

FileMaker Web Direct Clock

I was browsing over at /r/filemaker/ and came across the following thread.

I’ve got a running clock javascript that works when opening from Filemaker, but can’t run correctly on webdirect because of the GetDate() function. I was wondering if anyone had a solution to pulling the date from the server so the clock will be uniform everywhere.

ltlmanandy

After spending about an hour or so playing around, to see if we could actually build a running Filemaker Web Direct clock. We produced the following solution:

Let (
  [
    _ts = 
      ( GetAsNumber ( Get ( CurrentHostTimestamp ) ) 
      - GetAsNumber ( Timestamp ( "1/1/1970" ; "00:00:00" ) ) 
      + Floor ( Get ( CurrentTimeUTCMilliseconds ) / 1000 ) 
      - GetAsNumber ( Get ( CurrentTimestamp ) ) ) * 1000 ;
    _html = 
      "
      <!DOCTYPE html>
        <html>
          <head>
            <script>
              function startTime(today) {
                var start = new Date(" & _ts & ");
                setInterval(function(){
                  /* delta is used to reduce time slippage */
                  var delta = new Date() - start;
                  if(today) {
                    var today = new Date(
                      today.getFullYear(), 
                      today.getMonth(), 
                      today.getDate(), 
                      today.getHours(), 
                      today.getMinutes(), 
                      today.getSeconds(), 
                      today.getMilliseconds() + delta
                    );
                  } else {
                    var today = new Date(
                      start.getFullYear(), 
                      start.getMonth(), 
                      start.getDate(), 
                      start.getHours(), 
                      start.getMinutes(), 
                      start.getSeconds(), 
                      start.getMilliseconds() + delta
                    );
                  }
                  var h = today.getHours();
                  var m = today.getMinutes();
                  var s = today.getSeconds();
                  m = checkTime(m);
                  s = checkTime(s);
                  document.getElementById('txt').innerHTML =
                  h + ':' + m + ':' + s;
                }, 100);
              }
              function checkTime(i) {
                if (i < 10) {i = '0' + i};
                return i;
              }
            </script>
          </head>
          <body onload='startTime()'>
            <div id='txt'></div>
          </body>
        </html>
        " ;
    _result = 
      Case (
        PatternCount ( Get ( ApplicationVersion ) ; "Web" ) ;
          "data:text/html;base64," & Base64Encode ( _html ) ;
        "data:text/html," & _html
      )
  ] ;

  _result
)

Paste the above code into any web viewer and it will produce the following FileMaker Web Direct Clock!

Filemaker Web Direct Clock

The script makes use of Get ( CurrentHostTimestamp ). This keeps any client, whether using web direct or FileMaker Pro, in sync as the time source is consistent between clients.

The _ts variable is used to convert a FileMaker Timestamp into an Epoch timestamp required in Javascript.

The javascript var delta is required because the javascript function setInterval(...) is not guaranteed to consistently run at the ms value you give it. This variable allows for the slippage to be calculated and be added or subtracted to the time to keep it in sync. We initially didn’t have this and noticed that within 5 minutes our web direct clock and our system clock where out of sync by a matter of minutes.

References

Get ( CurrentHostTimestamp ): https://fmhelp.filemaker.com/help/17/fmp/en/index.html#page/FMP_Help/get-currenthosttimestamp.html

FileMaker Epoch Method: https://community.filemaker.com/thread/93917

Javascript Clock:https://www.w3schools.com/jS/tryit.asp?filename=tryjs_timing_clock

Javascript timer slippage: https://stackoverflow.com/questions/29971898/how-to-create-an-accurate-timer-in-javascript