Last week I had an interesting discussion with Kevin Frank.
We were trying to figure out what would be the best way to insert or update dates when using the myFMbutler DoSQL plug-in.

Before we go and look at the solution, let’s give you a little bit of background about how the plug-in works. When entering date and time values, you must match the format of the database file locale. This is the system format used on the machine where the file was created.
For example, if the database was created on a Belgian system, you would need to use the Belgian date and time format.

There can be a big difference between the format returned from a calculation by the calculation engine and the format that will be required for the plug-in environment.

An Example
Let’s pretend we have created a file on a machine with a US system and we want to use the file in an international environment, so Europeans can view and enter the dates in their own date format. To do so we will set the following File Option: “Always use current system settings”.


US System
Date(1 ; 31 ; 2010) returns 01-31-2010 (MM-DD-YYYY)
Format required for INSERT or UPDATE: {01-31-2010} {MM-DD-YYYY}

Belgian System
Date(1 ; 31 ; 2010) returns 31-01-2010 (DD-MM-YYYY)
Format required for INSERT or UPDATE: {01-31-2010} {MM-DD-YYYY}

On both systems, you need the use the US format when using the plug-in.

But if the file was originally created on a Belgian system, the situation would be different:

US System
Date(1 ; 31 ; 2010) returns 01-31-2010 (MM-DD-YYYY)
Format required for INSERT or UPDATE: {31-01-2010} {DD-MM-YYYY}

Belgian System
Date(1 ; 31 ; 2010) returns 31-01-2010 (DD-MM-YYYY)
Format required for INSERT or UPDATE: {31-12-2010} {DD-MM-YYYY}

An additional problem that may arise is when a user makes an empty clone or a recovered version of your file. When making an empty clone or when recovering a file, the original system formats will be removed. The first system that opens the new file, will save its system format to the file.

Say you have a solution that was originally created on a US system, and all DoSQL statements in that solution are using the {MM-DD-YYYY} format, if a Belgian user makes an empty clone of your solution and opens it on his machine, the solution will break.

Determine the Date Format
You can determine the file’s date format with the following calculation:

Case (
not EvaluationError(mFMb_DoSQL("SELECT {1/31/2010}")); "MMDDYYYY" ;
not EvaluationError(mFMb_DoSQL("SELECT {31/1/2010}")); "DDMMYYYY";
not EvaluationError(mFMb_DoSQL("SELECT {2010/31/1}")); "YYYYMMDD";
not EvaluationError(mFMb_DoSQL("SELECT {2010/1/31}")); "YYYYDDMM";
"????????"
)

Solution number two
Another way to insert or update a Date, is by using the DATE function. When you use the date function, you must always use the ISO format YYYY-MM-DD.

UPDATE FROM table SET myDateField = DATE ‘2009-01-31’;

Bonus: Determining the Decimal separator
Another problem that you can face in International projects is to determine the decimal separator. There is an easy calculation for that:

Middle( PI ; 2; 1)

When using DoSQL to create a ChangeLog of your records, you want to avoid creating log records when nothing has actually changed.

The trick is to prevent DoSQL from firing unless you want to log a change... take a look at the example and check the validation on _K in the Data table... basically, if the parent sees that nothing has changed since the last corresponding ChangeLog record was created, then doSQL won't trigger.

Note that if you have no ChangeLog record for a given parent record, then it will be created *regardless* of which parent field you edit, but... once you have at least one change log record for a given parent, then any "don't log" fields will be respected.

The thing I like about this technique is that ChangeLog records will be created when a Replace is performed and when parent records are imported.

DoSQL ChangeLog Example

DoSQL Plug-in download

This new version provides a new function:

mFMb_DoSQL_QuoteField( fieldname )

The function converts "FileMaker-style" syntax like my Table::my Field to SQL syntax like "my Table.my Field" (with quotes). The nice thing about this is that it allows you to double-click a field to insert it in a calculation.

Combined with FileMaker Pro 10’s new ‘GetFieldname’ function, this allows you to make the SQL statements much more universal.

