Filemaker query

Filemaker query DEFAULT

I speak "Access" and "Filemaker" so maybe I can translate the terminology a bit as I go.

Filemaker doesn't rely on SQL expressions to define record sources or Record Sets.

Instead, each layout is pointed at a specific table occurrence and data on that layout (think form) can reference fields in other tables based on the relationships defined in Manage | Database | Relationships. That's roughly the same as the "join" part of a SQL expression.

To use your layout to view a specific group of records, you perform a "find". While viewing the layout, you enter find mode, enter search criteria in the fields (including operators and wildcards) and perform the find. This extracts a group of records from the layout's underlying table and this group of records is called the "found set" and is a bit like a form or report's Record Source in Access. Thus performing a find is a bit like the "WHERE" clause in SQL. You can also sort the found set by selecting Sort from the Records menu.

All of the above steps can be performed in a script to automate the process.

What you'll probably find is that simple data searches are much easier to set up in Filemaker, but complex searches may be as complicated or more so than using VB to manipulate a SQL expression.

Hope that gets you started. Feel free to post back with additional questions.

Sours: https://community.claris.com/en/s/question/0D50H00006ezQQwSAM/how-to-create-a-query

SQL Explorer

Learn FileMaker SQL – Write Queries Faster

SQLexplorer lets you use FileMaker metaphors you already know, like Relationships, Search, Sort, and more, to write sophisticated SQL queries. You can then copy and paste the resulting queries right into your FileMaker 12 files with all the correct calculation syntax. Follow our simple SQL Wizard to create and test SQL queries against your own data.

Complete online documentation and step-by-step videos.

Learn FileMaker SQL using FileMaker metaphors you already know. Follow along in our wizard and you’ll be writing queries against your own data: queries you can paste into your own files.

Download - It's Free

A dolphin is the mascot of MySQL, and when FileMaker could first talk to SQL sources we were all very excited about finally interacting with the other databases like MySQL.

But it turns out that one of the most interesting uses of SQL is within FileMaker itself, using the ExecuteSQL() function introduced in FileMaker 12.

We've been blogging about the impact of this but the key idea is that it lets you grab data out of context, meaning we can have fewer table occurrences in our relationship graph. For more on what this means, checkout this series of articles, especially "Why SQL". Enjoy.

Click "What is this" again to close.


Overview

Jason Young, the author of SQLexplorer has done a great job documenting each step of creating queries, providing detailed videos as you go. We’ve been using this tool internally for a while now to get our own queries written faster (and error free). We think you’ll really find this useful.

For the Pros: Abstracted Queries

The latest explorer (March 1, 2013) lets you copy abstracted queries to your clipboard: these are less verbose and survive field renaming, so if you’re past learning SQL, this option is for you.

Going Further (the newest SQLexplorer)

Jason has added a lot of refinements to the new SQLexplorer–stuff the pros will really like–while adding a whole new way of refining queries for those of us not adept and stringing “And” clauses together.

This version uses jQuery and HTML5 for displaying query results and you’ll love what this lets you do.

Sours: https://www.seedcode.com/filemaker-sql-explorer/
  1. Convertible cars 2000
  2. Zelda and link
  3. Michael carson artist biography
  4. 9 rounds carrollton

Query Builder

by Mislav

QueryBuilder

  • This module provides a simple utility for writing and troubleshooting SQL queries, including abstracting field and table names, for use with FileMaker’s ExecuteSQL function.
  • Use this module if you prefer typing out your query manually instead of using a wizard (like the excellent SeedCode SQL Explorer) but would like to make the troubleshooting and field/table name abstraction process faster. There’s also a simple query formatter to help you keep your queries readable.
  • Read more about this module here.
  • See video.
  • Don’t forget to check out the sample queries available in the Scripts menu.
  • The installation takes about a minute.
  • See installation instructions in the readme script.
  • Note: This module includes three custom functions that require a copy of FileMaker Pro Advanced to install in another FileMaker file.
  • Requires FileMaker version 12 or higher.
  • MIT License – see the readme script.

© Copyright - Modular FileMaker - Enfold Theme by Kriesi

