Fetch Record action

Use the Fetch Record action to retrieve records from other tables. 

This action is an automatic looping action. The query loops through each row retrieved and the selected actions are performed for each row.

You cannot use the Fetch Record action to retrieve records from a Header Detail table. Instead, fetch the Header records, then use the Create HD Structure action to create a Header Detail structure. This enables you to interact with the Header as a Header Detail so you can perform functions such as using the Fetch Detail Records action to fetch Detail records from the HD Structure. 

The Fetch Record action is one of several fetch actions you can use in logic blocks. Fetch actions use expressions to refine the records which are fetched. Learn more in Logic block fetch actions and Logic block expressions.

Chunk Fetch

You can also select the Chunk Fetch checkbox to fetch, process, and release results in batches until the query has been completed. This is helpful for fetches that are looped over a long period of time so that the system can process large amounts of records in smaller batches. Chunk fetch works on join tables and with dynamic expressions, but is not supported with aggregations. 

Fetch Record actions configured with Chunk Fetch must be run live, not in NATE containers. 

Sorts

The sort defined in the logic block is always used when present in a logic block fetch. If no sort is defined in the logic block, it uses the database’s sort preference. This may align with the sort specified on the table index, but that is not guaranteed and should not be relied upon.

Logic block fetch actions

Logic blocks contain a number of fetch actions, such as Fetch Detail Records, Fetch Records and Lock, and Fetch a Table Lookup, that allow you to access data from different sources. 

There are two types of fetch actions that you can use in a logic block: fetch actions that retrieve from the database and fetch actions that are limited to data that is exclusive to that specific logic block. 

Fetch from database

The following fetch actions are designed to retrieve records from the database. When the logic block fetches the records, it gains access to and loops over each record.

Fetch actionsUse this action to...
Fetch Detail RecordsRetrieve records from the detail table of a Header Detail application. This action fetches all detail records or the just the active row from the UI. Only use this action with logic blocks that are built over a Header Detail structure. After the logic block fetches the records, actions later in the sequence can access those records.
Fetch Original RecordFetch the original version of a record. That is, fetch the version of the record saved to the database (outside of the NATE Container), which can be different than the current version (inside the NATE container).
Fetch Records

Access records from a single table that is not the driver table. Use expressions in the action to specify the records that you want to fetch from the table.

Fetch Records and LockRetrieve records from a table and, while they are being fetched by the logic block, locks them from being changed by others. Fetch and locks only concern record updates. If a normal fetch action is used on the same record as the fetch and lock, the normal fetch action proceeds as normal. If two fetch and lock actions are used on the same record, the first fetch and lock must finish processing before the second action can proceed. 
Fetch a Table Lookup

Fetch a record from the source table. This action fetches all of the fields in the record associated with the table lookup field, not just the table lookup field. If you do not use a Fetch a Table Lookup action, the logic block only has access to the table lookup field. 

Fetch within logic block

The following fetch actions are designed to retrieve data created and stored within a logic block. These fetch actions do not leave the confines of the logic block and are not able to interact with the database. 

Fetch actionsUse this action to...
Fetch Messages

Fetch all messages, such as severity or type, set by a called logic block.

Fetch Temp Table 

Fetch updated records after you create and insert records into a temporary table. Select one or more fields to sort by and then configure the sorting order of the records.


Update Records action

Use the Update Records action to update an existing record with values set by prior logic block actions. 

In the action, define the table which you want to update. The action updates existing records, not individual fields, with values defined in previous actions. 

If there are Pre-Update or Post-Update table triggers on the table being updated, those logic blocks run. If the table triggers do not result in an error, the updated records are saved to the table. As with any logic block, if an error occurs, no changes are saved. 

After records have been updated, you can use the updated values in later actions. For example, if you use a math action after you use an Update Records action, the updated value is used in the math action.

Set Values action

Use the Set Values action to set or clear a value in a field or variable, or to set a data source name (DSN) from a different DSN. Values can be field values, variables, constants, system values, or workflows. Data source names can be newly created by the action or existing. 

