Logic actions

Logic actions add to, update, delete, and query data.

Use an Add Column action to add a new column to a table. This is might be used to add a key column to your table.

Add Column action properties

Use this property To do this
General tab
Label Specify a name to identify the action on the macro workspace.
Comments Add notes or comments to the action.
Connection Name Select a connection to the data source where the table to be updated is located.
Table Name Specify the name of the table to be updated.
Configure tab
Add Column

Click the Add Column button to add a new column to the table.

Specify the Column Name, Data Type, whether the column is a Key column (indexed), whether the Column ID should increment automatically as rows are added, a seed value, whether the value should be reseeded. Key columns must be string or integer type fields.

To remove a column, select Delete for that column.
If Columns Exist... Select this checkbox to prevent overwriting an existing column.

Use a Create Table action to create a new table.

Create Table action properties

Use this property To do this
General tab
Label Specify a name to identify the action on the macro workspace.
Comments Add notes or comments to the action.
Connection Name Select a connection to a data source where the table should be created.
Table Name Specify a table name for the new table.
Output Mode This is set to create a new table or overwrite an existing one if the table already exists.
Delete This Table... Select this checkbox to delete the table once the macro or the parent macro run successfully.
Configure tab
Add Column

Click the Add Column button to add a new column to the table.

Specify the Column Name, Data Type, whether the column is a Key column (indexed), and whether the Column ID should increment automatically as rows are added. Key columns must be string or integer type fields.

Use an Update action to modify values in a table.

Update action properties

Use this property To do this
General tab
Label Specify a name to identify the action on the macro workspace.
Comments Add notes or comments to the action.
Configure tab
Connection Specify a connection to a data source.
Scoped Connection Options Specify a connection to the data source:
  1. In the Connection Name field, specify the connection for the input data.
  2. In the Scope Type field, specify the scope for the input table. Choose from the following:
    • Public — run the action using the standard session scope.
    • Private — create a unique session within which copies of all referenced assets are placed, and run the action within it. Private sessions are destroyed automatically when the action completes.
    • Named — run the action in a unique session that is referenced by a specified name. Other macros located in the same app or in different apps and run by the same user or different users can access the named session. Named sessions are persisted until they are destroyed by the End Named Session action.

      If you choose Named, the Scope Name section is displayed. Do the following:

      1. Click .
      2. In the Parameters section, click Add Parameter or Constant.
      3. Specify the session name.
  3. If you need to create table joins for use with the action, click Create joins and create the necessary joins.
Update/Assignment Actions Specify the tables and fields that you want to update. Do the following:
  1. Click Add Statement.

    A blank statement is displayed.

  2. Construct the query as necessary using the Statement Editor. See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.
Conditions Specify any conditions that must be met to perform the update. Do the following:
  1. Click Add Statement.

    A blank statement is displayed.

  2. Construct the query as necessary using the Statement Editor. See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.
Sort By

Filter the data to further narrow the scope of the query. Do the following:

  1. Click Add Statement.

    A blank statement is displayed.

  2. Construct the query as necessary using the Statement Editor. See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.
Only the first __ records Limit the update to the specified number of records first returned by the action.
Preview tab
Generate data preview

Click to view information on how much data is affected by the action. The preview will use the public session.

On the Data tab, you can view affected records in a list of tables or as a grid of records. Click a table to see the records in a grid. When viewing a grid, close the grid to return to the list.

You can change how many records are displayed by entering the limit in the "Only The First X Records" field, then clicking Refresh.

If the action is part of a macro or references a dynamic view that uses parameters, the "Generate data preview" and Refresh buttons will prompt for parameter values before displaying the preview.

If the macro has joins defined within it, the preview will only display the views, tables, and columns used in the macro's joins.

Use the Update Table action to update existing rows in a single table or to add columns to the table before updating. This is different from the Update action which allows you to update existing columns in multiple tables as well as assign values to parameter values.

Update Table action properties

Use this property To do this
General tab
Label Specify a name to identify the action on the macro workspace.
Comments Add notes or comments to the action.
Configure tab
Scoped Connection Options Specify a connection to the input data source:
  1. In the Connection Name field, specify the connection for the input data.
  2. In the Scope Type field, specify the scope for the input table. Choose from the following:
    • Public — run the action using the standard session scope.
    • Private — create a unique session within which copies of all referenced assets are placed, and run the action within it. Private sessions are destroyed automatically when the action completes.
    • Named — run the action in a unique session that is referenced by a specified name. Other macros located in the same app or in different apps and run by the same user or different users can access the named session. Named sessions are persisted until they are destroyed by the End Named Session action.

      If you choose Named, the Scope Name section is displayed. Do the following:

      1. Click .
      2. In the Parameters section, click Add Parameter or Constant.
      3. Specify the session name.
  3. If you need to create table joins for use with the action, click Create joins and create the necessary joins.
