User Tools


Business Questions

SQL Query. Filter Conditions

When “SQL Query” is marked:

  • the Columns are meant to be a comma-separated list of columns
  • the Conditions field is meant to be a correct list of joins and a where conditions
  • the sort and group by also must be a correct column specification

When “Conditions in Filter Format” is marked, the Conditions are supposed to be a JSON array specification of fields and conditions grouped as per the syntax followed in the filter system. For example:

[[{"field":"subject","op":"c","value":"an","glue":"or"},{"field":"pl_gross_total","op":"g","value":"20","glue":""}]]
[[{"field":"subject","op":"c","value":"an","glue":"or"},{"field":"pl_gross_total","op":"g","value":"20","glue":"and"}],[{"field":"pl_gross_total","op":"l","value":"20","glue":""}]]

with these columns should work:

subject,quote_no,pl_gross_total

Materialized Views

Adds the functionality of creating materialized views, which are based on coreBOS database tables.

The Business Question module stores the configurations for each materialized view.

Note that in MySQL 5.x, which is the version used by coreBOS, materialized views are not supported yet. Subsequently, we are going to create physical tables in order to mimic the functionality of a materialized view.

A Business Question configuration would be composed of the following attributes:

  • Name of the materialized view
  • SQL Query flag, which indicates that the materialized view should be created based on a SQL query.
  • Module - this is the primary module of the materialized view
  • Unique ID Field - this serves as a unique identifier of the records of the materialized view
  • SQL Query - contains the SQL SELECT query which determines the data that should be copied into the materialized view.
  • Columns - list of materialized view's columns
  • Condition - contains the conditions of the SQL query

There should be inserted some useful actions in the DetailView of each Business Question:

  • Test SQL - runs the SQL query against the database and returns a success / no success message.
  • Create Materialized View - creates the physical database table, populated with data from the SQL Query defined in the Business Question
  • Add Materialized View Workflow - creates two workflows in the Module specified in cbQuestion: one workflow on each save and the other on delete.
  • Remove Materialized View - drops the materialized view
  • Delete Materialized View Workflow - drops the workflows at the Module specified in Business Question.

coreBOS Documentación