- 0 minutes to read

Formula - sqllookup

Enrich your Search Fields by looking up values from a SQL database with the Nodinite sqllookup Formula. Use it to translate business-friendly inputs (like a city name) into normalized codes (like an area prefix) at query time—without changing your logging.

  • ✅ Turn a value (e.g., city name) into a related database lookup value (e.g., area prefix)
  • ✅ Centralize reference data in SQL—no hard-coded mappings in logs
  • ✅ Works with message body, context, or the result from other Formula functions
  • ✅ Simple syntax; designed for business users and integrators

Prerequisites

Important

This function reads data from a SQL database using a configured connection string.

  • A connection string entry exists in the Nodinite Logging Service configuration and is identified by a name (the first function parameter). This is referred to as CSTRID.
  • Connection strings are encrypted using the certificate-based scheme and preserved when updating Nodinite.
  • The Windows account running the Nodinite Logging Service when using integrated security must have read permissions (database and target table).
  • Ensure the network/firewall allows read access to the target SQL Server instance.
  • For performance, index the column you search on (the fourth parameter).

Manage connection strings (how to update)

You must add or update named connection strings in the Nodinite Logging Service's configuration when sqllookup needs to access external databases. Follow these rules and the example below carefully.

  • Add the new connection string entry in appSettings.json (or your environment-specific configuration) using a clear-text connection string. The Logging Service will encrypt the connection string using the certificate-based scheme on startup.
  • After changing appSettings.json you must restart the Nodinite Logging Service for the changes to take effect.
  • Do NOT change the NodiniteConfig connection string unless the configuration database has actually moved to another SQL Server instance — changing it is potentially dangerous and may break the system.
  • Do not rename an existing named connection string: changing the name will break current sqllookup expressions that refer to it. Each entry must have a unique name.

Example (clear-text connection string for a local default SQL Server instance):

"ConnectionStrings": {
  "NodiniteConfig": "PROTECTED_1...",
  "SqlDbLookupLocal": "Server=localhost;Database=Directory;Integrated Security=True;Connection Timeout=30;Encrypt=True;TrustServerCertificate=True",
  "SqlDbLookup": "PROTECTED_1..."
}

Note

  • The example uses SqlDbLookupLocal as the CSTRID name. Use that exact name in your formula expression:
    sqllookup('SqlDbLookupLocal', 'Directory', 'AreaCodes', 'Name', 'AreaCode', body)
  • When the Logging Service starts it will encrypt the clear-text value and replace it with the certificate-encrypted form.
  • Ensure the Windows account running the Logging Service has read access to the specified database and table.

What does the sqllookup Formula do?

The sqllookup(connectionStringName, databaseName, tableName, searchColumn, resultColumn, value) function performs a simple equality lookup on searchColumn = value and returns the corresponding resultColumn for all matching rows. Use it to enrich log search results with external reference data. When multiple rows match, the function returns multiple values.

You may see the function described with short parameter names to match the underlying order:
sqllookup(CSTRID, dbname, tableName, colNameToSearch, colNameForResult, inputValueString)
For readability in examples, this page uses more descriptive names; the function remains strictly positional.

How it works: Input ➜ sqllookup ➜ Result

graph LR A["Input: Message/body/context"] --> B["sqllookup(conn, db, table, searchCol, resultCol, value)"] B --> C["Result: Mapped/normalized value(s)"]

Flow: The input value is matched in the database; the corresponding result value is returned for use in views and filters.

Visual: Input → SQL lookup → Result

flowchart LR U["User/Search Field"] --> F["sqllookup(...)"] F -->|uses CSTRID| LS[":Nodinite: Logging Service"] LS --> DB["SQL Database"] DB --> T["AreaCodes table"] T --> R["Result value(s) (e.g., 054)"] R --> V["Log View / Output"]

Diagram: The Search Field triggers sqllookup, which uses the configured connection (CSTRID) via the Logging Service to query the SQL table and return a mapped value.

Example 1 — City name ➜ Area prefix

You have a reference table mapping city names to phone area prefixes:

Table: AreaCodes (Name NVARCHAR(128), AreaCode NVARCHAR(8))
Rows:
  ("KARLSTAD", "054")
  ("STOCKHOLM", "08")

Minimal sample schema (for illustration):

CREATE TABLE dbo.AreaCodes (
  Name      NVARCHAR(128) COLLATE Latin1_General_CI_AS NOT NULL,
  AreaCode  NVARCHAR(8)   NOT NULL
);

CREATE NONCLUSTERED INDEX IX_AreaCodes_Name ON dbo.AreaCodes(Name);

Input from body

KARLSTAD

Formula Expression

sqllookup('CSTRID', 'Directory', 'AreaCodes', 'Name', 'AreaCode', body)

Result

054

Example 2 — Using message context

Assume context key city contains the input value:

Input

STOCKHOLM

Formula Expression

sqllookup('CSTRID', 'Directory', 'AreaCodes', 'Name', 'AreaCode', context('city'))

Result

08

Features

  • Enrich Search Fields with external reference data from SQL
  • Keep logs clean—no hard-coded dictionaries in message payloads
  • Combine with other Nodinite Formula functions for layered transformations
  • Use in Nodinite Log Views for filtering, grouping, and business-friendly displays

Tip

For best performance, add a non-clustered index on the searchColumn. Consider limiting lookup scope to one database and a compact table.

🧪 Test Your Expression

You can validate your formula before saving:

  • Paste a sample input value in the relevant source (body/context)
  • Enter your sqllookup(...) expression
  • Verify the returned value and total matches

✅ Successful Match: Returns one or more mapped values (e.g., 054).
❌ No Match: Returns an empty result when no row matches.

How to use the sqllookup Formula

  1. Select 'Formula' as the expression type plugin.
  2. Enter your sqllookup expression in the text area.
  3. Provide a string value for the final parameter (from body, context, or another formula).

Syntax

  • Using message body:
    sqllookup('ConnStringName', 'DbName', 'Table', 'SearchCol', 'ResultCol', body)
  • Using message context:
    sqllookup('ConnStringName', 'DbName', 'Table', 'SearchCol', 'ResultCol', context('key'))
  • Using another formula as input value:
    sqllookup('ConnStringName', 'DbName', 'Table', 'SearchCol', 'ResultCol', SomeOtherFormula(Content))

Short-form signature (positional parameters):
sqllookup(CSTRID, dbname, tableName, colNameToSearch, colNameForResult, inputValueString)

Parameter notes:

  • CSTRID: Name of the connection string in the Logging Service configuration.
  • dbname: Target SQL Server database name containing the reference table.
  • tableName: The table that holds your reference data (e.g., AreaCodes).
  • colNameToSearch: Column to match against the input value (e.g., Name).
  • colNameForResult: Column whose value is returned (e.g., AreaCode).
  • inputValueString: The input string (from body/context/other formula), such as KARLSTAD.

⚠️ Considerations & limits

  • The lookup compares equality on the provided searchColumn.
  • If multiple rows match, multiple values are returned (order not guaranteed).
  • If no match is found, an empty result is returned.
  • Each lookup incurs a round-trip to SQL—use indexes and keep the reference table compact.
  • Use least-privilege access for the Logging Service account (read-only).
  • Matching respects database/table collation. For case-insensitive matching, use a CI collation (for example, Latin1_General_CI_AS) on the search column or normalize stored/input values.

Next Step

How to Add or manage Search Fields
How to Add or manage Log Views