By Brian Ouimette:

In this article, we will be looking at a demo of a check-in/check-out system based on geolocation that is specifically for FileMaker WebDirect clients.

HTML5’s GeoLocation API gives us a web-native way of doing this within the webviewer. However, in FileMaker 18 or earlier, there is no built-in way for us to get geolocation data out of the webviewer and back to FileMaker. (Stay tuned for a FileMaker 19 version of this solution.) For now, the constraints that gave rise to this solution are that (1) we have a FileMaker Server 18 hosted WebDirect solution and (2) we want to avoid plugins or other technologies that are not directly supported by

FileMaker Server.

To start, we have a FileMaker file that references a basic HTML page containing a single button that is conditionally formatted

based on the record state (never checked in, currently checked in, or checked out). We add a little JavaScript code to get coordinates using the HTML GeoLocation API and send them along with the FileMaker recordID to a PHP page, which then makes a REST API call to FileMaker Server via the Data API.

If being entirely standalone isn’t a requirement, you could use something other than PHP to serve up the HTML/JavaScript file. At Proof, we generally prefer AWS Lambdas APIs or Claris Connect to glue our workflows together. For an all-in-one standalone solutions, enabling PHP on FileMaker Server gives us an easy win.

If you want a serverless solution, you could skip the PHP/server-side matter altogether and just have your JavaScript function to make a call to some REST API that would do the heavy lifting of writing back to FileMaker Server.

The webviewer in the FileMaker file references the HTML page and has parameters that are passed in like the FileMaker recordID, as well as values that may have already been recorded in the geolocation fields.

The Files: A Quick Breakdown

  • geolocation.fmp12 FileMaker database to host on FileMaker Server. It contains two layouts: one is for WebDirect and the other is for the Data API. The WebDirect layout has a webviewer at the top with a the single button that is conditionally formatted based on the record state (never checked in, currently checked in, or checked out). The remainder of the layout consists of FileMaker fields. The Data API user is `dapiuser`. This user only has access to the Data API layout and to modify the four geolocation fields.
  • geo.html –This is an HTML file you will host on FileMaker Server. It is the web page used in the webviewer and allows the WebDirect client to get the geolocation data.
  • sendLatLng.php – This file receives several parameters: Latitude and Longitude as well as a FileMaker recordID used to update the record via the Data API. Depending on the button state in the HTML file, the parameter names will either be checkinLat and checkinLng or checkoutLat and checkoutLng. The PHP takes these parameters and makes a post back to FileMaker via the Data API and updates the record. The way this file is currently set up is to hold the base64encoded username and password for the Data API. This could be improved, but for the purpose of this demo, this is how we went about storing the credentials.

Access these files by downloading the demo here.

Set-Up

Open the PHP and HTML files. Replace anywhere where <<host>> is with the FQDN where the files are being hosted. In FileMaker in the webviewer replace <<host>> with the FQDN. In the PHP file, you will also need to update the base64 encoded credentials. If you use the dapiuser, the base64 would be `ZGFwaXVzZXI6ZGFwaXVzZXI=`. You will need to enable the Data API, which means you will also need to install an SSL certificate. Web Publishing and PHP will both also need to be enabled as well.

And there you have it! Feel free to give this solution a try and drop us a comment below to let us know what you think.

By Andrew Koller

FileMaker offers a plethora of premade tools to organize and display data, so it may come as a surprise to many new developers that there are no native functions built in for phone number formatting.

Lucky for us, this is a quick and easy DIY project, and a great chance to expand your knowledge of functions and script triggers. Read on for a step-by-step walkthrough on how to create and implement this yourself!

Or, if you don’t feel like learning, just scroll down and swipe the code. Nobody’s watching. 👀

Time: 20 minutes

Level: Beginner What you’ll need: a basic understanding of layouts and custom functions

TLDR

  • Create a custom function to format the phone number field after some text has been entered by a user:
  • In Field Options, set the field as a Calculated value of the custom function and pass in the field itself as a parameter. Make sure that the “Do not replace existing values” is unchecked.
  • Create a script for a script-trigger to format the phone number as you type:
  • Set script trigger to OnObjectKeystroke, and pass in the field itself as a parameter.

Walkthrough

As with any good solution, the first step is identifying the problem and the desired outcome. In this case, I want the phone number field in my FileMaker app to format a user-entered string (some combination of characters) with a desired format of xxx-xxx-xxxx.

Broken down into bite-sized chunks, the task list reads as follows:

  1. Accept input from a specified field
  2. Remove all non-numeric values
  3. Remove any digits beyond the first ten
  4. Place dashes in front of the fourth and the seventh values
  5. Store our calculated value in a specified field

