Thinkwise Datamodeling Guidelines

Thinkwise Data Modeling Guidelines — Domains, Tables, and Columns

Reference conventions for the Thinkwise Software Factory data model, compiled from the official Thinkwise data modeling guidelines (docs.thinkwisesoftware.com) and Thinkwise Community articles. Apply these when designing a new data model, reviewing an existing one, or using sf_mcp tools that touch domains, entities, or columns.

Bulk-importing a whole data model in one call — check for a custom task first

Some models have a custom-built task (not a standard feature — may or may not exist in the model you're working with) that upserts a whole batch of domains/tables/columns/indexes/references from one JSON payload, instead of creating each object individually via the flow described in the rest of this skill. See references/bulk_import_data_model.md for how to check whether it exists, confirm its shape, and fall back to the normal per-object flow if it doesn't.

General naming rules (apply everywhere)

• Singular, lowercase names — never plural, never mixed case.
• Self-explanatory names — a reader shouldn't need extra context to understand what it represents.
• Split into subnames with underscores (sales_order_line, not salesorderline or SalesOrderLine).
• Avoid abbreviations — spell words out fully, except where a platform name-length limit forces it, or for the conventional exceptions id and no.
• No meta-information in the name — don't encode data type, length, or table membership into the name itself.
• Prefer reusing an existing, already-reviewed naming word component over inventing a new one when an equivalent term already exists. Every new distinct word introduced into a table/column/domain name enters its own review step (a Software Factory validation tracks unreviewed naming components) — reusing vocabulary already used elsewhere in the model, when the meaning genuinely matches, avoids growing that backlog for words that already mean the same thing. This is the naming-level version of the label-reuse rule under "Form & grid groups" below.
• Never name a column or domain after a SQL reserved/keyword-adjacent word — level, year, order, group, date, time, table, view, index, key, value, user, values, check, default — even when the target RDBMS happens to tolerate it unquoted. These names get woven directly into generated SQL identifiers, and hitting one live (both level as a column and year as a domain, in the same model) surfaced the problem only once hand-written SQL referencing them was deployed, not at generation time. If a column/domain already has one of these names, use the dedicated rename task (for columns/domains, not a delete-and-recreate) to fix it — but the rename task only updates model metadata and structural DDL; it does not rewrite any hand-authored control-procedure/view/task template text that already referenced the old name by string, so grep every template referencing the old identifier and update it by hand afterward, then regenerate. It also leaves the old id's translation object behind as an orphan — the renamed column/domain gets a fresh transl_object/transl_object_transl under its new id, but the old id's rows aren't deleted, just no longer reachable from anything live. Run task_delete_unused_transl_objects (bound to branch_appl_lang — see thinkwise_software_factory_translation_objects) afterward as a branch-wide cleanup, rather than leaving stale rows to accumulate.

Language of names

Every name (tables, columns, domains, domain elements) is written in one consistent human language — this skill's own examples are English, but the actual language is whatever the model already uses.

• Expanding an existing data model: match the language already in use. Check existing table/column/domain names before adding anything — if the model is in Dutch (werknemer, verkooporder), new additions must be Dutch too, not English, even though this document's examples are in English. Don't mix languages within one model.
• Starting a new data model from scratch (no existing tables/domains to infer from): ask the user what language to model in before naming anything, unless they've already stated it (e.g. they wrote the request in a specific language, or named entities themselves in it) — in that case just follow their input instead of asking a redundant question.

When a guideline conflicts with an existing model's own established convention

The rules in this document describe the ideal. A real, already-existing model sometimes doesn't follow it consistently — e.g. every table already shares one generic domain for surrogate keys instead of a per-entity domain (see "Never create/use a bare id domain" below), or the model has never used diagrams, or never tuned per-column sort/search/filter/grouping settings anywhere. This is the same shape of decision as the language rule just above, generalized: when expanding an existing model, matching its real, pervasive convention for new/expanded objects is usually more valuable than introducing a new, inconsistent convention that only the newest objects follow — even when that real convention falls short of this document's stated ideal. Silently "fixing" only the new objects creates a model that's inconsistent with itself, which is its own cost.

This isn't a license to ignore every guideline below — it applies specifically when a convention is genuinely pervasive across the existing model (essentially every comparable table/column already does it the same way), not when only one or two prior objects happen to deviate. When it applies, say so explicitly rather than silently picking a side — note the deviation from the ideal (or the deviation from this document) so the tradeoff is visible to whoever reviews the change, instead of letting it pass unremarked either way.

Tables (entities)

Classify every table into one of four types before modeling its columns; if a table doesn't cleanly fit one, that's a signal to restructure rather than just rename:

1. Strong entities — exist independently, single-column primary key with no foreign keys as part of it (e.g. sales_order).
2. Weak entities — can't exist without a parent (e.g. sales_order_line). Name = parent entity name + qualifying addition. The foreign key to the parent is part of the primary key, and comes first (topmost) in it — followed by the entity's own discriminating column(s). See "Column order" below for exactly where an identity column fits into that sequence.
3. Link tables — resolve many-to-many relationships. The primary key is the composite of the foreign keys to the two linked tables — no separate identity column is needed, since the pairing itself is the row's identity. Name = <table1>_<table2>, and the primary key column order follows the same order as the name: for a link table employee_employee_function, the primary key is employee_id first, then employee_function_id second. (If the association needs to repeat over time — e.g. it carries a historized start/end date range, so the same pair of foreign keys can legitimately appear in more than one row — it's no longer a pure link table; it becomes a weak entity of one side with its own identity column added as the last primary key column, per rule 2 and "Column order" below.)
4. Inheritance tables — implement a 1:1 "is-a" relationship with a parent; named for the specialization.

