Tips and tricks with MBS

[MonkeybreadSoftware]

In this article today I will show you 5 tips and tricks with MBS that can make your life easier. I wish you a lot of fun with it. 

Open files from container with the click of a button

Did you know that with the MBS FileMaker Plugin you can open files directly by script function? This is possible with the function Files.LaunchFile. In the parameters we specify the path to the file that should be opened.

Set Variable [ $r ; Value: MBS("Files.LaunchFile"; "/Users/sj/Desktop/abc.png")

If you want to open a file from a container, you first have to take a small indirect route, e.g. we store the files in the temporary folder. For this we first use the function Folders.UserTemporary which gives us the file path to the temporary folder. With the function Container.GetName we can find out the name of the file which is in the container. These two components are then merged to a path where we can then place the file with the function Files.WriteFile to open this file again with Files.LaunchFile.

Set Variable [ $PathTemp ; Value: MBS("Folders.UserTemporary") ] 
Set Variable [ $FileName ; Value: MBS("Container.GetName"; OpenFile::Container) ] 
Set Variable [ $FilePath ; Value: MBS("Path.AddPathComponent"; $PathTemp; $FileName) ] 
Set Variable [ $r ; Value: MBS("Files.WriteFile"; OpenFile::Container; $FilePath) ] 
Set Variable [ $r ; Value: MBS("Files.LaunchFile"; $FilePath) ] 

We also have a new function that helps you delete these stored files when you don't need them anymore. With the new function Files.DeleteLater. This function creates a list of file paths that are deleted after FileMaker is closed. This keeps your folders clean.

Check IBAN numbers 

Do you want to have more security when entering IBAN numbers? Then MBS has something for you. With the component IBAN you get some functions that can be useful for IBAN handling. First of all we can format an IBAN to make it more readable for a user. For this we use the function IBAN.Format. If we want to use the IBAN without spaces, we can use the IBAN.Compact function. We can use the function IBAN.CalcCheckSum to get the checksum from an IBAN. The checksum is the two digits after the country code of the IBAN. If we don't know this, we simply enter these two numbers with 00 and the function determines the checksum for us, wich we can insert there. In addition, with IBAN.IsValid we can also validate an IBAN number, i.e. check whether it corresponds to a certain scheme that is defined for the country to which the IBAN belongs. With this function you don't check if the IBAN exists, but if it has the right format. This check is done with a so called RegEx, which you can also get for each country by specifying the country code with the function IBAN.RegEx.

Open dialog

Do you know this? You want to save a file in a container field that your users can freely select and the possibilities FileMaker offers you are not enough, then MBS can help you. MBS has functions to customize open and save dialogs to your needs. The settings for the dialog are set in FileMaker for all scripts. That means you can make the settings for your dialog in one script and then call it in another script. On the other hand, it also means that if we set up a completely new dialog, we have to make sure that we get rid of the old settings first. For this we have the function FileDialog.Reset. That resets all settings to the defaults. First you can set the title of the dialog with FileDialog.SetWindowTitle. Then the text that is displayed in the dialog with FileDialog.SetMessage. This can also help you to give the user more precise instructions on which file to select. You can also specify the text of the default button with FileDialog.SetPrompt. If you want to start searching for the right file in a specific folder, FileDialog.SetInitialDirectory will help you. Furthermore you can use FileDialog.SetFilter to set filters for the file selection. This prevents e.g. that you open a text file, even if a picture was desired. Also if you want to select one or more files at the same time, you can set with FileDialog.SetAllowMulti function. For displaying a dialog use one of the following functions according to the dialog type you need: FileDialog.OpenFileDialog, FileDialog.SaveFileDialog, FileDialog.SelectFolderDialog or FileDialog.SelectItemDialog.

Square pictures

Do you know this? Sometimes you want to crop images squarely when you want to insert them into a square container. This is also possible with MBS. For this you use the functions from the GraphicsMagick component which deals with all functions around images. First we can load an image from a file (GMImage.NewFromFile) or a container (GMImage.NewFromContainer) into our working environment. Then we determine the height and width of the original image. We distinguish our procedure for portrait and landscape format. If we have a landscape format, we want the original height to be the side length of the cutout. Next we have to decide what the offset of the section is. In landscape format it is zero at y, because we do not have to move away from the top edge. How much we need to move away from the left margin we calculate. We want to have the section in the middle, that means we first subtract the image size from the complete width, so that only the size of the piece we don't want to have remains. The distance should now be the same on both sides. So we divide the value by 2 so that right and left are cut away equally. This is then the offset of X. For portrait format we simply turn the principle around. The X-Offset remains at 0 and Y is composed as just described for the Y-Offset. Now we can assemble the geometry that we will use in the GMImage.Crop function that creates the cutout. First we specify the width x height and then the offset for X and Y connected with a plus. The image is written into a container after it has been cropped and the working environment is released again.

Set Variable [ $img ; Value: MBS( "GMImage.NewFromContainer"; OpenFile::Container ) ] 
Set Variable [ $h ; Value: MBS("GMImage.GetHeight"; $img) ] 
Set Variable [ $w ; Value: MBS("GMImage.GetWidth"; $img) ] 
If [ $h  ≤  $w ] 
	Set Variable [ $size ; Value: $h ] 
	Set Variable [ $OffsetX ; Value: ($w-$Size)/2 ] 
	Set Variable [ $OffsetY ; Value: 0 ] 
Else
	Set Variable [ $size ; Value: $w ] 
	Set Variable [ $OffsetX ; Value: 0 ] 
	Set Variable [ $OffsetY ; Value: ($h-$size)/2 ] 
End If
Set Variable [ $Geometry ; Value: $Size & "+" &$OffsetX& "+" &$OffsetY ] 
Set Variable [ $r ; Value: MBS("GMImage.Crop"; $img; $Geometry) ] 
Set Field [ OpenFile::Container Output ; MBS("GMImage.WriteToContainer"; $img; "abc.png") ] 
Set Variable [ $r ; Value: MBS("GMImage.Release"; $img) ] 

Rename table headers in Excel

One thing that many customers ask about is how to rename the column headers in Excel, for example, if you have export records from FileMaker to Excel. For this we use the additional license of LibXL. With LibXL and MBS you can write, read and change Excel data without Excel being installed. We assume that we already have an Excel file in our container, from which we want to change the table header in the first row. 

First we have to initialize LibXL. You need the LibXL library that matches your operating system and specify the path to this file in the XL.Initialize function. If you want to simplify it, find the initialization script from a sample file and copy it into your project and put the XL libraries into the same folder as your project. With XL.IsInitialized you can check if you need to start this script or if LibXL is already initialized. Then we can load the excel file with the function XL.LoadBook from a container or from a file. This function gives us a reference number to the working environment in which the opened document is now located and with this we can continue working. We now want to overwrite the current headings of the columns with the headings Last name, First name and Age. For this we use the function XL.Sheet.CellWriteStyledText. As you can already see from the name, you can not only write simple text with this function, but you can also choose an already defined format or create a format that you use to write the text. In the parameters of this function you first specify the reference, then follows the sheet index. If there is only one sheet, it is 0 and then we specify which cell we want to label. We specify the row index and the column index. Both start at 0. Then we specify the text and if necessary the format already mentioned. At the end you save the PDF and write it to the path. If you already have a LibXL license, the script part will look like this: 

Set Variable [ $r ; Value: MBS("XL.Initialize"; "/Users/sj/Desktop/libxl.dylib"; $LicenseName; $LicenseKey) ] 
Set Variable [ $XL; Value: MBS("XL.LoadBook"; "/Users/sj/Desktop/ExcelTest.xlsx";1) ] 
Set Variable [ $r ; Value: MBS("XL.Sheet.CellWriteStyledText"; $XL; 0; 0; 0; "Last name") ] 
Set Variable [ $r ; Value: MBS("XL.Sheet.CellWriteStyledText"; $XL; 0; 0; 1; "First name") ] 
Set Variable [ $r ; Value: MBS("XL.Sheet.CellWriteStyledText"; $XL; 0; 0; 2; "Age") ] 
Set Variable [ $r ; Value: MBS("XL.Book.SaveToFile"; $XL; "/Users/sj/Desktop/ExcelTestNew.xlsx" ) ] 
Set Variable [ $r ; Value: MBS("XL.Book.Release"; $XL) ]

If you do not have a LibXL license yet and are still testing these functions, the first line of the Excel document will always contain a note about the license purchase. This is also not overwriteable. If you want to test the functions anyway, it is a good idea to first copy each cell down by one, and finally write the column headers in the row with the row index 1. The function XL.Sheet.CopyCell helps you with the copying process. This function copies a cell including its formatting into another cell. In the paraments the reference is specified, then the sheet number, followed by the original cell from which the text is to be copied with row and column and then the information of the target cell.

...
If [ $LicenseKey = ""  and $LicenseName = "" ] 
	Set Variable [ $count ; Value: MBS( "XL.Sheet.GetLastFilledRow"; $XL; 0 ) ] 
	Set Variable [ $index ; Value: $Count-1 ] 
	Loop
		Set Variable [ $Format ; Value: MBS( "XL.Sheet.CopyCell"; $XL; 0; $index; 0; $index+1; 0 ) ] 
		Set Variable [ $Format ; Value: MBS( "XL.Sheet.CopyCell"; $XL; 0; $index; 1; $index+1; 1 ) ] 
		Set Variable [ $Format ; Value: MBS( "XL.Sheet.CopyCell"; $XL; 0; $index; 2; $index+1; 2 ) ] 
Set Variable [ $index ; Value: $index-1 ] 
		Exit Loop If [ $index < 0 ] 
	End Loop
	# 
	Set Variable [ $r ; Value: MBS("XL.Sheet.CellWriteStyledText"; $XL; 0; 1; 0; "Last name") ] 
	Set Variable [ $r ; Value: MBS("XL.Sheet.CellWriteStyledText"; $XL; 0; 1; 1; "First name") ] 
	Set Variable [ $r ; Value: MBS("XL.Sheet.CellWriteStyledText"; $XL; 0; 1; 2; "Age") ] 
	Set Variable [ $r ; Value: MBS( "XL.Book.SaveToFile"; $XL; "/Users/sj/Desktop/ExcelTestNew.xlsx" ) ] 
...

I hope that one or the other tip can support you in your everyday development work.