Download this example for more details on how this can be used (make sure you have version 1.2 of the DoSQL plug-in installed)

Although the use of spaces in field names is not recommended, this solution will continue to function properly if you do use spaces.

Download DoSQL 1.2

Initial testing of our plug-ins (DoSQL, DoScript, PrinterSwitch), have not shown any compatibility issues with the FileMaker 10 platform.

AutoSender 2.5 also seems to work fine with FileMaker Server 10, although we'll be providing a small update that will integrate a new JDBC driver by February.

Clip Manager 3 does have some issues for people trying to 'Get' or 'Set' a custom function with FileMaker Pro 10 Advanced. We will be releasing version 3.1 later this month. This version will also provide a number of other fixes.

We also recently found issues (unrelated to FM10) when using the 'Copy to Clip Manager' function on Brian Dunning's CF web site. Starting with a certain build of Flash 9 and all builds of Flash 10, the copying no longer works. We are currently working on a fix and hope to provide it by next week (actually, we already fixed it today).

We added a couple of new functions:

In the previous posts about this topic, we created a spotlight-like portal filter with the DoSQL plug-in.
Last time we created a custom function to simplify the implementation.
This time we will enhance that custom function so you can implement a spotlight-like portal filter in your solution without the need of any SQL knowledge.

Download the myFMbutler DoSQL plug-in here.
If you have not read the previous post, you can do it here.

Scenario
In our first article, we were searching for all records that CONTAIN our condition. To obtain this we used the % wild card before and after the condition. The search criterion "€œan"€ will return "€œan"€, "€œanna"€ and "€œjan"€.
Wouldn't it be nice, if could extend this with other search options like "€œbegins with"€, "€œends with"€ and "œliteral"€ ?

Step 1: The LIKE condition
The LIKE condition is used to specify a search for a pattern. % is a wild card and can be used both before and after the pattern.

"Contains" example
SELECT id FROM contact WHERE firstname LIKE '%an%'
The search criterion "€œan"€ will return "€œan"€, "€œanna"€ and "€œjan"€.

"œBegins with" example
SELECT id FROM contact WHERE firstname LIKE 'an%'
The search criterion "€œan"€ will only return "€œan"€ and "€œanna"€.

"œEnds with" example
SELECT id FROM contact WHERE firstname LIKE '%an'
The search criterion "€œan" will only return "€œan" and "jan".

"Literal" example€
SELECT id FROM contact WHERE firstname LIKE 'an'
The search criterion "an" will only return "an".

Step 2: The custom function
Now we are going change our custom function, and add an extra parameter "condition".

mfb_DoSQLPortalFilter( theTablename; theIDname ; theFields; theFilter ; theCondition )

In our example it will look like:
mfb_DoSQLPortalFilter( "contact"; "id" ; "firstname¶lastname¶"; "an" ; "contains" )
This function will return the SQL statement:
SELECT id FROM contact WHERE lower(firstname) LIKE '%an%' OR lower(lastname) LIKE '%an%'

And the function:
mfb_DoSQLPortalFilter( "contact"; "id" ; "firstname¶lastname¶"; "an" ; "begins with" )
will return:
SELECT id FROM contact WHERE lower(firstname) LIKE 'an%' OR lower(lastname) LIKE 'an%'

Download the custom function as a myFMbutler Clip Manager Clip.
Download the example file.

Now the custom function is ready and can easily be implemented in your solution.

This example requires that the DoSQL plug-in be installed. You can download DoSQL here.

Download the Clock example

A couple of weeks ago, we showed you how to create a spotlight-like portal filter using the DoSQL plugin. At the same time it was an example of how you can implement an OR relationship, without overloading your database with complex calculations.
This time we are going to expand the demo with a powerful search option.

Download the myFMbutler DoSQL plug-in here.
If you have not read the original post, you can do it here.

Scenario
Our FileMaker solution contains a table with contacts. We want to create a spotlight-like portal filter where we can search by first name OR by last name OR by state OR by county. This time we want the user to be able to choose which fields to search, similar to the search options of e.g. Mail.app.

