Product Logo

CNS Query

Query your databases without relationships.

Functions

CNSQuery_FindRecords

NameCNSQuery_FindRecords
Description

This function does a Find against a Table and returns all matches. The first parameter is the Name of the Table Occurrence to perform the Find in. If you do not specify this parameter, CNS Query will use Get(LayoutTableName) to get the current TO to perform the Find in. The second parameter is a return- or paragraph mark-separated list of “FieldName=FieldValue” pairs that describes the Criteria for Finding the Records you want. See the “Finding What You Want” section below for help in defining your Find Criteria. The FieldNamesToReturn parameter is a return- or paragraph mark-separated list of Fields you want returned in the Result. In other words, if you have 10 fields in your Table, but you are only interested in the values of the “FirstName” and “LastName” fields, you would specify “FirstName¶LastName” as the FieldNamesToReturn parameter. (Note: You can specify fields in the FindCriteria other than what you want returned in the result.) The fourth parameter is a return- or paragraph mark-separated list of “FieldNames=SortDirection” pairs that define how to Sort the Result. For the Sort Direction you can use “Asc” or “Desc” to sort Ascending or Descending respectively. By default, CNS Query will use a comma between multiple Fields and a return between multiple Records. If you need to use different Separators, you can use the fifth and sixth parameters. The Field and Record Separators are not limited to a single character. By default, CNS Query will check to make sure the TO Name and all the Field Names you specify exist before attempting to perform the Find. If you already know for sure that the TO Name and the Field Names exist, you can pass True as the seventh and eighth parameters. If you want the Find to be Case Sensitive, you can specify True as the ninth parameter. Finally, if you are running FileMaker Pro 11 or above, you can use the tenth parameter to specify how the results are returned to you, either in plain text, JSON format, or that the function store the results internally to be retrieved later using the CNSQuery_GetRecordData function.

Finding What You Want

The FindCriteria parameter in this function and other related functions allows you to define exactly which Records in your Table that you want to work with. Not every Find you want to do is as simple as saying, “This field needs this exact value in it”. So, this section explains how to do more complex Finds to get exactly what you want. First, let’s say we have a table with the following values in it:

RecordFirstNameLastNameColors
1MaryJonesRed
Blue
2LeAnnJohnsonGreen-Yellow
Red
3AnnParsonsGreen
Orange


Now, the following are different FindCriteria parameters you can use and what CNS Query would return for each:
  • "FirstName=Mary" - This would return Record number 1. Specifying a single Value for a Field will return any Record where that Field's Value is exactly what you specified.
  • "FirstName=Mary¶Ann" - This would return Record numbers 1 and 3. Specifying multiple Values for a Field will return any Record where that Field's Value is exactly one of those Values. Note that this does not match the value "LeAnn" in the second record.
  • "::Contains::FirstName=Ann" - This would return Record numbers 2 and 3. Using the special "::Contains::" keyword before the Field Name tells CNS Query to match the Value anywhere in the Field instead of matching the Field Value exactly.
  • "::Begins-With::LastName=J" - This would return Record numbers 1 and 2. Using the special "::Begins-With::" keyword before the Field Name tells CNS Query to find any Record whose Field Value starts with the Value you specify.
  • "::Ends-With::FirstName=n" - This would return Record numbers 2 and 3. Using the special "::Ends-With::" keyword before the Field Name tells CNS Query to find any Record whose Field Value ends with the Value you specify.
  • "Colors=Blue" - This would not return any Records because none of the Records have the exact value "Blue" for the "Colors" field.
  • "::Contains::Colors=Blue¶Green" - This would return Record numbers 1, 2 and 3. Since the "::Contains::" keyword will tell CNS Query to find the Value anywhere in the Field, this will match "Green-Yellow" in the second Record.
  • "::Contains-Value::Colors=Blue¶Green" - This would return Record numbers 1 and 3. The special "::Contains-Value::" keyword before the Field Name tells CNS Query to find exact Values in the Field. (A Value is defined here as any Text that is on a line by itself.)
Return TypeText
FormatCNSQuery_FindRecords ( TOName ; FindCriteria ; FieldNamesToReturn ; SortFields ; FieldSeparator ; RecordSeparator ; SkipTONameCheck ; SkipFieldNameChecks ; CaseSensitive ; ReturnAs )
Required Parameters
TOName

The Name of a Table Occurrence of the Table to perform the Find in. Specify “” to use the Current Layout TO. In FileMaker Pro 11 and above, you can optionally specify the Database Filename (without the “.fp7” extension) containing the TO in the format “FileName.TOName”.

FindCriteria

A return- or paragraph mark-separated list of “FieldName=FieldValue” pairs describing the Criteria for the Find. See the “Finding What You Want” section above for help in setting up the Find Criteria.

Optional Parameters
FieldNamesToReturn

A return- or paragraph mark-separated list of Field Names that you want returned from the Find. If you do not specify this parameter, all Fields will be returned.

SortFields

A return- or paragraph mark-separated list of “FieldName=SortDirection” pairs describing the Sort order for the returned data. Use “Asc” or “Desc” for the Sort Direction to indicate Ascending or Descending respectively.

FieldSeparator

The Separator to use between multiple Fields. By default, Fields will be separated with a comma.

RecordSeparator

The Separator to use between multiple Records. By default, Records will be separated with a return.

SkipTONameCheck

If True, CNS Query will not check to make sure the TO Name you specified actually exists before performing the Find.

SkipFieldNameChecks

If True, CNS Query will not check to make sure all the Field Names in the various parameters actually exist before performing the Find.

CaseSensitive

If True, CNS Query will preserve the Case in the Field Values of the Find Criteria. (In other words, if CaseSensitive is True, “Green” would not match “green”.)

ReturnAs

Specify “Text”, “JSON”, or “RecordData” to determine how you want the result returned to you. If you choose “RecordData”, this function will return a blank response when successful and you can use the CNSQuery_GetRecordData function to retrieve any data. The default is “Text”.

Examples
Example 1

Returns all Records from the "MyTable" Table where the Field "AField" has the value "Some Value".

CNSQuery_FindRecords( "MyTable" ; "AField=Some Value" )
Example 2

Returns all Records from the current Table where the "LastName" Field is set to either Jones or Johnson. The Result will only contain the LastName and FirstName fields (in that order) separated by a comma and a space, and Sorted Descending by Last Name and then First Name.

CNSQuery_FindRecords( "" ; "LastName=Jones<CR>Johnson" ; "LastName¶FirstName" ; "LastName=Desc¶FirstName=Desc" ; ", " )
Example 3

Returns all Records from the current Table where the word "Green" appears somewhere in the "Colors" Field.

CNSQuery_FindRecords( "" ; "::Contains::Colors=Green" )
Example 4

Returns all Records from the current Table where the "LastName" Field starts with the letter "J".

CNSQuery_FindRecords( "" ; "::Begins-With::LastName=J" )
Example 5

In FileMaker Pro 11 and above, use this form to Find Records in a Table Occurrence in a separate, open Database file and store the results internally to later be retrieved with CNSQuery_GetRecordData.

CNSQuery_FindRecords( "AnotherFile.MyTable" ; "AField=Some Value" ; "" ; "" ; "" ; "" ; "" ; "" ; "" ; "RecordData" )