Product Logo

CNS Query

Query your databases without relationships.


Coding Considerations

Getting Results

Every time you use an CNS Query function, status text is returned back from the plug-in. This information can be very useful, for example, to let you know if an error occurred. Consider the following code:

CNSQuery_SetFieldValueByID ( “” ; “MyField” ; “new value” )

The above code will set the field “MyField” in the current table to the value “new value”. If it is successful, the plug-in will return:

Field Updated.

However, if there was an error setting the field, for example if the field doesn’t exist, the plug-in will return something like:

ERROR: SetFieldValueByID: A Field named “MyField” does not exist in “MyTable”.

Curly Brackets

Curly brackets indicate that a parameter or parameters are optional. Take the CNSQuery_ExecuteSQL function for example:

CNSQuery_ExecuteSQL( SQL {; FieldSeparator {; RecordSeparator }} )

The only required parameter in this function is the first parameter. Each of the other two parameters are surrounded by a set of curly brackets indicating that they are optional. Using this function by only specifying the first parameter looks like:

CNSQuery_ExecuteSQL( “select * from MyTable” )

If you wanted multiple field values separated by a dash instead of the default comma, you could specify the ‘FieldSeparator’ parameter like this:

CNSQuery_ExecuteSQL( “select * from MyTable” ; “-” )

Note that if there are any optional parameters before the parameter you need to use, you must include them before the parameter you want to use. For example, if you wanted to define the ‘RecordSeparator’ parameter but didn’t need to change the default ‘FieldSeparator’ parameter, you would not do this:

INCORRECT: CNSQuery_ExecuteSQL( “select * from MyTable” ; “</li>¶<li>” )

You would need to include the second parameter as well, even though you aren’t specifying anything (indicated by two double-quotes):

CORRECT: CNSQuery_ExecuteSQL( “select * from MyTable” ; “” ; “</li>¶<li>” )

Other functions have curly brackets around the entire parameter section (what goes inside the parenthesis). This indicates that all of the parameters are optional. The GetFieldNames function is an example:

CNSQuery_GetFieldNames{( TableName {; SkipTableNameCheck } )}

Using the function without any parameters looks like:

CNSQuery_GetFieldNames

The above code would return all the field names for the current database. If you wanted only the field names in a table called “MyTable”, then you would use this:

CNSQuery_GetFieldNames( “MyTable” )

CNS Query also has several functions where the first parameter is considered optional, but then there are other parameters that are required. An example is the FieldExists function:

CNSQuery_FieldExists( {TableName} ; FieldName {; SkipTableNameCheck } )

The first ‘TableName’ parameter is surrounded by curly quotes. This means that the ‘TableName’ parameter is optional, but you still need to specify a parameter in this location. If you want the function to test for a field in the current database, simply pass two double-quotes for the first parameter:

CNSQuery_FieldExists( “” ; “testfield” )

FileMaker Version Considerations

When plug-ins were first introduced, the only place you really wanted to use a plug-in function was the Set Field script step. However, since FileMaker 4, many new advancements have taken place. Though the Set Field script step is still a very common place to use plug-in functions, there are now many places that can logically be used. For a list of a few of these places, see the next section titled “Places to use CNS Query Functions”.

It is important to keep in mind what versions of FileMaker will be in use when using CNS Query and other plug-ins. For example, creating a variable using the Set Variable script step can be very convenient, however, that functionality only exists in FileMaker 8 and greater. If you or your users use FileMaker 7, then your script calls would fall on deaf ears if you used Set Variable script step. In addition, when using functions in a variable you will be less likely to see the results returned from the plug-in (such as error messages), because a variable cannot but put on a layout like a field.

Places to use CNS Query Functions

You can use CNS Query functions in any calculation engine dialog in FileMaker. Keep in mind that just because you can do something does not mean it is actually useful.

There are several places that fit very well depending on the situation:

  • Calculation field
  • Auto-Enter Calculated value
  • Validation by calculation
  • Set Field Script/Button step
  • Insert Calculated Result Script/Button step
  • Show Custom Dialog
  • Set Variable Script/Button Step
  • Custom Functions
  • and more…