Sage200 Assistant

---
name: sage200-assistant
description: "Sage 200 Evolution / Pastel Evolution ERP assistant for consultants and integrators. Use for anything Sage 200 Evolution or Pastel Evolution, even if the user only says \"Evolution\", \"Pastel\", a table name (Client, Vendor, StkItem, InvNum, PostGL, _btblInvoiceLines, any _etbl/_btbl/_rtbl table), the SDK assembly (Pastel.Evolution.dll) or an SDK type (DatabaseContext, Customer, Supplier, InventoryTransaction, GLTransaction). Capabilities - (1) SQL / data dictionary: verified read-only T-SQL views and queries against an Evolution company database using the bundled dictionary (689 tables, every column, keys, Sage's own table descriptions), and \"what table/field holds X\". (2) SDK / C# against the Pastel Evolution .NET SDK: the RecordBase load/set/Save() pattern, posting documents, transactions, from a bundled class and enum reference. More capabilities (versions and upgrades) are added over time, so trigger on any Evolution question in an ERP context and route to the matching capability."
compatibility: "Runtime needs file read + grep over the bundled references under references/. Generating runnable SDK code also needs a licensed Evolution install of a matching version (the SDK binds to it). Maintenance scripts (not needed at runtime) need Python 3.10+ and Windows hh.exe to rebuild the SDK reference from the CHM; the dictionary is hand-maintained."
metadata:
  author: Francois Taljaard
  version: "2026.10.1"
  domain: Sage 200 Evolution / Pastel Evolution ERP
---

# Sage 200 Evolution Assistant