There are several considerations for the Set Values action, such as:

  • There are two components, the value you are setting From and the value you are setting To. 
  • The action replaces whatever value was previously in the To field or variable with the value you specify in the From section. 
  • You can use previously defined variables or data source names (DSN), or create new variables or DSNs in the action. 
  • The action can be used for almost any type of variable. 
  • The order in which you specify the action determines when it runs. 

Create new DSNs 

You can use the Set Values action to create new DSNs to serve as the source data for insert actions. This enables you to conditionally set values on the DSN, and use the Insert Subtable Record action to include subtable values in the DSN. The DSN can then be used as the input for the Insert Records action which inserts a record with all specified values, including the subtable records. 

For example, if you are using a logic block to create a new record in the Directory table and want to populate the Email and Phones subtables in the new record, you could:

  • Use the Set Values action to create a new DSN which points to the Directory table
  • Use the Insert Subtable Record action to insert the subtable records into the DSN.
  • Specify that DSN in the Insert Records action.

When the logic block initiates to create a new record, the subtable values are then included in the record creation.

Add, remove, or set values in a list

Lists are created in the Inputs and Outputs subtable of the logic block record or with the Create List action. Once a list is created, you can:

  • Add/Remove from list—Add or remove DSNs or variables from lists with the add/remove operators.
  • Clear/Set from list—CLear entire lists or assign the contents of one list to another through the clear/set operators.

Learn more in Logic block lists.

Create a UUID

You can use the Set Values action to create a standalone universally unique identifier (UUID). To create a UUID, in the Set Values Assignments section, have a To New Variable of type Text, and a From System of type UUID. 

Create a JSON request body

You can use the Set Values action to create JSON Objects and JSON Arrays as variables in your logic block. Then, you can use the JSON Objects in the request body for endpoint requests. 

JSON Arrays create lists of strings, texts, or JSON Objects, and are referenced within a JSON Object. JSON Objects are composed of keys and values. Keys have to be a string, while a value can be a text, number, list, or other object. 

You can Set a JSON Object From:

  • Another JSON Object
  • A variable or field of type Object
  • A serialized version of a JSON Object in a text value

The value at the JSON Object key can be set from variables, fields, or constants that match their data type.

You can Set a JSON Array From:

  • Another JSON Array
  • A serialized version of a JSON array in a text value

You can also create a new empty JSON Object or JSON Array using the Clear action. Then, add values to the JSON variable. 

You can Add to a JSON Array From any field, variable, or constant that resolves to a:

  • Number
  • Text
  • JSON Object
  • JSON Array

JSON Arrays are not type-specific, which means lists can hold numbers, texts, or other objects in the same list. 

Set values for different data item types

The table below details how the action works on different data items.

Data Item TypeSource/From value must...Value when cleared
AddressBe an address. It also must have multiple attributes, and cannot be a constant, system, or workflow value.null
Color
Be a CSS color value. 
null
CurrencyBe a currency.null
DateMatch the system date format. If assigned from a DateTime or a System value, a time zone must be provided.  Use the Date Time Decomposition action for breaking a DateTime into a Date and a Time.null
DateTimeMatch the configured data item format. Use the Date Time Composition and Date Time Decomposition actions to convert between DateTimes, Dates, and Times.null
EmailMatch the configured data item format. null
Expression [Limited Use]
Not supported. 
-
NameBe a name. It must have multiple attributes, and cannot be set from a constant, system, or workflow value.null
NumberMatch the configured data item format.null
Number (Lookup)Be a Number type data item configured with a list lookup value. 
PhoneMatch the configured data item format.null
PictureMust be a picture field. Source value cannot be a variable, constant, system, or workflow value. null
SubtableNot be set for the subtable. Set values of fields in the subtable instead. To clear, use the Clear Subtable action. Not supported.
Table LookupBe a nwId value. To set values of related values, do it in scope of a Fetch Table Lookup action.null
TextBe set from any value. Use the Convert to Text action to convert values.null
Text (Lookup)Be a Text type data item configured with a list lookup value. 
Text AreaBe a text value if setting from a field. Can't set from a system or workflow value. If setting from a variable it must be the same type of data.null
TimeMatch the configured data item format. Must use a duration subtype when setting a duration subtype. null
True FalseBe set from a true or false value. false
Web Link
Not supported.
-

