At FileMaker DevCon 2019, I led over one hundred FileMaker developers in six hours of learning and working with JavaScript. It was a good day. We learned a lot. We made lots of mistakes. We fixed those mistakes. We felt pangs of frustration with the web viewer object. And we shouted with satisfaction when we got the web viewer to render our chart or datatable. In those six hours, however, we had to gloss over a few things. I intend to double back and talk in detail that which I blew right by to give all attendees the fullest experience. So here I’ll discuss how to extract and use the FM Web Viewer Bridge code and scripts from the JSPlayground file to your own.

One note: If you didn’t attend the session, feel free to participate in this post and video. There’s a lot in this video I’ve skipped over. But never fear: through future videos and webinars, I’ll retread what we did in the DevCon session.

Extracting the FM Web Viewer Bridge code

Working with the FileMaker Web Viewer Bridge was sort of the culmination of the JS training session. We were able to integrate the C3 Charting library and cause it to change without the web viewer flashing as the code reloads and without the rendered chart losing its state. (Again, we’ll come back to this later in another video.)

But we worked with the Bridge API within the context of the JSPlayground2019 file. Which is fun to play in, but hardly useful for production custom apps. So here’s how to extract the FM Web Viewer Bridge code into your own file. Follow along in the video. I’ve also written down the steps below. It’s pretty simple to do, and once you’ve done so you can use that first extraction in all your projects going forward. Let’s dive in.

Video Explanation

(15 minutes)

Written Explanation

Okay. Here are the steps you need to follow to extract the C3 chart with the bridge functionality out of your Playground file.

Export the Code

Export the code we wrote using the red Export button on the top right of the JSPlayground.fmp12 file. This exports this single record and all its code into a .fmp12 file to your desktop.

In this file, there’s an HTML_Calc field, but it is a text field. You can update it to be a calc field so that you can continue to tweak the JS code and have the web viewer grab the latest code. Here’s the code for the HTML_Calc field:

Substitute ( Code_HTML ; 
 ["__Script__" ; Code_Script];
 ["__CSS__" ; Code_CSS];
 ["__Data__" ; Code_Data] ; 
 ["__Library__" ; Code_Library] ; 
 ["__Extension__" ; Code_Extension1] ; 
 ["__D3__" ; Code_D3] ; 
 ["__FileName__" ; Get(FileName)] 
  )

Set up the Web Viewer

Set up a web viewer object on any layout. It really doesn’t matter the layout since you’re going to use a script to load the code into the web viewer. In the example, I just use the base table. Clear out all the checkboxes of the web viewer object set up except the “Allow Interaction . . . ” one (the first one). And finally name the object in the inspector. I called mine “BridgeWeb”. But that can be changed and updated in the scripting.

Custom Function

There is one small custom function, RandomNumber, that I used in the CreateColors script to pick two random colors from a list. Use it or not.

Windows Users Only

If you’re on Windows, or if any of your clients that will use this chart are on Windows, you need to add one more field to the table of the context that the web viewer object is on. Add any text field–I call mine ‘CopyPasteTarget’– and set the field to the right off the layout. Name it “CopyPasteField”. This will only be used when the FM WebViewer Bridge API is working with a hash (the URL) that is more than 2000 characters. It’s just necessary. :/

Copy / Paste Scripts

The next step is to copy and paste the necessary scripts into your file. If you wrote the C3 chart scripts, then you’ll copy those along with the scripts that runs the FM Web Viewer Bridge API. Select each of these, then copy them. Finally paste them into your file. If you do it that way, you’ll get no broken script references.

Here’s the full list:

  1. Load Bridge Example: this is the script that loads the code from the HTML_Calc field into the web viewer. Adjust this as you deem necessary, changing contexts to get to the layout with the web viewer. Here’s one place you need to update the web viewer’s name if you changed it.
  2. C3 Chart Folder: These are the scripts we wrote in the session. Copy the entire.
  3. FM WebViewer Bridge folder: Some of these scripts we’ll keep and some we’ll delete once the folder is in our file.

Copy all of these scripts/folders at one time and paste them into your file at one time. That will prevent any broken script references from showing up.

There will be a few errors; most of these are just broken field references. As you paste the scripts in, each script with an error will open in the Script Workspace. Go through each of these and fix what’s highlighted in red.

Delete Some Scripts

We don’t need all the scripts in the FM Web Viewer Bridge folder. Here’s a list of the scripts you can delete.

  1. ClickOn
  2. Inlined
  3. All the “Set ENV” scripts
  4. Compile URL Copy
  5. IsWebViewer Loading
  6. Close Web
  7. Generate JSON object

As you delete them, test to make sure you haven’t broken anything. To be super-sure, use FMPerception to find unreferenced Scripts.

Complete

After the above steps, you should be complete. You’ve successfully extracted the FM Web Viewer Bridge Code. The scripts and the functionality is copied from the JSPlayground file to your own custom app. From here you can use your file as a template.

If you do implement more than one bridge set up in your app, simply copy over that integration’s scripts. You shouldn’t have to recopy the Bridge scripts. It should be ready to go.

On Going

In the near future, we will continue our study of FileMaker & JavaScript. If you attended the session, I’ll have more videos and posts regarding stuff I had to gloss over. If you didn’t attend, I can lead you on from the beginning. Stay tuned for the plans we have.

Okay. Here’s the file

I planned on not uploading the file that I demoed here, but decided to reverse course. The more people that see and experience JavaScript in FileMaker, the better. So here’s the file. Pick it apart. And feel free to let me know if you have questions about it!

My username on the FileMaker community is “jeremy_fm_js”, and I’ve been known to advocate using JavaScript in FileMaker for a few years now. It might be interesting to be typecast into ‘the guy that always talks JavaScript’ (I don’t always recommend JS in my posts in the community), but I advocate for many good reasons. Some of them are reasons I’ve stated before, but also, there’s a few new ones I’ve thought of in the past year 🙂 If you need an escape hatch–this is, if you need to build an interface that is more interactive than we can do using ‘idiomatic‘ FileMaker–we can consider JavaScript, and we can consider learning the language. To help you learn JavaScript, JavaScript is part of FileMaker DevCon.