Table Name Select the table to update from the list.
Columns

Specify the columns that you want to receive data. Construct the queries as necessary using the Statement Editor.

See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.

Use Map Table Columns to create a mapping between the source and output tables if they are not automatically mapped.

Notes: 

  • If the macro has joins defined within it, the Columns section, including Map Table Columns, will only display the views, tables, and columns used in the macro's joins.
  • Inserting data into identity columns is not allowed.
  • Auto increment fields will default to blank with no ability to edit.
  • Nullable fields will default to blank rather than NULL (string).

Click Refresh Columns if the table was changed from when the action was created or last updated.
Conditions Specify any conditions that must be met to insert the data. Do the following:
  1. Click Add Statement.

    A blank statement is displayed in the Statement Editor.

  2. Construct the query as necessary using the Statement Editor. See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.
Sort By Filter the data to further narrow the scope of the query. Do the following:
  1. Click Add Statement.

    A blank statement is displayed in the Statement Editor.

  2. Construct the query as necessary using the Statement Editor. See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.
Only The First [value] Records Specify to select only a specified number of records with the highest values.

Use a Scalar Query action to return a single record from a column.

Scalar Query action properties

Use this property To do this
General tab
Label Specify a name to identify the action on the macro workspace.
Comments Add notes or comments to the action.
Configure tab
Connection Specify a connection to a data source.
Scoped Connection Options Specify a connection to the data source:
  1. In the Connection Name field, specify the connection for the input data.
  2. In the Scope Type field, specify the scope for the input table. Choose from the following:
    • Public — run the action using the standard session scope.
    • Private — create a unique session within which copies of all referenced assets are placed, and run the action within it. Private sessions are destroyed automatically when the action completes.
    • Named — run the action in a unique session that is referenced by a specified name. Other macros located in the same app or in different apps and run by the same user or different users can access the named session. Named sessions are persisted until they are destroyed by the End Named Session action.

      If you choose Named, the Scope Name section is displayed. Do the following:

      1. Click .
      2. In the Parameters section, click Add Parameter or Constant.
      3. Specify the session name.
  3. If you need to create table joins for use with the action, click Create joins and create the necessary joins.
Value Specify a table, column, and function or parameter. Do the following:
  1. Click .

    A blank field is displayed.

  2. Construct the query as necessary using the Statement Editor. See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.
Conditions Specify any conditions that must be met to return a value. Do the following:
  1. Click Add Statement.

    A blank statement is displayed in the Statement Editor.

  2. Construct the query as necessary using the Statement Editor. See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.
Sort By Filter the data to further narrow the scope of the query. Do the following:
  1. Click Add Statement.

    A blank statement is displayed in the Statement Editor.

  2. Construct the query as necessary using the Statement Editor. See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.

Use an Insert action to insert data into existing columns in a table.

Insert action properties

Use this property To do this
General tab
Label Specify a name to identify the action on the macro workspace.
Comments Add notes or comments to the action.
Configure tab
Input Scoped Connection Options Specify a connection to the input data source:
  1. In the Connection Name field, specify the connection for the input data.
    2. The System connection lets you access system elements like logs and histories. If you select this connection, be aware that the Preview function isn't supported for the System connection.
  2. In the Scope Type field, specify the scope for the input table. Choose from the following:
    • Public — run the action using the standard session scope.
    • Private — create a unique session within which copies of all referenced assets are placed, and run the action within it. Private sessions are destroyed automatically when the action completes.
    • Named — run the action in a unique session that is referenced by a specified name. Other macros located in the same app or in different apps and run by the same user or different users can access the named session. Named sessions are persisted until they are destroyed by the End Named Session action.

      If you choose Named, the Scope Name section is displayed. Do the following:

      1. Click .
      2. In the Parameters section, select Add Parameter or Constant.
      3. Specify the session name.
  3. If you need to create table joins for use with the action, select Create joins and create the necessary joins.
Output Scoped Connection Options Specify a connection to the output data source:
  1. In the Connection Name field, specify the connection for the output data.
  2. In the Scope Type field, specify the scope for the output.
Into Table Specify the table to modify. This will update the Columns field with columns from the table.
Clear Table Before Executing If you want to delete all of the data from the destination table before the input data is inserted, select the check box.
Columns

Specify the columns that you want to receive data. Construct the queries as necessary using the Statement Editor.

See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.

Use Map Table Columns to create a mapping between the source and output tables if they are not automatically mapped.