Aggregate action

Use the Aggregate action to find the maximum, minimum, total, average, or count of numeric or currency fields, or the count of non-numeric fields. 

Configuration

There are several considerations for configuration, such as:

  • The name you give your aggregate is used to reference the action in other logic block actions. For example, in conditional logic you would reference the aggregate name in the Table field. 
  • In the Expression section, you can fetch and filter your selected records.
  • In the Aggregation section, you can select the fields you want to aggregate. 
  • There are different operators for aggregation, such as total, count, average, minimum, or maximum. 
  • Different data item types can use different operators. 
  • You can select multiple aggregate operators. 

Group By

In the Group By section, you can group aggregates together. For example, if you wanted the total values of the Material Needed and Material Available in a manufacturing process, but you also want those totals to be grouped by the supply demand Date. 

In the Aggregate action, you would specify:

  • Field: Material Needed, Aggregation: Total
  • Field: Material Available, Aggregation: Total
  • Group By: Date

The table below shows sample data for this example:

Material NeededMaterial AvailableDate
2301/01/2022
2601/03/2022
1301/01/2022
3501/04/2022
4101/01/2022

The outputs for this configuration would be:

  • Date: 01/01/2022, Material Needed: 8, Material Available: 7
  • Date: 01/03/2022, Material Needed: 2, Material Available: 6
  • Date 01/04/2022, Material Needed: 3, Material Available: 5

To access the aggregate outputs, loop the fetched records and insert them into a temp table. You can then pull the desired outputs from the temp table.

Considerations

The use of this action should only be used when the data is well-known and the filter criteria of the aggregation is as restrictive and limited as possible. There should also be a supporting index on the table being filtered and aggregated upon.  For more information, see Index best practices. This action should never be used when the filter criteria is weak or if the logic is already looping through matching records for other reasons. Never use this action to check for the existence of a record.

Create a Temp Table action

Use the Create a Temp Table action to create a list of records that can be used and referenced in logic blocks. Temp tables hold records but do not commit them to the database. 

Records inserted into the temp table are shaped based on the fields of the table you select during configuration. You can select whether the temp tables holds one, or many records, and whether the temp table can be shared in other logic blocks.

To hold many records, the Allow Multiple Entries checkbox must be selected in the Create a Temp Table action. When not selected, only one record may be inserted, and that record can not be changed or fetched.

To share a temp table, the Shared checkbox must be selected in the Create a Temp Table actions in both logic blocks, and the names must be the same. Shared temp tables bring any contents held inside them into the new logic block. For example, if you create a temp table in one logic block and insert records into it, then you create a new temp table in a different logic block and give it the same name and select the Shared checkbox, the records inserted into the first temp table exist in the new temp table. 

Use the Fetch Temp Table action to fetch and loop over all the records in the temp table. 

Record and value lists

Alternately, you can create lists within the logic block Inputs and Outputs subtable, or with the Create List action. Lists functionally behave the same as a temp table, but provide more clarity during the development process on how and where the lists can be used and referenced. Learn more in Logic block lists.

Create HD Structure action

The Create HD Structure action allows application developers to group records from a Header Detail table, instead of handling the Header table and Detail table records separately. Use this action to insert new records to a Header Detail table, update Header Detail records, or pass a Header Detail record into a called logic block.

Create HD Structure

The Create HD Structure action must have an HD Template selected. This is the table that you want to base your HD Structure on, and persist records into. For example, if you are going to insert Header Detail records into the PaymentRun table, which is a Header Detail table, you would select the PaymentRun table as the HD Template. 

Use different types of HD Structures for different use cases in logic blocks. The table below describes the different types of HD structures, when you might use them, and how they work with Nate Containers:

