Our Blog, Filemaker only, Xojo only, Archive (older)

Follow us on Twitter, Xing, LinkedIn or Facebook.

Using found set in SQL queries


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:

MBS("FM.Loop"; "index"; 1; Get ( FoundCount ); 1; "GetNthRecord ( Contacts::ID ; index )")

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.


SSH tunnel in FileMaker


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.

New functions for smart card reading


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.

For FileMaker, we added a new SmartCard.ReadFile function for Mac and Windows in the upcoming MBS FileMaker Plugin version 8.5.

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.

Greetings from Gothenburg


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.

MBS Sale for Conferences


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.

Enjoy the conferences and see you soon!

MBS Pocket Knife


Have you seen the MBS branded pocket knife?

For years people joked about MBS Plugin being the Swiss pocket knife for FileMaker (and Xojo), so we got a few pocket knifes to give away.

If you like, please visit our booth at the FileMaker Konferenz here in Malbun beside the gummy bears and pens:



As the blade is smaller than 6 cm, you can even take it home via airplane.

Reading Belgian national identity card


As you may know all Belgians have an identify card. We recently added classes for CryptoTokenKit framework on Mac and translated a sample project from Swift to Xojo to read those card. Works very well to get basic information, address details and the picture of the person.

The same could have been done with our SmartCard classes. And as those classes use the PCSC interface on MacOS, Windows and Linux you could even get it done cross platform.

But for newer development on MacOS Apple prefers to use their own frameworks, so we got the CryptoTokenKit plugin part in the Mac64bit plugin.

MBS Booth at FileMaker Konferenz in Malbun


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.

Watch our MBS Plugin presentation here from DevCon.

Grüße aus Malbun


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.

FileMaker Conferences


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.

URLs without UserName and Password please


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:

In FileMaker:

Set Variable [ $curl ; Value: MBS("CURL.New") ]
Set Variable [ $r ; Value: MBS("CURL.SetOptionURL"; $curl; $URL) ]
Set Variable [ $r ; Value: MBS("CURL.SetOptionUsername"; $curl; "xxx") ]
Set Variable [ $r ; Value: MBS("CURL.SetOptionPassword"; $curl; "yyy") ]

In Xojo:

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.

MBS Xojo Plugins, version 18.5pr2


New in this prerelease of the 18.4 plugins:
  • Updated DynaPDF to version 4.0.24.62.
  • Fixed issues with printer settings for DynaPDFMBS print methods on Windows.
  • Improved RemoveAccentsMBS to include greece characters.
  • Changed CDBaseChartMBS.MakeChartPicture to set resolution of picture to ScaleFactor * 72 dpi.
  • Changed SQLDatabaseMBS to return RecordSet where you can query names of columns even if you have no rows.
  • Fixed bug in PaletteCalculatorMBS.GetNearestIndexOfColor with color parameter.
  • Fixed FinderUpdateMBS for 64-bit to work by calling internally NSWorkSpaceMBS.noteFileSystemChanged.
  • Added more NSErrorMBS methods to create custom error objects.
  • Fixed a bug to avoid crash with RegisterMBSPlugins with wrong license key.
  • Fixed bug in SSL initialization introduced in pr1.
  • Updated to zlib 1.2.11.
  • Fixed bug in DynaPDF print functions (made white pages) and fixed example (printed only one page).
  • Fixed bugs in WindowsMidiOutputMBS and WindowsMidiInputMBS for 64-bit.
  • Fixed SystemInformationMBS.SystemFont for Mac.
  • Added new plugin part to Mac64bit plugin for CryptoTokenKit from Apple.
  • Fixed problem with SystemInformationMBS.Computername and MBS.ComputerName returning extra characters.
Download: monkeybreadsoftware.com/xojo/download/plugin/Prerelease/.
Or ask us to be added to our shared Dropbox folder.

MBS FileMaker Plugin, version 8.5pr2


New in this prerelease of version 8.5 of the MBS FileMaker Plugin: Download at monkeybreadsoftware.de/filemaker/files/Prerelease/ or ask for being added to the dropbox shared folder.

FileMaker Promotion: Buy one, give one


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.

in German here at filemaker-magazin.de website.

In English here in the FileMaker Store: store.filemaker.com/product/PMO

Please check conditions for details.

CURL Up 2019


The CURL conference for 2019 is announced:

curl up 2019 will take place in Prague, the Czech Republic the last weekend of March.

When: March 29-31, 2019
Where: Prague, Czech Republic
Venue: Charles University

For details, please check the website.

Maybe we can arrange some developer meeting with our Xojo and FileMaker clients, too?

Email Script for FileMaker


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:

(more)

Adding Drag and Drop receiver to a layout


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.

 

(more)

Call stored procedures with output parameters


As you know MBS Xojo SQL Plugin can execute stored procedures. For that you normally just pass the name of the stored procedure instead of a SQL command. The plugin than queries the details for the stored procedure like the input and output parameters. You can set input parameters and call execute. After you got all the result sets read, the last data package from the server brings the return code and the output parameter values. Check this sample code: (more)

