In general, it is important to avoid unnecessary database fetches. This means that you should not attempt a fetch if you can easily determine that no data exists to be found. Configure your logic so that a fetch is only attempted if data is likely to be found.

If the goal of your fetch is to verify the existence of records (such as for filtering), use the Limit value in the Query field in your fetch action to limit the fetch to only 1 record. This means that you only fetch one record to verify if records exist.

Common logic blocks

Call a common logic block, or a logic block built specifically to be used in other families to access data, when you need to fetch a table that is not in the same family as your logic block. Do not directly fetch tables that are not in your family. This allows family owners to control how others interact with their tables. There are two exceptions:

  • Fetch a table lookup. This is because table lookups fetch the related fields to a table as read only. 
  • Fetching multiple records.  Nextworld does not provide a logic block that returns multiple records in one call.

For example, in the ProcessWOMaterialTransactionLines logic block you need to retrieve an item cost. In this case, you call the common logic block RetrieveItemCost rather than fetching the ItemCosts table directly.

Learn more about the different fetch actions in the Logic block fetch actions topic.

Fetches and indexes

It is important to consider table indexes when configuring a fetch action to ensure efficient fetches. Use expressions to configure the order of the fields in a fetch action to match the sequence of the table index. While you don't have to use all of the index fields in the fetch, the fields you use must match the order of the index. Remember that when you're developing logic blocks and testing them, you could be running them with small amounts of data, which doesn't impact performance too much. When the logic blocks run in production environments, they could be running with significant volumes of data, which could drastically impact performance.

Read more in the Index sequencing topic. 

Fetches and workflow

Customers can customize workflow and add new states within the workflow.  The following are the best practices when fetching using workflow fields. 

  1. Fetch using the state type.  Remember, you can add new state types for your workflow if the standard five options are not sufficient. 
  2. For master data tables, such as Directory, consider fetching equal to Active rather than not equal to Active as you may get more records than intended.  If a customer adds steps to approve, your logic most likely will not want records that are not approved and if you use 'not equal to active', the pending records will be fetched.  

Determining the best fetch action to use

There are multiple fetch actions that are possible to use and knowing which one to use is situational and not a universal rule. Below are some general performance considerations to take into account when choosing whether to use a Fetch action or a Fetch Details action. These statements are generally true, but won't always fit based on architecture, table size, table indexes and database load. Performance testing is always recommended and will give you the best answer for any given scenario and can take scalability, server, and database conditions into account. 

Fetch records and fetch detail records actions are inherently the same, but may have downstream performance considerations.

  • There should be no direct performance difference between Fetch Detail Records (with an existing HD structure) and Fetch Records (with an nwHeaderId)
  • However, there are notable performance differences downstream of that first fetch. Usage of the HD structure means maintenance on the Header and Detail tables together, which is worse for performance. Having said that, usage of the full HD is necessary when working in the action block or passing the structure to/from another logic block with the same shape. It is typically better to not use the HD structure if it is not necessary. 
  • If there is no need to define and maintain the relationship to a header table (because the logic only needs to interact with a detail table), it will be more performant to avoid the HD structure. 
  • Not using an HD structure in some situations, can introduce data integrity issues. There is a lot of processing that happens for you with the HeaderDetail (H/D) concept (e.g. common fields, HeaderDetail Triggers) whose behavior is bypassed when dealing with header or detail tables directly. This can result in inconsistent data between the detail, its header, and other data in the system.

Dynamic Expressions in Fetch actions best practices

These best practices ensure that the Build Dynamic Expression is used consistently in Fetch actions in logic blocks throughout Nextworld.

When you use the Build Dynamic Expression action, make sure to build your dynamic expression at the beginning of your logic block to ensure it is in scope for all actions.

If the logic block runs frequently, such as when it is used as an action block, you must verify that the sequence of fields in a logic block and the sequence of indexed fields in the table at least partially match. For example, if the dynamic expression is (1 and 2 and 3) and the fetch is (4 and 5 and the dynamic expression), then at runtime the expression is interpreted as  (4 and 5 and (1 and 2 and 3)).  An index must be configured using that order: 4, 5, 1, 2, and 3. 

Dynamic expressionFetchRuntime interpretationIndex order
(1 and 2 and 3)
(4 and 5 and the dynamic expression)
(4 and 5 and (1 and 2 and 3))
4, 5, 1, 2, and 3

While not required, it is best practice for the where clause to order the fields in the same order as the index. This allows you to more easily compare the logic block fetches with indexes on the table. 

When building an additive expression list, you can add dynamic expressions within dynamic expressions. For example, in the LoadBackorderReleaseLines logic block the SalesOrderDetailExpression dynamic expression is initialized in action 5 and is used throughout the logic block. Then, in action 17 the SalesOrderDetailExpression dynamic expression is initialized, and is comprised of the SalesOrderDetailExpressionForCustomer and SalesOrderDetailExpression, configured using the OR operator.

Fetch Records action best practices

These best practices ensure that logic block Fetch Records actions are built consistently throughout Nextworld.

Ensure that the sequence of fields in a fetch logic block matches, or partially matches, the sequence of indexed fields in a table if the logic block is one that runs often. It is not required to create an index for every combination used in a logic block, but it is important to consider the maintenance of indexes and the size of the table. A table with 100 records may be small enough to never need an index because processing the table doesn't affect performance that much, but a table with millions of records needs indexes for almost every kind of fetch that could be done because a full table scan can significantly affect performance. 

