Create List from SQL

Audience: Low-code Engineers

Skill Prerequisites: Actions, Lists, SQL, Tokens

Runs a SQL query and saves every row it returns as a list. Each row becomes one list item, and each column becomes a property of that item.

You can then go through the list with Execute Actions for each List Entry, or use it with the other list actions. The number of items is saved in the [<List Name>:Count] token.

The list only exists while the actions run. It isn't saved anywhere and isn't a Plant an App entity.

note

This action requires the Data Integration feature (Standard) to be licensed. If it isn't, the action fails with a message that the feature is unlicensed.

Typical Use Cases

• Send an email to every contact returned by a query
• Export query results to a file with Create CSV from List or Create Excel from List
• Return rows as JSON with List to JSON
• Copy rows from one database to another, with a different connection and Import List into Database
• Run the same actions for each row, for example to call an API for each pending record

Don't use it to

• Read a single row into tokens, or run an INSERT, UPDATE or DELETE. Use Run SQL Query instead.
• Load users to act on them, such as granting roles. Use Load Users from SQL instead.
• Export a large result to Excel. Use Create Excel from SQL query, which doesn't need a list.

Related Actions

Action Name	Description
Execute Actions for each List Entry	Runs actions once for each item in the list.
Run SQL Query	Runs SQL and saves the first row in tokens.
Create List from JSON	Creates a list from a JSON array.
Create CSV from List	Writes the list to a CSV file or token.
Create Excel from List	Writes the list to an Excel (.xlsx) file.
List to JSON	Turns the list into a JSON array.
Import List into Database	Saves the items of a list into a database table.
Paginate List	Keeps one page of the list.

Input Parameter Reference

Parameter	Description	Supports Tokens	Default	Required
Database	A Database connector to run the query against. If it's empty, the application database is used. See Connection.	No	empty	No
Override Connection String	A connection string, or the name of a connection string from web.config. When it's filled in, it's used instead of Database. See Connection.	Yes	empty string	No
Query Timeout	How many seconds the query can run before it times out. Empty or 0 means 600 seconds (10 minutes). Values from 1 to 9 are raised to 10. Negative or non-numeric values make the action fail.	Yes	600	No
SQL Query	The query that returns the rows. Tokens are replaced with SQL-safe text, but you add the quotes. See Tokens in the query.	Yes	empty string	Yes
Bind Tokens	Parameters for the query. Each row has a Parameter Name, for example CustomerId, and a Parameter Value, for example [CustomerId]. Use @CustomerId in the query. This is the safest way to pass values.	Yes (values)	empty	No
List Name	The name of the list, for example OrdersList. If a list with this name already exists, the rows are added to it, unless Clear before loading is on.	Yes	empty string	Yes
Clear before loading	Starts with an empty list, so the rows replace the items of an existing list with the same name.	No	false	No
Properties	Chooses which columns to keep and what to call them. Each row has a SQL Column, for example EmailAddress, and a List Property, for example Email. With no rows, every column is kept with its own name. See Columns and properties.	Yes (SQL Column)	empty	No
On Error	Actions to run if the query fails. They can use the [Exception], [ExceptionType], [ExceptionMessage] and [ExceptionStack] tokens. See Errors.	No	empty	No

Output Parameters Reference

Output	Description
List <List Name>	The list, with one item for each row.
[<List Name>:Count]	The number of items in the list, for example [OrdersList:Count]. When rows were added to an existing list, it's the total, not just the new rows.

Columns and properties

• With no Properties, each item gets one property per column, named like the column. Give computed columns a name, for example SELECT COUNT(*) AS OrderCount.
• With Properties, only the listed columns are kept. Square brackets around a List Property are removed, so [Email] becomes Email. Every SQL Column must be in the result, or the action fails.
• Values keep their database types. Numbers stay numbers and dates stay dates. This matters for actions that write the values, such as List to JSON or Import List into Database.
• NULL becomes an empty value.

Inside Execute Actions for each List Entry, use the properties as [ListName:PropertyName], for example [OrdersList:Email].

Connection

The action picks the database in this order:

1. Override Connection String, if it's filled in
2. The connection string of the Database connector, if one is selected
3. The application database

Override Connection String can also be the name of a connection string in web.config. A named connection string uses its own provider, for example ODBC. A connection string entered directly, or taken from a connector, is opened as a SQL Server connection. To use another database engine, add a named connection string with the right provider to web.config and enter its name.

