Aller au contenu
  • billets
  • commentaires
  • vues
    4 578

Billets dans ce blog

Soliant Consulting

FileMaker 16 introduced a series of functions to work with JSON data. The primary motivation for adding this functionality was to make it easier to work with web services that transfer data in JSON. However, there are plenty of other good uses for JSON outside of interoperability with external systems. Some examples include passing and receiving script parameters and results. You can also assemble data (for example, for a report), which needs to be collected and/or transformed in some way but which does not otherwise need to be stored in fields.

If you have data stored as JSON, presumably you will want to extract it at some point. And as part of doing that, you may end up assigning that data to variables, whether for better script readability or other reasons.

Adding these assignment steps to your script can be tedious, especially when the JSON document contains a lot of data. For instance, given the following JSON document that has key-value pairs for each letter in the alphabet, we would end up having to create 26 "Set Variable" script steps.

JSON example:

  • { "A" : "value1" , "B" : "value2", … , "Z" : "value26" }

Script steps:

  • Set Variable [$a; Value:"value1"]
  • Set Variable [$b; Value:"value2"]
  • Set Variable [$z; Value:"value26"]

The following custom function will create these variables for you automatically:

  • JSONCreateVarsFromKeys ( json ; namespace )

It will create local variables for all keys belonging to the JSON document's root node.

Take Note

  • You can leave the "namespace" parameter blank. If you specify a value, it is used as a prefix for the variables that get created.
  • Typecasting is handled by determining the type for each value and then using functions like Quote, GetAsDate, GetAsTime, etc., to ensure the correct type. I took this portion of the code from the FileMaker Standards # custom function. My understanding is that Daniel Smith and Jeremy Bante wrote this code section, so the credit for this goes to them. (If you know otherwise, please let me know using the comments section below.)
  • The variable names will be based on the keys that exist in the root JSON node, so this will only work if the key names follow the same naming restrictions as for FileMaker variables and fields. For instance, if a key name uses a reserved keyword such as AND or NOT, the custom function will return an error; specifically, error code 1204.
  • Errors will be indicated in the custom function result using standard FileMaker error codes. 0 means no error.
  • This function will return "?" when used in a pre-16 client.

Below is the custom function code.

 * JSONCreateVarsFromKeys ( json ; namespace )
 * json - JSON data
 * namespace - Prefix for the variables that are to be created; can be left blank
 * CREATED on 2017-12-08 by Mislav Kos <>
 * Create local variables for all keys belonging to the JSON document's root node.
 * For example, given the following JSON, this custom function will create $id and $color variables: { "id" : "123", "color" : "blue" }
 * This custom function will return an error code as a result.
 * 0 means success or empty JSON.
 * 5 means invalid JSON.
 * 1204 means one of the JSON root keys did not conform to the FileMaker naming restrictions for variable and fields.
 * Pre-16 clients will return "?".
 * Errors will be indicated in the custom function result using standard FileMaker error codes:
 * @NOTES:
 * Keys must be named following the same naming restrictions as FileMaker variables and fields:
 * Does not require any other custom functions. Requires v16 or later client. Pre-16 clients will return "?".

