Find Records example database

This example shows how to Query a table in your database and returned structured data using the CNSQuery_FindRecords function. Choose a field to search on with the “Field Name” menu, a selector to use when finding matches from the “Selector” menu, and then type in a value to match in the “Field Value” field.

For example, if you wanted to find all presidents that belonged to the republican party, you could select ‘Party’ from the “Field Name” menu, ‘Contains’ from the “Selector” menu, and type in ‘rep’ in the Field Value field.

Pressing the “Find Records” button calls the “Find Records” script which uses CNS Query to set the “Result” field with all matching presidents in ‘LastName, FirstName’ format sorted by last name.

FileMaker 7–11 Example

FileMaker 12 Example

To see the calculation that tells the plug-in what to do, open ScriptMaker/Manage Scripts by going to the “Scripts” menu, and choosing ScriptMaker/Manage Scripts. Once in ScriptMaker/Manage Scripts, double click on the ‘Find Records’ script. Note that we are using the “Set Field” script step which consists of two settings. The “Specify target field” button allows you to choose which field you want to set. In our case we want to set the “Result” field. The second specify button is where the CNS Query calculation exists. Click this button to see the calculation used to tell the plug-in what to do. The following image shows the first “Set Field” script step calculation. You can view the other script step calculations by following the same procedure.

The “Find Records” function is very easy to use. In our script, it looks like this:

Let( [ theSelector = Case( Find Records::Selector = “Equals” ; “” ; “::” & Find Records::Selector & “::” ) ; theFieldName = theSelector & Find Records::Field Name ; theFindCriteria = theFieldName & “=” & Find Records::Field Value ; theFieldNamesToReturn = “Last Name¶First Name” ; theSortFields = “Last Name=Asc” ; theFieldSeparator = ", " ]; CNSQuery_FindRecords( “” ; theFindCriteria ; theFieldNamesToReturn ; theSortFields ; theFieldSeparator ) )

We are using a Let statement to make the calculation easier to read. The first variable, “theSelector” is taking the value from the “Selector” menu and adding “::” onto the beginning and end unless the selector is Equals. When specifying Find Criteria with CNS Query, you use ‘fieldname=fieldvalue’ pairs to tell the plug-in what records you want to find. The default comparison is “equals” which means the plug-in is going to try to find records that match exactly, letter for letter. However, an exact match is not always want you want or need. So, the plug-in also offers “Begins-With”, “Ends-With” and “Contains” comparisons. To use these types of comparisons, you surround them with double-colons (“::”) and place them in front of the field name. For example, if you want to find all records where the “Last Name” field began with “Smi”, you would specify '::Begins-With::Last Name=Smi". So, the “theSelector” variable is adding on these double-colons if needed.

The second variable, “theFieldName”, is adding the “Selector” value onto the beginning of the “Field Name” value. The third variable, “theFindCriteria” can then take “theFieldName” and add an equals and the “Field Value” value. The fourth variable, “theFieldNamesToReturn”, tells CNS Query which fields from our found set we want information from. In this instance, we are only interested in the “Last Name” and “First Name” fields. We also want to sort the result ascending using the “Last Name” field. If we wanted to sort descending, we would specify “Last Name=Desc” here. We could also sort on more than one field by adding more ‘fieldname=sortdirection’ pairs separated by a return or paragraph symbol. For instance, if we wanted to sort on “Last Name” then “First Name”, we could specify “Last Name=Asc¶First Name=Asc”. The final variable is the “theFieldSeparator” variable where we are specifying a comma followed by a space. By default, the CNSQuery_FindRecords function will separate multiple fields from the same record with a comma by iteslf. However since we are trying to format our result in the form “Last Name, First Name”, we need to specify that extra space after the comma.

CNSQuery_FindRecords( “” ; theFindCriteria ; theFieldNamesToReturn ; theSortFields ; theFieldSeparator )

Finally, the calculation part of the Let statement uses the CNSQuery_FindRecords function with all the variables we set up to perform the actual find. The first parameter is where you specify the name of the Table you want to search. Since we are searching the current table, we can just specify “” here. There are four other parameters to the CNSQuery_FindRecords function that we are not using here. The sixth parameter would allow us to specify a different record separator if we wanted. By default, the Record separator is a single return. The seventh and eight parameters allow you to tell the plug-in to skip double-checking the Table and Fields respectively. This can speed the function up a little if you know for sure you have everything typed correctly. The final parameter to the CNSQuery_FindRecords function allows you to turn on character case sensitivity. If you specify True for this parameter, a search for “smi” would not match “Smi”. The default value for CaseSensitive is False, meaning character case is ignored when finding records.