A built-in validation (tsf_guidelines_categorize_entities) flags tables that don't qualify as any of the four.

Table description

tab_description should say more than the table's own name restated in words. A description that just spells out the title (sales_order → "Sales order") tells a reader nothing they couldn't already see from the name itself. Write what the table is actually for — the role it plays in the model, what business process or concept it captures, and anything a reader wouldn't guess from the name alone (e.g. sales_order → "A customer's confirmed order for one or more products, used to drive fulfillment and invoicing"). This applies whenever a table is created or reviewed, the same as naming and classification above.

Icon

Set tab.icon_id to a suitable icon from the repository as part of creating the table — it's the table's own icon wherever it's shown (menu item, document, tree/detail navigation), not a cosmetic extra to skip. Follow thinkwise_software_factory_icons for how to search/reuse the repository and what to do if nothing fits (ask the user, don't guess or leave it blank). A view used as a business work queue should get the work concept (e.g. "orders to release" → order/check), not a generic table/view icon — see that skill's subject-icon guidance.

Exposing new tables on a menu

Creating a table is only half of making it usable. A strong entity (rule 1) — or the parent side of an inheritance relationship (rule 4) — is a candidate top-level subject: something a user should be able to open directly from the menu, not just reach by drilling into a parent's detail grid. Weak entities (rule 2) and link tables (rule 3) are usually not top-level subjects — they exist to be detail rows or associations under something else, and defaulting them onto the menu clutters it with entries nobody opens directly. Treat that as a strong default, not an absolute: occasionally a weak entity is genuinely browsed standalone, and that's a judgment call, not a rule violation.

Don't decide silently which candidates become menu items. After creating or reviewing a batch of new tables, present every candidate top-level subject as a multi-select checkbox-style question (one option per table, so the user can tick which get added and leave the rest unticked — e.g. because they're only ever reached through a parent's screen, or aren't ready for end users yet) rather than adding all of them, or guessing which "obviously" belong.

For the ones the user picks, follow the thinkwise_software_factory_menu skill for the actual menu/group/item work — including its own golden rule to confirm menu type and group placement, and to create a new menu (rather than assuming an existing one fits) if the model doesn't have one yet for the relevant platform.

Columns