TypeUse to...How it works with NATE containers
NewInsert a new record to a Header Detail Table.This indicates an intention to persist both header and detail records. At the time the HD structure is created, no trip to the database is made. The end of the logic block or specific actions will trigger a push to the database, whichever comes sooner. All altered HD’s are pushed to the database at those triggering points. At the time of persistence, the presence or absence of a NATE container is relevant. If using a NATE container, no table triggers run. If not using a NATE container, table triggers run. See the note below this table for more information.
ExistingUpdate an exiting Header Detail record.This indicates an intention that the header is an existing record that will be inspected or updated. The details can be changed (inserted, updated, or deleted). Same rules apply to the NATE container as New.
TemporaryCreate a structure that isn't saved automatically such as passing a Header Detail record in to a called logic block. If you use temporary HD Structures but want to commit them, you must use the Save HD action.
This indicates the HD structure is backed by a temp table created elsewhere. As these pass between logic blocks, they are still in-memory lists, nothing persists. It is only when a Save HD action is called that anything happens in the database. Only then do these records turn into real persisted records. At that point, the presence or absence of a NATE container becomes relevant because the records are either persisted to the NATE container or to the database. See the note below this table for more information. Keep in mind that using the record's disposition or using filters and sorts on the Fetch Details action are not supported when using temp HDs.

A common use case is for one caller to create a temp table, create an HD structure out of it, and then call an action block. This pattern works because the action block is obligated to call the Save HD action. If no Save HD action is called, then nothing persists. The caller gets the mutated records inside their HD of type temp and nothing more.


Update HD Structure

If you use an HD Structure in a loop that fetches records, there may be changes to the data that makes it necessary to recreate the HD Structure. For example, if you had Header information that changes in a Header Detail table. Use the Create HD Structure action again in the logic and use the same name as your previous HD Structure. This creates a new HD Structure and deletes the old one.

Delete HD Structure

To delete a header and associated detail records from an HD Structure in a logic block, you must first fetch from the Header Detail table and then use the delete action specifying the data source returned by the fetch. An HD Structure is not required. 

Create Multiple HD Structures

If you want multiple HD Structures in your logic, use the Create HD Structure action multiple times, but with different names. 

Increment and Fetch Auto Number action

Use the Increment and Fetch Auto Number action to retrieve the next number from an automatic number definition.

Automatic numbers are non-repeating, alphanumeric values attached to data items that need to increment automatically as records are created. Automatic numbers are configured in the Automatic Number Definition application. Learn more in the Automatic number configuration topic. 

To use this action, the definition must already be configured. On the Details panel of the action, identify the data item and table from the automatic number definition you want to reference. 

When this action runs, the system automatically pulls in the next value from the definition without assigning it to a record. 

Retrieve Module Settings action

Use the Retrieve Module Settings action to retrieve values defined in a Module Settings application to use in your logic block. Module settings configure options or administrative settings that drive the behavior of applications within a family or module. 

For example, if you configure a logic block to populate the Due Date field in the Supplier Invoice application and the supplier doesn't have specific payment terms, you can use this action to retrieve the module settings that determine the default payment terms. 

 Module settings can be set on different levels including:

  • Global—Applies to all org units. In most cases, global settings are the default, applied at all levels unless explicitly overridden.
  • Org Unit—Set for a specific org unit that is defined in the company structure. If the org unit does not exist in the company structure, you receive an error. Org units defined as a Company are the highest level in the hierarchy defined by the Company Structure application. Company module settings are traversed above other org unit types. 
  • Person—Applies to all records associated with an employee record. 

The Retrieve Module Settings action retrieves all of the settings defined in a Module Settings application, regardless of their level. If the requested level corresponds to a setting that's empty, the action automatically traverses up the company structure until it finds a level that is not empty. If no org units have a non-empty value after traversing the entire company structure, the Retrieve Module Settings action returns the setting configured on the Global level. Employee records are not associated with the company structure, so you must define the org unit you want Person settings to traverse to in the logic block action. 

For example, you could have a Module Settings application configured with the following module settings:

  • Default Payment Terms setting: Table Lookup field, Do Not Traverse: False
  • Default Shipping Terms setting: Table Lookup field, Do Not Traverse: False
  • Receipt Required setting: Checkbox (True/False field), Do Not Traverse: N/A
  • Default Shipping Note setting: Text field, Do Not Traverse: True