In the Manage Database window, I can automatically modify a field’s contents by setting it to a calculated value. I can take my input in the same step by adding the field that’s being pulled from as a parameter. That would take care of steps 1 and 5, so I can set those aside for now and plan to build around that (don’t worry if this is a little confusing – I’ll do a more detailed walkthrough when it’s time to implement this portion). With this plan in mind, I’ll start by creating a custom function to do the necessary calculations. I’ll call the function “FormatPhoneUS,” and add a parameter to represent the input. I’ll call that parameter “string.”

From there, the first thing to do is remove everything from the string that isn’t a number. The function GetAsNumber may seem like an obvious choice, but this function retains periods (to represent decimal points) as well as numbers, so in this case Filter is a better choice. I’ll set the parameter “string” as my textToFilter, and all digits as the filterText (literally “0123456789”). I’ll need to access the filtered string throughout the function, so I’ll set it to a variable called “format” using the Let function.

Next comes formatting the number. I need ten digits to get the proper format, but what if a user tries to put in a longer string?

As with many scripting exercises, I’ll have to make some assumptions in order to handle potential user error. In this case, I’ll assume that any digits after the first ten were typed in error, and leave them out of the final equation.

Using the Left and the Middle functions, I can set three more variables to capture the three left, three middle, and four right digits. I’ll call those “l”, “m”, and “r”. By using the Middle function rather than Right on the last four digits, I can get the numbers I need without having to trim the string first.

I now have the numbers I need grouped in easy-to-call variables. I’ll use List to concatenate those variables. Since List separates each variable with a carriage return, I can use Substitute to get the dashes I want by substituting the carriage return (”¶”) with a dash (“-”). I’ll set my original “format” variable to the value returned by substitute, and have that be the value returned by Let.

All together, this looks like:

Now back to steps 1 and 5 so I can add this function to my field as a calculated value.

In the Fields tab in the Manage Database window, I’ll select the field that I want to edit (in this example, I’ll use a field named “phoneNumber”). Pressing the Options button brings up more options for the field. Under the Auto-Enter tab, I’ll select the Calculated value checkbox and set that to “FormatPhoneUS ( phoneNumber )” – (I could have also passed “Self” as the parameter to the same effect in this case). I’ll also make sure the “Do not replace existing value” is unchecked, and there we have it! I now have a field that will format phone numbers.

Predicting and handling edge cases: +1

While the phone number formatter technically meets all of the original requirements, there’s a glaring omission: what if someone prefixes a number with a “1”? As it currently stands, if a user enters 1-877-776-6301 for example, the field would populate as 187-777-6630.

That’s not what I want at all! Because the +1 prefix is used to represent the US country code, no US area code can start with “1.” With this in mind, I can make some assumptions. Any time the first number is “1”, I can assume that the user was accounting for the +1, and remove it prior to formatting.

This is easy to achieve with an If statement. If the first number of our filtered variable f is “1”, then I want to return everything in “format” minus the first number; otherwise, I’ll just return “format” as it is.

I’ll use Left to identify whether or not the first number is 1. If it is, I’ll use Right and trim the leading “1” off by simply return the Length of format – 1; otherwise I’ll just return format in its current form and continue the process.

Formatting on the fly

Let’s take this one step further and auto-populate the dashes while information is being entered. This can be achieved through the utilization of scripts and script triggers. The script trigger “OnObjectKeystroke” is what I’ll use in this case. I’ll set it to trigger on the field that I’m formatting and pass in the field’s contents as a parameter.

In the script, I can capture the keystroke that was entered by utilizing Get(TriggerKeystroke). I’ll set a local variable using the Set Variable script step, and set it to the value of Get(TriggerKeystroke). I’ll call that variable $keystroke. From there, I’ll take a page from the function I wrote earlier and filter $keystroke for digits.

I’d like to use an If statement in combination with Filter to determine whether or not a digit was entered; the problem with that is that If interprets “0” as a false statement, rather than as a digit. I can get around this by wrapping the Filter function with IsEmpty. If the filtered result of “0123456789” does not return empty, we know that a digit was entered and the script will move to the next step. The next part seems relatively straightforward: get our script parameter, add $keystroke to the end of it using “&”, run those through FormatPhoneUS, and insert those back into the field using Insert Calculated Result.

But what if our user tries to make edits to the middle of the string? According to this setup, the edit would be tacked to the end of the current field contents, no matter where the user is in the field.

No bueno. One way to get around this is by using Get(ActiveSelectionStart). I can effectively chop our current string at the selection position and use that to capture the left and right portions of the string in two separate variables.