Why JavaScript?

The answer to this question is: why not JavaScript? There’s a lot of innovation we can bring to custom apps using the language native to FileMaker. JavaScript and JavaScript libraries brings thousands of testers to its foundation. I’ve written about all this before.

More reasons

FileMaker itself seems to be giving some weight to JavaScript in the platform (the entire platform, of course). There are two pieces of evidence. First, exploring JavaScript, for the third consecutive year, is part of FileMaker DevCon, the FileMaker conference. Along with APIs and Zapier and microservices, attendees will have the chance to see and experience what JavaScript can do in our apps.

Secondly, JavaScript is specifically mentioned in the product roadmap. In the past it’s been rare to see something so ‘outside’ of FileMaker be mentioned in the FileMaker plans, but there it is. So FileMaker, Inc, and my friends in the product-development team, must have some special plans for it.

The roadmap states: “Within a web viewer, directly call JavaScript from FileMaker scripts and vice versa.” That’s a very amazing idea. We will be able to solve certain web viewer problems with this functionality and give more control to FileMaker interacting with the JavaScript code.

JavaScript at DevCon

All day pre-conference training

This year I have the honor of leading folks in a full-day training of FileMaker & JavaScript. We will work together for six hours to talk about the following objectives and concrete activities.

Learn the basics of the JavaScript language

We’ll work together on exercises that teach us the basics of the language. We’ll talk about JavaScript concepts, and we’ll connect what’s true about JavaScript with what we already know in FileMaker.

Each exercise comes with one or more extra practices. We’ll do those to drive the point home.

Identify the peculiarities of the web viewer object on different devices

The web viewer object in FileMaker is powerful, but it is quirky. We have to understand its needs for each device, so as we practice and exercise, we’ll talk about how the web viewer works differently in different operating systems.

Integrate JavaScript libraries into any custom app

The fun of this day will be to spend time putting charts or data tables or any number of other libraries into our apps. We’ll learn how to do this from scratch. We’ll also review the tools out there that make this part very easy.

Just so you know: In order to work with libraries, we need to know a little JavaScript. We can’t run until we learn to walk.

Learn to communicate between JavaScript and FileMaker easily

The FM WebViewer Bridge framework is an important last-piece of the puzzle. It solves quite a few problems when working with the web viewer, and it provides a less jarring experience for your users. We’ll examine how this framework works.

We will work through examples and practices to learn more about how we can use JavaScript, and it will be a productive day. Attendees will learn a lot about how to use the language. We won’t be JS experts, but we will at least know a bit more of our way around its use in FileMaker. I have two ultimate soft goals:

To be comfortable enough with JavaScript we can look a JavaScript library in the face and not run away screaming in terror.

and

To expand our horizons of what is possible with FileMaker and JavaScript.

The six hours will be more than enough time to get our feet wet. If you haven’t signed up yet for this training time, I encourage you to give it a consideration. JavaScript brings to our fingers much power, and this session can help you see how to harness that power.

Discussions throughout the week

I wish to continue the JavaScript discussion well after the training session ends (after we’ve had a chance to recover from the workshop). Last year I was available to talk shop and sat with a few folks to talk one-on-one about how to integrate a chart or a data table into their in-production apps. That was a blast. So this year I wish to continue that. While at the booth or at food time or in the hallway, I’ll be available to talk with folks about their use of JS in their apps. So feel free to find some time with me and we can continue to discuss this innovative tool.

JavaScript after DevCon

The fun doesn’t end when FileMaker DevCon 2019 ends. Oh no. Energized from that experience, I’ll continue to talk about JavaScript and FileMaker. I’d love to do a follow up training session with interested folks to continue to learn more about the language and how to use it in FileMaker. So stay tuned to this blog and our Twitter page for more information.

Preparing for DevCon

If you do plan (or are thinking about) on joining me for FileMaker & JavaScript, please take a moment to download the file (below) and test your setup (computer, browser security settings, FileMaker version etc) to see if all will go well with your time. Basically click some buttons and confirm you see the web viewer user interface and interactions. Those running macOS will be fine. Windows users should really test it out.

I want you to be successful in the session. The file here contains a small sample of what we will do; all the items you’ll test here we want to make sure work. If you download the file and run thru the tests and all is good, you’ll have no issues with the work we do during the training. If you do have issues, please reach out to me at support@geistinteractive.com and we can work on finding a solution so that you can attend and learn and exercise your innovative spirit.

Don’t worry if your copy ‘fails’. I have a plan in case you can’t view the C3Chart or the DataTables or the PivotTable. The only extremely-vital one is the Code Editor test.

See yah in just a month! And get ready for JavaScript at FileMaker DevCon.

Downloads

The following list of downloads reflects what we did during the FileMaker & JavaScript session. More is coming soon.

JS Test File: test your machine to be ready for the training

Session Slides: The slides we used to talk about JavaScript and FileMaker and the web viewer.

To view all the resources we used and to view tutorial videos walking through each exercise we did during the session, go to this page.

The transactional method of working with records has one goal: to ensure all changes to records (additions, changes, or deletions) are done at once or not at all. We’ve talked about this before. The transactional method prevents some records in a discrete set (such as invoices and invoice line items) from being changed while others are left in a previous state. We’ve reviewed in the past how to create records in a transactional method (here and here). Now let’s turn our attention to the FileMaker transactions and editing records.

Review the Concepts

There are a few important points about the transactional process. If we keep these ideas in mind, we will design a workflow that works for FileMaker transactions editing and continues the trend of data confidence. 

The process needs to have ownership of the records to be changed. That means each record is open, and there are no errors in opening the records.

As the process changes records, FileMaker keeps those records open in the memory of the device running the transaction. If George is running a script that edits 15 records, those records’ changes will be on his computer only.

During the process, all commit attempts must be controlled. The process cannot go to another layout or allow the user to click outside a field. No commits can happen until all the records have been changed.

