Manage Lookup Data Through an Administrative Pool¶

Goal¶

Provide a small administrative screen for maintaining shared lookup values without embedding those values in every form or granting general users access to the maintenance operation.

What You Will Learn¶

separate lookup maintenance from end-user workflow tasks
load relational rows into a repeating form table
assign stable identifiers to new rows and persist changes
keep inactive values instead of deleting referenced business data

Difficulty and Estimated Time¶

Difficulty: Intermediate
Estimated time: 25 minutes

Assumed Knowledge¶

You should be familiar with pools, forms, repeating table rows, XML paths, relational schemas, prework, and postwork.

Required Reading¶

Prerequisites¶

permission to import or create process definitions and relational schemas
an administrator identity that can open administrative pools
a clean test domain for verifying database writes

Example Overview¶

The example contains one administrative pool, one user task, one form, and one relational table:

Administrative pool: Lookup Data Administration
└── Manage Categories
    ├── prework: load Categories rows into XML
    ├── table form: edit Code, Name, and Active
    └── postwork: assign missing IDs and persist rows

The example uses soft deactivation through the Active field. Existing workflow records may store a category code, so deactivating a row is safer than deleting a value that historical data still references.

Steps¶

Create an Administrative Pool¶

Create a pool named Lookup Data Administration.
Enable the administrative-pool setting.
Add one initiating task named Manage Categories.
Add a Save action with validation group Save.

An administrative pool keeps maintenance entry points out of the normal process catalog. It does not replace server-side permissions; verify which administrators can open and execute the task in the target domain.

Define the Relational Table¶

Create schema TutorialLookup with table Categories and these fields:

Field	Type	Purpose
`Id`	Guid	stable primary key
`Code`	VarChar(30)	stable value stored by integrations or process data
`Name`	VarChar(100)	user-facing label
`Active`	Bit	controls whether the value should remain selectable

Use Id as the primary key. In a production design, also enforce the required uniqueness rule for Code at the database or server-validation layer.

Build the Repeating Form¶

Add a table-content control bound to Categories/Category and create columns for Code, Name, and Active.

require Code and Name for validation group Save
set the row identity path to Id
allow adding and deleting rows in the tutorial form
prefer deactivation over deletion when a row may already be referenced

Because the required rules belong to controls inside the repeating row and use the same Save group as the action, validation is displayed on the row and field that must be corrected. Use a table row rule when a constraint depends on multiple values in the same row. Repeat critical uniqueness and authorization checks on the server because row feedback improves usability but is not a persistence or security boundary.

Load Existing Rows in Prework¶

$Database.ExportToXml({
    TargetSchema: 'TutorialLookup',
    TargetTable: 'Categories',
    XPath: 'Categories/Category'
});

This makes the current relational rows available to the repeating form before it is rendered.

Assign IDs and Save in Postwork¶

$Xml.SelectAll('Categories/Category', function(category) {
    if (category.IsEmpty('Id')) {
        category.SetValue('Id', Script.NewId());
    }
});

$Database.ImportFromXml({
    TargetSchema: 'TutorialLookup',
    TargetTable: 'Categories',
    XPath: 'Categories/Category'
});

Assigning an ID before import gives every new row a stable primary key. Existing rows retain their ID and are updated instead of being inserted as unrelated duplicates.

Consume the Lookup with a Deterministic Query¶

In an end-user form, bind a dropdown to a table data source that selects only active rows and orders them by Name, then Code. Use Code as the stored value and Name as the caption. The explicit order keeps the list stable; the unique Code rule ensures that a lookup by code returns at most one logical record. Never select an arbitrary first row from an unordered, non-unique result.

See Table Data Source for the complete query shape. Keep the maintenance form and consuming form separate so end users receive read access to active lookup rows, not write access to the table.

How It Works¶

The form XML is a temporary editing representation. Prework exports relational rows into that representation, and postwork imports the edited representation back into the table. The relational table remains the shared source used by other processes and dropdown queries.

The example deliberately separates the maintenance operation from consumption. A separate end-user form should query only active rows and should not receive permission to update the lookup table.

Verify the Result¶

Import the definition into a clean test domain.
Open Lookup Data Administration as an authorised administrator.
Add GENERAL — General and keep it active.
Select Save.
Start the administrative task again and confirm that the row is loaded with the same ID.
Change the name, save, and confirm that the existing row is updated rather than duplicated.
Set Active to false and confirm that the row remains stored for historical references.

Failure and Edge Cases¶

Try to save a row without Code or Name; the Save validation group should reject it.
Confirm that the invalid row and field are visibly identified, then correct only that row and save again.
Add the same code twice; the tutorial explains the risk but requires a server-side uniqueness rule before production use.
Create two rows with the same display name and confirm that the secondary Code order keeps their ordering stable.
Open the pool as a non-administrator and confirm that the maintenance entry point is not available.
Repeat the save operation and confirm that stable row IDs prevent duplicate inserts.

Security and Portability Notes¶

The definition contains no tenant, user, group, or environment identifier.
Administrative visibility is not the only security boundary; apply the target domain's server-side authorization policy to the pool and table.
Do not accept schema or table names from user input.
Do not expose inactive values in new selections, but preserve them when displaying historical records.
Add a unique index or equivalent server-side validation for Code before using the pattern in production.

Download and Try It Yourself¶

Download the administrative lookup process definition.

Troubleshooting¶

Rows do not load: Confirm the schema/table names and the Categories/Category XPath in prework.
Every save creates duplicates: Confirm that existing rows preserve Id and that Id is the table primary key.
New rows fail to save: Confirm that the postwork assigns Script.NewId() when Id is empty.
General users can open the maintenance task: Review administrative-pool and table authorization in the target domain.