# View Datasource ## Overview A **View Foundset Datasource** represents a custom table, where the data is not stored in a physical database but instead exists as a definition. The data for this table is loaded at runtime, often based on a **QBSelect** query object. This can be highly useful for creating data sets that span multiple tables, improving performance over traditional methods like relations, valuelists, or aggregates. ## Creating View Foundset Datasources There are two ways to create view foundset datasources: 1. Using the [Solution Explorer](https://docs.servoy.com/reference/servoy-developer/solution-explorer) context menu under `Datasources -> View foundsets -> Create view foundset`. This opens a dialog where you can specify `view foundset` name.

Create View Foundset Datasource

After adding the datasource name, the [Table Editor](https://docs.servoy.com/reference/servoy-developer/object-editors/table-editor) will open, allowing you to define the structure of your table, just like any other table. 1. **View Foundset** can also be created using the following method: `databaseManager.getViewFoundSet(String name, QBSelect query)` This returns a `ViewFoundSet` object with the datasource: `"view:[name]"`. The `ViewFoundSet` object has a limited API compared to the regular `JSFoundSet`. It supports essential operations like `getSize()`, `getRecord()`, `forEach()`, and `loadAllRecords()`, but does not support `find()` or `search()`. For changes in the data, a new `ViewFoundSet` should be created with an updated `QBSelect`. ### Registering and Unregistering View Foundsets To use a **View Foundset** on a form, you can register it with the system by using the following method: `databaseManager.registerViewFoundSet(ViewFoundSet foundset)` This keeps the instance in memory for forms that use that datasource. If it is no longer needed, you can unregister it: `databaseManager.unregisterViewFoundSet(String datasource)` ### Data Broadcast and Monitoring Changes By default, a **View Foundset** does not react to databroadcast changes, but you can enable this feature by calling: `viewfoundset.enableDatabroadcastFor(QBTableClause queryTable, int flags)` This enables the View Foundset to listen for changes in specific tables within the query. Depending on the flags provided, you can monitor columns, join conditions, where conditions, inserts, deletes, or even aggregates. #### Available Flags for Data Broadcast: #### Available Flags for Data Broadcast: #### Available Flags for Data Broadcast: * `ViewFoundSet.MONITOR_COLUMNS`: Monitors column changes for the table/datasource. This flag is used by default if no other flags are specified. It listens for changes in the columns of the selected primary key (PK) for the datasource and updates the affected columns without requiring a full query. **Note**: The PK must be included in the result set for this flag to work. * `ViewFoundSet.MONITOR_JOIN_CONDITIONS`: Monitors changes in the join conditions of the table/datasource. For example, if there's a join between `order_lines.productid` and `orders`, and `productname` is displayed, any changes to `order_lines.productid` will trigger a query to detect and apply the change. * `ViewFoundSet.MONITOR_WHERE_CONDITIONS`: Monitors column changes in the `WHERE` clause of the query. If any column in the `WHERE` condition, such as `order_lines.unit_price > 100`, is changed, a full query is executed to detect and update the affected records. * `ViewFoundSet.MONITOR_INSERT`: Monitors new inserts into the table/datasource. This flag triggers a full query whenever a new record is inserted, ensuring that the `ViewFoundSet` reflects the new data. * `ViewFoundSet.MONITOR_DELETES`: Monitors deletions in the table/datasource. A full query is executed whenever a record is deleted, updating the `ViewFoundSet` to reflect the changes. * `ViewFoundSet.MONITOR_DELETES_FOR_PRIMARY_TABLE`: Monitors deletions specifically for the primary table of the query. When a record is deleted, the `ViewFoundSet` only removes records that match the deleted primary key, avoiding the need for a full query. **Note**: Only one delete monitor (either `MONITOR_DELETES` or `MONITOR_DELETES_FOR_PRIMARY_TABLE`) should be used per table/datasource, and the PK must be included in the results. * `ViewFoundSet.MONITOR_AGGREGATES`: Monitors changes in columns that affect aggregates in the query. This flag triggers a full re-query when there are inserts, updates, or deletes on the columns that influence aggregate values, such as `SUM` or `GROUP BY`. It's essential for queries that group by columns or use aggregates on selected columns. For instance, in a query like: `SELECT orders.customerid, orders.orderdate, SUM(order_details.unitprice) FROM orders LEFT JOIN order_details ON orders.orderid = order_details.orderid GROUP BY orders.customerid, orders.orderdate` Changes in `orders.customerid`, `orders.orderdate`, or `order_details.unitprice` will affect the aggregates and require a re-query to update the data. Flags like `MONITOR_COLUMNS` or `MONITOR_DELETES_FOR_PRIMARY_TABLE` typically have minimal impact on performance, as they don't require full re-queries. However, other flags, such as `MONITOR_WHERE_CONDITIONS` or `MONITOR_AGGREGATES`, can significantly affect performance since they may trigger full queries whenever a relevant change is detected in the data, in order to update the `ViewFoundSet`. ### Editable View Foundsets Besides monitoring for databroadcast changes, **View Foundsets** can be made editable by using one of the two `save()` functions. The key requirement is that for any column you change in a **ViewRecord**, you must also have the primary key (PK) selected for that table in the `QBSelect`. This allows Servoy to generate an update statement with the changed columns for that PK. Any data changes in a **View Foundset** will temporarily pause the databroadcast refreshes (including loading more data) until the records are saved. This ensures that no refreshes or queries are made until the data is committed, avoiding potential conflicts between new data and existing records. Expanding this node will display a list of all [View Foundsets](https://github.com/Servoy/gitbook/blob/master/guides/develop/application-design/data-modeling/viewfoundset-datasource.md) created for the active solution. These are also displayed in the [contextual list](https://github.com/Servoy/gitbook/blob/master/guides/solution-explorer-contextual-list.md#overview). ## Commands Summary Below is a summary of commands available via the right-click context menu for **View Foundset Datasources**: | Command | Summary | | ------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------- | | [Create view foundset datasource](#create-view-foundset-datasource) | Opens the [Table Editor](https://docs.servoy.com/reference/servoy-developer/object-editors/table-editor) | | [Edit table/view](#edit-table-view) | Opens the [Table Editor](https://docs.servoy.com/reference/servoy-developer/object-editors/table-editor) to edit the table | | [Delete View Foundset](#delete-view-foundset) | Deletes the View Foundset definition | | [Rename View Foundset](#rename-view-foundset) | Renames the View Foundset definition | ## Commands Details ### Create view foundset datasource Creates a view foundset datasource with a specified name (table name). Opens the [Table Editor](https://docs.servoy.com/reference/servoy-developer/object-editors/table-editor) to define and edit the table like any other database table. ### Edit table/view Opens the [Table Editor](https://docs.servoy.com/reference/servoy-developer/object-editors/table-editor) to edit the View Foundset table's structure and column information (metadata stored by Servoy Developer). ### Delete View Foundset Deletes the View Foundset definition. ### Rename View Foundset Renames the View Foundset definition.