ParamConverterEmailPro IconEmailProSours: http://www.modularfilemaker.org/module/query-builder/
FileMaker Tip: An Introduction to Query Builder

Querying an ODBC data source from FileMaker Pro Advanced

After configuring an ODBCclientdriver, you can interact with records, import records into an existing FileMaker Pro Advanced database file, or create a new FileMaker Pro Advanced database file from an ODBC data source (such as Oracle or Microsoft Access databases).

First, you access the data source you want to import from. Then you construct a query for the records you want to import from the data source. Finally, if you're importing data into an existing file, you map fields from your data source to fields in your FileMaker Pro Advanced database file.

Important  See Connecting to external data sources for information on how you can access and work with ODBC data sources without writing SQL queries.

You can access your ODBC data source through the File menu, with the Import Records script step, or with the Execute SQL script step.

All applications that support ODBC recognize a basic subset of SQL (Structured Query Language) statements. SQL is passed through the ODBC interface to the data source, performing queries such as and making updates such as the creation of a new record with .

Note  Because Microsoft SQL Server supports both Unicode and non-Unicode field types, you must prefix all Unicode strings with an uppercase "N" (which stands for "National" in the SQL-92 standard). Otherwise, when a Unicode string containing non-English characters is passed to Microsoft SQL Server, you may lose any data that doesn't exist in the Microsoft SQL Server code page.

To query an ODBC data source from FileMaker Pro Advanced:

1. In FileMaker Pro Advanced, do one of the following:

•To import into an existing FileMaker Pro Advanced file, choose File menu > Import Records > ODBC Data Source.

•To create a FileMaker Pro Advanced file from the data source records, choose File menu > Open. In the Open File dialog box, locate the file. For Files of type (Windows) or Show (macOS), choose ODBC Data Source.

2. Choose your data source and click Continue.

Most server-based ODBC drivers require the data source to be open, but the requirement varies depending on the driver.

3. Enter the user name and password (if any) for the data source you chose.