After all the records have been changed, the process tries to commit the records. 

  • If the commit is successful for each and every record, the all changes have been saved to the file.
  • If the commit throws an error for any one of the records, then all of the changes are reverted–that is, the changes are never saved to the file and instead discarded.

The use case for our consideration

As we take a look at these concepts in concrete detail, we need a use case. I chose to go with the statement: “The user wants to apply a discount to all individual line items” In this case: all the line items are assigned the same discount. It is important to do this all at once or not at all. And it is faster than applying the discount manually to each line.

Editing records

Our first step in preparation is to find the starting context. I’ve talked a lot about starting context in other posts, and the same information applies here. Karbon and DBTransactions uses a Transaction Log table as the start, and this is what I prefer. I want to log each transaction and what it is going to do. So let’s go over to the Transactions Log layout.

The starting context sans-portals
Portals in the starting context.

Editing the records

Now that we’re ready to edit the records transactionally, and, keeping the above concepts in mind, our script proceeds as follows:

One record at a time

Here’s what it looks like. Study this workflow diagram and see if you understand this. I’ll clarify a few things below.

Editing without a portal

If you’ve set up your FileMaker transactions editing process to work without a portal (and they can) then you need to follow these steps.

  1. Gather the primary keys of the records you want to edit.
  2. Go to the starting context. Create a new record.
  3. Add any logging information you might wish. Here’s what I might do:
    1. Describe the type of transaction: “Editing”.
    2. Identify the table we’re going to work on. “OrderLineItems”.
    3. Place the list of primary keys into a field.
    4. Enter the Start Time.
  4. In a loop, set the value of the OrderItemID field creating the relationship between this transaction and the one OrderItem record.
  5. Open the record. Check for an error
  6. Update the field. Check for an error.

Editing with a portal

If you’ve set up your FileMaker transactions editing process to work with a portal, then you need to follow these steps:

  1. Gather the Parent record Primary Key (in my example, the Order record primary key)
  2. Go to the starting context. Create a new record.
  3. Add any logging information you might wish. Here’s what I might do:
    1. Describe the type of transaction: “Editing”.
    2. Identify the table we’re going to work on. “OrderLineItems”.
    3. Place the list of primary keys into a field.
    4. Enter the Start Time.
  4. Set the Order primary key field with the value you gathered. This creates a relationship to the Order Item table and shows this order’s order Item table.
  5. In a loop, set update the record. This opens and then sets the record with the new value.
  6. Check for an error.

Check for errors

After each step in the above procedures, it is important to check for errors. We’re checking for errors each time we try to open any of the line items records and when we edit each record. If there’s any error in one of them, even if it’s the last one, we have to discontinue the process.

It seems extreme to discontinue and cancel the process when we get an error after either of the steps. It seems especially cruel to cancel if there’s an error on the last to-be-edited record. But remember what’s key about transactions: all of the records must be changed or none of them can be changed. If the last record has an error, then we can’t edit that one, so none of the other records can be edited.

Commit or revert

If we have edited all the records without incident or error, then all the records are committed. In our case, all the order line item get a discount applied to them and are updated in the file.

If there is an error and we need to discontinue the editing process, we have to revert the records.

We’ve talked about this before. The process is the same.

The FileMaker transactions editing process

The process of transactions, the main concepts, apply to all method of changing records. Editing FileMaker records transactionally is one way to change records, and it should be considered if it is vital all the records or none of them should be changed.

I am continuing my study of the new FileMaker 18 functions. We see this new release as a continuation of the dramatic change in FileMaker as a platform. Todd calls this new release the “End of the Beginning“. Therefore, it’s important to take a look at what’s new and see how game-changing each feature is. It’s just as important, too, to think of when new features will be used. And finally, it’s vital to think of how fast a new feature should be adopted. Let’s study the While Function up close and personal (with all the mistakes and pauses that happens in normal development) and learn more about the FileMaker While function.

Watch me learn

Since I didn’t know much about the While function, I explored it and recorded my exploration. I spend 30 minutes working through the function: reading the help, making mistakes, and occasionally finding success. I’m not sure how others learn and code, but I took my time and played around with things. Many times these new features require some testing and getting things wrong; they’re complex and need some study.

This exploration video is rough; you get to see me without a whole lot of post-production (I don’t even put a title screen on this video). But give it a try. See if it’s useful to you, and feel free to comment on how you code and how you do things the same or differently.

Anyway. Turn down the lights. Grab some popcorn. And explore the While() function with me.

Some Thoughts about the While function

The FileMaker While function is pretty cool. There’s a lot of new possibility, and with that comes great responsibility. We’re responsible for learning about the While() function–the structure and how it works–, and we’re responsible for learning when and when not to use it. So let’s write down some thoughts.

  • The syntax is very specific and takes some getting used to. I’m using aText to auto-create the function formatted in a way that makes sense.
  • You actually don’t have to declare all the variables in the While() declaration area, but you do have to declare most of them. If the variable is used in the result portion of the function, you must declare it above. Otherwise the function will return an error. You don’t have to declare the variable in the declaration area if the variable is ONLY used in the logic portion.
  • It matters where you place the iteration variable. Placing the iterator–in my case i + 1–before the rest of the logic changes what is returned. I don’t think I have a preference where it goes. I’m choosing to put it after the logic, just like I structure my Loop structure in a script.
  • The While() function has ‘block scope’. Roughly put, variables inside the function do not overwrite variables of the same name outside the function. The picture below shows the variable name with two values: the yellow name variable contains my last name. It’s outside the While() function. The variable can be used inside and outside the function, though I’m not sure why anyone would do this, to be honest.
  • I unchecked the “Automatically evaluate” box in the calculation dialog. I kept making mistakes, and I’d crash the window. I didn’t want the evaluation to happen while I was getting the syntax correct.
  • It’s a bit tough to debug. The logic portion might contain quite complex calculations, and you can’t really test it will. At least none that I’ve figured out yet. I have started to extract that logic and test it in another calculation dialog. Get it working, and then put it back in.

When to use While()