Case ( 
	IsEmpty ( json ) ; 0 ; // If JSON is empty, return 0 ("no error")
	Left ( JSONFormatElements ( json ) ; 1 ) = "?" ; 5 ; // If JSON is invalid, return 5 ("command is invalid")
	Let ( [ 
		~keys = JSONListKeys ( json ; "." ) ; // Get keys from JSON document's root node
		~key = GetValue ( ~keys ; ValueCount ( ~keys ) ) // Process keys starting with the last one; otherwise JSON arrays won't process correctly
	] ; 
		If ( 
			IsEmpty ( ~key ) ; 0 ; // If the JSON document's root node doesn't contain any keys, return 0 ("no error")
			// Create variable based on key, then delete key from JSON, and then recursively call CF again to process remaining keys
			Let ( [ 
				// Get value for key
				~value = JSONGetElement ( json ; ~key ) ; 
				// Ensure correct typecasting of value; without this, everything would get typecast as text
				// This next section was taken from the the # custom function and (I think) was written by Daniel Smith ( and Jeremy Bante (
				// See
				~plusOneText = GetAsText ( ~value + 1 ) ; 
				~isValidDate = not EvaluationError ( GetAsDate ( ~value ) ) ; 
				~isValidTime = not EvaluationError ( GetAsTime ( ~value ) ) ; 
				~number = GetAsNumber ( ~value ) ; 
				~value = Case ( 
					~value = "" or ~value = "?" or ~number = "?" ; Quote ( ~value ) ; 
					~isValidDate and ~isValidTime and GetAsText ( GetAsTimestamp ( ~value ) + 1 ) = ~plusOneText ; "GetAsTimestamp ( " & Quote ( ~value ) & " )" ; 
					~isValidTime and GetAsText ( GetAsTime ( ~value ) + 1 ) = ~plusOneText ; "GetAsTime ( " & Quote ( ~value ) & " )" ; 
					~isValidDate and GetAsText ( GetAsDate ( ~value ) + 1 ) = ~plusOneText ; "GetAsDate ( " & Quote ( ~value ) & " )" ; 
					~value ≠ ~number ; Quote ( ~value ) ; 
				) ; 
				// Create variable based on key and value (and namespace)
				~error = EvaluationError ( Evaluate ( "Let ( $" & namespace & ~key & " = " & ~value & " ; \"\" ) " ) ) 
			] ; 
				If ( 
					~error ≠ 0 ; ~error ; // If we encountered an error, return the error code and don't bother processing the rest of the keys
					Let ( 
						json = JSONDeleteElement ( json ; ~key ) ; // Delete key from JSON
						JSONCreateVarsFromKeys ( json ; namespace ) // Recursively call custom function to process remaining keys

Get the Demo File

Download the demo file which provides several examples, including how to use the custom function to receive script parameters and results.


Need Help?

If you have any questions on these instructions or the demo file, please let me know in a comment below. If you’re looking for help with customizing your FileMaker solution further, my team and I are happy to provide additional insights. Contact us today.

The post How to Automatically Create Variables from JSON Data in FileMaker appeared first on Soliant Consulting.

Voir le billet d'origine

Soliant Consulting

Create Variables from JSON Data

With the adoption of FileMaker 16 comes new functions to use JSON data, empowering you to do far more than just transfer data in JSON. By creating a custom function, you can take your FileMaker app further and use your data more effectively.

Learn more about how to use a custom function like this to receive script parameters and results, by downloading your free demo.

Complete the form to receive the demo:

Trouble with this form? Click here.

The post Create Variables from JSON Data in FileMaker Demo appeared first on Soliant Consulting.

Voir le billet d'origine

Soliant Consulting

As you may know, one of our offerings,, is built on AWS (Amazon Web Services). We're constantly looking for new ways to learn more about the leader in IaaS (Infrastructure as a Service) and build on our positive experiences with the platform. My colleague, Bill Heizer, and I therefore attended the annual AWS conference, re:Invent, held in Las Vegas, Nevada.

We're constantly looking for new ways to learn more about the leader in IaaS (Infrastructure as a Service) and build on our positive experiences with the platform

There are several AWS conferences throughout the year, including one held in Chicago regularly attended by another colleague, Brian Engert. AWS re:Invent, however, is a larger event with more activities and session.

Massive Turnout

Attendance for re:Invent 2017 was around 43,000 people, and I believe conference organizers were originally anticipating significantly fewer attendees. To serve all extra registrants, they ended up adding several overflow sessions and repeat showings of some of the more popular sessions.

Due to the turnout, the conference was spread out across the Vegas strip into four main venues. There were plenty of excellent sessions and activities to participate in. From keynotes and sessions to game day hackathons and hands-on labs to fitness challenges and broomball, it made for a full week.

Click to view slideshow.

A Wide Variety of Sessions

Myself, I enjoyed the Compute focused sessions, primarily at the Venetian conference center. We also took in quite a few other value-added sessions, including business topics (very informative), security, advanced networking, serverless computing, IoT (Internet of Things), and machine learning.

Next Time: Plan Ahead or Get White Castle’s

Admittedly, our week started out a bit rough. Bill and I couldn’t attend our first session after waiting in line to get in. It filled up before we got a seat! However, our luck seemed to change after a trip to White Castle. They do not have White Castle in the part of the country Bill lives, so this was a rare and welcome treat. The trip marked a turn of fortune for us.

After that, we seemed to have good luck all week getting into sessions. It may have been conference organizers working to accommodate the larger than anticipated crowd, but we attribute it to our White Castle visit.

Keynote Sessions

I enjoyed the Wednesday keynote by AWS CEO Andy Jassy and Thursday keynote by AWS CTO Werner Vogels. They were informative, highly-attended, and included several product announcements. New exciting features include GuardDuty, which records traffic and API logs, tracks regular behavior, and then alerts administrators about “irregular” behavior, therefore serving as a great security feature. Inter-region VPC peering, Fargate, and EKS (Amazon Elastic Container Service for Kubernetes) were also announced.

Customer Success Stories

And a conference wouldn’t be worth attending without quality entertainment and a handful of customer success stories. Lauryn Hill impressed the audience with her hit song "Everything is Everything." That is especially pertinent as the range of AWS offerings is immense and very broad. Companies like General Electric drove this point home as they shared how they use AWS to drive growth and efficiency. We also learned how educational institutions bid on spot price instance to lower costs and reduce time to complete massively complex calculations.

AWS Within Soliant Consulting Offerings

Bill and I gathered quite a bit of information to consistently to improve our FileMaker hosting services, We continue to focus on providing the best experience possible for our customers and expand into new areas.

We left with many insights to improve our hosting server and ideas to build on in the near future. My team and I will continue to provide a durable, reliable, and highly available service.

Stay tuned to this space for new developments, as we continue to grow and build.

Have questions about what we learned at AWS re:Invent 2017? Let us know in a comment below.

The post AWS re:Invent 2017 Recap appeared first on Soliant Consulting.

Voir le billet d'origine

Soliant Consulting

Wouldn't it be nice to have a button bar that you could place on every layout that handles basic FileMaker navigation and would automatically update itself when you make changes like rearranging, renaming, or adding layouts.

The appeal of this functionality is that it can be applied to most any FileMaker solution. I’ve seen other approaches over time, although not all have met all requirements that I would want of such a tool. These requirements include:

  • Works in Browse and Find mode
  • Uses Themes and Styles
  • Allows manually navigating to layouts
  • Works in List View
  • Works in Pro (both local and hosted), Go, and WebDirect
  • Works with multiple windows
  • Works natively (no plugins required)
  • Does not require additional schema
  • Handles a reasonable number of layouts
  • Minimal scripts required

Other nice-to-haves:

  • Does not require custom functions, which is better for portability
  • Retains the last visited sub-section, allowing for navigation of multiple tiers

Expanding brain image

Image from:

This has been something that has been in the back of my mind for a long time. An earlier version I created used a web viewer to display navigation but only supported two tiers. That solution had its drawbacks, notably a "clicking" sound on Windows that happens when a web viewer is loading. That original version was done some 10-plus years ago and was due for an update using modern techniques.

The newest iteration supports three tiers of navigation and uses only native FileMaker objects. You can also utilize Themes and Styles with current versions of FileMaker, allowing you to easily change the look and feel of your standardized navigation in all layouts that use the same theme. All layouts that use a common Theme will reflect updates across the entire file when you update the Theme.

Three Tiers Deep

This solution has three button bars, first introduced in FileMaker 14, that are stacked on top of one another. Your first tier is the top bar. Therefore, the minimum version of FileMaker for this technique is at least 14. Clicking on one of the top tier buttons takes you to the second tier and so on. If you think of the layout names as data, where we define a delimiter to separate the layouts, we can structure a naming convention that works with the navigation framework to build out the navigation scheme. Consider the following:


This is a straightforward list of values, which is easy to understand. If we consider each individual value as having a list of its own, this could be considered a multi-dimensional array. For example, “Company__Contacts__Info” is a list with three values delimited with an underscore.

In the above list, we have "Home", "Company" and "Prefs" on the first tier, and the second tier would be dynamic, based on the first tier we have selected. Company on the first tier has “Main,” “Details,” and Contacts” that appear on the second tier. Similarly, selecting “Contacts” on the second tier will show “Info” “One” and “Two” on the third tier. If there are no values to display on the second or third tier, that button bar is hidden.

That also means it is easy to change your navigation by simply rearranging the order of layouts. The next time you run the “Load Nav” script, or just close and open the file again, your FileMaker navigation will update through your entire solution.

Building the List

Since we can get all the layout names with a Design function, it is possible to parse through those values to build a series of global variables that will display those values on a button bar. Once we determine which first-tier section we are on – based on the current layout name – we can evaluate which second and third tiers need to display.

You may optionally set your own list of layout names to use, instead of using the design function. That gives you more control over naming layouts you do not want to appear, or layouts that may physically be out of order. If you want to keep it dynamic, there is a developer definable prefix to use that will omit layouts you do not want included. If you override with a static list, you can simply include only those layouts you wish to appear.

This solution supports up to three tiers, with each tier being able to support up to ten values. That means 10 x 10 x 10 for a total of 1,000 layouts that can be maintained automatically. That should suffice for most of the solutions that I work on.

Order is important

Since we parse through the layouts to build the navigation interface, those layouts need to be in the order you want them to appear. This makes it trivial to update your user interface. Did you want "Prefs" to appear before "Company"? Then simply re-order your layouts and open the file again. Your UI will update to reflect the change.

To populate all the needed variables, we need to run a script once when the file opens. In this version, all the buttons in the button bar get routed through a centralized “Nav” script, where exceptions can be handled. Alternatively, that script can simply use "Go to layout" by name.

We also handle support for multiple windows in the “Nav” script if the user holds down a modifier key. The command key on OS X, the “ctrl” key on Windows, or the shift key will open a new window before navigating to the desired layout.

Dynamically named variables

The solution makes heavy use of dynamically named variables, so it may limit the characters used in layout names because they are used to name variables. If your layout names follow the naming convention explained above, it all just works. Global variables are all “namespaced” with a "NAV" prefix to avoid collisions with other variables you may set in your solution.


FileMaker supports Unicode pretty much everywhere, so you can include emojis in layout names.  They will display as icons in the navigation bars. Note: this also means you can have emojis in variable names.

Under the Hood

Button bars make it possible to use calculations to specify the active button segment. The calculations that determine which segment should be active for each of the three tiers are dependent on each other. For example, to know what the active segment should be for the second tier, we need to have already calculated the active segment for the first tier.

So that we can more easily control which active segment is calculated first, the three active segments are determined using a single calculation, which has been placed in the top-most button bar. Layout objects are rendered left-to-right, top-to-bottom, so once the first-tier button bar evaluates its active segment calculation, the next two button bars will be ready to go.

Second and third tiers with a null value are assigned a default value to display, like "Main." Buttons that are not used are hidden so that they cannot be clicked. The remaining space is filled in with invisible buttons that have no action assigned to them and act as filler, so the buttons stay at even width.

If there are layouts you do not want automatically included in your navigation, you can exclude them by naming the layout beginning with "//". The notations used for the delimiter and exclude flag are configurable in the script that loads the navigation variables. If you want to use a different convention, you can change it to whatever you want, but the default is double underscore for tier delimiters and double slash to exclude the layout.

Navigation scheme

Navigation scheme.

This screenshot shows what the navigation scheme looks like. You can also see this in my accompanying demo file, which includes some sample layouts. The different tiers have been styled differently to set them apart.

The next screenshot shows how navigation appears when viewing the "Company__Contacts__Info" layout.

Navigation bar

Navigation changes when on the Company layout.

Because button bars are easy to manage, you can modify their placement and appearance easily. Would you rather display the third tier at the bottom of the layout? Just place the third button bar at the bottom of the layout. Layout size is wider than the sample file? Just resize the button bars to the desired width, setting the anchor points as needed.

Save Your Place

In each section, we can retain the last visited sub-section when navigating multiple tiers by setting the variables used to link to different layouts when navigating through the lower tiers. You would not want to keep having to navigate through 2nd and 3rd level tiers every time you go to “Company” for example. If you are drilled down, need to go to “Prefs” and go back to “Company,” you will arrive at the 2nd or 3rd tier you last visited.

Multi-Window Support

As we use global variables unapologetically, each window gets its own set of global variables to reference. These are set in the scripts and buttons, so there is nothing for you to update; it is all done for you.

Is this a little heavy-handed, brute force setting so many variables? YES! But the work is done all up front and only once, so you can benefit from it in lots of places.

Dynamic FileMaker Navigation Bar Instructions

To use this in your own solution, follow these steps:

  1. Import the Theme, or create styles for the parts. This can be done later if you already have a Theme in use.
  2. Copy the "Load Nav" and “Nav ( layout )” scripts, paste it in your solution, and configure “Load Nav” to run when the file opens.
  3. Copy the navigation buttons bars and paste them into your layouts.
  4. Configure a script trigger to run when opening a new window (File->File Options…->Script triggers: OnWindowOpen) to include the “Load Nav” script. If you do this, you can disable the script steps to run this script when checking for a modifier key in the “Nav ( layout )” script.

I will often use this technique on files that I have developed where I do not have time budgeted for layout navigation. With this solution, I have nice looking navigation available that can be implemented in a short amount of time.

Try it yourself. Download the sample file.

Need Help?

If you have any questions about building your own dynamic FileMaker navigation functionality, please don’t hesitate to ask a question below or contact my team and me directly.

The post How to Build a Dynamic FileMaker Navigation Bar appeared first on Soliant Consulting.

Voir le billet d'origine

Soliant Consulting

What are Ghosts Sessions?

When you lose your network connection or your FileMaker quits, sometimes your session doesn't drop off on the FileMaker server. This becomes a problem when that session hogs up one of your license spots. This doesn’t seem to happen often, but when I talked prentices to colleagues at the annual FileMaker Developer Conference, I learned it happens more often than we think.

The Process to Eliminate Ghost Sessions

So, once we identify we have a problem, we can start cooking up a solution. This particular server happens to be Windows Server. First I thought about VB scripting or Power Shell scripting. To be honest I've written one small Power Shell script, but this seemed a little above my head. So, I thought, well, here's a tool I know how to use: FileMaker. I wonder if it can do the job. It can, indeed.

The process is basically the following:

  1. Issue a command in Command Line to get a list of connected clients;
  2. Create records in a table;
  3. Compare them to see which ones are the same (same IP address or user);
  4. Issue a disconnect for everyone except for the newest connection.

So how do we do this from FileMaker? With the use of a plug-in, of course. In this case I grabbed the Base Elements plug-in, because it was already installed on the server.

The Base Elements plug-in can issue Command Line commands. I use it to get the list of connected clients:

BE_ExecuteSystemCommand ( "fmsadmin list clients -s -u <username>r -p <password>" )

The result comes back in fixed-width text format — which gave me a little headache, but I learned a lot from it. Fixed-width means that every word has a certain amount of space and the data is separated by an indeterminable number of spaces. To get that data into arrays, you have to calculate the position of the column headers.

Let (


text = GetValue ( $BE_output ; 1 ) ;

$$pID = Position ( text ; "Client ID" ; 1 ; 1 );

$$pUname = Position ( text ; "User Name" ; 1 ; 1 ) ;

$$pCname = Position ( text ; "Computer Name" ; 1 ; 1 ) ;

$$pCon = Position ( text ; "Connect Time" ; 1 ; 1 ) ;

$$pDur = Position ( text ; "Duration" ; 1 ; 1 )


$$pID  & ¶ &

$$pUname  & ¶ &

$$pCname  & ¶ &

$$pCon  & ¶ &



I then go through the result and create records from it. Now I have records to compare the timestamp on the similar records.

The way I do that is by setting up another table occurrence (user = user in my case) and then checking whether the record is a duplicate via this calculations:

TimeStamp ≠ Max ( UT__UtilityTable_User::TimeStamp )

Here is my table:

Utility Table

Utility Table (click image to enlarge)

Disconnecting the Ghost Sessions

The last thing is to find the records that are marked duplicate and then loop through to issue a disconnect command for them:

# Loop through the dupes and disconnect 

Go to Record/Request/Page 

[ First ]
Set Variable [ $rep; Value:1 ]
Set Variable [ $maxReps; Value:Get (FoundCount) ] Loop 

#Disconnect the client(s) 

Set Variable [ $ID; Value:UT__UtilityTable::ID ]
Set Variable [ $command; Value:"fmsadmin disconnect client " & $ID & " -y " & " -u " & $username & " -p " & $password ] Set Variable [ $BE_output; Value:BE_ExecuteSystemCommand ( $command ) ]
Set Variable [ $error; Value:BE_GetLastError ]
If [ $error > 0 ]

Send Mail [ Send yourself an email via SMTP so you know when there was a user disconnected.]

[ No dialog ] End If 

Exit Loop If [ $rep = $maxReps ] Go to Record/Request/Page 

[ Next ] End Loop 

As you can see I'm using username and password variables above. Those are set in my script with the FileMaker Admin console access values I need to issue the command line commands.

Following these steps should help you eliminate ghost sessions in FileMaker and avoid overusing unnecessary license spots. Good luck! If you have any questions, let me know in a comment below or contact my team directly.

The post How to Disconnect Ghost FileMaker Sessions on Your Server appeared first on Soliant Consulting.

Voir le billet d'origine

Soliant Consulting

As your knowledge of the techniques of FileMaker grows, so too must the depth of your knowledge on performing standard use cases in our custom apps. This thesis was my beginner session at FileMaker DevCon 2017.

We can’t rely on one method that we like or are good at simply for those reasons; instead, we must choose the best way for the current circumstance of our custom app.

Drawing on personal experience, I laid out a case for developers to have different ways to solve the same use case under their belts. I used the example of “Getting data from a found set” to illustrate my point.

To illustrate my point, I demoed multiple methods to accomplish the following use cases:

  • Creating a new record
  • Editing a record
  • Getting data from a found set

These examples created great discussion on how to choose different methods and demonstration of some of them.

The demo file, hosted at the FileMaker Community site, shows many other use cases and different methods you could choose for each. Please download an updated version from there, and take a look. I’m happy to answer any questions you have; just ask in a comment below.

Soon, I’ll provide some more follow up videos and posts regarding the information presented in the session. Stay tuned to our blog page for those.

This session presented the ideas and thoughts that I think through every day. We diligently and continually evaluate our techniques to see if they work for our current projects or if we should try something new.

I hope you find it useful. Feel free to suggest other techniques to solve any of these use cases.

Thank you


Watch the full session here:

The post Build Your Skills to Tackle Familiar FileMaker Challenges in New Ways appeared first on Soliant Consulting.

Voir le billet d'origine

Soliant Consulting

When scripting in FileMaker I pay particular attention to the following:

  • Code clarity: make sure your code is humanly readable; clean and documented;
  • Reuse elements: declare a variable once, use it whenever you need it;
  • Keep your code compact: if you don't need it, don't put it in or leave it in;
  • Generate a sub-script if you have to use the same set of lines in multiple places;
  • Speed: make it as fast as possible; and
  • Error handling: make sure errors are caught and appropriately handled.

You don't have to adhere to all of the above requirements, but your process will work better if you do. Recently I had a situation where speed was not sufficient for the process I was working on so I had to rethink the approach. The process involved populating a SQL table with data generated within a FileMaker app.

If you work in environments with multiple software systems, you cannot escape having to exchange data with other databases, such as Oracle, SQL, or mySQL. FileMaker offers several features that allow for interacting with data in other sources. ESS uses the ODBC connection set up on your server (or desktop) to access supported external SQL sources as if they are a FileMaker table. The same ODBC data source can be used to execute commands.

There are two ExecuteSQL features in FileMaker: one is a calculation function and the other a script step.

ExecuteSQL Calculation Function

If you haven't yet, I'd recommend getting familiar with the ExecuteSQL calculation simply because it just makes life a little simpler in FileMaker. I like to use it to get an ID of a certain record or simply get a record count for a set criteria. It's also useful for

ExecuateSQL has become one of my favorite calculation functions. It's fast and gets the results done but it can only query the data source (SELECT). There are plug-ins out there that can perform other steps, such as INSERT. It can get sluggish if you use it on a dataset larger than 40,000 records.

ExecuteSQL Script Step

My user scenario was the following: we scan data at a facility into a database hosted using FileMaker Server. There are multiple users scanning date at the same time. The data needs to be posted to a SQL database in almost real-time, because they need to run reports on it in another system.

Since we had issues on the client's SQL server (which we suspect might have something to do with the ESS tables being accessed all the time), we decided to try a different method. I set up a script to push the records into SQL via the ExecuteSQL script step executing the INSERT command.

This was my initial approach to the new process:

  1. Search for the unprocessed records;
  2. Generate a field list variable;
  3. Generate a data list variable;
  4. Insert the current record’s data into SQL; and
  5. Loop to repeat steps 2 - 4 for each record in the found set.

Throughout the process I learned that I needed to issue a command before inserting a record:

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

This command can be combined with the INSERT lines, which makes it simpler.

This script got the job done, but it would take five minutes to process fewer than 1,500 records. Since they wanted to run the script every five minutes the script would bite its own tail. I started tinvestigating to see if there’s a way I can cut the time down. Turns out you can INSERT multiple rows into SQL with one command, as many as 1000 records at a time. I refactored my script once more to post records in a batch. Along the way, I also learned that FileMaker Server does not suppress the 401 error, so I modified my process — see the reference above.

This is the new process:

  1. Check for unprocessed record with ExecuteSQL calc;
  2. If they exist, search for the unprocessed records;
  3. Generate a field list variable;
  4. Loop through up to 1000 records to generate the data list variable;
  5. Push data to SQL; and
  6. Continue looping until all the records in the found set have been processed,

Now steps-wise this doesn't look like a huge change, but the script time got reduced from about five minutes to four seconds.

Below is the set of SQL commands I used to insert 1,000 rows into SQL at the same time.

The Command

"SET IDENTITY_INSERT [dbo].[SCN__Scan] on " & ¶ &

"INSERT INTO SCN__Scan (" & $_fieldset_horizontal & ") VALUES " & $$_data & ¶  &

" SET IDENTITY_INSERT [dbo].[SCN__Scan] off"

The Field Set

The $_fieldset_horizontal variable is just a comma-separated list gathering the names of the fields on a layout using the function FieldNames ( Get (FileName) ; Get (LayoutName ) ). So once you gathered your fields, you need to replace your carriage returns with commas: Substitute ( $_fieldset ; ¶ ; ", " ).

The Data

The $$_data variable is also a comma-separated list of values with prentices surrounding each record; it essentially looks like this:

(value1, value2, value3), (value1, value2, value3), (value1, value2, value3), etc.

Error Checking

You cannot program without debugging. You can get error reporting from the Get ( LastExternalErrorDetail ) function after each execution of the ExecuteSQL script step. It's very detailed (much more so than the error reporting on the ExecuteSQL calculation function) so you can correct errors as they occur.


When you are testing you generally need test data. I assembled a quick script that generates 1,500 records with Perform Script on Server (PSOS) script step, but first truncates the table. Since I have access to the client's SQL data source, I can also truncate the SQL table. Wash, rinse, repeat.

Running It from a Server

It's always another can of worms when you run a PSOS or scheduled server script. Two things that come to the rescue are logging any and all errors and logging script execution times. I set up an ErrorLog and a ScriptLog table in my solution and log the results.

Happy FileMaking!

The post How to Use the ExecuteSQL Script Step in FileMaker appeared first on Soliant Consulting.

Voir le billet d'origine

Soliant Consulting

A year ago, I knew very little about the web viewer and how to use it in a FileMaker custom app. I could use the presets, of course, but couldn’t adapt it to my own needs.

My, how much has changed in one year. Through careful study that took many hours, I finally came to some understanding of how to integrate languages of the web (HTML, CSS, JavaScript) into a custom app to provide deeper functionality.

As a result of that hard work, I was lucky enough to be able to share my experience and offer some advice at FileMaker DevCon 2017 on this very subject, and I’m grateful for the opportunity.

This hour-long session barely scratched the surface of the lessons I’ve learned over the last year. I could have gone for two or more hours on these insights I have to share. In an effort to give DevCon attendees the information I find most important, I focused my session on the best practices of integrating a JavaScript library into FileMaker, showcasing my guides through the lens of real-life examples.

Web Viewer Library-FileMaker Integration Best Practices

These best practices include:

  1. Placing all web-language code into fields;
  2. Using one field for each of the files a library requires;
  3. In the main field, called HTML in my library, putting in placeholders, such as “**CSS**” that will be eventually substituted out for the code in another field; and
  4. Using the fmp protocol to call back to FileMaker and perform some action on the data.

I illuminated these best practices and ideas through a few examples of the web viewer library, a collection of 35 (and growing) integrations. I also reviewed how to manipulate an integration and finally, how to push it to any custom app.

Does an Integration Make Sense for Your Custom App?

Throughout the discussion, I emphasized a key point: always make sure an integration is the best method for your custom app. They look pretty and can deepen FileMaker’s functionality, but the end solution may not always be worth your development efforts.

I tried to set up my library to make it very easy to implement one of these, but if you don’t know JavaScript that well, your productivity can be less than desired.

The session gave us a chance to skim the surface of this amazing possibility. If you were unable to make it, FileMaker is sharing the entire session here in a video. I hope that you found (or find) the hour engaging and informative and most of all, inspiring.

In the coming months, I’ll be putting together short videos explaining many of the integrations in depth. Stay tuned to our blog page for those, and of course, follow me on twitter, @jlbmagic, where I’ll highlight all that I’ve learned and have put together.

Watch the full session here:

The post Best Practices for FileMaker Web Viewer Integrations appeared first on Soliant Consulting.

Voir le billet d'origine

Soliant Consulting

FileMaker Pro is well-known as a desktop application and is making great strides in expanding its market in mobile and web with FileMaker Go and WebDirect. Traditionally, it has been viewed as a Rapid Application Development (RAD) platform for use in On-Premises deployments. However, that view is changing.

I found this unattributed image online and thought it fit with my team’s services. I really like the succinct way of explaining these different concepts.

Illustration of services

I’d like to expand on the ideas presented in this visual.

On-Prem FileMaker

On-Prem refers to a locally hosted server in an office setup, on which users can access the locally hosted application server. Having a local, easily-developed application has many advantages if your users access it at the office and you can architect your networking to provide outside access. This strategy has served as the old standby for years, especially when internet costs were much higher 10+ years ago.

As the landscape has continued to evolve with agile flex space allowing companies to remain competitive, FileMaker has also expanded with continual network improvement and different access methods.

FileMaker Infrastructure as a Service (IaaS)

With options such as Amazon Web Services (AWS) providing a pay-for-what-you-use infrastructure on demand, FileMaker can play an important part of your company’s growing cloud strategy.

Of course, you still have the consideration of configuring and developing your solutions, just like you would have when hosting on premises.

FileMaker Platform as a Service (PaaS)

If you adopt hosting services like or FileMaker Cloud, you essentially have Platform as a Service. PaaS serves up all the FileMaker goodness and removes infrastructure concerns. You no longer need to configure or provision machines to host your solutions and can instead concentrate on solution development.

FileMaker Software as a Service (SaaS)

Finally, by combining professional development services with our FileMaker hosting services, you effectively have your very own custom Software as a Service. Plus, you have a few extra perks -- normally with SaaS, you have limited options in customization, but with FileMaker, you can tailor your solution to fit your exact business needs.

Soliant Consulting + = SaaS

For more information about transforming your FileMaker solution into a truly custom application for your business, contact my team and I today.

The post FileMaker Software as a Service (SaaS) appeared first on Soliant Consulting.

Voir le billet d'origine

Soliant Consulting

Secure Sockets Layer (SSL) is a critical part of security, but many FileMaker developers don't know much about it. We all use it, probably every day, but most of us have not had to set it up or really understand how it actually works. Maybe we think of it as some sort of voodoo that happens behind the scene, and as a user, that's fine; there's no need to understand it any more than that. But as a developer or a server administrator who has to set it up, it can be helpful to know a bit more about how it works.

I’ve invested some time learning about SSL and even presented a session at the 2017 FileMaker DevCon called "Demystifying SSL."

What is an SSL Certificate?

Let’s take a closer look at how SSL works. When you set up SSL, you have to get this thing called a certificate. If you're new at this, you may have the following questions:

  1. What is a certificate?
  2. What is being certified?
  3. What is contained inside the certified?
  4. Why do I have to pay some other company (with this funny name "certificate authority") to give me one of these?
  5. Why can't I just use the standard certificate that comes by default when you install FileMaker Server?

If you’ve never set up SSL before, I’m sure you’ll have at least a few of the above questions. If you’re considering using SSL, the somewhat convoluted setup process may scare you off. In fact, unless there is a very explicit demand or requirement that SSL is used, you may decide not to even bother with it. The process of getting the certificate and the process of installing it might just seem too involved to be worth it.

And I bet many developers have made such a decision for these reasons, and they're able to get away with it, because, well, if you don't use SSL, all of the functionality is still there. Everything still seems to work just fine.

Why You Need to Use SSL With FileMaker

So, if that’s true, why do we bother with SSL? I answer this question in some detail in my session, but in short, we use SSL to prevent eavesdropping, data tampering, and impersonation. The security principles underlying these three objectives are called confidentiality, integrity, and authenticity.

DevCon Session Overview

The goal of my DevCon session was to demystify the whole thing – to break it down into parts, explain each of the constituent parts, and share why each step of the process is necessary.

The session covered the following topics:

General SSL background

  • Why use SSL: objectives and security principles
  • Difference between encryption and encoding
  • Symmetric and asymmetric encryption methods and the key distribution problem
  • Message authentication codes (MAC)
  • SSL certificates: what is being certified, what is inside a certificate, what is a certificate authority, and how digital signatures work
  • Chain of trust, root certificates, and root certificate stores
  • SSL handshake
  • Certificate validation methods: domain (DV), organization (OV), extended (EV)
  • Certificate types: single domain, multi-domain, and wildcard

Getting and installing an SSL certificate for FileMaker Server and FileMaker Cloud

  • Certificate signing requests (CSR)
  • Certificate authority landscape
  • Proving domain control: email challenge response, file lookup over HTTP, DNS lookup
  • Installing the certificate and testing
  • Alternate scenarios: multiple servers
  • Making changes to a certificate
  • Patching SSL
  • Review question: Why should the standard FileMaker Server certificate not be used in production?

Watch the Video

You can watch a recording of the session and get a copy of the accompanying slides.

Have Questions on Implementing SSL?

My team and I are happy to answer any other questions you have about how to use SSL with your FileMaker solution. You can either ask in a comment below or contact us directly.

The post Demystifying SSL appeared first on Soliant Consulting.

Voir le billet d'origine

Soliant Consulting

Were you able to catch my DevCon 2017 session on FileMaker and AWS: What You Need to Know? I used the opportunity to show how to spin up a FileMaker Server with Amazon Web Services (AWS) and discussed relevant services and concepts. If you couldn't make the annual developer conference, you can watch my session in its entirety here.

Toward the end of the session, I demonstrated an AWS service called AppStream 2.0, which offers a fully functional FileMaker Pro experience using nothing more than an HTML 5 web browser.

Get a full explanation here:

In this demonstration, we access a solution on Soliant Consulting's FileMaker hosting service,, in AWS. There is very low latency, because all network traffic from FileMaker Pro to FileMaker Server is essentially on the same local network.

Amazon AppStream 2.0

With Amazon AppStream 2.0, you only pay for the time that sessions are active, and they can be configured to auto scale on demand. When users connect, more sessions are created. When they log out, the sessions end, and you save on cost. Elasticity is a huge benefit with AWS cloud computing.

AppStream also supports federated logins from other sites, including SAML or Security Assertion Markup Language. While FileMaker on its own does not support SAML, it is found more readily in enterprise-level companies for authenticating users to several services from one authority.

From there, it is possible to have the Windows image used by AppStream configured to use SSO (Single Sign On) and log you straight into your FileMaker solution.

Security Configuration

You can also configure security to only allow access from cloud hosted subnets, and not open your FileMaker Server to the world. You can configure to allow access from your "on premises" corporate location, as well as secure, cloud based clients such as AWS Workspace or AppStream 2.0. Below is a sample scenario we showed during the session.

EC2 in a VPC Sample Scenario

Sample scenario shown during my "FileMaker and AWS: What You Need to Know" session at DevCon 2017

Of course, this is just one sample scenario and could be built out to your exact needs.

All of this demonstrates how the FileMaker platform can indeed be a good fit for an enterprise, a point FileMaker highlighted during the DevCon Keynote address - a sizable amount of its business is from the enterprise market.

Of course, you could have this built to your exact specifications, or use the hosting we provide, to get started today with FileMaker and AWS.

Next Steps

If you have any questions about FileMaker and AWS or want to learn more about, please let me know in a comment below. You can also contact my team and me directly here.

The post FileMaker, AWS, and Amazon AppStream 2.0 appeared first on Soliant Consulting.

Afficher la totalité du billet

Soliant Consulting

FileMaker's Devcon is always a special time of the year - an opportunity to learn and meet old friends, make new ones, and share information.  And this year's Devcon was extra special: record attendance!

FileMaker Inc. has just released the first batch of video recordings of the Devcon sessions. I'm happy to see my presentation on the FileMaker External Authentication options of EA, SSO, AD, OD, OAuth, and LDAP, included in this selection.

If you want to see what the FileMaker 16 platform brings in terms of new OAuth external authentication you are in the right spot.

As always, feel free to follow up with questions, here or on the FileMaker forums.

You can watch this directly on YouTube here.

The post FileMaker 16 OAuth External Authentication: An Exploration of Your Options appeared first on Soliant Consulting.

Afficher la totalité du billet

Soliant Consulting

Chicago, Ill. – August 21, 2017  –  Soliant Consulting was honored by earning the FileMaker Excellence in Education award at the platform's annual developer conference.

The FileMaker Excellence awards recognize outstanding companies for development, design, innovation, education, and advocacy on the platform. Individuals and international partners are also recognized at the annual summer event.

Soliant Consulting was recognized as a leader in FileMaker education for its dedication to helping others learn and succeed on the platform.  This includes the firm’s contributions to FileMaker’s recently-launched free video tutorials. Soliant Consulting developers also frequently contribute to the FileMaker community forums, offering insights, step-by-step solutions, and feedback on processes. The firm also regularly shares content on its website to help both developers and users learn more about the platform, including blog posts, downloadable demos, and video tutorials.

"Training and education have always been an integral part of our team culture, and I'm very proud of the contributions our team makes to the FileMaker community," shared Bob Bowers, Soliant Consulting CEO. "We're looking forward to continuing to help others learn more about the platform and build great solutions."

This is the most recent of Soliant Consulting’s distinctions at the platform’s annual developer conference. In 2015, Senior Technical Architect, Wim Decorte, was recognized as FileMaker’s Leader of the Year award for his involvement in the community, forums, and conferences around the world. Soliant Consulting also earned FileMaker Excellence Awards in 2008 and again 2012 for Partner of the Year. Soliant Consulting CEO, Bob Bowers, has also earned numerous recognitions for leadership and developing technical resources for the FileMaker community over the last two decades.

Soliant Consulting looks forward to continuing to help FileMaker developers and users learn more about the platform and build strategic solutions for the community.

  • Learn more about Soliant Consulting’s dedication to education on the platform here.
  • Watch the free video tutorials Soliant Consulting’s FileMaker team worked on here.
  • See other award winners from FileMaker’s Developer conference in Phoenix, AZ in July 2017 here.

The post Soliant Consulting Honored for Excellence in Education at Annual FileMaker Developer Conference appeared first on Soliant Consulting.

Afficher la totalité du billet

Soliant Consulting

Fourteen years ago, I attended my first DevCon in Phoenix, and it was a pleasure to return this summer and recall old times (or young times, as it were). I learned a lot, caught up with longtime friends, and even had the opportunity to present a session myself called "How Scripts Think."

View my entire session on YouTube here.

My topic was decision-making in FileMaker scripts: the fundamental concepts of script logic and a strategy for outlining decisions in your own scripts. Here's what I covered:

  • Learning the basics: logical script steps, functions, and variables
  • Creating workflow diagrams
  • Outlining your script using comments
  • Building your logical structure
  • Filling in your actions
  • Testing your script with the Script Debugger and Data Viewer

I put together a demo file that illustrates my process for building logical scripts, including some examples I didn’t have time to present as well as several templates for key logical structures. That file is available in the FileMaker Community here.

It's one of my favorite challenges to put together clear and approachable training materials that use concrete, everyday examples to introduce abstract concepts. I was especially excited about my central example this year: following a recipe to make chocolate-chip cookies.

Since I try to create as many of my own presentation images as possible, that meant conducting a big photo shoot in my kitchen while baking a bunch of cookies. And then eating them one after another, compulsively, as I sat at my desk working hard on this talk.

I hope you'll take the time to view the video and let me know whether my examples made sense to you, or if they just left you feeling hungry. And perhaps you can advise me: the next time I give a session, should I switch to leafy greens -- or cheesecake?

The post How Scripts Think in FileMaker appeared first on Soliant Consulting.

Afficher la totalité du billet

Soliant Consulting

Over a week later, and I’m still on the high of speaking at FileMaker DevCon – no time travel necessary, as it’s vivid in my memory! It felt like just yesterday that I was testing my demo file on repeat, making sure that each example was ready to show on the big screen. And then came the time to stand up in front of a crowd and share things I’m passionate about: FileMaker, empathy, and interface.

My session focused on ways to get the right answers from your users, where I showed User Interface (UI) tips and tricks. The FileMaker platform is one of the best tools out there for improving and extending your business and the way you manage your data and workflow.

So why does the UI matter? Why does the experience of the user matter? Because we’ve all grumbled at bad design. Because we’ve all yelled at our screen when a website “doesn’t make sense.” Because bad UI makes for sad users. As a developer, you have the ability to make users happier people, better workers, and more productive employees.

My slides focused on some key questions to ask your users, so that you can empathize and understand what they’re dealing with. Half of the battle is understanding what they need, while the other half is figuring out what currently frustrates them (and then figuring out how to solve that). After talking to users and planning out what needs to be created or changed, the next step is to start planning out your FileMaker solution – the workflow being key.

You’ll want to make sure that whatever you build doesn’t hinder productivity, but in fact, seamlessly lets a user move and work faster. So what are some of the FileMaker tools that you can take advantage of?

FileMaker Tools for Better UI

  • Theme & Styles / Object States
    • Consistency is key
    • Allows you to reuse a style for faster development
  • Conditional Formatting
    • Warn the user or draw attention to something
    • Provide feedback on what a user has done
  • Conditional Visibility
    • Show the fields and objects that matters
    • Hide functionality that isn’t useful to the user
  • Buttons and Button Bars
    • Consistent interface
    • Allows you to use text and icons to communicate what a user can do
    • These can include popovers
  • Custom Dialogs
    • Warn the user (more harshly than conditional formatting)
    • Provide feedback
  • Card Windows (My favorite!) / Popovers
    • Focus the user on the right stuff
    • Provide a clean and organized interface
    • Consider these a potential [smarter] replacement for dropdown and pop-up menus
    • Awesome for selecting related values and allowing searching

This list is just the tip of possibilities and features the FileMaker platform delivers to better navigate and control your users. Now this all comes with the caution that too much control is a bad thing. FileMaker Pro provides us with a slew of features that can benefit your users. If you have someone in the organization who is interested in learning about FileMaker Pro, you might find yourself with a power user, and that person can be a huge asset to helping you and the business succeed.

Ask Yourself the Right Questions for Better UI

Remember, FileMaker provides us with a set of tools; our job is to learn how they work and then get as creative with it as we can.

To get you started, here are a few questions to ask:

  1. Who is your user? (a farmer who is looking to advance his business with technology? An office worker who wishes they were still using pen and paper? A CEO who wants to streamline their business?)
  2. What does their job entail?
  3. What challenges do they face in their role?
  4. What are their pain points?
  5. What would make their job easier?
  6. What does the business need from that user?
  7. How can you add value to both your user and the business?
  8. Are you going to teach them how to use FileMaker Pro or how to use the app you build?

With the FileMaker platform, you have a flexible and fluid tool to help solve business problems and improve the user's experience. By focusing your attention on the workflow and the needs of your users, you can leverage FileMaker Pro, FileMaker Go, and FileMaker WebDirect to get the right data from your users.

A quick (and overzealous) aside: I was proud of my invisible popover for my demo file. It was a way to show off what we were going to do by creating a sort of overlay on the layout. Just one more way to manipulate FileMaker Pro and to use a tool in a slightly different way.

better ui

Watch the Video

Want to learn more? You can watch my full 2017 DevCon session on better UI in FileMaker on YouTube.

If you have any questions, please feel free to do so in a comment or by contacting our team directly here.

The post Get the Right Answers from Your Users: A Guide to Better UI in FileMaker appeared first on Soliant Consulting.

Afficher la totalité du billet

Soliant Consulting

PivotTable.js DemoPivot table reporting is very difficult to do in FileMaker. The process requires a complex combination of portals or repeating fields, ExecuteSQL, and sometimes calc fields. It’s a tough report to build, and many of our clients request export features to manipulate their data within Excel and build a pivot table externally. That’s quite a few steps just to see your data.

Fortunately, we’ve uncovered a method to creating a pivot table directly in FileMaker through a PivotTable.js integration, simplifying and speeding up the process. Explore our demo of its key functionality to learn how to implement the integration in your own FileMaker implementation.

Don’t forget to read Jeremy Brown’s step-by-step guide on how to use this demo to your full advantage.

Complete the form to receive the demo:

Trouble with this form? Click here.

The post PivotTable.js in FileMaker Demo appeared first on Soliant Consulting.

Afficher la totalité du billet

Soliant Consulting

In my DevCon 2017 session on Web Viewer Integrations, I very briefly showed an extension of the concept presented in the form of a pivot table. I had roughly 20 seconds to show it, and so I provided a quick overview of some of its features. In the waning seconds of the hour, I promised some follow up, and here it is.

Pivot table reporting is somewhat of the holy grail in FileMaker development. A quick search in the FileMaker Community discussion board revealed over 20 posts about this very topic. Unfortunately, this kind of reporting is difficult to do in native FileMaker. It requires some combination of portals or repeating fields, ExecuteSQL, and maybe some calc fields. It’s a tough report to build, which is why many of my clients ask for exports. Then they use the data within Excel to build a pivot table externally.

That’s too many steps just to see your data. We want you to stay in FileMaker, so we need to provide for them a great way to use a pivot table.

Today I’m sharing how to create a pivot table through a PivotTable.js integration and how to leverage its best features.

Example of a pivot table

Figure 1. Pivot table created using a PivotTable.js integration


Let’s start by showing the capabilities of this integration. The above pivot table takes data from a sales table and summarizes it from many different perspectives. More than 17,500 records show a date, unit price, quantity sold, and total price of products. Once you load the data into the integration via a script, you can view the data viewed from many different angles and view it very quickly. There’s no reloading of the data from FileMaker. The library does all the work on its own.

Note: This is the beauty of many JavaScript integrations. Once loaded, the data calculations and rendering moves quickly. In a hosted version of this file, it takes FileMaker less than 2 seconds to load the pivot table into the web viewer. From there, changing the perspective, adding or removing attributes, and seeing the updated perspective is almost instantaneous. The slowest part of this process is gathering the data.

Library features include the following:

  1. 13 different renders of the data, including the basic summary table you see above, heat maps, C3 charts, and even an export view that allows the user to (if they must) export the data to Excel using comma-separated values. See Figure 2 below.
  2. 22 ways to summarize the data: Sum, maximum, minimum, average, Sum as Fraction Rows, etc. See Figure 3 below.
  3. The ability to drag and drop an attribute to either the x or y axis. Multiple attributes can be used, as you see in the first picture.
  4. Filtering functionality for each attribute. For example, I can click on “Type” and remove one or more products from the summarization. See Figure 4 below.
  5. Sorting capabilities. You can sort both columns and rows in default order or by total descending or ascending.
Menu of pivot table types

Figure 2. Pivot table types

Screenshot of the menu with 22 ways to summarize the data

Figure 3. 22 ways to summarize the data

Filtering functionality for each attribute

Figure 4. Filtering functionality for each attribute

The Setup

Overall the setup is not complex in any way. I’ve worked out what is needed and have tweaked the function to make it fit a normal need in FileMaker. Here are your requirements:

1. A summary table from which to gather the data
Summary table

Figure 5. Summary table

2. The HTML and CSS code
HTML and CSS code

Figure 6. HTML and CSS code

3. The data gathered in a certain format and a script that gathers the data in this format
Data gathered in a certain format

Figure 7. Data gathered in a certain format

4. The other required libraries
Required CSS and JS libraries

Figure 8. Required CSS and JS libraries

As with my Web Viewer Integrations library, you can easily import these fields into your own custom app.

Gathering Data

You must gather and set the data for the table to properly summarize it. Follow these steps for a successful setup:

  1. Gather all data as individual arrays, with each array separated by a comma.
  2. Make the first row the header, i.e. the name of the attributes displayed in the chart and moved around.
  3. Of course, the order of the data gathered matters. Gather your data in the same order as the header. In the example above, the “Sales” header is first, and the sales data comes first in each array.
Sales data comes first in each array

Figure 9. Sales data comes first in each array

  1. Using FileMaker 16’s new JSONSetElement() function, you can set a field to gather the data.
JSONSetElement() function

Figure 10. JSONSetElement() function

  1. Then use the ListOf summary field type to gather all the data during a script. Notice this script first sets the header row and then adds to that variable the LIstOf data.

Of course, you can gather the data gathered in a multitude of ways. It all depends on the circumstances you have for the given custom app.

Final Tweaks

As with other Web Viewer Integrations, you can edit the style of the pivot table, but everything else is ready for you to use. All you have to do is provide the data, and you have a complete, multi-function pivot table.

I’ve made some other tweaks to this to make it easier to use and more useful for you.

  1. I added another field to the pivot table to ensure you could easily add any field. In this demo, you’ll see Customers. You would need to collect the data again to use this. In this case, the data is collected with a new script called “Gather Data_Loop” and it does that, setting each array needed using JSONSetElement(). The script gathers data from the sales table and from the related table of Customers.
  2. I set up some defaults to the chart. My chart, in the function, is set to render the table showing the sum of Type in the column and Year and Month in the rows, as shown in Figure 11.

Chart parameters set in the function

Figure 11. Chart parameters set in the function

How to Set This Up in Your Custom App

It is very simple to push this to your custom app. Just follow these four steps:

  1. Import these fields into your custom app into a new table. You’ll be bringing over my data, so you may want to erase that data once you have the fields imported.
  2. Copy the HTML_Calc calculation field from this demo to a new field in this table.
  3. Write a script to gather the data. See mine as a template. Set the data into the field.
  4. Accept or change the defaults that are in this library.

That’s it. You now have a fully-functioning, multi-use pivot table!

PivotTable.js in FileMaker Demo

I’ve done enough research to know that just about anything that you want to do in FileMaker that isn’t already possible can be accomplished by using a web viewer and a JavaScript integration. Check this one out and see if it works for you.

Good luck! Please feel free to ask questions in the comment fields.

The post How to Build A Pivot Table in FileMaker appeared first on Soliant Consulting.

Afficher la totalité du billet

Soliant Consulting

Workgroup Conundrum WhitepaperIdentifying the optimal path for your workgroup processes: off-the-shelf solutions or custom-built solutions

Every company has to start somewhere. Along with this business adage comes
the mentality that not only does a company have to get scrappy in its early
days, so do its tools and technology. Most start with the basics – such as a spreadsheet tool like Excel – to manage their internal data and processes. And in the early days, it’s enough.

However, as a company grows, so do its workflows and teams. Businesses
quickly outgrow their trusted tools, and teams become silos. As teams are split up by responsibilities and goals, they often find themselves facing different challenges from other business units. As a result, technology working well for one group, miserably fails for another.

We address this problem, which we refer to as the workgroup
conundrum, and potential solutions in the A Journey Through the Workgroup Conundrum white paper.

Please complete the form to receive the whitepaper:

The post A Journey Through the Workgroup Conundrum Whitepaper appeared first on Soliant Consulting.

Afficher la totalité du billet

Soliant Consulting

Last week Soliant Consulting joined 11,000 attendees at the 2017 AWS Summit in Chicago. This is the third year someone from my team has attended the event. Much like moving to the cloud, AWS Summit Chicago has grown significantly. Last year, less than 8,000 people registered for the conference. I noticed some visible growing pains as several sessions filled up very quickly and organizers struggle to manage the influx of attendees on the first day.

An Overview of AWS Summit Chicago

The AWS Summit included many informational sessions about all types of services focused on both developers and system administrators. Every session has an associated skill level, allowing attendees to quickly determine if it's a good fit for them. For example, the 200 level topics are aimed at someone with no real experience with the technology subject. In addition to these sessions, AWS Summit included "The Hub," where sponsors and vendors showcased their products and services in everything from infrastructure and monitoring to backups and security.

Click to view slideshow.

AWS Keynote Insights

The AWS Summit Chicago keynote included two examples of how using the right tools in AWS minimizes costs and maximizes performance.

Redshift Spectrum

The first example showed loading one exabyte (1,000,000 terabytes) of structured data into s3 and then running a complex query on that data. The presenters started running the data through Apache Hive with 1,000 nodes, let it run for a while, and then determined the process would take five years. They then ran the same data through Redshift Spectrum, and the process took 155 seconds. That’s more than 6,451 TB/S! Keep in mind, though, Hive can run on unstructured data but Spectrum can't.


Morning Star's CTO, Mitch Shue, provided the next example on his company's migration to AWS. Their first stage of the process was a like for like migration. Their team moved from Elastic Compute Cloud (EC2) instances and started processing data using S3 and Lambda, a change that shortened its nightly import time to only two hours. As a result, Morning Star decreased costs by 97%.

Lambda works by running small jobs on AWS. Think of it as moving the line where AWS is in charge of the infrastructure. For EC2, AWS is in charge through the hyper visor.  With Lambda AWS also manages the OS; you only need to worry about your code and only pay for the seconds and memory your code requires to run. Lambda is the optimal choice for jobs that don't run all of the time or for parallel work, such as nightly imports.

Want to hear more? Watch the full AWS Summit Chicago keynote here.

AWS Elastic File System (EFS)

One of my favorite talks was the deep dive on AWS EFS given by Darryl Osborne. He discussed EFS, S3, Elastic Block Store (EBS), and their differences. For example, EBS can only be accessed by one EC2 instance, much like a hard drive in a physical computer. It can, however, have very high IOPS and decent throughput, depending on the type of storage you pick.

With S3, you use APIs to access data. You will probably need to rewrite your programs to access your data, but you'll benefit from inexpensive storage and very high throughput. EFS has slower throughput than S3; its throughput is related to the amount of data you have stored in EFS. EFS, much like S3, is elastic storage, so you only pay for what you need. EBS requires you to provision the disk for a particular size. During the demo, Darryl showed how a T2.micro can perform better with EBS than an m4.2xlarge over a short period of time due to T2's burst performance capabilities.

Looking forward to AWS Summit Chicago 2018

I strongly recommend both developers and system administrators attend next year's event, as the conference includes many topics for both groups. I enjoyed attending on behalf of Soliant Consulting for the 2017 event and look forward to attending for our team's fourth year in 2018. If you have any questions about the event, please ask in a comment below. I'm happy to provide answers and additional insights about the conference.

The post AWS Summit Chicago 2017 appeared first on Soliant Consulting.

Afficher la totalité du billet

Soliant Consulting
Day 3 started fresh, clear, and crisp, outside anyway. Inside my head, I was a bit tired and muddled. The week thus far refueled the inspiration gas tanks, but the actual gas tank that ran me was a little low.

Staying up late, talking, and walking half a mile to the conference area depleted my energy. A bit bedraggled, I headed out the hotel room door and began the day.

Day 3 Sessions

My Soliant colleagues, Anders Monsen, Dawn Heady, Mislav Kos, and Mark Baum spoke during the first half of the day, and they killed it. Mark was his usual boisterous self and actually broke out in song while writing a script. Anders delivered precise techniques for working with Perform Script on Server (PSOS) and reporting, Dawn presented some great real-world scenarios during her session on security, and Mislav helped to demystify SSL by explaining how to get and install a custom certificate. Their sessions were extremely useful for both beginner and advanced users. I bounced back and forth between these sessions to support my colleagues and to see what I could learn.

Click to view slideshow.

Snack time came after the morning sessions and breakfast. I weaved my way through the large food area to say hi to friends I've seen for many years and those I’ve just met this year. Colin is one of the latter–someone who came to his first DevCon this year. I helped him out as I could. And I met Sarah and Clarke, two other first-timers. I enjoyed meeting them and hearing that this DevCon has been amazing for them as well.

The day continued with classes, breaks, and food. I spoke once more in a session called, “Refactoring Your Skill Set: Changing How You Do the Same Thing.” In this session, I pointed out that there are many ways to solve a use case in FileMaker, and we shouldn’t stick to one because it's our favorite or the one with which we have the most experience. Instead, we looked at common use cases and the different ways those could be solved.

Closing Session

The conference ended with the closing session during which FileMaker Inc. recognizes outstanding contributions in the community. Two awards are given to people in the FileMaker community, and nine other awards are given to FileMaker Busniess Alliance (FBA) members. Soliant Consulting won an award for excellence in education.

After the closing session, my Soliant colleagues and I gathered to celebrate our win and the great week. Ten speakers spoke in twelve sessions throughout the three days, and since we rarely see each other, we enjoyed catching up.

The DevCon experience was inspiring, challenging, tiring, and exciting. There is so much currently going on in the platform. People all over the world are doing amazing things with FileMaker Pro and the other apps, and I am glad to be a part of it. I’m inspired to continue my own learning on such things as FileMaker Data API and move forward and help others learn the platform the right way the first time.

The post FileMaker DevCon 2017: Day 3 appeared first on Soliant Consulting.

Afficher la totalité du billet

Soliant Consulting
The excitement of Day one bled over into Day two, as if we all were dreaming about the great things happening in the FileMaker platform. As I joined the rest of the 1,600 attendees, I could feel the excitement of the day ahead. It did not disappoint.

I woke early to get ready for the day. I wanted to attend some sessions and prepare for my first session, “Web Viewer Integrations.” The hawks outside my hotel deck greeted me with their cries, giving a perfect zen-like atmosphere to the morning.

I ventured down to the conference area and joined the community for some breakfast. Many people, arrived at breakfast close to the end of the hour. It seems we all stayed up a little late the previous night! The food was good, plentiful, and healthy. After downing some oatmeal and bacon, I ventured into the classrooms.

Day 2 Sessions

Day two's lineup of sessions promised to be full of great content. There were both beginner and advanced sessions showcasing innovation and great insights for attendees. One even included a demo of a great product by one of the conference’s exhibitors. I enjoy the beginner sessions, and how many of them overlapped in topics. I used to be a teacher, and the more a student is exposed to the same idea, the more the idea will be internalized.

I started my day by attending Anders Monsen’s session on JSON parsing and then the beginner session, “Relationships Thoroughly Explained.” Both were excellent in scope and great for attendees.

I also attended Wim Decorte’s session, "External Authentication Options: EA, SSO, AD, OD, OAuth, LDAP" as well as a session titled, “Increasing Code Quality While Staying Lean” with John Sindelar of SeedCode.

I popped into Mike Duncan’s session, "FileMaker and AWS: What You Need to Know" and Martha Zink’s session, "Get the Right Answers from Users - UI Tips and Tricks" to support my colleagues. Both seemed very popular; the content resonated well with attendees.

Click to view slideshow.

This is my first year presenting at DevCon, so I spent part of the morning focused on preparing for my session. I had to clear my mind and make sure I was ready to go. I tested my computer many times, backed up my demo files, and recharged my clicker. I wanted to be ready. People say I "overthink" it, but I know how I need to get ready. As a teacher, I always had to take a moment before the kids burst through the door. Leading a session at DevCon is no different. (Although, I guess, the attendees are a bit better behaved.)

I enjoyed the new “FileMaker in Action” sessions, during which users of FileMaker are invited to speak on their successful use of the platform in their daily business. Martin Williams and another person from Volvo spoke of how their use of FileMaker has transformed the way they can respond to machine breakdowns out in the field. When a machine reports an issue, FileMaker picks it up and immediately tells the dispatcher the nearest dealer to that machine. All of that would be hard to do in a basic spreadsheet.

Women of FileMaker Luncheon

Day two also included the annual Women of FileMaker luncheon. I, obviously, did not attend, but I heard from my female colleagues that it was fun and empowering for the women who did. My coworkers got to network and meet wonderful women while discussing how to propel more women forward in the FileMaker community. Gillian Gentry, one of my team’s Managing Directors, stood up to discuss the Women of FileMaker scholarship program, which helps send more women to DevCon every year. Ladies, if you couldn’t make it this year, I highly suggest getting involved with the group and attending next year!

Click to view slideshow.

A Relaxing End to the Day

And, of course, the day finished with a pool party. Food and drink stations were scattered around the huge pool complex, and all of us FileMaker people, in our name tags and swimsuits, stood around and talked, ate, and drank. Some of us even swam.

Day two of DevCon was full of learning, people, food, and community. The sessions were strong and informative, and the people were excited and engaged. I can't wait to see what day three brings!

The post FileMaker DevCon 2017: Day 2 appeared first on Soliant Consulting.

Afficher la totalité du billet

Soliant Consulting
Contrary to my last post, the sky wasn’t clear for most of Day 1 of DevCon. It was cloudy, cool, and rainy, the kind of rain that might dampen enthusiasm for an upcoming event. But that didn’t happen here.

There was no stopping the excitement that over 1,600 developers (a conference record) brought to the conference. Let’s examine what was exciting about today.

Training Day

FileMaker hosts a training day for the first day. There’s the typical beginner, intermediate, and advanced sessions (the advanced session was led by Soliant Consulting’s CEO, Bob Bowers). However this year, FileMaker added two courses that allowed many more to get involved: A project-management course led by Sara Severson and a training session called, “Connecting FileMaker To the Web,” led by Todd Geist. The wide scope of these sessions gave many more people a reason to attend.

Sara Severson’s session called, “Demystifying Agile Development: Practical Tools for the Risk-Averse” gave the very-important project managers in the FileMaker community a place to learn and grow their skills. One of her assistants, Annie Tobiasiewicz, had this to say:

Sara did a fantastic job teaching and promoting agile methodologies to be used for projects. The training session was a nice size that allowed for discussion between attendees in small groups. Not only was Sara able to cover all her planned material, but she was also able to answer questions that came up throughout the day. Attendees gained a larger understanding of agile in hopes that they can try it in their projects when they return home.

Bob Bowers' advanced training session was extremely useful to all who attended. Beth Bennett reported that Bob’s session covered such advanced techniques as JSON, using card windows in creating pickers, virtual lists, Perform Script on Server, and ExecuteSQL. The attendees participated in many activities that gave them a taste of all that can be done. Tweets flew out of that session filled with the very awesome tips and tricks that were presented. Tweets from actual birds also bounced around the room as birds, taking up residence in that space, conversed together about the topics in the session and occasionally flew up to the screen to get a closer look.

Click to view slideshow.

Keynote Session

After a long day of training, sitting, and participating, you’d think we’d be done and ready for food. But no. The main event was yet to come. At least an hour before the doors to the grand ballroom opened, people were waiting in the hallway, ready to get in and get the best seat for the opening session. People were ready – they were excited to hear about the current platform and what’s yet to come.

And boy, is the future bright and awesome.

The content of the keynote was great. Six numbers represented the current state of the company:

  • 16: the Current version of FileMaker
  • 1,600: DevCon Attendees
  • 3,000,000: Downloads of FileMaker Go
  • 78 quarters of profitability
  • 1,300,000 subscriptions of the platform
  • 40% of FileMaker expenses spent on Research & Development

Andy LeCates, the Director of Product Evangelism, gave another chapter in some amazing customer stories from around the globe, including Soliant Consulting's story about the Luke Commission with Ross Johnson and Josie Graham. He also spoke of the commitment of FMI to the platform for businesses of all sizes. Andy discussed the concept of bimodal IT, as well. If you're interested in learning more about that topic, check out insights on bimodal IT from our CTO, Steve Lane.

And then of course, the FileMaker Product Team got up to talk about the future of the platform. I can't share those details quite yet, but there are some exciting features ahead!

The keynote was great and filled with amazing ideas. But what struck me the most was the passion for the platform that each FileMaker team member -- from the president to the director to the project managers -- had in their own way. Each person brings to the platform their heart and soul to make sure it is the best it can be for the citizen and professional developers. It's exciting to see where they want the platform to go, keeping it strong for advanced developers and making it easier for new developers to come to the table.

Evening Reception

The night continued in the welcome reception area. Food and drink were stationed all throughout the large exhibitor hall, within an arm’s reach. And it was a time to say hi to old friends, meet new friends, eat, and simply enjoy time with fellow developers around the world.

As I was heading back to my room to practice and remember what I want to say in my session on Tuesday, I walked by many people still milling around the bar, in the lobby, and basically, anywhere there was space. Everyone was talking about FileMaker, continuing to catch up, and getting to know others.

The excitement that started on day one is sure to carry over into the rest of the week, allowing the sessions to be fun, engaging, and full of great content. I can’t wait to attend some sessions and to present my own (and get mine done with!)

The post FileMaker DevCon 2017: Day 1 appeared first on Soliant Consulting.

Afficher la totalité du billet

Soliant Consulting

The traditional IT model is broken.

Since its inception in the 90s, the IT department has grown in its focus and responsibilities. Unfortunately, instead of segmenting the department into focused workgroups, most organizations have fallen into seemingly organized chaos.

Projects take precedent, pushing the regular responsibilities of operations and maintenance to the wayside. Or, engineers drag their feet to create new things and innovate within legacy systems because they have too much maintenance work on their plate. It creates a stagnant environment for companies, a dangerous situation to be in in 2017.

But this problem can be solved. It really just comes down to resources. And there’s a way to segment everything as a solution.

Gartner coined this solution as Bimodal IT.

What is Bimodal IT?

Bimodal IT splits the IT department into two teams, or, as the definition implies, two modes. One focuses on all technology and applications that absolutely need attention. These core systems, legacy applications, and solutions that keep the wheels on absolutely need maintenance. They require a dedicated team, so they should have one.

The second team focuses solely on innovation. What can they build to make the business more efficient and successful? How quickly can they build it? This group is made up of “dreamer” engineers who envision new technology to push their companies forward.

Development Cycles

Each group, of course, will operate completely differently. The first operates much more slowly with longer, passive development cycles. Updates to core technology are no joke. They must be vetted, tested, approved, and then slowly rolled out to each user in the company. It takes time, patience, and a ton of buy-in.

The second team, however, is the hare to the tortoise. Their innovation requires speed and agility. This team prescribes to rapid application development – things are built, updated, and enhanced more quickly, with short, tight, development cycles. A decision is made, and things are built in a short window afterward.

Business Involvement

Of course, as these different teams have different goals, they also have different involvement levels in the company. IT often only interacts with other teams if a problem arises with a system or if an update will affect users. Think about your last email from IT. It was because a system was “undergoing maintenance,” or to close a ticket on an issue you reported, right? These IT team members fly under the radar. Business leaders rarely interact with them, and their work, while critical to the business, hums under the surface, garnering little acclaim or attention. The first mode just doesn’t require a ton of oversight from other departments.

This second IT mode operates completely differently. They have ideas that will change the business, and that means they need buy-in from everyone else as well. They are often tasked with presenting their ideas to other departments, effectively explaining how things will work and the impact the proposed new technology will have. They’re responsible for delivering on their promises and sticking to strict development deadlines. Everyone is watching them, especially those whom the new applications or solutions will affect.

As a result, this second mode also needs more oversight. Your business will need a strong, skilled leader, who can balance pushing his or her team to be more innovative while overseeing them and understanding their work and goals.

Team Members’ Responsibilities

Think about your IT department. Many of those team members fall under the radar, don’t they? Their work is crucial – they literally keep the business running, but most of their coworkers and colleagues don’t realize that.

Sometimes the flashier ones make a splash, though. These are the IT team members pushing for new systems, new development, and a fresh look at their technology. If you’re following our line of thought here, this is an intuitive way to split up your team to achieve bimodal IT.

More traditional developers who have been with the business longer often fall into the first team. They know the systems and are the experts. They know the ins and outs of the systems that cannot fail and often know the tricks to get things humming again that others don’t.

Developers hungry to try something new and “go fast and break things” often fall into the second mode. They want to push boundaries and invent something new. They also often have been formally educated with the most recent development techniques and have the skills to pull off rapid application development.

Risks of a Bimodal IT Organization

Of course, any major structural change also comes with its risks, and there’s been plenty of discussion on all the reasons why a bimodal IT organization just can’t work. Here are a few arguments against it:

Bimodal IT Builds Walls

Instead of all your best and brightest technology specialists and developers working together, you officially pit them against one another, possibly creating more problems than solutions. Splitting instead of pooling your resources can halt innovation and create tension between teams vying for budget, time, and attention.

Modern Technology Connects

More technology bridges old with the new to drive efficiency within businesses. After all, any tech leader will agree that merging two systems and/or applications will make things easier on other departments. Unfortunately, this creates confusion within a bimodal IT organization. Which team determines which customizations should be made to the new add-on technology? Who deploys it once it’s finished? Which team maintains the technology? As integration becomes more popular, lines become blurred within a bimodal IT strategy.

Legacy Systems Never Evolve

If you hand off legacy systems to a team focused strictly on maintenance and never on innovation, you’re going to have a difficult time moving forward. And when core technology can’t move forward, neither can the business. No new applications can drive sustainable growth if legacy systems still hold entire teams back.

Developers Get Disgruntled

If you could pick between working maintenance on an old system and completing IT tickets or collaborating for an interesting and innovative solution, which would you choose?

Right, it’s an obvious choice for most in the IT world. If you give your developers and engineers the choice, you’ll most likely end up with lopsided teams. If you make decisions without their input, you risk disgruntled team members and employee churn. Pigeon-holing your IT team members won’t be easy.

And if you’ve filled out the second, more agile team of your bimodal IT organization, it will be difficult to recruit. That creates a very big problem in the long run.

The Reality of Bimodal IT

For many large businesses, a bimodal IT strategy makes sense. They can hire an entire new team of software and application engineers for internal development work and ask them to build visionary solutions for the company. They can afford to hit a few bumps when hiring resources and trying out new things. They can have a project or two crash and burn and absorb the costs of failure.

Medium and small-sized businesses, on the other hand, quite simply can’t. Resources are tight as it is and the idea of hiring an entire team for a few innovative applications feels foolish, because, well, for smaller businesses, it absolutely is.

That’s why our team acts as businesses’ second IT team, as their innovative arm of the business. Our team of certified and innovative developers, solution architects, and UX consultants, help you dream up the perfect applications, build it, and then launch it. We help your “first mode” team learn it and then teach them how to maintain it, adding it to your depository of owned technology.

Then, when you’re ready, we move onto the next project you have in mind.

If your business isn’t in the right place to build a bimodal IT organization, don’t. We can serve as that team for you and deliver rapid application development to keep your business innovative without the onerous overhead.

Contact our team today to get started.

The post Bimodal IT: A Buzzword, a Solution, or a Smokescreen? appeared first on Soliant Consulting.

Afficher la totalité du billet

Soliant Consulting

All is ready for FileMaker developers here at DevCon 2017 in Phoenix, AZ. The sun is hot, the sky is clear, and the cacti are sharp. Inside the sprawling campus of the conference center, developers from all over the world are arriving after traveling the 30 miles or so from the airport in a Lyft, Uber, or taxi. Even on Saturday, quite a crowd of FileMaker developers had gathered. I could tell who they were: computer on the table top and beer in hand.

There is a lot of excitement about this year’s FileMaker Developer conference. That excitement is not misplaced. This year’s conference is looking to be a great one in many ways.

First, we are working with an exciting release: FileMaker 16, which is a game changing release, full of features that help better secure our data, connect to web services, and build better workflows for our customers.

Second, the sessions that are scheduled are astounding, not only in the content but the diversity. Let me describe the highlights:
Tomorrow is the training day. If you’re here at DevCon, but haven’t signed up yet, I encourage you to do so. Sara Severson and Bob Bowers are leading two Training Day sessions about Project Management and Training for Advanced Developers. Todd Geist has a training session, "Connecting FileMaker to the Web." That’s an intriguing course as I greatly enjoy extending FileMaker’s functionality with web viewer and JavaScript integrations. There are two other Training Day sessions: "FileMaker Training for Beginners" and "FileMaker Training for Intermediate Developers" led by James Medema and Chris Ippolite, respectively.

Tuesday and Wednesday are full days of sessions, each one worthy of attendance. Soliant Consulting team members are presenting twelve sessions. I’m excited and nervous for my sessions titled, "Web Viewer Integrations" and "Refactoring our Skill Set: Changing how we do the same thing." Outside of speaking, I’m looking forward to Mike Mitchell’s session “Abstraction and Indirection in FileMaker” and Ibrahim Bittar’s session called, “FileMaker is Not an Island.” New this year is a group of sessions titled, “FileMaker In Action.” In these sessions, users of FileMaker will present how the platform is making their work lives easier.

And third, the community. FileMaker developers are a great group of men and women from all over the world that are passionate about the platform. We love talking FileMaker. We love talking about the ‘best’ way to write a script, and we love making our users more efficient with their work inside the custom apps we build. At dinner, in the hall, at the bar, or at the pool party, there are folks ready to talk and argue, show off, and ask questions.

I look forward to the long week here in the oven (outside) and refrigerator (inside). See yah there!

The post FileMaker DevCon 2017: Day 0 appeared first on Soliant Consulting.

Afficher la totalité du billet