Aller au contenu

Soliant Consulting

Membres
  • Compteur de contenus

    124
  • Inscription

  • Dernière visite

    jamais

À propos de Soliant Consulting

  • Rang
    100

Profil général

  • Genre
    Non précisé

Profil FileMaker

  • Certification
    FileMaker 15 Certified Developer
  1. Getting Started with FileMaker and REST

    Representational State Transfer (REST) defines a set of standards and principals commonly used to provide web and application developers an Application Programming Interface (API). Built atop the HyperText Transfer Protocol (HTTP), REST is commonly used for viewing web pages. You are currently reading this blog post using HTTP. FileMaker and REST In every HTTP request, there is a specified method. If you type in a URL and press enter, the Method used is GET. If you submit a form on the web, you most likely use the POST Method. The difference is that a POST request can include a payload that is not part of the URL. GET requests, if they include data, will include a question mark at the end of a URL and path. This is followed by data that usually appears in key-value pairs. RESTful services may or may not use key-value pairs and instead include all needed data in the URL path. Usually, data is transferred in XML or JSON as part of a POST payload in the HTTP request. In general, following REST methodology, a GET request is used to read data, whereas a POST request is used to create a resource. These correlate to database interaction known as CRUD, or Create, Read, Update, and Delete. Modern versions of HTTP also support PUT and DELETE methods, which REST then commonly translates to Update and Delete database operations. PUT and DELETE are functionally identical to POST requests, the only difference being that the method specified is different. This also includes encrypted in-transit information if you are using SSL and specify "https" in your URL. FilerMaker "Insert from URL" Script Step With me so far? Good. So how does FileMaker work with RESTful APIs and web services? With FileMaker 16, there was a lot of changes to the Insert from URL script step that allows you to use cURL functions. This also works across the entire FileMaker platform, so this includes server-side scripts as well as WebDirect and FM Go. Anatomy of the script step: Figure 1: Configure the script step Figure 2: Specify target The script step is configurable with several options. You can specify a Target, which can either be a field or a variable (see Figure 2). To specify a variable, select that option and give the variable a name you can reference it with, so you can use it later. Whether you use a global variable or not depends on where you need to use it. Then specify the URL, which is the base URL and usually the complete path to the resource you are working with. Specify cURL Options Then there are the cURL options, which give you access to an entire library of functions for handling web requests. FileMaker supports quite a bit, but not all, cURL functionality. I recommend checking that documentation. There are a couple points of interest when working with cURL options in FileMaker, specifically. The syntax for specifying variables can be confusing. You create a variable like you normally would, with a single or double dollar sign. However, when specifying the calculation to result in your desired cURL options, the variable name is INSIDE the quote marks, like so: "--data @$myVariable" This makes more sense if you are familiar with using the curl application with command line in a terminal window. For example, to upload a file with a command line, you use the atmark (@) and then the path to the file you want to include. Similarly, an atmark tells the cURL options in FileMaker where the variable you want to include resides. Referencing variables this way will also take care of any needed quoting, so your data does not break during transit. When you send any kind of payload in the HTTP body using "--data" or "-d", then POST is used by default as the method. If you need another method, you can specify it with the "--request" or "-X" flag in your cURL options. Another point of note: using the "-D" or "--dump-header" option that allows you to get any returned HTTP headers and store them in a variable. This is especially important, as many RESTful services will return error codes as part of the HTTP header returned. By the way, most cURL options are specified with flags, like "-D" above, but most also have the option to use a more verbose variant, which can be more readable, like "--dump-header". They both work the same. One more...since most RESTful services expect the payload to be JSON, you will also want to specify that as an HTTP header, like so: " --header \"Content-Type: application/json\"" If the payload is XML, for example, you would change the content-type to match what you are sending, like text/xml. This will depend on the web service you are integrating with. Troubleshooting "Insert from URL" Scripts Trying to debug and troubleshoot when working with Insert from URL results can present challenges. It's a little like working blind, where you construct a request and send it off without seeing how the request is being received on the other side. There are a couple websites that allow you to create endpoints that log requests and then view them. In my experience, however, they can be unreliable or slow. To that end, I have written my own debugging tool that helps return information about requests sent with FileMaker. You can use it for free and put it on any web server that can serve PHP: https://github.com/SoliantMike/PHP-HTTPDebug If you already have FileMaker Server, you can simply enable the web server with PHP and use it from there. Once hosted, simply point your Insert from URL script to that PHP scripts URL and test any cURL options you need. Results are returned in plain text instead of HTML, since you would view the output in a FileMaker field or variable anyway. Other Factors Concerning FileMaker and REST You can encounter a host of other factors involved when working with APIs and web services. Those can include oAuth or information in your HTTP headers, such as signed requests or size information. It all depends on the service with which you integrate. This guide should help you start with the basics. Resources REST (Representational state transfer) CRUD (Create, read, update and delete) Available cURL options in FileMaker The post Getting Started with FileMaker and REST appeared first on Soliant Consulting. Voir le billet d'origine
  2. What is the FileMaker Data API? Released in beta with FileMaker 16, the FileMaker Data API allows you to open up your FileMaker data to other systems. This makes the sharing of this data more straightforward than it’s been in the past. The functionality opens up your data capabilities through a variety of potential integrations. You can access your FileMaker data from any system that can make HTTP calls and accept JSON data in response. This creates hundreds, if not thousands, of opportunities for your organization. In the past, accessing your FileMaker data from outside systems required complex programming in XML, PHP or related technologies, often requiring the creation of middleware. With the FileMaker Data API, external systems can access FileMaker data directly, via the widely-used REST approach. This simplifies solutions and reduces the need for middleware or additional libraries such as ODBC. What Do You Need to Use the FileMaker Data API? You need a FileMaker Server license to use this functionality. How Can You Use the FileMaker Data API? The possibilities for this functionality are endless and ultimately depend on your business. The FileMaker Data API opens the door to your FileMaker data to work with other systems. This enhances your offerings and better serves your key audiences. You now have the capabilities of other systems at the disposal of your FileMaker data in a newer, faster, and more secure way. But what does this mean exactly? Here are a couple of use cases for several industries to give you an idea. Higher Education Colleges and universities can utilize the FileMaker API to bring data from various FileMaker solutions into common learning platforms. For example, they can connect Blackboard, a popular technology for student-teacher communication, with departmental systems that contain department-specific or course-specific information. From Blackboard, students could also modify data within individual departments’ FileMaker databases. Printing Large printing-software packages such as Heidelberg Prinect could query data from more agile line-of-business FileMaker systems. For example, sales leads stored in a FileMaker system could be queried in order to generate jobs and projects in the main Heidelberg system. Food & Beverage Companies with a wealth of product, distributor, customer, and other types of data at their fingertips have a significant opportunity to customize their offerings. First, however, they need to analyze their data with complex reporting to uncover necessary insights. With the new FileMaker Data API, data managers can connect their clients’ account data with Tableau. This popular data analytics platform makes analysis much easier and streamlined. School Districts While K-12 schools continue to heavily emphasize parent involvement in students’ education, many cannot determine the best way to communicate with parents about their students without reducing teachers’ time with students. For schools using a FileMaker database to track students’ progress, they can bridge the gap with a common communication application. This automatically pulls data from FileMaker and sending a daily update report to parents without monopolizing educators’ time. CMS Integration It’s been possible in the past to share FileMaker data with widely-used content management systems (CMS) such as WordPress, Drupal, and Joomla, to name a few. The FileMaker Data AI makes it faster and simpler. CMS users can inspect data stored in a FileMaker database, and integrate that data directly into web pages. Users could also send data back into FileMaker. For example, they can addresses and other profile information inside WordPress. This data would then be sent back and stored in a FileMaker System. Enterprise Integration Platforms Enterprise Integration Platforms such as Informatica and Dell Boomi bring together data from many disparate sources across an organization. For example, an organization may wish to bring together data from Salesforce, ERP systems, EDI systems, CMS systems, and distributor catalogs. With the FileMaker DATA API, these platforms can easily query data from FileMaker and include it in their overall integration architecture. Infinite Capabilities Of course, these are just a few examples. The FileMaker Data API can bridge thousands of applications to drive more power with your data. You can connect Quickbooks to FileMaker, pulling customer data into your invoice process automatically to make your internal workflows more efficient. Through a Toggl-FileMaker integration, you can report on how your employees spend their time by data organized in FileMaker, such as project or task data. We recommend brainstorming how your existing business applications could work together to improve efficiency and speed up your workflows. Then, you can move onto exploring other systems and how you can gain greater insights by connecting them with your FileMaker data. How Much Does the FileMaker Data API cost? This is a trickier question. At this point, it’s unclear how FileMaker plans to license this functionality. They probably won’t announce their intentions until FileMaker 17 is released. We don’t anticipate the FileMaker Data API carrying a significant added cost for typical users. Our guess is that costs will be data volume based, as opposed to call volume based. In other words, the more data you plan on sending through the API, the more you should expect to spend. This is all conjecture, though. We I do recommend getting an idea of what kind of value you expect so that when pricing is released, you can weigh costs versus benefits. Next Steps While still in beta, this functionality is already quite powerful. You can start exploring benefits for your organization right away and prepare for a full version launching with FileMaker 17. Since the announcement of the FileMaker Data API, we’ve been helping our clients understand and leverage its capabilities. We’ve been empowering organizations to connect their FileMaker data to external systems for years through our free SimpleFM and fmDotNet tools and have years of experiencing in this area. To showcase this experience, we will be delivering a session at this year’s FileMaker Developers Conference. Join us for “IoT and the FileMaker Data API: Sending Sensor Data to FileMaker Server from a Raspberry Pi.” We’re happy to help you navigate the best path forward with the new API. Contact us to schedule a call with our team of certified FileMaker consultants today. For a great introduction to how to use the FileMaker Data API functionality, check out this helpful article my team wrote. The post Your Guide to Using the FileMaker Data API to Enhance Your Application appeared first on Soliant Consulting. Voir le billet d'origine
  3. Zulu Time and Duration Formatting with FileMaker

    Occasionally, I will save bits of code in small files to reference later. This is one of those files. I have to warn you though, if you are triggered by discussions of Time Zones and Daylight Savings, you can exit here. I will discuss them in the context of FileMaker functions to be compatible with other standards and systems. With that in mind, please forgive the following preamble if you are already familiar with the concepts. Time Zone Concepts The concepts of time zones were formalized in Greenwich, England, where it was decided that it (Greenwich) would be the first zone, or mean, and all others “zones” would be offset from there. This really drives at the underlying concept that there are two types of time; a universal time that is the same worldwide, and the relative time that we use in different zones. Relativity I say "relative," because the observed time is relative to where the sun comes up in your part of the world. Noon is when the sun is highest in the sky at 12:00 PM at relatively the same time of day. Similarly, we have breakfast in the morning (a relative term) around when the sun comes up and dinner when the sun goes down. That way, no matter where you travel around the globe, the same relative time is observed. You don't have to eat breakfast at 11 PM. Coordinated Universal Time, or UTC Since time zones were invented in Greenwich, England, they defined it as the time zone by which others are measured. That zone is known as Greenwich Mean Time, or GMT. Every other time zone is measured as offset from GMT. Where GMT is an actual time zone, UTC (Coordinated Universal Time) is the standard that time zones are set by. UTC time never changes because of daylight savings; only certain time zones do. Functionally, GMT and UTC are the same time, but one is a time zone. The other is the time standard. Standard Formats for Sharing Information It is common practice to define fields in your database for creation date and time as well as modification date and time. However, those are commonly defined to use the relative time. This can lead to unforeseen issues, such as a user in California creating a record one hour after a user in New York but will appear to have been created two hours before the user in New York. This occurs because of the local times in both those places are in different time zones. If you have a multi-regional business or do business globally, you might want to consider this while designing your system. At a minimum, you will want to use a standard definition for everyone in your system, for example, the time on the server. It might still change for daylight savings, but at least it changes at the same time for everyone. That can also create issues if you are considering record syncing. You'll need to handle records from different time zones based on their modification dates, among any other business rules. Of course, you can always use the FileMaker Function Get ( CurrentTimeUTCMilliseconds ), which will return the number of seconds that have passed since 1/01/2001. It uses the universal standard for time. If you use syncing at some point, you may want to consider adding a field set to auto-enter the Get ( CurrentTimeUTCMilliseconds ) function. Zulu Time "Zulu" is the phonetic alphabet name used by NATO military for the letter Z, which stands for Zone, as in Time Zone. This is a standard for writing UTC time that follows a certain format, starting with the largest measure of time. This is an agreed upon format documented in ISO 8601. You have several options. A timestamp in Zulu format would look like TZ. That is a date "YYYY-MM-DD" with the four-digit Year, two-digit month and two-digit day, "T" for "time," followed by a time formatted as "HH:MM:SS" with hours, minutes and seconds, all followed with a "Z" to denote that it is Zulu format. This format is used quite a bit, especially in calendar applications such as Calendar on OS X or Microsoft Outlook. Creating a Calculation in FileMaker While FileMaker has no built-in function to automatically format a time stamp as Zulu time, we can create a calculation to format a string as text in the correct format. However, remember that due to time zones, if we have stored a relative time, we will need to get the offset from the UTC to get the correct Zulu time. For example, the following is a calculation that outputs the current time, in Zulu format: Let ([ Dn = GetAsTimestamp ( Get ( CurrentTimeUTCMilliseconds )/1000 ) ; date_string = Year(Dn) & "-" & SerialIncrement ( "00" ; Month(Dn) ) & "-" & SerialIncrement ( "00" ; Day(Dn) ) ; time_string = SerialIncrement ( "00" ; Hour ( Dn ) ) & ":" & SerialIncrement ( "00" ; Minute( Dn ) ) & ":" & SerialIncrement ( "00" ; Seconds( Dn ) ) ]; date_string & "T" & time_string & "Z" ) This is made much easier with the existence of the function to get the current time in UTC milliseconds. However, that only returns the current time in UTC. If we want to get any arbitrary date in UTC, things get a little more complicated. First, we need to calculate the offset from the UTC for the given date. For the moment, we will assume we have a calculation that indicates if daylight saving time is in effect for the given date. We will go into more detail momentarily. The calculation to show that would look like the following: Let ([ Dn = GetAsDate( start_date ) ; date_string = Year(Dn) & "-" & SerialIncrement ( "00" ; Month(Dn) ) & "-" & SerialIncrement ( "00" ; Day(Dn) ) ; offset = Get ( CurrentTimestamp ) - Floor ( Get ( CurrentTimeUTCMilliseconds ) / 1000 ); daylight = start_isdaylight * 3600; Hr = GetAsTime ( start_time ) - ( offset ) - ( daylight ) ; time_string = SerialIncrement ( "00" ; Hour ( Hr ) ) & ":" & SerialIncrement ( "00" ; Minute( Hr ) ) & ":" & SerialIncrement ( "00" ; Seconds( Hr ) ) ]; date_string & "T" & time_string & "Z" ) You will note that we use the current timestamp to calculate the offset, which would work for wherever you run the calculation from. However, you could also just enter an offset manually if you want to figure it for a different time zone from your current location. In fact, the free sample file you can download here does this. It allows you to change the offset manually or calculate the offset automatically. Daylight Saving Disdain I realize there is a lot of debate about daylight saving time (DST). However, whether I am for or against the observation of it (I am against it) does not change the fact that you have to account for it in your applications. Or, you may never have to deal with this issue. If that is the case, great! Daylight Savings is far more difficult to deal with than time zones, which are hard enough. The rules have changed quite a lot over time, so a date in the past may or may not have the same rules applied as one today. For example, legislation was passed that changed when we here in the US observe daylight saving time, starting in the year 2007. Consequently, until computers were updated with the newer time zone changes in the internal databases, they would not be able to show what time it was, based on the time zone they were configured for. This is one of many reasons why you want to keep your OS up to date. Assuming you primarily work with dates from the last ten years or so, and in time zones generally in the United States that observe daylight savings, we can calculate if daylight saving is in effect or not with a single calculation. We can then use that in our calculation to format a timestamp in Zulu time. If you need to go back further than that or are in a different location that observes different rules, you may need to modify the calculations. Zulu Duration There is another standard in the Zulu format that indicates the duration of time that has passed between two points in time. This is used a lot as well as a standard of information exchange, especially in calendaring programs. Unlike formatting a single time, a duration of time should not include any daylight saving or time zone business. Fortunately, this is the way that calculating time works in FileMaker. The math is based on the underlying application framework's Epoch reference date, which is the number of seconds that have passed since January 1, 2001. For this example, we need a second date and time to compare. It then gets tricky to calculate to support all options, but the general format looks like this: P3D1H15M30S Which is to say, a Period of 3 Days, 1 Hour, 15 Minutes and 30 Seconds in the above example. This can be expanded on to meet your requirements if you to show a unit greater than a day, which would be months, weeks or years. The calculation in the sample supports increments of days, hours, minutes and seconds. Feel free to download and examine to see it working. Download the Sample File You can get the sample file that demonstrates these functions and calculations here: https://github.com/SoliantMike/FM-ZuluTime References Get(CurrentTimeUTCMilliseconds) ISO 8601 Time Zone Database The post Zulu Time and Duration Formatting with FileMaker appeared first on Soliant Consulting. Voir le billet d'origine
  4. ExecuteSQL() – the Good, the Bad and the Ugly

    Ever since the ExecuteSQL() function was introduced in FileMaker Pro 12, there’s been much debate over its use and usefulness and its speed. What Does ExecuteSQL() Do? First off, if you need to get up to speed on what ExecuteSQL() does and does not do, the best reference is this blog post by Kevin Frank and Beverly Voth. Don’t let the age of the blog post give you pause; all of it is still very relevant if you are not so familiar with all the ins and outs of the function (and even serves as a refresher if you are familiar with the function). Restrictions to Keep in Mind The big obvious restriction is that ExecuteSQL() does SELECTS only, in other words: you use it to collect data, you cannot use it to create or update existing records. If you need the ability to manipulate data, there are many FileMaker plugins that provide that capability. Then there are subtle differences that can catch people out: The fact that SQL queries are case sensitive. Looking for “wim” will not return records where the value is “Wim”. SQL treats empty different than null. (Learn more here.) ExecuteSQL() supports a particular subset of the SQL syntax; it does not always support SQL syntax that you may be used to from other environments. There are reserved SQL keywords that can clash with your field and table names. ExecuteSQL() Performance For now, I want to focus on performance. While participating in the online FileMaker forum, it struck me that ExecuteSQL() seems to be getting a bad reputation, and I do not think it deserves it. I certainly wouldn't want to see people avoid it. So here is the good, the bad and the ugly: The Good: ExecuteSQL() is blazingly fast. The Bad: But not always. The Ugly: When it is slow it can be extremely slow. Using ExecuteSQL() effectively all comes down to understanding the circumstances around what makes it fast and what makes it slow. In our experience, there are two broad areas where it will slow down: Complex queries that include JOINS, GROUP BY or SQL functions Running queries (even very simple ones) against a table where the user has an open record in That second behavior where ExecuteSQL() will slow down when the user has an open record in the target table is discussed and demoed in my 2014 Devcon presentation. Get the demo file here. The fix can be as simple as making sure that all records are committed before running the ExecuteSQL() query. However, you may also need to carefully consider using the function in areas where you do not always full control over the state of the record -- places such as calculation fields, hide conditions, conditional formats and tooltips. You can often mitigate the first behavior with (semi) complex queries by not cramming too much into one request. Very often you can achieve faster results by breaking up a semi-complex query into different parts, sometimes even by mixing and matching ExecuteSQL() with other FileMaker features. References for Success This thread includes links to two success stories: “From 6 hours to 6 minutes with ExecuteSQL()” "Looping script that went down from 3h 15m to 1m 45sec" It also provides pointers to possible performance optimizations: Using sub-selects with the IN clause (Greg Lane of Skeleton Key) Explicitly repeating the JOIN predicates in the WHERE clause (by FileKraft) Here's another good discussion about avoiding slow JOINs with sub-queries, by Taylor Sharpe. One potential roadblock in using the IN clause to speed things up is that it does not allow dynamic parameters like elsewhere in the ExecuteSQL() calls. A good approach to that is available here. You can also find an approach in that 2014 Devcon demo linked earlier in this blog post. In Encouragement of Using ExecuteSQL() ExecuteSQL() is an incredibly useful tool in our tool belt, and I would strongly encourage FileMaker developers to use it where it fits. It does some very unique things extremely well provided you stay within its performance envelope. It is not a very wide envelope, but do not let that discourage you or shunt it. The Good is really good; the Bad and the Ugly can mostly be avoided. There are many good ideas that have been logged to make ExecuteSQL() better in the FileMaker Product Ideas space. You can search there for “executesql”. I recommend finding the ideas you like and voting them up. It’s the best way to provide input into the direction of this useful feature. Need help? If you have additional questions about ExecuteSQL() or need help enhancing a FileMaker solution, our team may be able to help. Contact us today to learn more about our work and see if we're a good fit for your project. The post ExecuteSQL() – the Good, the Bad and the Ugly appeared first on Soliant Consulting. Voir le billet d'origine
  5. FileMaker Opener Files and Snapshot Links

    Recent versions of FileMaker have made tremendous strides in making it easy to launch your solutions. With the introduction of the Launch Center, end users have a nice interface for getting to their favorite FileMaker-built apps. However, there are scenarios where you want more options to get into your solutions. While there is a range of techniques for opening FileMaker apps in different ways and on different platforms and versions, this post focuses on two techniques specific to FileMaker Pro on either Windows or Mac OS. Opener File This technique uses a FileMaker file that basically does one thing -- open your main solution. A user can open a file that resides locally on their machine which then opens your remotely-hosted file, acting as a shortcut to your app. These files can be simple, with a script that runs when the file is opened and that looks like this: Open remote file: YourApp.fmp12 Close opener file It can be made to do a little more than just that, like going to a layout, resizing windows, etc., but these two steps are the most important. The remote file can be managed in External Data Sources in the opener file. To configure an existing opener file to use with a different solution, you can create a copy of it and change the External Data Source in the file to point to the different solution. We can improve the opener file by creating a nice looking layout that provides the user with some feedback; e.g. "Opening...". Figure 1 shows what this might look like. It also shows how the file options have been specified for the opener file. Not shown in the screenshot: The Script Triggers are set to run a script "OnFirstWindowOpen". Figure 1 - Opener file and setting the file options. Keys to the Castle Opener files are also a convenient way to open another file or solution with a certain set of credentials. Tired of typing in full account username and password when developing a solution? An opener file will, by default, try to open files with the same set of credentials. The account name and password have to match in both files, but the privilege set does not. You can use a lower-level account in the opener file and have that account use a different privilege set (even a full access privilege set) in the remote file. This can be useful if you ever need to distribute an opener file that you do not want users to tamper with. Opener file accounts: User: RealAdmin Pass: supersecretpass Privilege set: Low User: OpenerAdmin Save/Send Records As->Snapshot Link... User: OpenerAdmin Pass: secretpass Privilege set: Full Access Main solution file accounts (auto-open not set): User: RealAdmin Pass: supersecretpass Privilege set: Full Access Snapshot Links Figure 2 - Create the snapshot link Snapshot Links are a different kind of beast. To create a snapshot link, open the solution and go to File->Save/Send Records As->Snapshot Link... (Figure 2) This allows you to save a file with an "fmpsl" extension, a special type of file extension that FileMaker Pro knows how to open. When you install FileMaker Pro or FileMaker Pro Advanced, it registers this extension with the operating system, along with "fmp12". A snapshot link file is actually an XML file with information about the file location, layout, and record set you are currently viewing. If you open a fmpsl file with a text editor, you can see the details that it contains. You will also see that snapshot links include a found set of records, making it convenient to use if you want to direct another user to a specific record. Note that authentication information is not saved, so you will need to log in when using a snapshot link if the solution is not already. (This is a good thing.) Good to Have Options These two techniques represent two available options for how to link to your FileMaker apps. There are others, but these are probably the most commonly used ones. You can use the one that best suits your needs or preference. Each one has its pros and cons, so it is good to have an array of options. Do you have a favorite technique, or one that has not been mentioned for FileMaker Pro? Resources Saving and sending records as a snapshot link - FileMaker Help Setting file options - FileMaker Help OnFirstWindowOpen - FileMaker Help FileMaker Help article: How to create a way to quickly open networked files in FileMaker Pro - FileMaker Support The post FileMaker Opener Files and Snapshot Links appeared first on Soliant Consulting. Voir le billet d'origine
  6. 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. /** * @SIGNATURE: * JSONCreateVarsFromKeys ( json ; namespace ) * * @PARAMETERS: * json - JSON data * namespace - Prefix for the variables that are to be created; can be left blank * * @HISTORY: * CREATED on 2017-12-08 by Mislav Kos <mkos@soliantconsulting.com> * * @PURPOSE: * 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" } * * @RESULT: * 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: * Errors will be indicated in the custom function result using standard FileMaker error codes: https://fmhelp.filemaker.com/help/16/fmp/en/#page/FMP_Help%2Ferror-codes.html. * * @NOTES: * Keys must be named following the same naming restrictions as FileMaker variables and fields: https://fmhelp.filemaker.com/help/16/fmp/en/index.html#page/FMP_Help%2Fnaming-fields.html. * * @DEPENDENCIES: * 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 (github.com/dansmith65) and Jeremy Bante (github.com/jbante) // See https://github.com/filemakerstandards/fmpstandards/blob/master/Functions/%23Name-Value/%23.fmfn ~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 ) ; ~number ) ; // 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. References Creating JSON in FileMaker 16 Parsing JSON in FileMaker 16 FileMaker 16 JSON functions FileMaker 16 error codes Naming restrictions for FileMaker fields Naming restrictions for FileMaker variables # custom function from FileMaker Standards 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
  7. Create Variables from JSON Data in FileMaker Demo

    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. Read the blog post. 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
  8. AWS re:Invent 2017 Recap

    As you may know, one of our offerings, Soliant.cloud, 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, Soliant.cloud. 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
  9. How to Build a Dynamic FileMaker Navigation Bar

    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 Image from: http://knowyourmeme.com/memes/expanding-brain 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: Home Home__Dashboard Home__Reports Home__Reports__Additional Company Company__Details Company__Contacts__Info Company__Contacts__One Company__Contacts__Two Prefs 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. BONUS 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. 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 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: Import the Theme, or create styles for the parts. This can be done later if you already have a Theme in use. Copy the "Load Nav" and “Nav ( layout )” scripts, paste it in your solution, and configure “Load Nav” to run when the file opens. Copy the navigation buttons bars and paste them into your layouts. 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
  10. 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: Issue a command in Command Line to get a list of connected clients; Create records in a table; Compare them to see which ones are the same (same IP address or user); 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 & ¶ & $$pDur ) 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 (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
  11. 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 Jeremy 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
  12. How to Use the ExecuteSQL Script Step in FileMaker

    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: Search for the unprocessed records; Generate a field list variable; Generate a data list variable; Insert the current record’s data into SQL; and 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: Check for unprocessed record with ExecuteSQL calc; If they exist, search for the unprocessed records; Generate a field list variable; Loop through up to 1000 records to generate the data list variable; Push data to SQL; and 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. Testing 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
  13. Best Practices for FileMaker Web Viewer Integrations

    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: Placing all web-language code into fields; Using one field for each of the files a library requires; 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 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
  14. FileMaker Software as a Service (SaaS)

    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. 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 Soliant.cloud 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 Soliant.cloud 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 + Soliant.cloud = 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
  15. Demystifying SSL

    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: What is a certificate? What is being certified? What is contained inside the certified? Why do I have to pay some other company (with this funny name "certificate authority") to give me one of these? 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
×