As I was playing with this new function, I was thinking of where it could be used. Like the good ol’ ExecuteSQL() function, this new one could be overused and used too fast. It might be adopted as the new and shiny thing without thought of consequence or consideration. I have no issue with using the function, but I want to stop and consider its use.

We have a bunch of JSON Additions custom functions that use the well-used recursive ability. They work just fine. These functions could be re-written using the FileMaker While() function, but they already work. So why rewrite them?

Additionality I like to use scripts and subscripts to do a lot of my looping over records or to process records. The While() function could take the place of many scripts, for sure. But I wonder if I’ll do this. I can easily debug scripts. And I can write modular scripts to work from different contexts, and I am decent at writing scripts.

Using the FileMaker While() function will take some consideration.

Onward

This new function is worthy of study and use in innovative apps. It does solve some problems for us and it does give us an efficient (one-function) way of performing tasks on records or data. As with any new feature the FileMaker While() function deserves a place in our toolbelt.

FileMaker’s innovative platform requires ever more security as the platform’s transition to a First Class Web Citizen continues. As more services connect to our systems and as more people interact with the data, a file needs tighter and more security. FileMaker 18 has introduced changes that increase security, remove false security, and open up more security access to authorized users.  Those changes include Enabling “Require Full Access Privileges to use references to this file” by default, removing false security of password challenge for full-access, alerting the developer to unsigned plugins, and adding a new option to allow non-full access users to manage non-full access accounts.

Let’s take a look at the FileMaker 18 security changes that have been introduced.

A new security interface

For one, the security interface has been redesigned to be more efficient for a developer or user coming to adjust the security settings.

FileMaker 18's redesigned security interface

FileMaker 18’s redesigned security interface

Those people who are assigned the role (more on that later) can adjust the privilege set, enable or disable it, or set/reset the password. This redesign seems to solve the “number of clicks” problem. The developer or a user can do what they need to do in fewer of them.

A Privilege to manage user accounts

As clients become more used to manage their own day-to-day security, they expect more control over accounts and such. As a developer we can provide scripts to do some of this, but that is a pain. FileMaker 18 adds a new privilege for any non-full-access account to manage non-full-access accounts.

The new privilege to manage non full-access accounts

The new privilege to manage non full-access accounts

A user with this privilege can do what we as developers used to have to do manually or build scripts. She can add accounts, change privilege sets (but not create them), change passwords, and more. If the same user has the older “Manage Extended Privileges” privilege, then she has more control over user accounts.

This person cannot touch the full-access accounts in the file. Though she can see them, she cannot edit or delete them.

Require Full Access privileges

This is sort of a feature that’s been around for a long time but sort of been hidden and off by default. For those files created with FileMaker Pro Advanced 18, this setting is now on at file-creation. This is a good thing.

The Require Full access privileges is activated by default.

This feature ensures that only authorized files can access this file, and that authorization can only be given by the full-access account of the file. If File A has this option turned on, then File B can only reference this file after File A’s full-access username and password has been entered when setting up the reference.

This feature is off by default for files created with FileMaker Pro 17 Advanced and earlier. But it’s easy to set up. Simply navigate to the Authorization area, turn it on. Then you have to go to all files that reference this one and authorize it.

I find this feature amazing. So we’ll talk about it in the near future.

Removing password challenges (most of them) in Manage Security

A FileMaker developer’s job is to deliver to the client an innovative app that connects to other services, processes data, and displays summaries, among other things. We’re logged into the file using our full-access account for a work session, opening the script workspace, the custom function dialog and other places. In previous versions of FileMaker the Manage Security dialog was hidden behind a full-access credentials challenge. So we stop our work and enter those. We make changes in the security, then close the dialog. We’re challenged again with credentials.

And then we walk away from our computer to get coffee or a donut or something. Our computer is open and we’re still logged in.

In the past, we might not have thought about that too much. After all, no one can get into the security part of the file. That seemed like it was a security feature.

Why remove

The credentials challenge created a false-sense of security. For sure, Manage Security is one important part of the system, but it’s one part. Any full-access account can still create scripts that create accounts, run any script, delete records, see any layout. etc.  So the password challenge was not much of a security point. It was a work-break point. I am the developer with full access. I need to get everywhere in the system quickly. Now I can.

Challenge accepted

The only time we are challenged with full-account username and password is when we are trying to change that password of the full access account. FileMaker wants to make sure you know what the new password is.

Be thoughtful in security

What we have to do as developers is think security in every part of our work: from our work to the times we’re not working. At the very least, when we walk away, we should log out of the file. Even better: lock the computer.

Again, this is a topic for a later post.

Invalidated plugin warning

The FileMaker 18 security changes include a warning when plugins are invalid in some way: the plugin is unsigned or has expired or other forms of validation fails. The user will get a weird (to them) message when the file tries to use an invalid plugin.

Since FileMaker now does this, we as developers should plan for this. We should use only valid or signed plugins, and we should test to make sure that users won’t get this message.

Luckily we have a small group of those plugins, and all the developers are part of the community, so I’m sure they’re working on getting this fixed. I doubt any plugins we typically use will cause this error.

FileMaker 18 security changes

FileMaker’s security changes show us that FileMaker, Inc. is indeed concerned about security. Every release continues the security updates as the platform reaches further and further out from our local computers and touches more people. The changes introduced in FileMaker 18 are necessary and good. They tighten up security and make our delivery to our clients more efficient and neater.

FileMaker 18 is another important step for the FileMaker Workplace Innovation Platform. It has a nice collection of new capabilities and features that make our jobs as Workplace Innovators even easier. But I like to take these now-yearly releases as an opportunity to step back, look at the bigger picture and fit this release into the broader trends that I see both inside FileMaker, the community and in Workplace Innovation in general.

The Beginning

Two years ago I wrote about how important FileMaker 16 was. I called it the Biggest Change Since FileMaker 7.  We got full cURL support, JSON parsing functions, a REST API and lots of others features that enabled FileMaker to become a first class citizen of the world wide web. It is hard to imagine being able to build apps today without access to those features.  But it was really only the very beginning of what was to become a major transition for FileMaker and the community. We are now well into that transition. In fact I’d say we have reached “The End of the Beginning”

