Indexed Fields in Dynamics ERPs

When integrations read data from Microsoft Dynamics ERPs (NAV, Business Central, or Finance & Operations), performance depends heavily on whether the fields used in filters or sort orders are indexed in the source table.
An index acts like a shortcut that allows the system to locate records quickly without scanning the entire table.

If a field (for example, SystemModifiedAt, Last Date Modified, or Posting Date) is not part of a key/index, the ERP must perform a full table scan every time the integration runs a filtered or sorted query. This causes slow responses, timeouts (e.g. 408 or 504 errors), or high load on the ERP service.

For OData / API Pages

  • The field must be:
    • Present on the page.
    • Bound directly to the table field (Rec.SystemModifiedAt).
    • Included in a key for efficient ordering.

This matters because most integrations use datetime fields for delta logic. Without an index, these operations trigger full scans and degrade performance as data grows.
 With a proper index, the ERP uses seek operations, resulting in faster, predictable reads and stable sync cycles.

Best Practice

Always ensure:

  • The delta or sort field is indexed and part of a key.
  • Composite keys (e.g. Company, SystemModifiedAt) are used for multi-company setups.
  • The field used for $orderby or filtering is bound directly to a real table column.