Default Payment TermsDefault Shipping TermsReceipt RequiredDefault Shipping Note
Global settingNet 30GroundTrueThank you for your business!
Company type Org Unit settingNet 45
False
Org Unit setting
Ground ExpressTrue
Person settingNot Defined

If the Retrieve Module Settings action retrieves the Company level, the returned data would be:

  • Default Payment Terms setting: Net 45
  • Default Shipping Terms setting: Ground 
  • Receipt Required setting: False
  • Default Shipping Note setting: N/A

If the Retrieve Module Settings action retrieves the Org Unit or Person level, the returned data would be:

  • Default Payment Terms setting: Net 45
  • Default Shipping Terms setting: Ground Express
  • Receipt Required setting: True
  • Default Shipping Note setting: N/A

Example

The WriteItemBalance logic block is called by the WriteItemLedgerTransaction logic block. The purpose of the WriteItemBalance logic block is to validate, and then update or insert records in the ItemBalances table, based on records passed in from the ItemLedgerTransactions table. This logic block uses module settings to validate if the Allow Negative Inventory Balances setting is selected if the transaction results in a negative Quantity On Hand. If the Allow Negative Inventory Balances setting is cleared, the user receives an error message. 

The logic block has a Retrieve Module Settings action that retrieves the Inventory module settings. When it retrieves the module settings, it retrieves the record configured for the org unit that was passed in. If there isn't a setting record for that org unit , the action traverses the company structure until it finds an Inventory module setting record for a traversed org unit. If no org units are found after traversing the entire company structure, the Retrieve Module Settings action returns the setting configured on the Global level.

Traverse tree action

Instead of performing and nesting multiple fetches, use the Traverse Tree action to step up each level of a tree. The Traverse Tree action allows you to find the path of a unit in a structure.

For example, the DeriveGLAccount logic block traverses the tree to find the company for a defined org unit. That is, the logic block traverses the tree of the OrgUnitRelationships table, starting from the org unit defined in the OrganizationalUnit field. When a match is found, the logic block uses the result of the Traverse Tree action to set a field value.

When you choose a table to traverse it must be associated with a Relationship application. The field you choose to start from is usually a table lookup field that looks up on the table you choose to traverse. For example, in the DeriveGLAccount logic block traverses the OrgUnitRelationships table, starting from the OrganizationalUnit field of the GLMapping table. The OrganizationalUnit field of the GLMapping table is a table lookup field that looks up on the OrgUnitRelationships table. 

The Traverse Tree action checks up the tree from the Start From field to the top. Control where in the tree the action stops with a Conditional action or a counter. Use a conditional action when you know what level you want to stop on. For example, the DeriveGLAccount logic block uses a conditional to check if the OrganizationalUnitType of the contact = Company. This means that when the level in the tree is a company level, the action stops traversing. Use a counter when you know how many levels you want to traverse. For example, if you only want to traverse 3 levels up the tree.  

When you use the Traverse Tree action you can access all of the fields in the records without fetching them separately. However, if you want to update the records, you must use the Fetch Records action in the Data Found branch of the Traverse Tree action. 

Build Directory Collection action

The Build Directory Collection action is used to provide records from the directory. 

Use this logic block action to provide a list of records from the Directory table to another application or process, such as the Message Definitions application or Workflow Approvals application. 

Update Workflow action

Application developers use the Workflow Update action to set the workflow state and workflow state type on a set of records with existing workflow defined. This action is the preferred approach for workflow conversion since the system reevaluates each records workflow type based on the workflow types available in that environment. This includes any workflow types added by partners or customers in downstream development. 

To use the Update Workflow action, use the Fetch Records action in your logic block to:

  • Select the records which need workflow conversion. Filter for the appropriate records using regular fields, as well as workflow state, workflow state type, and workflow type.
  • In the Data Found loop, use the Update Workflow action to set the desired workflow fields on the records. Optionally, assign a new value to the workflow state and workflow state type. If you leave the workflow state or workflow state type mappings empty, the workflow field is not changed in the record. Workflow type, however, is always reevaluated by this action.  
  • Use the Update Record action to save these changes. The Update Workflow action only sets the workflow fields on the DSN, so you must use the Update Record action to persist the changes.