I’ll start by going back to the top and setting a new variable called $parameter to Get(ScriptParameter), and another called $position to Get(ActiveSelectionStart) – 1. I’m subtracting one because Get(ActiveSelectionStart) returns the ordinal position, but the string is indexed at zero.

From there I’ll go back inside of the If statement and set a new variable called $leftString. Using the Left function, I’ll use $parameter for the text, and get $position numberOfCharacters. Setting the variable $rightString to capture the right side looks similar. I’ll use the function Right to get $parameter for Length ( $parameter ) – $position numberOfCharacters.

I can stitch together the three string components as a formatted string by setting a variable (I’ll call it $result) to the value of “FormatPhoneUS ($leftString & $keystroke & $rightString)”. Now I can insert $result using Insert Calculated Result, and boom! I have a field that automatically formats as the digits are being typed.

Accounting for Edits

Since I filtered the keystroke to include only digits, I’ll need to add an exception to allow users to move around the field and make edits. In this case I’d like them to be able to use the backspace, delete, left arrow, and right arrow keys, as well as to use tab to move to the next field. I’ll set this up as an Else If statement and use FilterValues. I can filter according to the unicode values of the keys I want by using Code to interpret $keystroke as the textToFilter, and using List to hold the unicode values I want to accept. If the statement evaluates as true, I’ll set another variable called $validChar to 1 (aka “true”).

Now all I have to do is add a quick If check for validChar, and exit the script if $validChar evaluates as false. And there we have it! A phone number field that automatically formats with each keystroke.

In a typical day, a FileMaker user is interested in storing information in a custom app and then reporting out on that data in many different ways. A record, at the very heart of FileMaker, accomplishes this need. Here, data is entered and stored, and from this object, information is retrieved. One way data is entered is through FileMaker Drag and Drop, and therein lies a problem.

The Life Cycle

A record has a life cycle. It is created, edited, and committed to the file. Sometimes it is deleted. This cycle happens over and over. A record may go through many edits and commits in its life.

FileMaker drag and drop

A record’s life cycle

A primary job of FileMaker developers is to control that record life cycle. We set up strict controls about when a record can be created, edited, and deleted. We build logic to ensure a record has the correct type of information, and we work to ensure that the record is committed with integrity.

Like a vampire or a walker, however, there’s a user experience piece of FileMaker that is completely outside this life cycle and disrupts how a record is manipulated and then committed. That is the Drag and Drop.

FileMaker Drag and Drop: the Gotchas

FileMaker drag and drop is a great feature for fast data entry. This feature is commonly used for container fields and PDFs or pictures, but it can also be used to drag text data to a field. And that action is the disruption. When dragging data from another source into FileMaker, a user is almost completely subverting the record’s carefully structured life cycle, and thus the unexpected can happen.

Here’s a possible scenario: The user drags a text block from a website or email. She wants to save the text of an article or the current price of an item. The user simply highlights the text on the web page and clicks on it. Holding the mouse down, she drags the text over to the open empty and inactive field on the layout. Finally, she lets the mouse go and the text fills the field.

To the user, all is good, but under the hood, all sorts of unexpected things are happening:

The record is never opened. Or is it?

Normally when I type in a field, the record opens. FileMaker holds this record open for all edits. This doesn’t happen in a FileMaker drag and drop scenario. The data viewer, when watching the function Get(RecordOpenState), does not update from zero. Practically it may have opened, but the problem is that we cannot get to the open state of the record.

The record is never committed.

Well, I take that back a bit. Yes. It is committed because the data is stored in the field and record. Like the open state, we cannot get to the moment before the record is committed.

The modification count goes up

The result of the function Get ( RecordModificationCount) in the data viewer, increments by one each time.

Object script triggers do not run at all or properly

Mislav Kos and Wim Decorte at Soliant Consulting have a great rundown of all the script triggers and when they happen. The script trigger sequence, however it breaks down in a drag/drop scenario:

  • onObjectModify does run, but it runs after the record has already been committed (or the record has been closed).
  • The rest of the triggers do not run at all, no matter if they were meant to be a pre- or a post- trigger.

These facts present many problems for the developer. We are not able to control the life cycle of the record. We don’t get to capture when a record is open or before it is committed. Our lack of control over these means we cannot rely on script triggers to check field data for validation or for anything else we might do.

The Solutions

We can’t allow users to enter data without going through the proper business logic. So I can think of two solutions: either disallow drag and drop or highly control when a user can enter data into a record by any means they wish.

Disallow it

