There is currently no direct way to tell SQL to use the current found set as condition for a query. But with the GetNthRecord function from FileMaker and the FM.Loop function in MBS FileMaker Plugin, we can build a list of IDs and generate automatically the SQL condition to use the found set.
The following line queries a list of IDs from current record:
As you see we define variable and count it from 1 up to the number of records in found set. For each loop iteration, GetNthRecord is called to query field named Contacts::ID and we get back the list of IDs like this:
2
3
5
6
7
8
We can change the calls to produce the right list for SQL with IN operator. We add some text before and after and replace the new line with the comma going between values. The new loop call looks like this:
"(ID in (" &substitute(MBS("FM.Loop"; "index"; 1; Get( FoundCount ); 1; "GetNthRecord ( Contacts::ID ; index )"); ¶; ",")& "))"
The text returned may look like this: (ID in (2,3,5,6,7,8))
Please note that for UUIDs you may need to add quotes to get them in quotes, e.g. like this:
"(ID in '" &substitute(MBS("FM.Loop"; "index"; 1; Get( FoundCount ); 1; "GetNthRecord ( Contacts::ID ; index )"); ¶; "', '")& "')"
The new list has quotes for the ID values: (ID in ('2', '3', '5', '6', '7', '8'))
Next we can build a SQL line using the IDs and query another field like the last name:
MBS("FM.ExecuteFileSQL"; ""; "SELECT \"Last\" FROM Contacts WHERE (\"ID\" in (2,3,5,6,7,8))")
As you see we put Last in extra quotes as last is a reserved value in SQL. In general it doesn't hurt to put all field and tablee names in quotes, so the SQL works for all names including those with spaces. The field used for ID may also need to be quoted and if you use UUIDs, please put values in single quotes.
One little check is required: Please make sure your found set is not empty before using this. Otherwise you end with an error message from FM.Loop or invalid SQL.
PS: With Command-Option-C you can copy expressions with color from the calculation dialog.
For the upcoming 8.5 release of the MBS FileMaker Plugin, we got new functions to create a SSH tunnel from within FileMaker. You can connect to a server via SSH (IP or Domain), authenticate with your password or better your private key and start a tunnel to forward a connection from one side to the other.
This is great to for connect to MySQL server, which is not exposed to the internet. You connect to your web server via SSH and than forward port 3306 from your local machine to port 3306 on the remote machine. Once the tunnel runs, the SQL functions in our plugin can connect through the tunnel to the remove database server.
For details, please see the SSH.Tunnel.Run function to start the tunnel and SSH.Tunnel.Messages to see debug messages. Please don't do anything with the SSH session while the tunnel is running. But you can of course run multiple SSH sessions in parallel to run shell commands or several tunnels.
We had this implemented earlier this year for Xojo (see SSH2TunnelMBS class), but when I mentioned at the FileMaker conference that I have the code to do a tunnel, I was quickly asked to port this to the FileMaker plugin, so here it is.
In the last two weeks I worked on new functions to read smart cards. We got a client interested in reading Belgian national identity card via CryptoTokenKit functions in our plugins and a FileMaker client interested in reading Swiss Health Insurance cards. We got both working with our latest MBS Plugins.
For testing, we used both the Smart Card Reader from König (left) and the SmartTerminal ST-1144UB from Cherry (right). Both do the job with a little difference. For the left device, you can connect and query status, even if there is no card. The right one only allows to connect when you have a card inserted. Other readers work probably as this uses a standard interface and drivers are built into the operation system.
For Xojo we got the new CryptoTokenKit classes in MBS Xojo Mac64bit Plugin for the modern way Apple likes to handle this with asynchron operation and delegates. Beside this, we also go the older cross platform way via PCSC API and the ReadFile method in and SmartCardMBS class.
Whatever function you use, you can pass the right file ID and read the files you need from the smart cards. We also got a function to split the data into the parts. See SmartCard.SplitValues for FileMaker and SmartCardMBS.SplitValues function for Xojo.
Just in the middle of the seven FileMaker conferences in Europe. Three are done (Dutch, German and Italian) and three are to start this week (English, Spanish and French). And today the conference in Sweden started with over a hundred attendees:
Next I will travel to London for the English conference tomorrow after my MBS Plugin session.
In the last week and the coming week, we have seven FileMaker conferences in Europe. Five are sponsored by Monkeybread Software. For three conferences, I will attend and present about our plugin.
We like to reward people coming to the conferences, so we make a special conference offer:
20% on all licenses including updates.
If you like to use that offer, just order via web store and use coupon code FMK2018. Add first to shopping cart and than put the coupon code in the field.
For Malbun we brought our usual posters and decorated a table with papers:
Please come by to say hello, get a pen, some gummy bears and the cool MBS pocket knife (okay for air travel).
The coupon code for our web shop with 20% off from all licenses is FMK2018 this year and valid til end of October.
Diese Woche startet hier in Malbun, Liechtenstein die deutschsprachige FileMaker Konferenz mit Teilnehmern aus mindestens fünf Ländern. Ich hoffe ihr kommt nicht nur zur Konferenz, sondern nehmt euch auch einen extra Tag frei um die schöne Berglandschaft zu genießen.
Zu Fuß oder per Seilbahn kann man auf den Berg Sareis kommen und die Aussicht über das Tal genießen. Der Blick geht weit nach Österreich und die Schweiz rüber. Ab der Kapelle in Malbun kann man über den geschotterten Weg in ca. 4 km bis zum Café an der Bergspitze laufen mit moderater Steigung.
We are looking forward to two weeks with conferences. As you may know there are seven FileMaker conferences this year in Europe.
I visit the German conference in Malbun (Liechtenstein), the Scandinavian conference in Gothenburg (Sweden) and the English conference in London (United Kingdom) this year. And for the German conference, Stefanie will join me to help on the booth and with our training day on Wednesday.
See you there!
Greetings to the people in Gent, which start the FM Summit today.
With last plugin release we enabled the option for CURL to disallow you passing user name and password. As URLs are sent to proxy and may be logged at the web servers, we prefer to not include credentials in the URL. And if you use the email address for user name, the name in the URL never worked.
So for the future, please separate them and put the credentials in the relevant options:
dim curl as new CURLSMBS
curl.OptionURL = URL
curl.OptionUserName = "xxx"
curl.OptionPassword = "yyy"
If you still use URLs with username and password, please move to passing them with the username and password properties.
If you really can't change your solution, you can still use CURL.SetOptionDisallowUserNameInURL (FileMaker) and OptionDisallowUserNameInURL property in CURLSMBS/CURLNMBS and CURLMBS classes (Xojo) to enable username and password in URLs.
Today a client sent me his email script using our plugin. It is based on one of our examples, but got quite a few enhancements.
First like with every professional developer, you see a few comments about what it does and where data is coming from. Also changes are logged at the end. Part of the standard setup of the methods is to disallow user abort and activate error capture to avoid error dialogs.
All the configuration details are not part of the script, but come from a configuration table. For tracing the soluton, a trace script is called to log what happens. Results from sending emails are stored in the table, so you can check for each email whether it worked and what the error message is. Please read the script below:
One of our clients asked how to add a Drag & Drop area on the layout to receive file drops.
Well, the obvious answer is to copy everything from DragTest Windows.fmp12 to their solution and enjoy it for both Mac & Windows. But it's not that simple as you need to know where to look. So here a step by step solution.
Before you start, please make sure you have a recent plugin version. The Drag & Drop features for Windows were introduced in MBS FileMaker Plugin 8.1, but we got bug fixes later, so best is to use current 8.4 release.
Add placeholder rectangle
First in our DragTest Windows.fmp12 file is a picture used for the drop area. The picture is there twice. Once assigned to the rectangle in the layout and second in a global field. So add the DropLogo field to the layout and you can export the image named "Drop-Files-Here-extra.jpg".
In your solution, you may find a layout where you like people to drop stuff. This should be a layout where you would enable Drop in one trigger and than disable it when the user leaves the layout.
To the layout, we add a rectangle area in the header for the drop. Our image is 500 x 224, so our rectangle should be 250 x 112 points big to use 2x image for sharp picture on high resolution screens. In the inspector for fill option, please select picture and select the image file. For the aspect ratio, please choose "Scale to Fit" below. In the first inspector tab, please give the rectangle a name, e.g. "drop".
Please add a global container image named DropLogo. Put it on the layout and import the image here, too. After the import, you can remove the global field from the layout. We need this image later in the script. It's a good idea to actually have two images, one of drop off and one for drop on. This way you can see whether the plugin is working.
As you may have seen the videos from FileMaker DevCon 2018 in Grapevine, Texas are planned to arrive soon.
The DevCon 2018 sessions by track website shows 9th October 2018 as release dates for the videos.
This may involve dropping 32-bit for some things as Xcode 10 doesn't support 32-bit any more. And it may be we could drop 32-bit as target and just make new plugins to be 64-bit only.
Next we may make MacOS 10.9 the minimum, which would drop a few older Xojo and FileMaker versions. What minimum would you like to have?
Of course just going with 10.10 or 10.11 as minimum could simplify coding for us.
Nickenich, Germany - (September 25th, 2018) -- MonkeyBread Software today is pleased to announce MBS FileMaker Plugin 8.4 for macOS, iOS, Linux and Windows, the latest update to their product that is easily the most powerful plugin currently available for FileMaker Pro. As the leading database management solution for Windows, macOS, iOS and the web, the FileMaker Pro Integrated Development Environment supports a plugin architecture that can easily extend the feature set of the application. MBS FileMaker Plugin 8.4 has been updated and now includes over 5400 different functions, and the versatile plugin has gained more new functions:
For the MacOS 10.14 Mojave release, we got a few changes in our plugin. We added a new function SystemInfo.isMojave, which allows you to easily detect the new operation system. For developers we got the script search, the goto box and the search box improved to work better on MacOS 10.14.
Our new Bonjour functions allows you to search for services on the local network. This is the same technology used in FileMaker to find FileMaker servers. You can look for services and register a new service to find instances of your application in the network.
The new Keychain functions allow you to work on the passwords stored in the Keychain database on Mac and iOS. Your solution can add passwords for files, so the user is logged in automatically. If needed, you can change the passwords or delete them.
For MacOS we now have two function sets to work with contacts. First the older Addressbook API for MacOS and iOS. The new Contacts API is for both MacOS (64-bit only) and iOS. Those APIs have differences, so we want to keep both and you may need to use both sometimes. Older API can give you modification date for records while newer API can handle multiple accounts better.
As you may know we have a lot of XML functions. We added LibXLST and a new function XML.ApplyStylesheet to apply a XLST transformation to an XML document. Our XML.ExtractText function got improved to handle CData better.
For iOS we improved the plugin and now only include 64-bit version. Plugins for Device and Simulator and now separated and we ship them as frameworks. We got a new sign script for you to sign the plugin for use on your own devices for local testing. The QLPreviewPanel functions allow you to preview files on iOS including 3D models and augmented reality.
The AVRecorder functions for MacOS and iOS can now detect QR-Codes in the video image and trigger a script to process results. And you can check status and request permissions for audio and video devices. Our AVRecorder can now also record screen via the new AVRecorder.SetScreenInput function.
Check the new functions to create and resolve alias files (Mac) and shortcut files (Windows). The Font.CalculateTextFits and Font.CalculateTextHeight functions can calculate how much space some text needs. The Amazon S3 upload example is updated to do multi part uploads for huge files. SQL functions can now better query options set on connections, command or field.
Finally we updated CURL to version 7.61.1, DynaPDF to version 4.0.23.61, LibXL to version 3.8.3, LibXML2 to version 2.9.8, SQLAPI to version 4.3.3, SQLite to version 3.25.0 and updated Xcode to version 9.4.
Lee Smith (Calculation Edito…): I like this a lot. Anna Antonioli (MBS Pocket Knife): The best image for mbs pl… Christian Schmitz… (Code Signing File…): The script is included wi… gen (Code Signing File…): Hello
I could not find … brian doyle (Fix for FileMaker…): Would love a link to the … Christian Schmitz… (LDAP in FileMaker…): Well, first learn how LDA… Bernard Mortreu (LDAP in FileMaker…): Hello, I’m trying the LDA… Christian Schmitz… (FileMaker iOS SDK…): It was solved until Apple… Michael Ashley (FileMaker iOS SDK…): Hi, is the issue with sub… David Thorp (Tips for FileMake…): Ok, so it’s basically the…
There is currently no direct way to tell SQL to use the current found set as condition for a query. But with the GetNthRecord function from FileMaker and the FM.Loop function in MBS FileMaker Plugin, we can build a list of IDs and generate automatically the SQL condition to use the found set.
For the upcoming 8.5 release of the 
We are looking forward to two weeks with conferences. As you may know there are 
FileMaker currently runs a promotion till 20th December 2018 where you can buy a FileMaker license and receive a second one to give to a friend.
Today a client sent me his email script using our plugin. It is based on one of our examples, but got quite a few enhancements.
One of our clients asked how to add a Drag & Drop area on the layout to receive file drops.
We are trying to move to Xcode 10.