• Same general rules: lowercase, singular, self-explanatory, underscore-separated.
• Don't prefix non-key columns with the table name — redundant since the table context is already known.
• Primary key: name = <table_name>_id.
• Foreign key: must have the exact same name as the parent table's primary key column — this is also what lets OData/Indicium auto-derive relationship names.
• Prefer INT identity for surrogate primary keys; use BIGINT only for tables expected to exceed ~2 billion rows. Only non-FK primary key columns should be identity columns.
• One fact per column, atomic values only — don't pack multiple values into a delimited string.
• Prefer NOT NULL with a sensible default over nullable columns, unless the absence of a value is itself a meaningful business state.
• A column's default value must match its data type and, if backed by a domain with elements, be a valid element (or within the domain's min/max range for a ranged domain). This is checked by a Software Factory validation, not enforced at write time — stage_resource/patch_resource will commit a default value that's the wrong type or out of range without complaint, and it only surfaces later as a validation error. Double-check a newly-set default against its column's actual domain/type before considering the column done, including for a data-migration script's own column defaults.
• Before finalizing a new column's name, check its translated label doesn't collide with another column's label already in use on the same table — two columns with different ids but identical rendered captions is flagged by a Software Factory validation and confuses a user reading the form/grid.
• A column can be calculated/expression-backed (calculated_field_type non-zero) instead of physically stored — it reads back identically to a real column on a normal query, with no visible difference. See "Calculated columns" below for the different kinds and when (rarely) to reach for one; this matters most when writing hand-written SQL against the table directly (control procedures, migrations) — see thinkwise_software_factory_create_control_procedures's "Calculated columns are not physical" note before including such a column in an insert/update/merge statement.

Data sensitivity classification

Every column carries a data-sensitivity/privacy classification, and a Software Factory validation flags any column left at its undecided default — more strongly for columns the platform can already infer are likely sensitive from their name/domain. Decide it deliberately when creating a column, the same as its domain/type: for an obviously personal or confidential field (name, email address, national id, financial account, health data) set the classification explicitly rather than leaving it unset; for a column where sensitivity genuinely isn't obvious, ask the user rather than guessing — this has real compliance consequences, not just a lint warning. Confirm the exact field/enum name live via the column entity's own metadata before writing to it.

Calculated columns

Default to a real, physically stored column (calculated_field_type = none) and only reach for a calculated column when the value genuinely cannot be a normal one — it must always be perfectly derived from other data that can change independently, with no acceptable point where it could instead just be set once (an insert/update-time Default control procedure, or plain application logic). A calculated column is a special-case tool for that narrow situation, not a stylistic alternative to writing normal columns — most of a table's values belong in real columns, per "One fact per column" and "Prefer NOT NULL..." above.

col.calculated_field_type has four values, verified live against real models:

PostgreSQL-specific pitfall, verified live: a calculated_column's expression compiles to a native generated/persisted column on PostgreSQL, which requires every function used to be IMMUTABLE — concat() is STABLE, not IMMUTABLE, and fails with error 42P17. See thinkwise_software_factory_create_control_procedures's references/sql_dialects.md ("PostgreSQL generated/calculated columns require IMMUTABLE functions") for the fix and the NULL-handling difference between || and concat(). This does not apply to expression-type columns below — those are correlated subqueries, not stored generated columns.

A calculated column still needs col.dom_id set, the same as any physical column, verified live — even though its value comes from calculated_field_query rather than being written directly, the domain is still mandatory (it's what drives the column's data type/display). This is easy to miss when a table's other domains are all entity-specific. When a calculated column used as a table's look-up display column (see "Look-up display column" below — and give it a descriptive name there, never the generic lookup) doesn't map naturally onto any domain you're already creating for the table, consider one small, genuinely reusable domain for that purpose (a generic display-label string type) shared across tables' calculated display columns, rather than inventing a one-off domain per table.

When to use which

• none — the default, always, unless one of the below genuinely applies.
• calculated_column — when the formula only touches other columns already on the same row: arithmetic, concat, cast/case, date differences, bitwise flips, hashes. Confirmed live examples: active = ~archived; duration_seconds = datediff(second, start_date_time, finish_date_time) PERSISTED; full_name = concat(first_name, ' ', last_name) (SQL-Server-shaped; if PostgreSQL is a target platform, write this as coalesce(first_name,'') || ' ' || coalesce(last_name,'') instead — see the PostgreSQL caveat above). Decide PERSISTED vs. not based on read/write balance — see Performance below.
• expression — only when the value genuinely needs to reach outside the row: a join/subquery to another table, a translation fallback, a session-context-dependent value (current language/user). This is the more expensive option (see Performance below), so don't reach for it when calculated_column would do.
• calculated_column_function — only for logic complex/reusable enough to justify a shared function definition instead of an inline expression, and only after confirming its actual behavior live, since no example exists here to model from.

