View Entities

View entities are backed by an OQL query. They appear in the domain model but have no database table (and no database view either) – their rows are computed from other entities via aggregation and joins, evaluated by the runtime per query. “Read-only” is a common but imprecise shorthand: a view row has no storage and commit does not write it back, yet it is editable in memory (it behaves like a non-persistent object), so a form can bind to one and write changes back through the source entity. See View Entity for the full model and the CE6770 type-match gotcha.

Sales Summary by Category

-- Source entities
CREATE PERSISTENT ENTITY Reports.ProductCategory (
  /** Category display name */
  CategoryName: String(200) NOT NULL
);
/

CREATE PERSISTENT ENTITY Reports.SaleTransaction (
  /** Transaction amount */
  Amount: Decimal NOT NULL,
  /** Date of the sale */
  SaleDate: DateTime NOT NULL
);
/

CREATE ASSOCIATION Reports.SaleTransaction_ProductCategory
  FROM Reports.ProductCategory TO Reports.SaleTransaction
  TYPE Reference OWNER Default;
/

-- View entity: aggregates sales by category
CREATE VIEW ENTITY Reports.SalesTotalByCategory (
  CategoryName: String(200),
  TotalAmount: Decimal,
  TransactionCount: Integer
) AS (
  SELECT
    c.CategoryName AS CategoryName,
    sum(s.Amount) AS TotalAmount,
    count(s.ID) AS TransactionCount
  FROM Reports.SaleTransaction AS s
  INNER JOIN s/Reports.SaleTransaction_ProductCategory/Reports.ProductCategory AS c
  GROUP BY c.CategoryName
);
/

Querying a View Entity in a Microflow

View entities can be retrieved like any other entity, including with WHERE filters:

CREATE MICROFLOW Reports.GetSalesTotalForCategory (
  $Category: Reports.ProductCategory
)
RETURNS Decimal AS $TotalAmount
BEGIN
  DECLARE $TotalAmount Decimal = 0;

  RETRIEVE $Summary FROM Reports.SalesTotalByCategory
    WHERE CategoryName = $Category/CategoryName
    LIMIT 1;

  IF $Summary != empty THEN
    SET $TotalAmount = $Summary/TotalAmount;
  END IF;

  RETURN $TotalAmount;
END;
/

Displaying in a Page

View entities work with data grids and list views like persistent entities:

CREATE PAGE Reports.SalesByCategory_Overview (
  Title: 'Sales by Category',
  Layout: Atlas_Core.Atlas_Default
) {
  DATAGRID dgSales (DataSource: DATABASE Reports.SalesTotalByCategory) {
    COLUMN colCategory (Attribute: CategoryName, Caption: 'Category')
    COLUMN colTotal (Attribute: TotalAmount, Caption: 'Total Sales')
    COLUMN colCount (Attribute: TransactionCount, Caption: 'Transactions')
  }
};
/