To skip this dialog box in the future, select Save user name and password (this option is only available if you're importing into an existing FileMaker Pro Advanced file).

4. Click OK.

The FileMaker Pro Advanced SQL Query builder dialog box appears.

5. Construct your query. See Constructing an SQL query for importing via ODBC for details.

You can execute the query immediately, or copy and paste it into a field for later use. Use the Import Records or Execute SQL script step to execute a query stored in a field. See Storing an SQL query in a field for details.

6. Click Execute.

•If you are importing data into an existing file, FileMaker Pro Advanced displays the Import Field Mapping dialog box. You map fields from the data source to fields in your FileMaker Pro Advanced database file. See Setting the import action and mapping fields during import and Importing data into an existing file.

•If you are importing data into a new file, FileMaker Pro Advanced puts the imported records into your database file.

Notes 

•ODBC import, the Execute SQL script step, and external SQL data sources are not supported in runtime solutions.

Related topics 

Using ODBC and JDBC with FileMaker Pro Advanced

Configuring an ODBC client driver

Was this topic helpful? Send feedback.


Sours: https://fmhelp.filemaker.com/help/18/fmp/en/FMP_Help/querying-odbc-source.html

Query filemaker

ExecuteSQL

Purpose 

Executes an SQL query statement for the specified table occurrence in a FileMaker Pro file.

Format 

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

Parameters 

sqlQuery - an SQL SELECT 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.

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.

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.

Data type returned 

text

Originated in 

FileMaker Pro 12.0

Description 

ExecuteSQL enables you to execute SQL SELECT statements containing dynamic parameters to safely query FileMaker Pro databases in order to avoid security vulnerabilities through injection attacks.

ExecuteSQL does not recognize relationships created in FileMaker Pro, which gives you flexibility to define relationships in SQL statements and retrieve data from any table, independent of the layout context.

ExecuteSQL cannot be used with SQL statements that modify data or the database schema (such as the Insert Into or Delete Table commands).

If an error occurs during query parsing or execution, FileMaker Pro returns ?.

Notes 

•To apply the correct formatting to dates in an SQL query, use the DATE statement. If you do not use the DATE statement, ExecuteSQL treats dates as literal strings.

•FileMaker Pro returns date, time, and number data in Unicode/SQL format, not in the locale of the operating system or the file.

•ExecuteSQL accepts only the SQL-92 syntax ISO date and time formats with no braces. ExecuteSQL does not accept the ODBC/JDBC format date, time, and timestamp constants in braces.

•FileMaker SQL uses the Unicode binary sort order, which is different from the FileMaker Pro sort order used with language sorting or with the default language-neutral sort order.

•For more details about SELECT statement syntax, supported SQL statements, expressions, and Catalog functions, see FileMaker ODBC and JDBC Guide and FileMaker SQL Reference.

Example 1 

Suppose a database contains two tables, Employees and Salaries, which are related through the EmpID field.

List displaying employees and salaries

Note  The Employees::EmpID, Salaries::EmpID, and Salaries::Salary fields are number fields.

You can use the ExecuteSQL function to return a field value from a specific record without changing the current record or modifying the found set.

ExecuteSQL ( "SELECT Department FROM Employees WHERE EmpID = 1"; ""; "" ) returns Development regardless of the current record, found set, or layout.

Example 2 

Suppose you want to add a field to the Employees table to display the percentage of an employee’s salary relative to the total salaries in a department. Though you could use a calculation in FileMaker Pro to generate this value, you can use the ExecuteSQL function to specify this query using dynamic parameters. The example below uses table aliases for the Employees table (E) and the Salaries table (S) when specifying fields (S.Salary, E.EmpID, and S.EmpID).

Define a calculation field in the Employees table, then use the ExecuteSQL function to specify the following query statement:

Round (
   100 * Salaries::Salary / ExecuteSQL (
      "SELECT SUM (S.Salary)
      FROM Employees E
      JOIN Salaries S
      ON E.EmpID = S.EmpID
      WHERE E.Department = ?";
      ""; ""; Employees::Department
   ) ;
2 )

On each employee record, the calculation field displays the percentage of the employee's salary relative to the sum of the salaries for the employee's department. For example, the record with a Last Name of "Smith" returns 52.97, and the record with a Last Name of "Mehmet" returns 100.

Related topics 

Functions reference (category list)

Functions reference (alphabetical list)

About formulas

About functions

Defining calculation fields

Using operators in formulas

Was this topic helpful? Send feedback.


Sours: https://fmhelp.filemaker.com/help/16/fmp/en/FMP_Help/executesql.html
FileMaker Tutorial of Relationship Based Data Filtering

FileMaker SQL Options

There are several excellent utilities available for constructing SQL queries for use with FileMaker’s ExecuteSQL function. I like the SeedCode SQL Explorer developed by Jason Young. Unfortunately, as I create new queries for current projects, I find myself inclined to type them out manually instead of using a wizard (which is how SQL Explorer works). It just seems faster that way, especially now that I’ve gotten some SQL experience under my belt.

This process involves opening up the Data Viewer and writing out the query there. If the query has some moderate degree of complexity, I usually end up having to wrap it in Andries Heylen’ssql.debug custom function at least once along the way to troubleshoot what I did wrong. Finally, I abstract the field and table names so that the query doesn’t break if the names end up changing later.

These last two steps – troubleshooting and name abstraction – end up being a bit of a nuisance, so I built a tool to make this easier and faster to do. My Query Builder tool also does some SQL formatting to make queries easier to read.

Download Our Complimentary FileMaker SQL Builder

I followed Todd Geist’s Modular FileMaker approach and submitted the tool on his site.

Complete this form to get the Query Builder tool.

You can use the file in your solution in one of two ways: either add table occurrences from your solution to this file or install it as a module into your solution, (which takes about a minute to do).

I also created a video to demonstrate how to make the most of this utility. You can check it out here.

Let me know what you think, or if you find a bug (not that that would ever happen), or if you have a suggestion on how to improve it.

FileMaker SQL Query Builder Screenshot

Get More FileMaker SQL Insights

Have questions about how to use leverage SQL in FileMaker? Contact our team of certified experts. As a Platinum Partner, we can provide direction and development support to help your team drive innovation in your FileMaker application.

Sours: https://www.soliantconsulting.com/blog/filemaker-query-builder/

Similar news:

.



221 222 223 224 225