Since FileMaker 16 shipped, several important things have happened or will be happening later this year. They show a company and a community that is more focused than ever and is fully engaged in strategy that I believe has a high chance of success.  Certainly there are challenges, more on this below.  But we are coming out of the awkward 🙂 beginning stage.

First Class Web Citizen

New features are great and individual companies and users can adopt them as soon as they are available.  But practically speaking the ecosystem as a whole is working with an average of the versions that are currently supported. There are still a lot of people using FileMaker 15, which does not support cURL and JSON.  Therefore the whole ecosystem doesn’t get to assume they can use JSON or any other feature in 3rd party products or sharable code, without excluding some portion of the ecosystem players.

Later this year FileMaker 15 will drop out of FileMaker, Inc.’s list of supported versions. All supported versions of the FileMaker Workplace Innovation Platform will be first class citizens of the web, able to integrate with just about any service, API, or app out there.

Modern Cloud Server

FileMaker 18 Server is the first version that has been built with core support for using multiple processors. While some of the benefits of this will take some time become obvious, we need to recognize how important this is.  Re-writing a database engine for a new feature like this is not easy. It’s not like just adding a new script step or a new layout control.  It is messing with a database’s highly optimized core job of writing data to disk correctly.

This had to get done for FileMaker to move fully into the cloud. We now have a server that has the required technical foundations to function as a cloud service or a on-prem server.

Workplace Innovation Platform

I am now into my 3rd decade developing on top of FileMaker and for the first time, I feel that I am in sync with–and have an essential role in–FileMaker’s strategy. FileMaker is and always has been a Workplace Innovation Platform. But there was never a common understanding even within FileMaker, Inc., never mind within the broader community, of what it was. Now there is. The time of figuring out what FileMaker is over, and the time to grow into that category is now in full swing.

We have a new community forum, new hilarious marketing videos, and even a new design on FileMaker front page. The roadmap promises more important changes coming soon. Everything is being shifted and brought into alignment with the new focus on the Workplace Innovation Platform. We even have new changes at the top jobs at FileMaker.

Changes at the Top

Dominique Groupil as CEO kept FileMaker relevant and profitable for two decades, a truly remarkable accomplishment. But even more amazing to me is the fact that he had the foresight to kick off this transformation and make sure it was on the right track before announcing his retirement and turning over the reins to Brad Freitag.

I have had the opportunity to work with Brad on the FBA Partner Council and in various other venues.  He is a good listener. He takes the time to collect and consider many viewpoints before taking action, sometimes bold action. He also has the confidence to course correct when the landscape shifts. In short, I am sure he will do an excellent job in leading the team at FileMaker, and I have high expectations for the years ahead.

The End of the Beginning

The new FileMaker 18 Platform is the world’s leading Workplace Innovation Platform used to help problem solvers address their unique business challenges. It includes great new features for developers to create, share, and integrate custom apps.

It also marks the end of the Beginning of FileMaker’s transformation. We have new features, new marketing strategy even a new management team. The transition is now well underway. Buckle up!

Future Challenges

This all great news. FileMaker needed to rise to the challenge being presented by the market today. In my opinion the challenges that lie ahead have a lot to do with developing the right language to describe what it is that Workplace Innovators have to do everyday. We have a good description of the problems people face trying to use technology to solve problems, and we now have a good set of low level capabilities like cURL, REST, WYSIWYG forms etc. So we know the goal, i.e. where we are trying to go, and we have a good tool that can get us there, but we need to make it even easier for people to pull it off.

Let me put it this way.  In 1995, I could use FileMaker 3 to build a custom information system using the just 6 concepts: Tables, Fields, Layouts, Value Lists, Scripts, and Relationships.  If you understood those six things you could build just about anything you needed at the time.

Today, I have different requirements. Things are lot more complex. I can still do everything I need to do, but it can take a too long to do it myself.  Can we find a way to “compose” new types of objects together so that we can assemble systems faster, using larger teams of people? I am not sure we can ever get back to just needing to know about 6 building blocks. But I believe there is set of modern system design patterns that we can break down, understand and recompose with FileMaker.

For example. Since version 16 FileMaker has had the capability to connect to any API and exchange data. Thats awesome! But once you start down that road you find that it is still a lot of work. We need to make that easier. I say “we” as in we the FileMaker  community and the company. It is up to all of us.

https://www.filemaker.com/learning/devcon/2019/index.html

FileMaker DevCon 2019

I intend to share more thoughts on this topic at the FileMaker DevCon in a couple of months. I am giving two talks on Workplace Innovation Platform. One will be focused on where we are right now. The other presentation will be on where I think we are headed in the next few years.

Also Jeremy Brown is giving a Training Day Session on JavaScript and Geist Interactive is also a conference sponsor. We’ll have an awesome booth. Hope to see you there.

All the best

Todd Geist
CEO, Geist Interactive

The process of actually transacting, of creating or editing or deleting records may seem like a complex concept, but once you understand the idea it’s actually rather simple. We create or edit or delete records from the starting context through the relationships we’ve created. Let’s take a look at the FileMaker transaction process with portals.

Total recall

Let’s remember what we set up as we started the transaction.

  1. We are on a starting context of some kind: either the Estimates table or the DBTransactions-kind of table. In the following explanation, we’ll be on the DBTransactions table, just as Karbon or the FileMaker transaction module in modularfilemaker.com.
  2. There is a relationship between the starting context record and Orders and OrderLineItems Tables set to “Auto Create”. We have named portals set up on the DBTransactions layout,
  3. We’ve collected the data in some manner Either an API request has returned a response or you’ve stored the entire Estimate and estimate line items data in a JSON object. Or the starting context is one that you can grab data from it and related tables.
  4. The current record is owned (thus opened) by the Start Transaction process. There were no errors in creating a new DBTransactions record or opening the Estimate record and its related ones.
    Any log information has been set if applicable.
  5. Oh and I’ve set up the Relationship to look like this. I’m going to store the ID of the Transaction in the order and Order Line table.

