Use the Query module to create customized database queries on your Chameleon data types.

You can create and manage your Queries in Flow. From the Home page, select Content Control > Query.

Add a New Query

You can add new Queries to the All Queries list or to a Query Playlist. The All Queries list serves as the master list of queries whereas a Query Playlist simply holds a reference to the Query that exists in the master query list. If adding a new query to a Query Playlist, the new query is first added to the All Queries master list, and then a reference item to the query is added to the playlist.

Select a list - either choose the All Queries list or a Query Playlist.
Click on the Add New button.
You can add a query manually or by using the Query Wizard. Click on the Add Now button if you want to save the query.

Manual Edit

With manual edit you enter the desired SQL statement directly in the edit window.

Query Wizard

The wizard allows you to select tables and columns of data that you want.

Query Option	Details

Query Option

Details

Name

The name of the query item. This is a required field. The value of the name must also be unique.

Description

A description of what the query does.

Manual Entry / Query Wizard

Use Manual Entry to manually create or modify your query.

Use the Query Wizard to help with building your query by choosing the tables and columns to use.

Using Parameters

In Flow version 15.7.3.4 and up, there is now support for parameters inside the queries.

If you include the @ symbol in your query, it'll be used to add a parameter to the query, allowing you to use a single query, in a variety of ways.

Example:

SELECT * FROM `league` l WHERE l.`code` = @LEAGUE

When you try out the MySQL statement by pressing the 'Execute Query' button, the Query Parameters dialog will pop up to provide you with a means to enter a value (and the value type) for the @LEAGUE parameter.

There are 4 value type settings that can be chosen:

text - the value will be treated as text (string). Note that enclosing quotes (' or ") around a text value is not needed.
number - the value will be treated as a number (can be an integer or decimal value)
boolean - the value will be treated as a boolean value
date - the value should be in MySQL text format, i.e., YYYY-MM-DD, for example: 2023-05-31.
as-is - use this setting if the @ parameter is meant only for inline usage and not to be substituted for. An example of such parameter usage might be for generating output row numbers. When selected, the value textbox will be grayed out and become inactive.

Set Parameters in a query when testing

Note: In the Query Parameter dialog, you can choose what type the value of the @ parameter should be so that it matches the data in the database.

Set Parameters in a rundown

Clicking on the item's 'Edit Parameters' button will cause the 'Query Parameters' dialog to appear with the values currently assigned to the item. This button and the label that indicates how many parameters there are will only appear if there are @ parameters in the query item's MySQL statement.

Set Parameters in BLADE

Clicking on the 'Edit Parameters' button will bring up the 'Query Parameters' dialog.

Special Cases

When you want to have the query output links to an items media like the standard BLADE formatted data does there is an option to enable this.

Local Media URL

You use the text LocalMediaUrl(mediaIdField) where the mediaIdField is the table and column name of the field with the media ID value. (as of version 16.2.2.2)

SELECT LocalMediaUrl(t.logoID) FROM `team` t where t.`code` = @teamCode;

This will be replaced before it is run with text similar to:

 SELECT CONCAT('https://tickit.company.com/tickit', '/blade/media/', IFNULL(t.logoID, 0)) FROM `team` t where t.`code` = @teamCode;

Local Flow URL

Similarly the text 'LocalBaseUrl' will be automatically be replaced by the URL of the Flow instance. (Example: https://tickit.company.com/tickit) (as of version 16.2.2.2)

Time Zone Specific Now Time

The text TZ_NOW() will be automatically be replaced by the value of the current time in the time zone given. In a Query run in Flow - it is the time zone of the user. In BLADE the time zone can be specified using a parameter with the value being time zone id. (as of version 16.2.3.4)

Manage Queries

You can manage existing Queries as follows:

Action	Details

Action	Details
Remove a Query	Click on the remove icon to remove a query.
Remove Selected Queries	Select the Queries you want to remove. You can do this by: checking the selection checkboxes on the left side of the items, or by holding down the CTRL key and clicking on individual items, or by selecting an item first, then holding down the SHIFT key and clicking on an item to select the range of items in between. Then click on the Remove Selected button to remove the selected queries.
Remove all Queries from a Playlist	This option is only available when working on a Query Playlist. Click on the Remove All button to clear the all the items from a playlist.

Note: Removing a query in a playlist only removes the item from the playlist but does not remove the original source query (which exists in the All Queries master list). To delete the actual query, choose the All Queries list, select the query and remove it by clicking on it's remove icon

or click on the Remove Selected button.

BLADE for Queries

Queries can be accessed through BLADE. See the topic BLADE - Query for details.