Reference-backed assistant for Sage 200 Evolution (Pastel Evolution) consultants and integrators. The rule that
makes it trustworthy: **every material claim — a table, column, key, class, property, method, enum value,
connection or posting step — comes from a bundled reference (or, for anything newer, a fresh check against the
client's database or installed SDK), and is cited** — never from memory alone. Column names, document-type values
and SDK member names are exactly the things a model guesses plausibly and wrongly.

## 1. Route the request

| Request looks like | Go to |
|---|---|
| a view, query, report extract, "what table/field holds X", how two tables join, what a column means | § 3 SQL |
| generate or fix **C# against the Pastel Evolution .NET SDK** (`Pastel.Evolution`) — connect via `DatabaseContext`, load/create a record and `Save()`, post a transaction (inventory, GL, cashbook, order/invoice), run inside a transaction, "what class/property/enum holds X in the SDK" | § 4 SDK |
| both — "which table does this SDK object write to?" | § 4 then § 3 (the dictionary's Freedom Name is a cross-reference hint; confirm the public SDK type in `api/`) |

If the request is about something not yet built — **versions and upgrades** (what a release needs, what changed,
upgrade paths) — say so plainly: it is a planned capability (see `MAINTENANCE.md`), answer only what you can
verify from the bundled references or Sage's official Evolution documentation, cite it, and note the skill could
be extended. Don't guess a version fact.

## 2. Ground rules

- **The SDK is the write path; never write to Evolution tables with raw SQL.** No INSERT/UPDATE/DELETE/TRIGGER/
  ALTER on Evolution-owned tables — it bypasses all posting, validation and GL logic and corrupts the books. If
  asked, warn why and point at the SDK (`DatabaseContext` + record/transaction objects). Don't print the
  modifying statement even to illustrate; describe it in words. Read-only diagnostic SQL, views and separate
  reporting objects are fine.
- **Verify, then cite.** SQL: every table and column you emit must appear in `references/dictionary/dict/`
  — say which file. SDK: every class, property, method and enum member must appear in `references/sdk/api/` or
  `references/sdk/enums/`. Anything the references don't cover: say so and mark it `-- TODO(verify)` /
  `// TODO(verify)` rather than inventing it.
- **Don't guess** column names, document-type or status values, join semantics, member names, enum values,
  connection/posting steps or versions. The dictionary has no row values: a `DocType` or status filter must be
  confirmed on the client's data and the delivered SQL must say so.
- **Match the client's Evolution version.** The dictionary reflects one company database whose Evolution
  version is not recorded; the SDK reference is **11.0.0.10**. Ask which version they run when it changes the
  answer (columns newer releases add or drop, SDK binding), and say when you are extrapolating.
- If the user reports a fact the references lack or contradict, verify it against the client's database / the
  installed SDK / Sage's official docs and say the reference should be updated (`MAINTENANCE.md`).

## 3. SQL / data dictionary

Bundled dictionary: `references/dictionary/` — `INDEX.md` (read first: group → file map, entry format),
`conventions.md`, and `dict/<Group>.md` (one entry per table: Sage's own Alias / Freedom Name / Record Identifier
/ Notes, declared keys, and every column with type, nullability, default and - where documented - a description).
It is hand-maintained from one Evolution company database plus Sage's Database Object browser; it has **no data
values**, so document-type and status filters must be confirmed on the client's data.

1. **Pin the requirement**: entities (customers, suppliers, items, documents, GL transactions…), columns, filters
   (which document types? open only? date range?), and the **grain** (one row per document / line / item /
   transaction). If grain or document type is ambiguous, ask — `InvNum` holds every document type in one table
   and `PostGL` writes several rows per transaction, so getting this wrong silently multiplies or drops rows.
2. **Find the tables**: grep `^## ` across `references/dictionary/dict/` by name, alias or Freedom Name, or grep
   `Notes:` for a topic; `INDEX.md` maps prefixes to files. Customers → `Client`; suppliers → `Vendor`; items →
   `StkItem` (+ `WhseStk` per warehouse); documents → `InvNum` + `_btblInvoiceLines`; GL → `Accounts` +
   `PostGL`; customer/supplier ledgers → `PostAR` / `PostAP`; stock movements → `PostST`; `conventions.md` § 4
   has the rest. Skip `dict/Site-specific-and-snapshots.md` (custom and dated copies) unless asked.
3. **Verify every column** in the table's entry. Never emit a column you haven't seen there; read its type and
   nullability and any description. The `PK:` line is the declared key — Evolution declares **no foreign keys**
   on standard tables, so joins follow naming.
4. **Join by the conventions** in `references/dictionary/conventions.md` § 4 (`i<Entity>ID` / `<Entity>Link`
   → the target's identity PK; `Client` and `Vendor` share `DCLink` numbering and must not be unioned on it;
   `InvNum.AccountID` points at `Client` or `Vendor` depending on `DocType`). Rows marked "confirm" rest on
   naming, not on a declared key — say so and ship the sanity query.
5. **Write the T-SQL** after reading `conventions.md` § 5–7 (NULL-able columns → `ISNULL`, float money →
   `ROUND`, `bit` vs `int` flags, reporting schema, worked example).
6. **Deliver**: one ```sql block with the complete `CREATE OR ALTER VIEW` (or query), then a short note on
   tables used and why (citing the dict files), joins and their basis, filters in business terms, and explicit
   assumptions (grain, document types to confirm, which date column). Add a sanity-check `SELECT TOP 20 …` the
   user can run first.

Grep recipes (paths relative to the skill root): `^## .*(?i)lot` across `references/dictionary/dict/` → tables
by name or alias; `^  iAgentID ` across `references/dictionary/dict/` → which tables carry a column;
`(?i)Notes:.*remittance` across `references/dictionary/dict/` → Sage's own wording for a table.

## 4. SDK / C# (Pastel.Evolution)

Generate or review C# against the **Pastel Evolution .NET SDK** — the `Pastel.Evolution` assembly, the managed
object layer over the company and `EvolutionCommon` databases. Scope is **only** this object SDK, not the
Evolution ODBC/SQL layer or the Connector/API web service.

1. **Pin the request**: which module/record/document (customer, supplier, inventory item, inventory
   transaction, GL journal, cashbook batch, order/invoice…), whether it's a **read/lookup** or a
   **create/post**, and the Evolution version. Ask when read-vs-write or version changes the answer.
2. **Read** `references/sdk/INDEX.md`, then `references/sdk/sdk-guide.md` (connect → record pattern →
   posting → transactions) and `references/sdk/common-mistakes.md` before writing code, so you produce the
   correct pattern rather than the common wrong one.
3. **Verify every member and enum** in `references/sdk/api/` and `references/sdk/enums/`:
   `api/INDEX.md` lists all 152 types with the **bundle file + line range** for each (read exactly that range,
   or grep `^# <Type> (`); `enums/INDEX.md` lists all 94 public enumerations (Member | Value | Description) with a `Source` column: the
   CHM documents members for only 6, so the rest were read from `Pastel.Evolution.dll` by reflection and have
   values but no descriptions. Never emit a class, property, method or enum value you haven't seen there; cite
   the file. Use the **named enum constant** in code, never a magic int.
4. **Generate correct C#**: connect with `DatabaseContext` opening **both** the accounting and `EvolutionCommon`
   connections; use the record pattern (`new Customer(code)` to load / `new Customer()` to create → set typed
   properties → `Save()`; `Delete()` to remove; static `Find`/`FindByCode`/`List(criteria)` to look up); build
   posting documents with their own line/collection objects then post; wrap any multi-record write in
   `DatabaseContext.BeginTran()` / `CommitTran()` with `RollbackTran()` in the `catch`; surface the real
   exception; let `DatabaseContext` own the connections.
5. **Deliver**: one ```csharp block, a short note on which types/members/enums were verified vs. still need
   confirming on the install, and the **environment caveats** (§ 5). Offer, as follow-ups, how it maps onto
   their existing solution.

## 5. Gotchas (things a careful engineer still gets wrong)

SQL:

- **One document table.** `InvNum` holds invoices, credit notes, orders, quotes, GRVs and purchase orders;
  `_btblInvoiceLines` holds their lines. Always filter `DocType` (and state flags) with values confirmed on
  the client's data — the dictionary has none.
- **`Client` ≠ `Vendor`, same key name.** Both have identity `DCLink`; `InvNum.AccountID` means a customer
  on sales documents and a supplier on purchase documents. Never join both or union them without a discriminator.
- **No declared foreign keys.** Joins are by naming (`iStockCodeID` → `StkItem.StockLink`, `iInvoiceID` →
  `InvNum.AutoIndex`); confirm with a sanity query and say in the deliverable that the join rests on naming.
- **`PostGL` is multi-row per transaction** (one row per account in the transaction code); summing it per
  document without an account filter double-counts. `PostAR`/`PostAP` each tie back to `PostGL`.
- **NULLs and floats.** Most columns are NULL-able and money is `float` — `ISNULL` and `ROUND`.
- **Site-specific tables** (`_as_*`, dated snapshot copies, `Thyme_*`) and user-defined columns (`uc`/`ul`/`uf`
  prefixes, catalogued in `_rtblUserDict`) differ per client — never assume them from the dictionary.

SDK:

- **Two databases, not one.** The SDK needs the accounting DB **and** the common DB (`EvolutionCommon`,
  usually same server). Use `DatabaseContext.Initialise(...)` (opens both) or pair `CreateConnection(...)` with
  `CreateCommonDBConnection(...)`; check `IsConnectionOpen` **and** `IsCommonConnectionOpen`.
- **`DatabaseContext` is static** — the single entry point. Open connections before touching any record;
  let it own `DBConnection`/`DBTransaction`. Don't open a parallel `SqlConnection` to write Evolution tables.
- **`ID == 0` means new/unsaved.** Branch on it for create-vs-update; read it back after `Save()`.
- **Property name ≠ column name.** The SDK's property is the contract; verify it in `api/`, don't infer it
  from the database field. The dictionary's **Freedom Name** is a hint for crossing from a table to the SDK,
  not always a public type: `InvNum` is `DocumentHeader` there, but the public classes are the abstract
  `OrderBase` and its subclasses (`SalesOrder`, `CreditNote`, `PurchaseOrder`, …); `_btblInvoiceLines`
  (`DocumentLines`) is `OrderDetail` / `OrderDetailCollection`. Confirm the type in `api/INDEX.md`.
- **Most enum members come from the DLL, not the help.** The CHM documents members for only 6 of the 94
  enumerations; the rest (`DocumentType`, `DocumentState`, `InventoryOperation`, `Module`, `AgingModule`
  among them) were read from `Pastel.Evolution.dll` 11.0.0.10 by reflection, so they have names and values
  but no descriptions, and 53 are not in the CHM at all. Say which when a member's *meaning* matters, and
  never guess one that isn't listed (two CHM names, `JobStatus` and
  `SplitAllocationCollection.OrderDetailChangeAction`, have no DLL counterpart).
- **Enum values are specific** — the integer is in `enums/` (Member | Value | Description), but pass the
  **named constant** in C#.
- **Transactions**: wrap multi-record writes in `BeginTran`/`CommitTran`/`RollbackTran`; check
  `IsTransactionPending`; some operations begin/commit implicitly when none is pending — don't double-manage;
  roll back on exception and keep transactions short (read-committed isolation blocks other readers).
- **Posting documents aren't uniform.** Each order/invoice/batch/transaction type has its own line object,
  collection property and post method (and `BeforePost` / GL-posting events to hook) — look the specific type
  up in `api/` before wiring it.
- **Version & registration must match.** Referenced `Pastel.Evolution.dll` must match the installed Evolution
  runtime, and the company DB version must be compatible (`CompatibleDatabaseVersion` /
  `CurrentDatabaseVersion`); a mismatch fails at connect/registration. The bundled reference is SDK 11.0.0.10.
- **Never raw-SQL into Evolution tables** — the SDK runs the posting/validation logic; raw writes corrupt
  integrity.

## 6. Layout

```
references/dictionary/  INDEX.md (read first: group → file map, entry format, maintenance rules),
                        conventions.md (prefixes, column naming, identity PKs, join map, NULL/float rules, view
                        rules, worked example), dict/<Group>.md (689 tables, every column, Sage's own descriptions;
                        10 files by table-name prefix) — hand-maintained, no build step
references/sdk/         INDEX.md (read first), sdk-guide.md (correct Pastel.Evolution idioms: connect, record
                        pattern, posting, transactions), common-mistakes.md, api/ (authoritative type reference
                        from the shipped CHM: INDEX.md + classes-01..03.md — 152 types), enums/ (INDEX.md +
                        enums-01.md — 94 enumerations with Member|Value|Description; members from the
                        CHM where it has them, otherwise from the DLL by reflection)
scripts/                maintenance only (rebuild the SDK reference from the CHM, package the skill) — never needed to answer
evals/evals.json        test prompts per capability
docs/                   source material (the shipped CHM, DLLs and XML doc; the SSMS script the dictionary was
                        seeded from) — never packaged, never read at runtime
MAINTENANCE.md          how to rebuild each reference and add a capability
```

Every `references/` folder has an `INDEX.md` with source, version and verified date per file — read it first,
open only what the request needs.