Simple & complex

The concept is simple and complex. Records can be created or edited or deleted across the relationship. This is the simple part.

It gets complex when we begin to create the one order and many order line items records in the respective related tables. There’s two ways to do it: in a portal for each relationship (we’ll discuss now) or without a portal (yes, this can be done, and we’ll discuss in the next post).

And now we’re ready to go. Let’s copy the estimate and its lines to Order and its lines.

In this section we’ll talk about creating records. There’s a separate discussion have when we are editing existing records and when we’re deleting records. The concept is the same for all three instances, just a few refined specifics for each.

Our transaction script continues like this (or a separate, “Do Transaction script”, begins):

Step one: create the order record

The first step of the FileMaker transaction process is to create the order record. Simple enough. Navigate to the ‘order’ portal. Go to the portal Row [last] and create that row’s data with data from the JSON object. Set the fields in the related table with data.

Some notes about this:

  • I have only a few fields in the portal. These few are simply to have a visual to confirm the record is created. Any fields will do. You might want to put one data field and any utility fields in the portal for debugging purposes.
  • Once the relationship has been created by setting data in the Portal Row[last], the rest of the record can be filled in with data from the estimate JSON.
  • The moment I create the order record, that record is open. In the Data viewer the Get(RecordOpenCount) is 2: The DBTransactions record and the Order record.

Step 2: Get the order ID

Since our goal in the FileMaker transaction process is to create an order with related order line items, we need to capture the primary key of the order we just created. Nothing’s simpler.

Step 3: Create the order line items

Next we create the order line items through the Order Line Items portal. We have captured the estimate line items previously in the JSON object ( in the “LineItems” attribute), and are ready to parse through it. We do the same thing:

  • Navigate to the Order Line items portal
  • Go to Portal Row[last]
  • Set all the fields in that row with estimate data from one estimate line item
  • Set the foreign key (ID_Order) with the primary key of the order we created above.
  • Continue this process until all data from all elements of the array have been set into the Order Line items table

Some notes:

  • As each order line item record is created, a new record is opened. After creating three line items records, five records are opened:
    • DBTransactions record
    • The Order Record
    • Three Order Line items records
  • We always go to the last row in the portal because it is the place to create a record. Even, as in the Order creation step above, only one record is to be created, we still should go to the Last row. Just keep the process consistent.

Commitment issues

Every time the FileMaker transaction process with portals creates a record in the object, one more record opens up. I’ve made it a point to highlight that fact. We want to keep these records open and we do not want to commit them until the entire record set, the order and all its order line items, have been created. So don’t commit or do anything that commits a record:

  • Do perform a find
  • Click outside a field
  • Commit Record script step

Set let the process run. Let the records build up in memory. And then, when all the records have been created, commit them together as we finish the transaction. Or revert them and NOT create them if there’s an issue somewhere.

FileMaker transaction process with portals

You don’t technically have to use the portals in the FileMaker transaction process. With a few changes, I can do all the work of creating records without a portal.

But portals are a great way for a developer to debug the transaction and watch the process. While running the script debugger, you can watch the records being created (but not committed).

I’m also storing the ID of the transaction in the order and order line item tables. So I can continue to view the records created via the transaction after the transaction has ended.

The FileMaker transaction process, ongoing

In the next post we’ll talk about how FileMaker transaction process without the portals. Stay tuned. It’s pretty cool.

FMPerception’s realtime developer intelligence is essential for developing. We’ve mentioned that many times. But it’s also good for talking with others about systems and about development practice. Let’s listen in on a FileMaker code review run by a MENTOR with her MENTEE using FMPerception.

———-

INT. Day. MENTOR’S computer. She starts up the meeting. MENTEE Joins.

MENTOR: Hello?

MENTEE: Hey there.

MENTOR: How was your weekend?

MENTEE: Oh it was fine. Spent most of the weekend studying for my certification test. It’s coming up soon.

MENTOR: Ah yes. Well we can chat about that if you want later, but first let’s do some FileMaker code review using FMPerception on your latest project.

MENTEE: Sounds good. I look forward to it

FMPerception in a FileMaker code review

MENTOR: This morning I ran a DDR and loaded it into FMPerception to–

MENTEE: When did you run it? I was working on it this morning.

MENTOR: Oh. Ok. Well let me run the DDR again and load it into FMPerception again. Should just take a few moments. In the meantime, I’ll share my screen. There. Can you see my screen?


MENTEE: Yep. Woo. FMPerception already loaded the DDR. That’s pretty quick. It took like five seconds.

MENTOR: It really is. I use this probably twenty times a day to analyze the files I’m working on and look for something like a script or variable or whatever.

MENTEE: Cool. I should use it more.

MENTOR: Yeah. It’s useful for a developer. Okay. So now it’s loaded I want to review a few things with you. First did you have any questions about your file or specific scripts or workflows?

MENTEE: Well I did want to ask about script and layout organization. I’ve not been putting them into folders and wanted to know what you thought about that. And, um, I’m not sure what else.

MENTOR: That’s okay. We will take a look at your folder structure in a bit. Let’s turn to FMPerception and your file.

Report card

MENTOR: In the FMPerception report I pulled up earlier I noticed a few things and wanted to mention those. First, the Report Card for the file shows a lot of good information. This report, as it says next to the name is slow, but the slowness is just a matter of a few more seconds than the rest of FMPerception.

MENTOR: Here it is. What I notice first is that across the top you’ve got 156 unreferenced and 256 broken references. That’s quite a lot. We should probably get those numbers down. Broken refs could disrupt the intended workings of the file, and unreferenced just might be making the file bigger than it needs to be.

MENTEE: That’s a lot of broken references.

MENTOR: Well it could be many things. When I ran the DDR it was missing a few files.

MENTEE: Yah. Those were taken down temporarily while Fred works on those.

MENTOR: Okay. And as I review the broken references details I see a lot of them, about half, are <function missing>. I must not have the plugin. So no big deal. Others, like “Empty Layout Reference” might need some fixing.