The first method is easier said than done. FileMaker Drag and Drop a preference in the app on each user’s machine. It is NOT specific to the file. So a developer or IT person would have to turn this off for every user. And of course, it can be turned on again in the preferences menu by a savvy user.

The Drag & Drop functionality can be turned off via the app preferences.

It would be awful to have to update this for each user

Disable it

The second method holds more promise: As a developer I might want to control when a user can enter data. I would have a separate data-entry layout and scripting to open a record and then check to make sure the record can be locked. When finished, the user would click a button that runs a script that checks various validations and allows the record to be committed or causes it to be reverted. Controlling the life cycle of the record means I could allow drag and drop.

Disallow it (Again)

Going back to method one, there is a way to disallow FileMaker Drag and Drop for all fields in a table without resorting to visiting each machine to turn off the app preference. The technique consists of a nondescript little field (text or number type) in the table where we want to prevent inadvertent drag and drop. I named my field “zz_ValidationField” so it sorts to the bottom of the field list. I’ve learned this from wise people: sort fields that do not contain data to the bottom.

On this field, I added validation in this manner:

  • The “Allow user to override during data entry” is unchecked.
  • The Validation can happen either “Always” or “Only during data entry”.

A validation of the field

Data Validation for my zz_ValidationField

  • Validation by calculation is used. Inside this is the function Get(RecordOpenState).

Validate using Get ( RecordOpenState)

The field’s calculation

  • Uncheck the “Validate only if field has been modified” checkbox at the bottom left.

Explanation

This setup simply prevents drag and drop from happening when the record is not previously open.  This field’s validation will fail if the record is not open ( Get ( RecordOpenState) = 0 ) and thus will not let the data be saved. The custom message doesn’t come up since the record wasn’t open in the first place.

Conversely, if the user opens the record  (Get (RecordOpenState) > 0 ) via scripting or clicking in any field and then drags and drops, the validation will allow the drag-drop to happen as normal.

As FileMaker developers, we have to control every aspect of our custom app so that we can ensure data integrity. The FileMaker Drag and Drop experience, dragging text blocks or PDFs gets around our control unless we take an extra step to ensure that users don’t get carte blanche in adding data to the file.

Download Demo File

It happens. You’re moving quickly, jammin’ on your favorite FileMaker custom app. Laying down some insane custom business logic in the form of one awesome, totally badass FileMaker Script. Then you notice that other script. The one you need to get rid of. No time like the present. So you pop over to it, hit “delete”, crush the popup confirmation before you have time to read it and… Whoops, something went very wrong!

NOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO!

You deleted the wrong script.  Your awesome, badass script with the insane logic is nothing but a memory. There is no undo. It’s gone. It’s not in the backup because you were just working on it.  It’s too new.  You are totally f#%@ed! You weep, you rage, you even try mediation. Nothing works. The script is still gone.

Then you remember. “Wait I just made a fresh DDR for FMPerception“. A smile creeps across your face. Yeah, you’re gonna be OK.  FMPerception is going to save your butt.

True Story

The above scenario happened to me this week. I deleted the wrong script. For a moment, it was bad. But then I remembered that FMPerception had the script. I could easily restore it, and reconnect it the other scripts that were using it. Just 2 minutes later I was done.

Phew!  Watch the video above for exactly how to recover a deleted script using FMPerception.

Need For Speed

FMPerception is so fast that it doesn’t get in the way of your workflow. As a result, you’ll use it all the time. You will be much more likely to have a recent analysis than if you are using an analysis tool that forces you to wait minutes or hours. Most FMPerception users know all about that, but what they might not know is that your analysis is also a backup, and can be used to restore code that you deleted or broken. And that can save your butt!

 

 

With the release of FileMaker 16 and native JSON functions, the ability to create a testable solution has been vastly improved. In this blog post, I will walk you through a simple example of what testing is, and how it can improve your solutions. Most developers have had situations where they need to alter some existing logic, and are concerned that they might alter something with unforeseen consequences. Wouldn’t it be nice if you could ensure that your changes didn’t break existing code? Welcome to FileMaker Testing.

Automated Software Testing

Software testing has a lot of different variations and buzzwords associated with it. You’ll hear all sorts of phrases and acronyms thrown about like TDD, BDD, Unit Testing, Regression Testing, etc. It’s worth a learning a little something about these at some point. But at the heart of all of them is a simple concept; write some code to automatically test some other code.  If you do, your code will be less fragile, and easier to maintain.

What is FileMaker Testing?