Step 1
Download the example file of part 1 of this post.
To enable a search only on selected fields, we create a value list "Fields" and populate them with the values: First Name, Last Name, State, County.

In the table "portalfilter" we create a new global text field "fields" and add it onto the layout.

Download step 1

Step 2
In the second step we are going to figure out which fields we have to search. Let's change the calculation of the field IDs:

Let
(
[
theNumberofFields = ValueCount(fields)
;
searchField1 = GetValue(fields ; 1)
;
searchField2 = GetValue(fields ; 2)
;
searchField3 = GetValue(fields ; 3)
;
searchField4 = GetValue(fields ; 4)
;
searchFieldName1 = Case(searchField1 = "First Name" ; "firstname" ; searchField1 = "Last Name" ; "lastname" ; searchField1 = "State" ; "state" ; searchField1 = "County" ; "county" ; "")
;
searchFieldName2 = Case(searchField2 = "First Name" ; "firstname" ; searchField2 = "Last Name" ; "lastname" ; searchField2 = "State" ; "state" ; searchField2 = "County" ; "county" ; "")
;
searchFieldName3 = Case(searchField3 = "First Name" ; "firstname" ; searchField3 = "Last Name" ; "lastname" ; searchField3 = "State" ; "state" ; searchField3 = "County" ; "county" ; "")
;
searchFieldName4 = Case(searchField4 = "First Name" ; "firstname" ; searchField4 = "Last Name" ; "lastname" ; searchField4 = "State" ; "state" ; searchField4 = "County" ; "county" ; "")

...


This could be implemented more elegantly with a recursive custom function, but for now let's leave it like this. The next thing we have to do, is to change our SQL statement:

...
theRowSeparator = mFMb_DoSQL_SetRowSeparator( 13 ) // Return
;
theSQL = "SELECT id FROM contact"
;
theSQL = theSQL & If(not IsEmpty(filter) and theNumberofFields>0 ; " WHERE " )
;
theSQL = theSQL & If(not IsEmpty(filter) and theNumberofFields >0 ; "lower(" & searchFieldName1 & ") LIKE " & mFMb_DoSQL_Quote( "%" & Lower(filter) & "%" ) )
;
theSQL = theSQL & If(not IsEmpty(filter) and theNumberofFields >1; " OR lower(" & searchFieldName2 & ") LIKE " & mFMb_DoSQL_Quote( "%" & Lower(filter) & "%" ) )
;
theSQL = theSQL & If(not IsEmpty(filter) and theNumberofFields >2; " OR lower(" & searchFieldName3 & ") LIKE " & mFMb_DoSQL_Quote( "%" & Lower(filter) & "%" ) )
;
theSQL = theSQL & If(not IsEmpty(filter) and theNumberofFields >3; " OR lower(" & searchFieldName4 & ") LIKE " & mFMb_DoSQL_Quote( "%" & Lower(filter) & "%" ) )
;
theIDs = If( theNumberofFields ≠ 0 ; mFMb_DoSQL( theSql ) )
]
;
theIDs
)


If all fields are selected, the SQL statement will look like this:
SELECT id FROM contact WHERE lower(firstname) LIKE '%an%' OR lower(lastname) LIKE '%an%' OR lower(state) LIKE '%an%' OR lower(county) LIKE '%an%'

But, if only the field "firstname" is selected, the SQL statement will look like this:
SELECT id FROM contact WHERE lower(firstname) LIKE '%an%'

Download step 2

Step 3: Custom Function approach
In the previous steps we have changed the example file to add a search option for the fields. If you look at the calculation it's becoming complex. In this step we will create a custom function that makes the implementation of this technique in other files much easier.

mfb_DoSQLPortalFilter( theTablename; theIDname ; theFields; theFilter )