MENTEE: Got it. That’s useful. I wouldn’t have known where to find the broken references. FMPerception shows them pretty well.

MENTOR: It looks like some of the scripts are missing all their tables.

MENTEE: Oh yeah. I imported that script and haven’t finished setting up the references. I keep forgetting that I should import the table first, then the script.

MENTOR: Doing that would resolve the links. You could simply delete this script, copy the table from your sample file to this file and then paste the script back in.

MENTEE: I’ll do that.

Global variables

MENTOR: Moving on. I wanted to review your use of variables. You’re using only a handful of global variables. Global variables are fine to use; they just get tough to trace sometimes when they’re set or used in many parts of the file. Luckily we can see the information here and click on any number to take a look at the details. I can click on “Impacted Layout Objects” and see, in your case, the six items that are affected by a global variable.

MENTEE: Should I not be using global variables?

MENTOR: I think it’s fine to use them. Just be aware of them. I wouldn’t use them to store any security-related information, but otherwise they’re fine.

MENTEE: I don’t follow.

MENTOR: Many developers use global variables to store certain privilege information or other settings. If many of the scripts in the same file have logic in them that depends on the values in the global variables, it would be very tough to track all those down. FMPerception shows you everywhere your global variables are used so you can better track those down.

MENTEE: Okay. So use global variables, and use FMPerception to keep tabs on them.

MENTOR: Yep.

MENTEE: Okay.

Standard script steps

MENTOR: I poked around in other areas of your file. The “Standard Scripts Steps” section of FMPerception. What do you notice about this report?

MENTEE: Hmm. You clicked on “Set Variable” of my Clients file. On the right and in the pane below I can see all the times that is used. I can see the details of the step and its script name and even the line number.

MENTOR: What else?

MENTEE: What’s that “Button” designation?

MENTOR: That’s shows every time the Set Variable step is used in a single-step button.

MENTEE: Oh yeah. Wow. That’s cool. I can see here I’ve got three buttons that perform this step. I wonder if I should turn those into scripts…

MENTOR: I would. It’s certainly harder to debug or change these steps.

MENTEE: I’ll get on it.

Single step buttons

MENTOR: You can use the “Single Step Buttons” line on the report card to track down all of the buttons that do a single step and update those. This section shows you: (1) the layout this single-step button it is on, and (2) the coordinates and region it is located. You may want to review the steps beforehand. If many buttons do the same thing, such as “Delete Record/Request” (3), you can create a script that does that one step and assign the script to many buttons. This report shows a lot more buttons that perform single steps. Use it to eventually clean those up.

MENTEE: Got it.

Standard Functions

MENTOR: Moving on. The Standard Functions area is similar in reporting. Here we can see all the uses of each of the standard functions. Like the Standard Steps, this section shows only those functions you’ve used in the file. You can use the Search bar to find any function you’d like.

MENTEE: I like this. Oh. I can use it to look up my uses of ExecuteSQL.

MENTOR: That’s right. You can see the count here, click on the function and see all the places it is used.

ExecuteSQL()

MENTOR: What’s more, FMPerception treats ExecuteSQL specially. This function is a common performance-issue creator, so FMPerception has a special column just for that when you view all the script steps. The dot here in the row indicates this step uses the function. After sorting by the ExecuteSQL() column and all the steps that use this function are sorted to the top, I can look through each one. So far you’ve only used the function one time.

MENTEE: Ah. Interesting. I like using ExecuteSQL.

MENTOR: It’s useful but can cause issues in performance, so use it, but be aware of each use and any implications. And, of course, there are many other ways to get the same result. FMPerception has this separate ExecuteSQL() column in many sections: Scripts (flat), Steps (flat), Layouts (flat), Layout Objects, Table fields, calculations and other places where ExecuteSQL could be used.

MENTEE:  My first DevCon was in 2012.. I clearly remember sitting in the audience by myself and listening to one of the presentations. ExecuteSQL had just been introduced and the presenter was gushing over the fact that he was able to reduce the number of table occurrences by a huge percentage using ExecuteSQL. Everyone applauded. There were gasps.

MENTOR: I remember that. ExecuteSQL is great, but its performance considerations are worthy of thought. In most cases adding a table occurrence and relationship, if you keep the graph organized, is less of a performance impact than using ExecuteSQL in the wrong place. I’m finding I use ExecuteSQL less and less. There are other ways to get the data I need.

MENTEE: I like using ExecuteSQL, but will explore other ways to return lists.

Take a break

MENTOR: Great. Hey. Can we continue in just a few moments? I have to step away. Let’s connect again in 15 minutes. Okay?

MENTEE: Okay. Sounds good. Talk in a few.

END SCENE

Realtime developer intelligence code review

The mentor and mentee took a break. We’ll get back into their conversation in the next post. As you can see in this first half, however, FMPerception is useful in a code review. The conversation centers around objective facts found in the intelligence tool. The mentor can spot bottlenecks or potential issues, and the mentee can use the facts to improve his code.

Let’s get back to the FMPerception FileMaker code review in the next post.

FileMaker Transactions give us the complete tools we need to ensure records are completely created, edited, or deleted. We’ve seen in a previous post the idea behind it. Let’s now take up the idea of starting a FileMaker transaction. Our Karbon framework goes to extensive lengths to start the transaction, but your process can be pretty simple. Check out Karbon or look at the transactions module on modularfilemaker.org. Both are great examples of the entire transaction process.

Starting context

It’s important to get started on the right foot before a transaction begins. We need to begin the transaction from somewhere.

In Karbon and in the Transactions module, we use a DBTransactions table.

It contains fields for logging the transaction’s data, start and end times as well as any information about the success or failure. And it contains fields which are used for the relationships to tables in which records will be created or edited or deleted.
If you use the DBTransactions table, it is the starting context for every transaction in your entire custom app. It’s helpful to have all of them start at one place.

