You are here:

Dynamic Input Tool

The Dynamic Input tool allows the user to read from an input database at runtime and dynamically choose what records to read in. Alteryx does not input the entire database table content, instead it filters the data and only returns the user specified criteria and joins it to the data coming into the tool.

Using the Dynamic Input tool can have a significant impact on speed when reading a relational database across the network. It can also perform Spatial queries on relational databases that have no spatial objects in them.

Configuration Properties

Specify the Input Path to the database table for reading. Click the Edit Button and the Input Data Source Dialog box displays.
- When reading a relational database that contains multiple tables, the Choose Table or Specific Query dialog box will display.
Choose how to read and the action to execute with this input. Choices include:
- Read a List of files: Reads a list of files contained within the connection stream and performs the specified action, updating the input path with incoming field data.
- 1. Specify the Field name containing the list of files. All fields being passed to this tool will be available.
  2. Select the appropriate Action. Choices include:
    - Change File/Table Name: Takes the specified database table name and changes it based on the values in the specified field.
    - Change Entire File Path: Takes the entire specified database input path and changes it based on the values in the specified field.
    - Append Suffix to File/Table Name: Takes the specified database table name and appends a suffix to it. The suffix is the value in the specified field.
    - Prepend Prefix to File/Table Name: Takes the specified database table name and prepends a prefix to it. The prefix is the value in the specified field.
- Modify SQL Where Clause: Allows the user to specify which records and values get returned from the database.
- 1. Click the Add button to include a new clause. Choices include:
    - Pass a Field to the Output : Takes a column of the data being passed into the Dynamic Input tool and appends it to the data being returned from the database.
    - 1. Specify the field from the connection to append to the data being pulled from the specified database. All fields being passed to this tool will be available.
    - Replace a Specific String: Searches the database for the specified string and replaces it with values from an incoming field.
    - 1. Specify the string to replace
      2. Select the field to replace the string with. All fields being passed to this tool will be available.
    - SQL: Update WHERE Clause: Changes the initial WHERE clause that was specified when the input database was chosen.
    - 1. Select the SQL Clause to update. All predefined SQL Clauses will appear in this dropdown list.
      2. Select the Type of field value that will be replaced
      3. Specify the Text to Replace from the existing WHERE Clause
      4. Choose the Replacement field. All fields being passed to this tool will be available.
      5. Checkbox for Group Replacement Value for SQL IN Clause. When checked, a single query is run. Left unchecked, an individual query is run for each IN clause unless the Size Limit is exceeded, in which case the single long IN clause is divided into a set of smaller IN clauses / queries that don't exceed the size limit. This box automatically gets checked when using an IN clause.
      6. Specify the Character Limit for the IN Clause. Default is 1000. The size limit is the threshold of how many characters can be used in the query.
    - SQL: Spatial Filter: Takes existing Latitude and Longitude coordinates from the database and determines whether or not they are contained within the bounding rectangle of a polygon object from the incoming connection.
    - 1. Specify the Latitude and Longitude Coordinate fields from the database.
      2. Checkbox, Fields are Integer. Check this box if the coordinate fields are whole numbers. Leave it unchecked if they are decimals.
      3. Specify the Polygon object field contained within the connection stream. All polygon objects being passed to this tool will be available.
    - SQL: Update Stored Procedure: Changes the Stored procedure that was selected when the database was chosen.
    - 1. Select the Parameter to update from the dropdown list. The Parameter Name and Value type will auto-populate based on the selection.
      2. Specify the Text to Replace from the selected Stored Procedure.
      3. Select the Replacement Field from the input stream that contains the text that will replace the text specified in the previous step.
  2. Click the Edit button to change the parameters of a clause
  3. Reorder the clauses by utilizing the Up, Down Buttons.
  4. To delete a clause, select it and click Delete.

Things to watch out for:

Make sure the schema of all the files you are reading is the same, otherwise an error will occur.

Other Tips:

You can dynamically change part of a query fairly easily. If you need more than the simple options in the tool, use a formula tool to remake the entire path or connection string or table and read based on that.