Use Limit in the Options section of the Details panel when you don't want to fetch and loop through more records than needed. For example, when you are trying to find a default value in a field in the logic block and the value of the field is going to be the same for all records in the table, it makes more sense to fetch just one record to retrieve the default value instead of fetching all of the records. 

Limit can also be used in combination with the sort options Sorted ascending and Sorted descending. For example, when you are trying to fetch records from the ContactAlerts table for the Directory table and you want find the maximum or minimum value of the Start Date field in ContactAlerts, you would configure the Options section of the Fetch Records action as such:

QuerySorted descending
Value TypeField
TableFetchContactAlerts
FieldStartDate
QueryLimit
Value TypeConstant
Value1

This gives you the single record with the most recent value in Start Date. 

When you want to retrieve a record based on a specified nwId, use a Fetch Table Lookup action instead of a Fetch Records action with a nwId filter. The Fetch Table Lookup action is a simplified version of the Fetch Records action with a nwId filter, making the logic easier to read and understand. 

Learn more about the different fetch actions in the Logic block fetch actions topic.

Fetch Original Record best practices

These best practices ensure that logic block Fetch Original Record actions are built consistently throughout Nextworld.

Configure your logic so that this action only runs when there is an existing record. Use the Conditional action to check if the Entry Mode is equal to either Update or Delete, and not Create. This means that the action only runs when the user is updating or deleting an existing record. 

Fetch Detail Records action best practices

These best practices ensure that logic block Fetch Detail Records actions are built consistently throughout Nextworld.

Use this action when you want the logic block to automatically determine which detail records you want to fetch. The logic checks the Header Detail Structure Name you defined and fetches the details from it.

The Fetch Detail Records action only works if there is a Header Detail structure for it to fetch detail records from. Use Fetch Detail Records if:

  • The table the logic block is built over is a Header Detail table.
  • You create a Header Detail structure in your logic block with the Create HD Structure action.

For better performance, use Fetch Current Row as the Fetch Type if applicable or the Disposition (created, changed, unchanged, removed).

Only use Fetch All as the Fetch Type if you must process all detail records. For example, use the Fetch All option if you need to sum a field's values from all records.

If you use Fetch All, use one of the sort options to make your logic more efficient.

Limitations

The Fetch Detail Records action only allows you to fetch all records or fetch current records. The action does not have any additional filtering capabilities. In order to add filters to incoming detail records, you must either:

  • Call another logic block to narrow down the fetched results.
  • Fetch the detail records with the Fetch Records action to use that action's expressions to narrow down the results. The Fetch Records action must contain an expression that links the nwHeaderId = nwId (Header table).

Fetch Records and Lock action best practices

These best practices ensure that logic block Fetch Records and Lock actions are built consistently throughout Nextworld.

Record Locking

Locks can be applied when fetching records to help minimize the possibility of multiple users attempting to fetch and update the same table record at the same time. When you lock a record, other users are unable to make modifications to the record. When locks are in place, records are updated individually only for the time it takes for execution to return to the calling logic block. Fetch locks only concern record updates. 

Only use Fetch Records and Lock on tables that need to be updated. Use the Fetch Records action on tables that do not need to be updated. 

The default choice should be not to lock.The use of a Fetch Records and Lock should be a deliberate decision, the reasoning for which should be noted in a Comment action. Locks only make sense when there is a need to update table records. Never use a lock simply to retrieve information from another table when there is no intent to update.

Transaction boundaries

If a fetch lock is necessary, then you must also have a firm understanding of the transaction boundary. A record lock is held until all processing within the transaction boundary is finished. Structure your logic blocks to minimize the duration of the lock by controlling the transaction boundary of its record updates.

Timeout errors and deadlocks become less likely as the transaction boundary gets smaller. The transaction boundary may need to be reduced, but remember that the transaction boundary must be large enough to encompass all interdependent table updates in order to preserve data integrity. 

Spend time figuring out how to make the transaction boundary smaller. In your initial logic block that fetches many records, do not perform updates on the records. Instead, within the fetch loop, call a second logic block passing one record at a time to be updated. Define the second logic block to run in a separate transaction boundary. That is, refetch the record with a lock in the called logic block.

Conflict considerations

Consider the impact of conflict error compared to timeout error. When a user gets an error, it may indicate that a business process is not working properly. A timeout error may be a bad user experience, but it may also be necessary to ensure the successful completion of another task. Visualize the scenarios involving concurrent processing. If they cannot be avoided entirely, choose the least harmful condition, apply fetch lock accordingly, and document your reasoning.

Manage the likelihood of conflicts between multiple applications. Conflict errors become more likely when asynchronous jobs that process a large number of records are kicked off while users are using applications that update the same records. Avoid these conflicts by running asynchronous jobs at night or find an external way of containing the amount of users who can run those types of jobs.

Integration jobs that update a large number of table records should not be run while users are updating the same tables. However, the business may require that some integrations be done concurrent with users working on the same table. You must decide if the integration job is important enough to lock records and cause users to experience timeouts or conflicts. 

You must determine a fetching protocol to prevent a deadlocking scenario such as the one described below:

  • A logic block always fetches and locks from Table A before Table B.
  • User 1 calls a logic block that locks record A.
  • User 2 calls a logic block that locks record B.
  • The transaction of User 1 then requires record B, while the transaction of User 2 requires record A.
  • Each user locks out the other.

Learn more about the different fetch actions in the Logic block fetch actions topic.

Minimizing Deadlocks

When using the Fetch Records and Lock action that could return multiple records, it should always contain a sort order or a limit of 1. This would indicate that it's expected to only lock one record. Without a sort order, multiple records could be locked, but in an arbitrary order. If this happens, it can cause deadlocks.