After generating, verify the specific column's rendered clause, not just the table's overall status. A calculated_column's expression is woven directly into the generated CREATE TABLE statement — a table-level "generation successful" result confirms the statement compiled, not that any one column's calculated_field_query rendered the intended expression/PERSISTED syntax. Read the generated DDL back and check that specific column's clause before considering a newly added calculated column done.

Writing an expression query

The current row is available as t1 — every confirmed live example correlates back to it. (The concat() use below is unaffected by the PostgreSQL IMMUTABLE caveat above — expression is a correlated subquery re-evaluated per query, not a stored generated column.)

-- translation fallback (table has a *_translated companion)
isnull(
  (select t.name from employee_function_translated t
   where t.appl_lang_id = session_context(N'tsf_appl_lang_id')
     and t.employee_function_id = t1.employee_function_id),
  t1.name)

-- composite identity pulled from other tables
concat(
  (select description from project where project_id = t1.project_id),
  ' | ',
  (select name from sub_project where sub_project_id = t1.sub_project_id))

Keep the query to exactly the scalar value needed — one narrow subquery per related fact, not a sprawling multi-join formula — since it re-runs on every row read, not once.

Performance: expression columns get expensive fast

An expression column is not indexed, not materialized, and not free: it's a correlated subquery the generated SQL re-runs for every row returned, every time the table (or anything that shows it, e.g. as a look_up_display_col_id — see "Look-up display column" below) is queried. This is easy to underestimate because it reads back identically to a real column, with no visible sign of the cost.

Do:

• Prefer calculated_column over expression whenever the logic is same-row only — it costs nothing beyond a normal column read, and can be indexed if PERSISTED.
• Mark a calculated_column PERSISTED when it's read far more often than the source columns are written, or needs to be filtered/sorted/joined/indexed on — trading a small write-time cost and storage for a much cheaper read.
• Keep an expression query narrow (one scalar value, minimal joins) and make sure whatever column it correlates on, on the other table, is indexed — an unindexed correlated subquery is the single most common way an expression column quietly gets slow as data grows.
• Check the generated SQL/execution plan for any expression column used somewhere high-traffic (a look_up_display_col_id, a default-visible grid column) before shipping it.

Don't:

• Don't reach for expression when the formula is really same-row logic — that just forces a per-row subquery where a free computed column would do.
• Don't stack multiple joins/subqueries into one expression when the value could be pulled from a single, well-indexed lookup instead.
• Don't use a calculated column (of any kind) as a substitute for a value that could just be set once at write time (a Default control procedure, or plain application logic) — recomputing something on every single read is wasted work when the inputs rarely change.
• Don't assume PERSISTED is always the right call on a calculated_column — a virtual (non-persisted) one is cheaper to write and perfectly fine for a formula that's read rarely.

Column order

• Primary key columns come first, in the order they appear in the key. For weak entities and link tables, that means the foreign key(s) to the parent(s)/linked table(s) come before the entity's own discriminating column(s) — mirroring the strong → weak ordering rule for composite primary keys.
• If the primary key includes an identity column, that identity column always comes last within the primary key — every foreign-key-shaped key column precedes it. A pure link table (rule 3 above) has no identity column at all; a weak entity that needs one (e.g. a historized association, or a classic detail like sales_order_line) puts the parent foreign key(s) first and the identity column last.
• After the primary key, place other foreign keys / reference columns next, followed by the table's regular data columns.
• If a table has trace/audit columns (created/modified by + date), put them last, since they're metadata about the row rather than business data — keeps the meaningful columns together and predictable to scan. This is ordering guidance only, not a prompt to add them — see "Integrity, structure, and consistency" below on when (not) to add them.
• Order-number increments: use the column's order-number (sequence) property to control this layout, and leave gaps rather than numbering consecutively — increase by 10 for each column, starting at 10 for the first column (10, 20, 30, …). This leaves room to insert a column later at, e.g., 15, without having to renumber every column after it.