{databaseOwner} and {objectQualifier} in the query are replaced with the values of the application database.

Tokens in the query

Tokens in SQL Query are replaced before the query runs. Single quotes in token values are doubled, so a value can't end a string early. The action doesn't add quotes, so put text values in quotes yourself:

SELECT * FROM Orders WHERE Status = '[Status]'

Tokens that don't exist are left as they are, because square brackets are also SQL syntax, for example [dbo].[Orders]. So a misspelled token ends up in the SQL as [TokenName].

Bind Tokens are safer. The values are sent as real SQL parameters, not pasted into the SQL. Values are sent as text.

SELECT * FROM Orders WHERE Status = @Status

Errors

The On Error actions run first. Then the action still fails, unless an On Error action ends execution itself, for example Stop Execution. Administrators and low-code engineers see the original error. Other users see a generic error.

Errors include a SQL error, a timeout, an invalid Query Timeout, a failed connection and a SQL Column that isn't in the result.

Considerations

• Loading again adds rows. Without Clear before loading, running the action again with the same List Name, for example inside a loop, adds the new rows to the list. Turn it on when you want only the latest rows.
• List names are case-sensitive. OrdersList and orderslist are two different lists. Use the exact same name in the actions that follow.
• Always set List Name. An empty name doesn't use the default list of a listing. It creates a list with an empty name, which other actions can't find.
• All rows are kept in memory. Limit the result in the query, for example with TOP or WHERE, and select only the columns you need.
• Only the first result set is read. If the query returns several result sets, the others are ignored.
• An empty query runs nothing. The list is created if it doesn't exist, and [<List Name>:Count] is 0.
• Put text tokens in quotes, or better, use Bind Tokens. In conditions, compare with quoted strings, for example [OrdersList:Count] != "0".
• Security. Only low-code engineers should write SQL. Never put user input straight into the query without quotes, and prefer Bind Tokens.
• In User Guides, the action has fewer settings. There's no Database, Bind Tokens or Clear before loading, and Override Connection String is called Other Connection String. Rows are always added to an existing list with the same name.

Examples

tip

To understand how to use the below examples, please see Running Examples.

1. Load a customer's open orders

This action loads the open orders of the customer in the CustomerId token, using a bind token. It creates the OrdersList list, with every column as a property. Clear before loading makes sure old items aren't kept.

{
    "Title": "Create List from SQL",
    "ActionType": "LoadEntitiesFromSql",
    "Description": "Load the customer's open orders",
    "Parameters": {
        "SqlQuery": "SELECT OrderId, OrderNumber, Total, CreatedOn FROM Orders WHERE CustomerId = @CustomerId AND Status = 'Open' ORDER BY CreatedOn",
        "BindTokens": [
            {
                "name": "CustomerId",
                "value": "[CustomerId]"
            }
        ],
        "EntityName": "OrdersList",
        "ClearBeforeLoad": true,
        "EntityProps": [],
        "OnError": []
    }
}

Next, add Execute Actions for each List Entry with List Name set to OrdersList. Inside it, use [OrdersList:OrderNumber] and [OrdersList:Total]. [OrdersList:Count] holds the number of orders.

2. Load contacts from another database and rename the columns

This action reads contacts from the database of the CrmDb connection string in web.config. Only two columns are kept, renamed to Name and Email. It can run for up to 2 minutes. If it fails, the error is logged before the action fails.

{
    "Title": "Create List from SQL",
    "ActionType": "LoadEntitiesFromSql",
    "Description": "Load the newsletter contacts",
    "Parameters": {
        "ConnectionString": "CrmDb",
        "QueryTimeout": "120",
        "SqlQuery": "SELECT FullName, EmailAddress FROM dbo.Contacts WHERE Newsletter = 1",
        "BindTokens": [],
        "EntityName": "ContactsList",
        "ClearBeforeLoad": true,
        "EntityProps": [
            {
                "name": "FullName",
                "value": "Name"
            },
            {
                "name": "EmailAddress",
                "value": "Email"
            }
        ],
        "OnError": [
            {
                "Title": "Log Error",
                "ActionType": "LogError",
                "Parameters": {
                    "Message": "Loading contacts failed: [ExceptionMessage]"
                }
            }
        ]
    }
}

Revised 09/27/2026