Notes: 

  • If the macro has joins defined within it, the Columns section, including Map Table Columns, will only display the views, tables, and columns used in the macro's joins.
  • Inserting data into identity columns is not allowed.
  • Auto increment fields will default to blank with no ability to edit.
  • Nullable fields will default to blank rather than NULL (string).

Click Refresh Columns if the table was changed from when the action was created or last updated.
Conditions Specify any conditions that must be met to insert the data. Do the following:
  1. Click Add Statement.

    A blank statement is displayed in the Statement Editor.

  2. Construct the query as necessary using the Statement Editor. See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.
Sort By Filter the data to further narrow the scope of the query. Do the following:
  1. Click Add Statement.

    A blank statement is displayed in the Statement Editor.

  2. Construct the query as necessary using the Statement Editor. See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.
Distinct Records Specify to insert values into distinct rows.
Only The First [value] Records Specify to select only a specified number of records with the highest values.
Data View tab
[output table name]

Click the name of the output table for a view of the current state of the data referenced by the action.
Preview tab
Generate data preview

Click to view information on how much data is affected by the action. The preview will use the public session.

On the Data tab, you can view affected records in a list of tables or as a grid of records. Click a table to see the records in a grid. When viewing a grid, close the grid to return to the list.

You can change how many records are displayed by entering the limit in the "Only The First X Records" field, then clicking Refresh.

If the action is part of a macro or references a dynamic view that uses parameters, the "Generate data preview" and Refresh buttons will prompt for parameter values before displaying the preview.

If the macro has joins defined within it, the preview will only include the views, tables, and columns used in the macro's joins.

Use a Select action to choose columns from a single table or multiple joined tables to produce an output record set that can be written to a new or existing table. You can:

  • Use Table mapping to define relationships that indicate which records are included (Join types).

  • Use a computed field to create an expression that computes a value using fields from one or more tables.

  • When using an existing table as the output, use field mapping to map output fields to the existing table structure.

Select action properties

Use this property To do this
General tab
Label Specify a name to identify the action on the macro workspace.
Comments Add notes or comments to the action.
Configure tab
Input Scoped Connection Options Specify a connection to the input data source:
  1. In the Connection Name field, specify the connection for the input data.
  2. In the Scope Type field, specify the scope for the input table. Choose from the following:
    • Public — run the action using the standard session scope.
    • Private — create a unique session within which copies of all referenced assets are placed, and run the action within it. Private sessions are destroyed automatically when the action completes.
    • Named — run the action in a unique session that is referenced by a specified name. Other macros located in the same app or in different apps and run by the same user or different users can access the named session. Named sessions are persisted until they are destroyed by the End Named Session action.

      If you choose Named, the Scope Name section is displayed. Do the following:

      1. Click .
      2. In the Parameters section, click Add Parameter or Constant.
      3. Specify the session name.
  3. If you need to create table joins for use with the action, click Create joins and create the necessary joins.
Output Scoped Connection Options Specify a connection to the output data source:
  1. In the Connection Name field, select the connection for the output data.
  2. In the Scope Type field, select the scope for the output.
  3. In the Table Name field, specify a name for the table.
  4. If needed, select the Delete This Table... option to delete the table once the macro is successful.
Columns

Specify the columns that you want to receive data. Construct the queries as necessary using the Statement Editor.

See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.

Use Map Table Columns to create a mapping between the source and output tables if they are not automatically mapped.

Notes: 

  • If the macro has joins defined within it, the Columns section, including Map Table Columns, will only display the views, tables, and columns used in the macro's joins.
  • Inserting data into identity columns is not allowed.
  • Auto increment fields will default to blank with no ability to edit.
  • Nullable fields will default to blank rather than NULL (string).
Conditions Specify any conditions that must be met to insert the data. Do the following:
  1. Click Add Statement.

    A blank statement is displayed in the Statement Editor.

  2. Construct the query as necessary using the Statement Editor. See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.
Sort By Filter the data to further narrow the scope of the query. Do the following:
  1. Click Add Statement.

    A blank statement is displayed in the Statement Editor.

  2. Construct the query as necessary using the Statement Editor. See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.
Distinct Records Specify to insert values into distinct rows.

Use a Delete action to delete data from a table.

Delete action properties