A test is a FileMaker just script used to ensure that other scripts are working properly. Tests ensure that scripts are not only accomplishing their goal but also producing the desired result when they fail. While there are many ways to perform FileMaker testing, we test using the following assumptions:

  1. Each script to be tested accepts & returns JSON as a parameter
  2. We will be using custom functions for handling errors, testing JSON objects, and validating a JSON payload

Writing Testable Scripts

To prepare your script that needs to be tested, it should always return JSON to the script that called it. This means that if the script fails, it should return an error JSON object. If it succeeds, it should return an object representing the data that was changed. See the example script called “Create a Project” in FileToTest. You will notice that it returns verbose errors on all failures, and returns a JSON object when successful.

Writing The Test Script

To prepare your test script, you need to do three things in karbon_tester:

  1. Create a JSON Object to pass the script using the layout “Edit Test Data”
  2. Create a script in the Folder “Project Testing” (Copy the existing scripts…you’ll get the idea!)
  3. Add the script you created to the script “Test – Project”

Do We Test Every Script?

There is no reason to test each & every script. We only write tests for those scripts that play a critical role in the solution. Once you are comfortable with testing, it will become clear what you should test. It’s up to you.

Benefits of FileMaker Testing

The benefits of creating test scripts are compelling. First, it provides any solution with a level of stability that is difficult to achieve in a timely fashion. If multiple developers are collaborating on the same solution, the test file enables everyone to run common tests on core business logic, and see if they have produced any unforeseen fatal errors.

Second, it provides a level of clarity for you, the developer. Once you begin writing tests, you will take on an alternate persona, where you are actively poking holes in your own code. This may seem like a small difference, but we believe you’ll be surprised once you try it. You become much more critical, which improves your focus & clarity.

Third, the readability of your code will improve. One thing that makes JSON difficult to handle is that there is not a repository for JSON objects within a FileMaker solution. When a script is designed to handle records in multiple tables, karbon_tester serves as a clear, contextual example of how to create & pass JSON objects to work within a solution.

Is it Worth It?

The first question many people seem to ask when introduced to testing is “is this worth it?” Our answer would be that it depends on the solution. If you have a complex system, multiple developers, and a need for stable solutions, it’s not just worth it, it should be the standard.

Download

To start, download our sample files here. Open “FileToTest”, and create a project. Review the script “Create a Project” to see how the file works. Then open the file “Karbon_Tester” and run the existing test. See how easy that was? Now you should create your own scripts to test creating a status.

What About Editing Data & Managing Test Data?

We will be releasing part 2 in this series in the future. We will walk through options for loading test data, & managing records that are used only for testing. The testing in this sample is intentionally simple to grasp and does not confront some of the nuanced issues involved in testing.

What about Karbon?

We know some have you have been waiting for us to release some of internal tools and frameworks that we group under something we call “Karbon”.  This is the piece piece of that puzzle.  More coming soon.

Looping through and filtering lists of values turns out to be a common task in FileMaker.  Having a few different techniques at your disposal is quite useful. In this post, we are going to look at a set of related techniques for processing lists.  First we look at how to loop through each item and perform some function or task on each item.  Then we will move on to a custom function for filtering lists by any valid FileMaker expression.  Finally we will use another Custom Function to easily parse Layout Object Names from a layout and use them in a dynamic slide navigation scheme.

Read more

FileMaker’s GetField() function has always been struck me as a curious beast.  Every since it’s release back in Filemaker 5.5 or there abouts I had assumed that it returned the contents of the field specified by the parameter.  But there were a couple of cases where it didn’t work that way and I’d get fooled every once in while.  I didn’t quite understand what was going on.  I think I finally stumbled on an easy way to keep it straight. Read more

Unit Testing is the practice of  testing individual chunks of code to make sure they are working as designed.  Ideally it is done with a single click of a button, so that you can do it all the time.  Traditional FileMaker coding doesn’t lend itself to this practice very well.  It’s just too tightly coupled.  But if you take some time to organize your code in a modular way you can start to incorporate something like Unit Tests into your development practice.

First you need to separate out the logic you want to test into well factored API like scripts.  These can take parameters and they usually return a result.  The main point is that you give these scripts the power to do what the need to do to your database, and you make sure that they report back if they fail or not.

Once you have your API scripts laid out you can the write yourself a bunch of test scripts that exercise those API script and make sure they are doing their job.  I put my Unit Tests in a separate file so I don’t clutter up my deliverable files. Then as I go along writing code, I periodically go back and run all the scripts. When I do something that cause my tests to fail, I can see it right away.  I know what test broke and hopefully I get some kind of error that might help tell me how it broke. From there, its relatively easy to go about fixing the code.

Here is a movie that shows what that ends up looking like.