In our example it will look like:
mfb_DoSQLPortalFilter( "contact"; "id" ; "firstname¶lastname¶; "an" )

This function will return the SQL statement:
SELECT id FROM contact WHERE lower(firstname) LIKE '%an%' OR lower(lastname) LIKE '%an%'

This custom function requires a list of field names, so we need to change the value list "fields". Create a new table with two text fields "fieldname" and "label".

Now we have to change the value list "fields":

The only thing we still need to do, is to change or formula of the field "IDs":
Let
(
[
theRowSeparator = mFMb_DoSQL_SetRowSeparator( 13 ) // Return
;
theSQL =mfb_DoSQLPortalFilter ( "contact" ; "id" ; fields ; filter )
;
theIDs = mFMb_DoSQL( theSql )
]
;
theIDs
)


Download the example file.

Download the custom function as myFMbutler Clip Manager Clip.

Portal filters are sometimes tough to create. Some combinations are almost impossible to establish with standard FileMaker functionality.

In the following example we will show you how you can implement a spotlight-like portal filter with the myFMbutler DoSQL plug-in.

Download the myFMbutler DoSQL plug-in here.

Scenario
Our FileMaker solution contains a table with contact persons (contacts). We want to create a spotlight-like portal filter where we can search on the first name or the last name or the state or the county.

Step 1: Principle
The principle of this example is very simple. We create a text field 'IDs'. This field will contain the id’s of the records that will be shown in the portal.

Download step 1

Step 2: the SELECT statement
Instead of manually typing in some id's, we will fill in the id's with the DoSQL plug-in. In this step we will ask FileMaker to return a list of all id's from the contacts table.
To achieve this, we will use the SELECT statement. The SELECT statement is used to select data from a table. To select all id's from the contacts table, we will use the following statement:

SELECT id FROM contact

Change the field 'IDs' in the table “portaltable” to an unstored calculation (TEXT) with the following formula:

Let
(
[
theRowSeparator = mFMb_DoSQL_SetRowSeparator( 13 ) // Return;
theSQL = "SELECT id FROM contact";
theIDs = mFMb_DoSQL( theSql)
]
;
theIDs
)

mFMb_DoSQL_SetRowSeparator( 13 ) will set the return as row separator.mFMb_DoSQL() will execute the SQL-statement.

Download step 2

Step 3: Add some filtering
If you explore the behavior of a spotlight search in Mail or in the Finder, you will notice that if no search criteria are defined all mails/files are shown. If you enter a search criterion all matching mails/files are shown. The search criterion "an" will return "an", "anna" and "jan".
To achieve this we need to amend the SELECT statement.
The 'where' clause needs to be added to the 'select' statement.

SELECT id FROM contact WHERE firstname LIKE '%an%' OR lastname LIKE '%an%' OR state LIKE '%an%' OR county LIKE '%an%'

% is a wild card and simalar to the * in FileMaker.
Select statements are case sensitive. We can use the lower function to make it case insensitive:

SELECT id FROM contact WHERE lower(firstname) LIKE '%an%' OR lower(lastname) LIKE '%an%' OR lower(state) LIKE '%an%' OR lower(county) LIKE '%an%'

Add a new global text field "filter". This field will contain the search criterion.
Change the calculation of the 'IDs' field:

Let
(
[
theRowSeparator = mFMb_DoSQL_SetRowSeparator( 13 ) // Return;
theSQL = "SELECT id FROM contact";
theSQL = theSQL & If(not IsEmpty(filter); " WHERE " );
theSQL = theSQL & If(not IsEmpty(filter); "lower(firstname) LIKE " & mFMb_DoSQL_Quote( "%" & Lower(filter) & "%" ) );
theSQL = theSQL & If(not IsEmpty(filter); " OR lower(lastname) LIKE " & mFMb_DoSQL_Quote( "%" & Lower(filter) & "%" ) );
theSQL = theSQL & If(not IsEmpty(filter); " OR lower(state) LIKE " & mFMb_DoSQL_Quote( "%" & Lower(filter) & "%" ) );
theSQL = theSQL & If(not IsEmpty(filter); " OR lower(county) LIKE " & mFMb_DoSQL_Quote( "%" & Lower(filter) & "%" ) );
theIDs = mFMb_DoSQL( theSql )
]
;
theIDs
)

Now we are ready.

Download the example file.

Note: Another example of more complex portal filtering can be found in the demo files included with the DoSQL plug-in.