API quirk, verified live: when scripting table creation through a metadata-driven modeling API rather than the UI, a column added before the table's real primary key has been committed can silently default primary_key = true (which then also forces mand/mandatory to read-only-true), regardless of what was requested for that column. This happens with no error — the write reports success. After the intended primary key column is committed, re-read the rest of the table's columns and explicitly correct primary_key/mand on any that picked up the wrong default rather than assuming the original request held.

Grid column visibility

A grid should only show the columns a user is likely to need at a glance — not every column the table has. Decide each column's grid_type_of_col (editable/read_only/hidden) deliberately rather than leaving it at its default (which behaves as editable, i.e. visible, for every column):

There is also a third, base-level field, col.type_of_col, separate from both grid_type_of_col and form_type_of_col — verified live, and easy to miss since only the grid/form-specific pair is documented above. In the Software Factory's own column-properties screen it surfaces simply as "Column type", grouped with general settings like domain/primary key/mandatory rather than anywhere near the grid- or form-specific settings, which is why it's easy to change the presentation-specific fields and still leave this one at its default. Same editable/read_only/hidden enum. Set it together with grid_type_of_col/form_type_of_col whenever a column's visibility decision is meant to hold everywhere (not just one presentation surface) — including the inherited-primary-key carve-out below.

• Hidden — surrogate/identity primary keys (meaningless to an end user), audit-only timestamps that aren't central to triage (e.g. a "processed on" date when the row's current status already conveys that), and secondary/conditional fields that are only populated or relevant for a subset of rows (e.g. a rejection reason that's empty except when a row was rejected). These belong on the form, not the grid.
• Read-only — foreign-key/look-up columns and status-style columns whose value is meant to change only through a task/control procedure rather than a direct grid edit (see "Status columns" below for the same read-only default applied to the underlying column itself). Still shown, just not inline-editable from the grid.
  • Carve-out: on a weak entity's own detail tab, the FK column(s) that form the inherited part of its primary key (e.g. customer_id on customer_address, per "Column order" above) default to Hidden instead, not just read-only — its value is already implied by which parent row the detail is scoped under, so showing it as a read-only column adds nothing. Only show it (read-only or editable) if the user specifically asks for it, e.g. because the table is also browsed unscoped, outside its normal parent-filtered context. Apply this on all three fields — type_of_col, grid_type_of_col, and form_type_of_col — unlike the general "form defaults to visible/editable regardless of the grid" independence described below, this specific column is redundant everywhere for the same reason, so set all three to hidden together rather than only the grid.
• Editable/visible (the default) — reserve this for the columns that actually answer "what is this row, and what state is it in" at a glance: a handful of core identifying columns, the table's primary business figure (an amount, a quantity), and its key status. If a table's column count means most columns end up hidden or read-only, that's expected — a wide table rarely needs a wide grid.

This is a deliberate per-table design pass, the same as grouping/sort/search/filter above — decide it once when the table's columns are created rather than leaving every column at its default and revisiting later.

Grid visibility is independent of form visibility — deciding one says nothing about the other. grid_type_of_col only controls the grid; form_type_of_col defaults to visible/editable regardless of what the grid is set to. The platform's default detail screen still shows a record's form even when the table's entire grid is read-only or task-driven (e.g. a history table where every write goes through a task) — so a table designed to have "nothing editable" can still show every column, including surrogate/identity ones, on the form. Before locking down a table's grid, separately decide (or ask the user) whether the table's form should be visible at all, and size its column grouping (see "Form & grid groups" below) against what the form actually shows, not against the grid.

Form & grid groups

Columns can be visually grouped on the form and, independently, in the grid header. Verified live against the Software Factory's own meta-model (col, hundreds of tables): this is a consistently applied convention, not an occasional nicety, and it should be planned at the same time as column order — before any column is created (see "Decide the group plan up front" below), not bolted on afterward.

This is about visually banding columns under a shared header, on the form and/or the grid. It's a different mechanism from grouping grid rows into a collapsible tree by column value — see "Grid row grouping and aggregation" below for that.

Mechanism — two independent pairs of fields on col (the same pattern exists on task_parmtr for task forms, see thinkwise_software_factory_tasks):

The flag and label are set on the column that starts the new group — not on the column that ends the previous one. Form and grid grouping are independent: a grid can group columns differently than the form, though in practice most tables just group the form and leave the grid ungrouped.

Switching a column between the group and section-break variant needs two writes, not one. The two mechanisms are mutually exclusive on a given column, and turning form_field_in_next_grp off also flips form_next_grp_label to a non-editable/hidden state — if the same write also tries to clear that label (e.g. set it to null) or set field_on_next_tab/next_tab_label in one combined call, the label write can be rejected because the field became non-editable partway through applying that same call. Clear the old mechanism's flag first (as its own write), then set the new mechanism's flag and label in a second write, rather than attempting the swap in one call.

When to use them: any table beyond a handful of columns, or with visually distinct concerns (identity vs. status vs. settings vs. description) — group them. Don't group a table with only 2-3 closely related columns; there's nothing for a heading to separate.

Which columns to bundle together:

• First group holds the identifying/core descriptive columns (conventionally labeled general).
• One group per cohesive concern after that (status, settings, description, positioning, authentication, …) — never fold unrelated concerns into one group just to reduce group count.
• If the table has trace/audit columns (only when the user actually asked for them — see "Don't add trace/audit columns on your own initiative" under "Integrity, structure, and consistency"), the standing convention is a trailing group labeled mutation, additionally flagged field_on_next_tab=true, next_tab_label="trace" so the audit columns live on their own tab instead of cluttering the main form — this holds regardless of the table's subject matter.

Naming: lowercase snake_case, 1-3 words, a topic noun (status, not "Status info" or "status_columns"). Reuse an existing label instead of inventing a new one whenever the meaning matches — the real model reuses a small vocabulary of maybe 20-30 labels across hundreds of tables (general, description, status, settings, progress, positioning, assignment, tag, generation, mutation, authentication, user_interface, condition, query, filter, default_value, …). This isn't just tidiness — see Translation below for why it's the whole point.

Translation: a group label's text is its own transl_object_transl row, keyed by the literal label string, not by table+column — confirmed live: the label general has exactly one translation row per language, shared and reused by every table that uses that label, already approved in ~17 languages. Reusing an existing label costs zero additional translation work. Inventing a new spelling/casing variant ("Status" vs "status" vs "state") creates a brand-new untranslated object that has to go through the whole translation/approval cycle for no modeling benefit. After introducing a genuinely new label, follow thinkwise_software_factory_translation_objects to fill in and approve it — the same standing requirement as any other new translatable object (see "Translating new objects" above).

Decide the group plan up front, before creating any columns

Work out the full grouping (which columns belong to which group, what each group is labeled, and where any tab-breaks fall) as part of the same design pass as column order (see "Column order" above) — before issuing the first stage_resource/create call for the table's columns, not as a follow-up editing pass once the columns already exist. Concretely: when creating column N, its order_no, form_field_in_next_grp, form_next_grp_label (and field_on_next_tab/next_tab_label if it starts a new tab) should all be set in the same write that creates the column. Planning this upfront and setting it immediately avoids a second round of patch calls per column purely to add grouping after the fact — each column is touched once instead of twice.

The plan must cover every column, including the first one. A leading identity/PK column is easy to treat as exempt from grouping because it precedes the first semantic group, but it still needs a group of its own (e.g. an "Identity" group over the record's own id and any leading display-name field) — a Form where the first column alone sits outside every group is still an incomplete grouping plan, not a finished one.

Keep the group plan current when the table changes later

The "decide up front" rule above covers a brand-new table's first Form. The same discipline applies afterward:

• Adding a Form to a table for the first time, after the table already has columns — treat it exactly like new-table design: decide the full group/section plan in one pass before setting any group flags, not incrementally per column.
• Adding a single new column to a table whose Form already has groups/sections — don't append the new column ungrouped at the end by default. Match it to whichever existing group covers the same concept, setting its order/group flags in the same write that creates the column. If no existing group is an obvious semantic fit, ask the user which group it belongs in (or whether it needs a new one) — see thinkwise_software_factory_mcp_base's "Ask, don't default" rule — rather than guessing.
• Retrofitting groups onto a table that already has ungrouped columns — check the first visible column too, not just the ones after the first existing group starts. It's easy to group everything from the first semantic boundary onward and leave the leading identity/PK column(s) stranded outside any group simply because nothing preceded them to trigger the check.

Sort, search, and filter

Every column has independent per-column settings for the table's default sort, its participation in find/search, and its participation in the filter panel — verified live on col: default_sort/sort_no/sort_order/allow_sort (sort), visible_for_search/search_order_no/ search_condition/include_in_global_filter (search/find), and visible_for_filter/ filter_order_no/filter_condition (filter). visible_for_search and visible_for_filter share the same three-way enum: always / extended / never.

Sort

Every table should have a default sort set on the most sensible column(s) — e.g. a natural ordering column (order_no), a name/code, or a date (often descending, for "most recent first"). Set default_sort=true on each column that participates, sort_no to control precedence when more than one column is involved (a composite sort), and sort_order (asc/desc) per column. Leave allow_sort=true (the default) on any column a user could reasonably want to sort by; only turn it off for columns where sorting is meaningless (large text/blob columns — see below).

When the sensible default sort isn't obvious, ask the user rather than guessing — unlike filter and search below, this isn't a "safe default, override later" setting: a wrong default sort is visible on every screen open and is a judgment call about the business data, not a mechanical rule.

Search (find)

A subject (strong entity, or the parent side of an inheritance relationship — see "Tables" above) should always have search configured if its screen type has a grid. Don't leave visible_for_search at its unconfigured default (which behaves as always on every column) or skip search setup entirely — deliberately choose always/extended/never per column following the guidance below, the same required-follow-up treatment as translations and menu placement get elsewhere in this skill.

Restrict visible_for_search=always to the table's genuinely important columns — the ones a user would actually type into a quick-find box to locate a row (names, codes, key statuses). Set visible_for_search=never on any column backed by a large-object type — NVARCHAR(MAX)/ VARCHAR(MAX), VARBINARY(MAX)/IMAGE, TEXT/NTEXT, XML (see "Data type recommendations" above) — searching these is either meaningless (binary) or expensive (unbounded text) and never what a quick-find is for. Default every other column to extended (available under advanced find/search, not cluttering the default quick-search) rather than always — mirroring the filter default below. Set search_order_no for a sensible position among the columns that do participate, and search_condition to the operator that makes sense for the column's data (contains for free text, equal_to for codes/numbers/domain-element-backed columns).

visible_for_search/search_condition/search_order_no alone don't make a column searchable — include_in_global_filter (a separate boolean, "Include in search") is the flag that actually puts the column into the searchable set. Set include_in_global_filter=true on every column that gets always or extended, alongside its visible_for_search/search_condition/search_order_no — missing this step leaves the column configured but silently excluded from search, with no error to flag it.

Filter

Default every column's visible_for_filter to extended. Only promote a column to always when it's genuinely one of the most important columns on the screen and one of the filters a user is actually likely to reach for often — treat always as the exception that has to earn its place, not the default. As with search, large-object-typed columns (NVARCHAR(MAX)/VARCHAR(MAX), VARBINARY(MAX)/IMAGE, TEXT/NTEXT, XML) should be never rather than extended — they can't be meaningfully filtered at all. Set filter_order_no to position the always/extended columns sensibly, and filter_condition to the operator that fits the column (contains for free text, equal_to for codes/domain-element-backed columns, between for ranges/dates).

For the design reasoning behind these settings — which sort pattern fits which subject type, which columns actually belong in search vs. filter, the deep-join/filter-form caution, tables-vs-views for presentation reasons, and lookup-subject design — see references/subject_presentation_design.md. This section covers the fields; that file covers why and which.

Decide all three before creating any columns

Exactly like grouping (see "Form & grid groups" above), work out sort/search/filter settings for every column as part of the same upfront design pass as column order and grouping — before the first stage_resource/create call for the table's columns. When creating column N, its `or

…(truncated)