How To: Create a Model That Makes a Correctly Formatted Query from a String Input with Spe

Summary

Models are often created that expose a string parameter, which is used to create a query to a database. For example, a model may be created that prompts the user for a county name, and then selects this county using the Select Layer by Attribute tool. Typically, this works correctly; however, database specific problems can arise. For example, when the input string contains a single quotation mark ('), it must be escaped when the query string is created. Instructions provided describe how to create a model that makes a correctly formatted query from a string input with special characters.

Procedure

Use the Calculate Value tool to do string manipulation in a model. This tool allows the use of the Python scripting language to do things like manipulate strings.

The following steps detail the process of creating a model that runs 'Make Feature Layer' with a query based on a user input string, and demonstrates how to manipulate the input string with the Calculate Value tool to ensure that the query works correctly. The example used throughout this procedure is for a query containing a single quotation mark (').

In this example, a model is being created that will select a restaurant by name from a file geodatabase feature class containing all restaurants in a city.

Make a new toolbox.

In this new toolbox make a new model.

When a new model is created, a new model window should appear.
Add the Make Feature Layer tool to the model. This tool is found in the Data Management Tools toolbox, in the Layers and Table Views toolset.

Add the tool by dragging it from the ArcToolbox window and dropping it into the Model window. The model should now have the Make Feature Layer tool added.
Add a dataset from a file geodatabase to the model. This is done by dragging and dropping a dataset from the Catalog Tree into the model. Alternatively, the 'add data' button in model builder can be used to add the dataset. The model should now have a tool and a dataset.
Double-click the Make Feature Layer tool that was added to the Model to open its input dialog box. For 'Input Features', use the drop-down menu to select the dataset that was added to the model.

Click Ok to close the Make Feature Layer tool dialog box. The dataset should now be connected to the tool.
Next, a string variable must be created. Right-click in the white space of the model and select 'Create Variable'.

Scroll down and select 'string' and click Ok. A new string variable should be added to the model named 'String'. Rename the variable to 'Restaurant Name'. Double-click 'Restaurant Name' to open its value. Give it a default value. In this case, the default value is Anderson's.

Click Ok to return to the main model window. The string variable now has a value and should turn blue. The model should now have three variables and a tool.
Now, the Calculate Value tool can be added to the model. Drag and drop the tool from the ArcToolbox window into the model. The Calculate Value tool is found in the Data Management Tools toolbox in the General toolset.

There should now be two tools in the model.
The Calculate Value tool is used to manipulate the contents of the string variable named 'Restaurant' to create a query for a file geodatabase. A simple query uses the following syntax:

"FIELDNAME" = 'value'

In this case, it is known that some restaurants will have a single quotation mark (') in their name, for example, Anderson's Restaurant. In a query to a file geodatabase, this single quotation mark (') must be replaced with two single quotation marks (''). For example:

"NAME" = 'Anderson''s Restaurant'

Therefore, when the user types in Anderson's Restaurant in the string parameter, it needs to be changed to Anderson''s Bar before it is used in Make Feature Layer's query parameter.

To do this, double-click the Calculate Value tool to open its input dialog box. In the Expression parameter type in:
```
"%Restaurant Name%".replace("'", "''")
```
The above expression also takes advantage of 'inline variables substitution'. A full explanation of inline variable substitution can be found in model builder at the following link: Inline Variable Discussion.

The other parameter is the data type of the output. Change this from Variant to String.

Calculate value should be ready.

Click Ok to close the dialog box and return to the main model builder window.
It is useful to change names of variables in the model so that they make sense. Change the name of Calculate Values output variable. Currently, it is 'output_value'. Rename this variable to 'FixedName', so that it is easier to remember.
Ensure that Make Feature Layer uses the fixed string in its query. To do this, double-click the Make Feature Layer tool to open its input dialog box. In the Expression parameter type:
```
"NAME" = '%FixedName%'
```
Again, making use of inline variables. In this case, the value of the FixedName variable needs to be substituted into the expression.

Click Ok to save the changes and return to the main model builder window.
Because the tools in this model are only tied to one another through inline variable substitution, it is important to fix the order of operations. To do this, a couple of preconditions need to be set. When a precondition is set on a tool, it means that the tool will not run until the precondition is set. Two preconditions need to be set in this model. First, ensure that Calculate Value will not run until the input string variable (Restaurant Name) has a value. Right-click the tool and select 'Properties'.

When the Properties dialog box appears, click the Preconditions tab. The Preconditions tab should have a list of all the variables in the model, including the Restaurant Name string variable. Select the Restaurant Name variable

and click Ok. The model should now have a dotted line between the string variable and Calculate Value.

This means that the Calculate Value tool will not run until the Restaurant Name variable has a value.
Ensure that the Make Feature Layer tool does not run until after the Calculate Value tool has completed. Set a precondition on the Make Feature Layer tool, which will prevent it from running until the output of Calculate Value (FixedName) has a value. Right-click the Make Feature Layer tool and select 'Properties'. When the Properties dialog box appears, click the Preconditions tab. The Preconditions tab should have a list of all the variables in the model. Turn 'FixedName' on.

Click Ok to save the change and exit the dialog box. The model should now have a dotted line between the output of Calculate Value and the Make Feature Layer tool.
The parameters on the tool need to be exposed, so when the user types in an input name, the output will be the output of Make Feature Layer. To make parameters in a model, right-click the variable that needs to be a parameter and select 'Model Parameter'. This needs to be done on two variables: the 'Restaurant Name' variable

and the output from the Make Feature Layer tool.

Once this is done, the model is ready to be run and tested.
```
Note:
It may be best to test this model in ArcMap, so that visual results are returned.
```
Save the model.