FileMaker DevCon Videos coming soon


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.


MBS Xojo Plugins, version 18.5pr1


New in this prerelease of the 18.4 plugins:
  • Updated to LibXL 3.8.3.
  • Removed draggingSourceOperationMaskForLocal events (deprecated in MacOS 10.7).
  • Rebuild with Xcode 10. Minimum MacOS version is now 10.9.
  • Added SetMethodPreference and SupportedAlgorithms methods to SSH2SessionMBS class.
  • Updated SQLite to version 3.25.2.
  • Added more methods for NSFileManagerMBS.
  • Changed methods in NSFileManagerMBS to be regular ones, not shared. This is needed as we may have multiple file managers.
  • Updated OpenSSL to version 1.1.1 for all targets.
  • Added CipherMBS.CipherNames and DigestMBS.DigestNames functions.
  • Added CipherNames and DigestNames to SSH2SessionMBS and CURLSMBS classes.
  • improved DynaPDFMBS.PrintPDFPage to use DevMode data if set.
Download: monkeybreadsoftware.com/xojo/download/plugin/Prerelease/.
Or ask us to be added to our shared Dropbox folder.

Older blog entries:

2018-10-02 MBS FileMaker Plugin, version 8.5pr1
2018-09-28 XDC 2019 Call for Speakers
2018-09-28 Sign up for European FileMaker Conferences 2018
2018-09-27 Moving to Xcode 10
2018-09-25 MBS FileMaker Plugin 8.4 - More than 5400 Functions In One Plugin
2018-09-25 MonkeyBread Software Releases the MBS Xojo Plugins in version 18.4
2018-09-25 Neues MBS FileMaker Plugin 8.4 - Über 5400 Funktionen in einem Plugin
2018-09-24 FileMaker and macOS 10.14 Mojave compatibility
2018-09-24 Xojo Stammtisch in Zürich, Schweiz
2018-09-23 Bitsundso Download
2018-09-22 Installation of MBS Plugin on FileMaker Server for Windows
2018-09-21 How to plan a conference
2018-09-21 Updated contextual menu for MBS FileMaker Plugin
2018-09-21 Under the Radar Podcast
2018-09-21 MBS FileMaker Plugin, version 8.4pr10
2018-09-21 MBS Xojo Plugins, version 18.4pr10
2018-09-20 Storing files for download on FileMaker Server with download URLs
2018-09-19 Missing LSSupportsOpeningDocumentsInPlace for iOS Apps
2018-09-19 Xojo job offer in France
2018-09-18 Ein Monat bis zur FileMaker Konferenz 2018 in Malbun

Archives

Oct 2018
Sep 2018
Aug 2018
Jul 2018
Jun 2018
May 2018
Apr 2018
Mar 2018
Feb 2018
Jan 2018
Dec 2017
Nov 2017
Oct 2017
Sep 2017
Aug 2017
Jul 2017
Jun 2017
May 2017
Apr 2017
Mar 2017
Feb 2017
Jan 2017
Dec 2016
Nov 2016
Oct 2016
Sep 2016
Aug 2016
Jul 2016
Jun 2016
May 2016
Apr 2016
Mar 2016
Feb 2016
Jan 2016
Dec 2015
Nov 2015
Oct 2015
Sep 2015
Aug 2015
Jul 2015
Jun 2015
May 2015
Apr 2015
Mar 2015
Feb 2015
Jan 2015
Dec 2014
Nov 2014
Oct 2014
Sep 2014
Aug 2014
Jul 2014
Jun 2014
May 2014
Apr 2014
Mar 2014
Feb 2014
Jan 2014
Dec 2013
Nov 2013
Oct 2013
Sep 2013
Aug 2013
Jul 2013
Jun 2013
May 2013
Apr 2013
Mar 2013
Feb 2013
Jan 2013
Dec 2012
Nov 2012
Oct 2012
Sep 2012
Aug 2012
Jul 2012
Jun 2012
May 2012
Apr 2012
Mar 2012
Feb 2012
Jan 2012
Dec 2011
Nov 2011
Oct 2011
Sep 2011
Aug 2011
Jul 2011
Jun 2011
May 2011
Apr 2011
Mar 2011
Feb 2011
Jan 2011
Dec 2010
Nov 2010
Oct 2010
Sep 2010
Aug 2010
Jul 2010
Jun 2010
May 2010
Apr 2010
Mar 2010
Feb 2010
Jan 2010
Dec 2009
Nov 2009
Oct 2009
Sep 2009
Aug 2009
Jul 2009
Apr 2009
Mar 2009
Feb 2009
Dec 2008
Nov 2008
Oct 2008
Aug 2008
May 2008
Apr 2008
Mar 2008
Feb 2008
XML: RSS Feed XML: Atom Feed