This is an old revision of the document!
Table of Contents
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.
Mermaid Graphs
A mermaid graph is defined by putting the body of the markdown inside the “Columns” field and the Mermaid graph type in the “Properties” field. Like this:
If we want to add some styling on the nodes and links we can use the standard mermaid syntax to do so adding the markdown directly in the columns field, like this.
We can also use a more advanced property definition to establish node and link styling that can be determined depending on the context of the record we are showing the graph in. This functionality is based on the ModTracker extension. Once ModTracker is tracing the changes in a module, the graph can receive a list of all the transitions a field has gone through and we will be able to style each node and link depending on the path followed by the record.
The idea is that we can have a process established in a module. This process is related to a field in the module that passes through some states. For example, the sales stage picklist in Potentials. As the opportunity is worked on, the different sales stage states are selected. At any given point in the life of the opportunity, we can load a mermaid graph that will ask ModTracker which have been the changes that the record (the sales stage field) has gone through, and for each one, we will be able to apply a style.
With this functionality you will see a different style on each record you open in detail view, reflecting the transitions in the life of each particular record. If you show the mermaid graph above, where the styling is fixed in the “columns” field, you would always see the same graph on all records which is not what you need to show the user.
The extended syntax looks like this:
{
"graph" : "LR",
"defaults":{
"nodestyleaccepted": "fill:greenyellow,stroke:green,stroke-width:4px",
"nodestylerejected": "fill:greenyellow,stroke:blue",
"linkstyleaccepted": "stroke:greenyellow,stroke-width:2px",
"linkstylerejected": "stroke:green,stroke-width:4px"
},
"nodes":[
{
"nodename":"B",
"condition": "44061",
"nodestyleaccepted": " fill:yellow,stroke:green,stroke-width:4px"
},
{
"nodename":"D",
"condition": "44061",
"nodestylerejected": "fill:red,stroke:red"
}
],
"links":[
{
"linkposition": "4",
"condition": "44061"
},
{
"linkposition": "3",
"linkstylerejected": "stroke:red,stroke-width:4px"
}
]
}
The only nodes and links that will be styled are those that the record has gone through, all the others will use the default mermaid styling.
If a node has a condition, the business map will be executed, if it passes the “*styleaccepted” will be applied and if not the “*stylerejected”. If either of them are empty, or there is no condition the styling rules defined in the “defaults” section will be applied.
The node and link MUST appear in the definition section or they will not be styled.
Potentials Example
A((Prospecting)) --> D B((Qualification)) --> D C((Analysis)) --> D D((Proposal)) --> E E((Decision)) -->F F((Perception)) --> G G((Quote)) --> H H((Review)) --> I((Won)) H --> J((Lost))
{"graph" : "LR",
"defaults":{
"nodestyleaccepted": " fill:greenyellow,stroke:green,stroke-width:4px",
"nodestylerejected": "fill:greenyellow,stroke:blue",
"linkstyleaccepted": "stroke:greenyellow,stroke-width:2px",
"linkstylerejected": "stroke:green,stroke-width:4px"
},
"nodes":[
{
"nodename":"A",
"nodestate":"Prospecting",
"condition": "44177",
"nodestyleaccepted": " fill:greenyellow,stroke:green,stroke-width:4px",
"nodestylerejected": "fill:greenyellow,stroke:blue"
},
{
"nodename":"B",
"nodestate":"Qualification",
"condition": "44177",
"nodestyleaccepted": " fill:greenyellow,stroke:green,stroke-width:4px",
"nodestylerejected": "fill:greenyellow,stroke:blue"
},
{
"nodename":"C",
"nodestate":"Needs Analysis",
"condition": "44177",
"nodestyleaccepted": " fill:greenyellow,stroke:green,stroke-width:4px",
"nodestylerejected": "fill:greenyellow,stroke:blue"
},
{
"nodename":"D",
"nodestate":"Value Proposition",
"condition": "44177",
"nodestyleaccepted": "fill:greenyellow,stroke:green",
"nodestylerejected": "fill:greenyellow,stroke:red"
},
{
"nodename":"E",
"nodestate":"Id. Decision Makers",
"condition": "44177",
"nodestyleaccepted": "fill:greenyellow,stroke:green",
"nodestylerejected": "fill:greenyellow,stroke:red"
},
{
"nodename":"F",
"nodestate":"Perception Analysis",
"condition": "44177",
"nodestyleaccepted": "fill:greenyellow,stroke:green",
"nodestylerejected": "fill:greenyellow,stroke:red"
},
{
"nodename":"G",
"nodestate":"Proposal/Price Quote",
"condition": "44177",
"nodestyleaccepted": "fill:greenyellow,stroke:green",
"nodestylerejected": "fill:greenyellow,stroke:red"
},
{
"nodename":"H",
"nodestate":"Negotiation/Review",
"condition": "44177",
"nodestyleaccepted": "fill:greenyellow,stroke:green",
"nodestylerejected": "fill:greenyellow,stroke:red"
},
{
"nodename":"I",
"nodestate":"Closed Won",
"condition": "44177",
"nodestyleaccepted": "fill:greenyellow,stroke:green",
"nodestylerejected": "fill:greenyellow,stroke:red"
},
{
"nodename":"J",
"nodestate":"Closed Lost",
"condition": "44177",
"nodestyleaccepted": "fill:greenyellow,stroke:green",
"nodestylerejected": "fill:greenyellow,stroke:red"
}
],
"links":[
{
"position": "0",
"from": "Prospecting",
"to": "Value Proposition",
"condition": "44177",
"linkstyleaccepted": "stroke:blue,stroke-width:2px",
"linkstylerejected": "stroke:green,stroke-width:4px"
},
{
"position": "1",
"from": "Qualification",
"to": "Value Proposition",
"condition": "44177",
"linkstyleaccepted": "stroke:blue,stroke-width:2px",
"linkstylerejected": "stroke:green,stroke-width:4px"
},
{
"position": "2",
"from": "Needs Analysis",
"to": "Value Proposition",
"condition": "44177",
"linkstyleaccepted": "stroke:blue,stroke-width:2px",
"linkstylerejected": "stroke:green,stroke-width:4px"
},
{
"position": "3",
"from": "Value Proposition",
"to": "Id. Decision Makers",
"condition": "44177",
"linkstyleaccepted": "stroke:greenyellow,stroke-width:2px",
"linkstylerejected": "stroke:green,stroke-width:4px"
},
{
"position": "4",
"from": "Id. Decision Makers",
"to": "Perception Analysis",
"condition": "44177",
"linkstyleaccepted": "stroke:blue,stroke-width:2px",
"linkstylerejected": "stroke:green,stroke-width:4px"
},
{
"position": "5",
"from": "Perception Analysis",
"to": "Proposal/Price Quote",
"condition": "44177",
"linkstyleaccepted": "stroke:blue,stroke-width:2px",
"linkstylerejected": "stroke:green,stroke-width:4px"
},
{
"position": "6",
"from": "Proposal/Price Quote",
"to": "Negotiation/Review",
"condition": "44177",
"linkstyleaccepted": "stroke:blue,stroke-width:2px",
"linkstylerejected": "stroke:green,stroke-width:4px"
},
{
"position": "7",
"from": "Negotiation/Review",
"to": "Closed Won",
"condition": "44177",
"linkstyleaccepted": "stroke:blue,stroke-width:2px",
"linkstylerejected": "stroke:green,stroke-width:4px"
},
{
"position": "8",
"from": "Negotiation/Review",
"to": "Closed Lost",
"condition": "44177",
"linkstyleaccepted": "stroke:greenyellow,stroke-width:2px",
"linkstylerejected": "stroke:green,stroke-width:4px"
}
]
}
This is how the Business Question looks:
This is how the Detail View widget looks in a potential record that has followed the defined steps:
and this is how it looks on a potential record which has not followed the defined steps:
This is the Business Action definition
block://DependencyGraphBlock:modules/cbQuestion/DependencyGraphBlock.php:targetid=$RECORD$&qnid=44181&targetfield=sales_stage
where “44181” is the crmid of the Business Question
and you must load the Mermaid library to convert the markdown to a graph with another Business Action like this
modules/cbQuestion/resources/mermaid.min.js