But your starting context can be any context that makes sense. If the use case is to copy Estimates and estimate line items to order and order line items tables, you could use the Estimates context as my starting point. If you need to copy estimate #4392 to an Order record, your starting point is Estimate #4392.

Schema

The starting context contains fields that are used in relationships to the tables where data will be added or edited. All the relationships are set to “Allow Creation of records in this table via the relationship”.

The DBTransactions starting context.

The Estimate starting context

In the starting context table I’ve got two fields, “TR_OrderId” and “TR_OrderItemId”. These fields are related to the primary key fields of the respective tables. We use the primary key fields because the relationship needs to be unique per record we will create.

Collect the data

Of course it’s always good to collect the data that will be added or edited. In the example we are following (copying estimate to order), we could collect all the data, both the estimate record information and each estimate line item data as a JSON object and store that somewhere temporarily. This gives us confidence that the data we have stored is correctly synced with itself. It is one complete object and one complete record. Once it is stored, changes can’t be made to individual fields in this one estimate record.

In our current example, if the starting context is indeed the Estimate record, then we don’t really have to collect the data since it’s already in the current record.

Validate and take ownership

Additionally we might want to validate the data. We want to make sure there is actually data to process and that there’s no errors in the data.

Also, we want to take ownership of the vital records. I don’t want anyone to edit the Estimate record and EstimateLineItem records while I’m copying its data to the Order table, so my process needs to own the record (lock it) during the process. If we’re editing records, it is possible to lock each of those in the starting process. So we open the Estimate and individual line items records.

Call a start transactions script

In Todd’s post about Transactions long ago, he states that there is no ‘start transactions’ script step that we can run. But we can put all of the starting tasks into a subscript and call this at the start. Our Karbon framework contains a script called “Start Transaction”. This file set and the transactions at modularfilemaker.org are good models for the script.

Any additional tasks

If you’re using the DBTransactions (or something similar) you might find it useful to create a new record for every transaction process (the entire process: creating all records in the order and order line item table). You can record the binary result of the process: did the transaction go through or was it rolled back.

The process to start a FileMaker transaction is fairly straightforward. Whatever the details, this step sets up a likely successful transaction. The process gets all the ducks in a row before the transaction takes place.

In the next post, we’ll talk about the transaction process itself.

A big part of our work as FileMaker developers is to design workflows that create or edit or delete records. If your scripted process requires that it completes the changes (adding or editing or deleting) to multiple records, then a database transaction is required. If a discrete entity (an invoice and invoice line items, for example) needs to be completely changed, then a database transaction is required. Let’s take a look at how database transaction works so that we can have confidence in FileMaker data changes.

An example to consider

Let’s imagine a custom app for a printing company. They create estimates with estimate lines.

The business workflow requires an estimate and its lines be copied into the order and order line items tables respectively. Here would be a typical script we might write.

There are issues with this script. Take a moment to study it. Based on what I’ve written above, what problems do you see?

I’ll wait. 🙂

The biggest problem is that I’m committing the new order record and the new order line items records individually. Here’s the sequence of events:

  1. I go to the Order Table and Create a record
  2. I go to the Order Line items table. In this action, the Order record gets committed
  3. I create a new record in Order Line Items table.
  4. After the first iteration of the loop, I create a new record. That commits the previously-created line item record.

We can see this commit after commit after commit happening. In the Data Viewer, Get(RecordOpenCount) shows 1. There’s only one record open. The previous records have been saved to the file.

If, while the script is running, the power goes out or the network collapses, some of the order line items records will have been created; others won’t.

That’s a problem, right?

The solution

FileMaker is a transactional database platform. A transaction is the complete process of getting records created or edited or deleted. The process is set up so that all records get saved to the file after creating or editing. The process is also set up to rollback changes made to records. The transactional model gives us peace of mind to have confidence in our FileMaker data

Commit all records

Your goal as a FileMaker developer is to provide a trusted workflow that keeps a user’s data intact and complete. Transactions ensure that all changes you made to records in the database get committed and saved to the database at once.

Rollback records

If there’s an error anywhere–power loss of incomplete data or record ownership issues–FileMaker Transactions rollback the data to their previous state. The changes (additions or edits or deletes) made against records, using a transactional model, do not get saved. The Pre-saved data is kept.

Transaction steps

The complete steps of a transaction are as follows:

  1. Start the transaction
  2. Create or edit or delete records
  3. End the Transaction

We can do all of these in FileMaker, and none of them are that difficult that even a new-to-FileMaker person cannot handle them. I’ll briefly explain these and then we’ll look at these in more detail in further posts.

Start the transaction

To perform the transaction, we need to start it. We get the correct context to the front, we get up the data to be added, and just get ready. The start step can include any of the following:

  1. Go to the proper transaction layout
  2. Validate the data to be added or edited.
  3. Create or open the transaction record. Record any transaction log information.
  4. Take ownership of records.

No matter the steps we take here, the last one is to continue or stop the transaction process if something’s not right.

Change multiple records

Once the transaction has started and we can continue, we add the records, edit the records, or delete the records.  This is the easy part. It’s what we script for every day. We’ll talk through this in detail in an upcoming post or tw.

End the transaction

In the transaction’s ending we:

  1. Try to commit the record changes made
  2. Check to see if there’s any errors
  3. Revert the records if there’s any errors
  4. Optional: Clean up. Record the success (or failure) and any other information about the transaction.

Transaction structure

There’s a simple structure to FileMaker Transactions. It requires just a few things:

  1. A Starting context
  2. A relationship to table occurrences in which records will be added. This relationship is set to “Allow Creation of records in this table via the relationship”.
  3. One or more scripts that control the record creation or editing or deletion and prevent any commit of records accidentally.

Have Confidence in your FileMaker data

Okay. This is enough for now. It’s a process, and one that deserves a through discussion. We’ll talk in the next few posts about the three steps: starting the transaction, doing the transaction, and ending the transaction. We’ll talk conceptual and practical. The whole point is to have confidence in our FileMaker data, to make sure that all of the changes get made or none of it. This topic merits more details. So we’ll talk through it here in upcoming posts. Stay tuned.