Product Logo

CNS Query

Query your databases without relationships.

Update Record


Update Record example database
This example takes the concept of Setting a Field dynamically and extends it to updating an entire record at once. In the "Field Values" field, you can type 'Field Name=Field Value' pairs separated by returns to specify which fields to update and what their new values should be. Then by pressing the "Update Record" button, the "Update Record" script is called which uses CNS Query to update multiple fields in the current record.

Download Example File

FileMaker 7–11 Example

FileMaker 12 Example

View Calculation

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 ‘Update Record’ 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.

Update Record Function

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

CNSQuery_UpdateRecordByID( “” ; Get ( RecordID ) ; Update Record::Field Values )

The first parameter is where you specify the name of the Table you want Update. Since we are updating a record in the current table, we can just specify “” here, otherwise this would be the name of a Table. Note that if you want to update a record in a different table, you do not need to define a relationship to that other table. Even if you do have a relationship to that table, it does not have to be valid at the time you call this function. You can also update records in tables that are in other database files. All you need is a Table Occurrence of the external table in your Relationship Graph in order to update a record in the table.

The second parameter is the “FMRecID” parameter which is where the “ByID” part of “CNSQuery_UpdateRecordByID” function name comes from. You must specify the internal FileMaker Record ID of the record you want to update here. If you simply want to update the current record, you can specify Get( RecordID ) here. There is a companion function named “CNSQuery_UpdateRecordByFind” which allows you to specify some Find Criteria for the plug-in to locate the record you want to update if you don’t know the internal FileMaker Record ID or if you want to update multiple records at once. The third parameter is where you specify the ‘Field Name=Field Value’ pairs of fields and new values for the plug-in to update the record with. Separate multiple pairs with a return or the paragraph mark. In our example, we are using the values we typed into the “Field Values” field.

There are three other parameters to the CNSQuery_UpdateRecordByID function that we are not using here. They allow you to skip double-checking the Table, Record ID, and Field Name parameters. Skipping these checks will speed the plug-in up a little bit if you know all the values are valid.