Use this property To do this
General tab
Label Specify a name to identify the action on the macro workspace.
Comments Add notes or comments to the action.
Configure tab
Connection Specify a connection to a data source.
Scoped Connection Options Specify a connection to the data source:
  1. In the Connection Name field, specify the connection for the input data.
  2. In the Scope Type field, specify the scope for the input table. Choose from the following:
    • Public — run the action using the standard session scope.
    • Private — create a unique session within which copies of all referenced assets are placed, and run the action within it. Private sessions are destroyed automatically when the action completes.
    • Named — run the action in a unique session that is referenced by a specified name. Other macros located in the same app or in different apps and run by the same user or different users can access the named session. Named sessions are persisted until they are destroyed by the End Named Session action.

      If you choose Named, the Scope Name section is displayed. Do the following:

      1. Click .
      2. In the Parameters section, select Add Parameter or Constant.
      3. Specify the session name.
  3. If you need to create table joins for use with the action, select Create joins to create the necessary joins.
From Table Specify the table to modify.
Conditions Specify any conditions that must be met to delete the data. Do the following:
  1. Click Add Statement.

    A blank statement is displayed in the Statement Editor.

  2. Construct the query as necessary using the Statement Editor.
Preview tab
Generate data preview

Click to view information on how much data is affected by the action. The preview will use the public session.

On the Data tab, you can view affected records in a list of tables or as a grid of records. Click a table to see the records in a grid. When viewing a grid, close the grid to return to the list.

You can change how many records are displayed by entering the limit in the "Only The First X Records" field, then clicking Refresh.

If the action is part of a macro or references a dynamic view that uses parameters, the "Generate data preview" and Refresh buttons will prompt for parameter values before displaying the preview.

If the macro has joins defined within it, the preview will only include the views, tables, and columns used in the macro's joins.

Use the Drop Table action to remove a table at the end of the macro run.

Drop Table properties

Use this property To do this
General tab
Label Specify a name to identify the action on the macro workspace.
Comments Add notes or comments to the action.
Configure tab
Connection Name

Select a connection to the data source where the table to be updated is located.
Tables to Drop Select which tables to drop from the macro.

Use an End Named Session action to terminate a named session.

End Named Session action properties

Use this property To do this
General tab
Label Specify a name to identify the action on the macro workspace.
Comments Add notes or comments to the action.
Configure tab
Session Name Specify the name of the session that you want to end. Type the name or select to construct a query to obtain the name.

Use a Check Named Session Exists action to determine if a named session is active.

Check Named Session Exists action properties

Use this property To do this
General tab
Label Specify a name to identify the action on the macro workspace.
Comments Add notes or comments to the action.
Configure tab
Session Name Specify the name of the session that you want to detect. Type the name or select to construct a query to obtain the name.

Use a Start Named Session action to start a named session.

Start Named Session action properties

Use this property To do this
General tab
Label Specify a name to identify the action on the macro workspace.
Comments Add notes or comments to the action.
Configure tab
Session Name Specify the name of the session that you want to create. Type the name or click to construct a query to obtain the name.

Use a Bulk Parameter Update action to modify the values of parameters using values contained within a lookup table.

Bulk Update Parameters action properties

Use this property To do this
General tab
Label Specify a name to identify the action on the macro workspace.
Comments Add notes or comments to the action.
Configure tab
Connection Specify a connection to a data source.
Scoped Connection Options Specify a connection to the data source:
  1. In the Connection Name field, specify the connection for the input data.
  2. In the Scope Type field, specify the scope for the input table. Choose from the following:
    • Public — run the action using the standard session scope.
    • Private — create a unique session within which copies of all referenced assets are placed, and run the action within it. Private sessions are destroyed automatically when the action completes.
    • Named — run the action in a unique session that is referenced by a specified name. Other macros located in the same app or in different apps and run by the same user or different users can access the named session. Named sessions are persisted until they are destroyed by the End Named Session action.

      If you choose Named, the Scope Name section is displayed. Do the following:

      1. Click .
      2. In the Parameters section, select Add Parameter or Constant.
      3. Specify the session name.
  3. If you need to create table joins for use with the action, select Create joins and create the necessary joins.
Parameter Name Specify the table and column that contains the parameter names that you want to update. Do the following:
  1. Click .

    A blank statement is displayed in the Statement Editor.

  2. Construct the query as necessary using the Statement Editor. See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.
    The parameter names in the table must begin with the @ symbol.
Parameter Value Specify the table and column that contains the parameter values. Do the following:
  1. Click .

    A blank statement is displayed in the Statement Editor.

  2. Construct the query as necessary using the Statement Editor. See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.
Conditions

Specify any conditions that must be met to update the data. Do the following:

  1. Click Add Statement.

    A blank statement is displayed in the Statement Editor.

  2. Construct the query as necessary using the Statement Editor. See Statement Editor Functions for a list of available functions. If your macro has joins defined within it, see Access a visual join from the Statement Editor.

Last modified: Thursday January 04, 2024

Is this useful?