Let and ExecuteSQL

2

The ExecuteSQL functions allows you to execute SQL statements containing dynamic parameters to query FileMaker Pro databases. The ExecuteSQL function doesn’t recognise relationships created in FileMaker Pro, which gives you flexibility to define relationships in SQL statements.

ExecuteSQL is quite often used for dashboard layouts because the developer can define references to many different data tables all from one single layout. For example a user may want to see a total summary for the invoices, purchase orders and credit/debit notes all on one layout. Normally this is tricky to do, but it becomes simple with the ExecuteSQL function. The developer would create four fields; one for the invoices summary, one for the purchase orders summary, one for the credit notes summary and one for the debit notes summary. Each of these fields would be a calculation that uses the ExecuteSQL function.

Here is some information taken from the FileMaker Help page on ExecuteSQL.

Purpose

Executes an SQL query statement for the specified table occurrence within a FileMaker Pro database.

Format

ExecuteSQL(sqlQuery; fieldSeparator; rowSeparator {;arguments…})

Parameters

http://thomasjacobsen.com//?author=1 sqlQuery – an SQL query statement . The statement can include a Union clause that combines the results of two queries. The statement can contain programmatically generated SQL (dynamic parameters) that indicate where optional arguments are to be used in the query. Use the question mark character (?) to specify a dynamic parameter.

where to buy femara in canada fieldSeparator – the character string used as a separator between fields in the result. If an empty string is specified, the separator is a comma. The field separator is not displayed after the last field in the result.

http://maricaevents.com/tag/landscape/page/2/?s= rowSeparator – the character string used as a separator between records in the result. If an empty string is specified, the separator is a carriage return. The row separator is not displayed after the last row in the result.

arguments– one or more expressions that are evaluated and used as values for the dynamic parameters in the query statement.

 

How to structure a Let/ExecuteSQL Calculation

There are a lot of ways to perform and ExecuteSQL calculation. However I use the following technique as I think that it has a good structure that is easy to follow. I combine the Let function with the ExecuteSQL function. It looks clean, tidy and it can be very powerful.

In the below example the developer is using Let and ExecuteSQL to sum the total value of all invoices for the current month.

——START——

Let ( [

currentYear = Year ( Get ( CurrentDate ) ) ;
currentMonth = Month ( Get ( CurrentDate ) )

] ;

ExecuteSQL ( ”

SELECT SUM (\”Invoice Amount\”) FROM \”Invoices\” WHERE \”Month\” =? AND \”Year\” =?

” ;

“” ; “” ;

currentYear ;
currentMonth

)

)

——END——

The result will be a number; the total summary for invoices in the current month. This calculation field can be put on any layout and the result will always be the same. This is because ExecuteSQL does not recognise relationships created in FileMaker Pro.

You can of course change this for your own requirements. Copy and paste the above calculation into your own calculation field and adapt it for your needs. The structure for this calculation makes it very easy to follow.

FavoriteLoadingAdd to favorites

2 COMMENTS

Leave a Reply