Creating a compound filter (filterWhere) using the Client Side API

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Creating a compound filter (filterWhere) using the Client Side API

  • Comments 4
  • Likes

This article is about the client side API.

Specifically this article is concerned with how to write a script to handle the information within a grid and then apply an action to the cells, row or columns of the grid where more than one criteria is needed.

Examples of using the client side API with grids have already been provided in the article "Control of Grids, Lists and Columns Using the Client Side API". 

The image below shows cells that match a criteria.

These 'Deals' have been highlighted using the following code that was added to the custom content.

[code language="Javascript"]
<script>
crm.ready(function(){
crm.grids().filterWhere('oppo_description', 'contains', 'Deal').highlightCell('goldenrod');
})
</script>
[/code]

But what if we only want to highlight 'Deals' where the opportunity is in the 'Proposal Submitted' stage? This is when the value of the oppo_stage field is 'Quoted'.

In this case we have to filter the grid by the first value and then execute a function on each of the returned fields checking other values in the row.

The API exec method allows you to execute a function on each of the returned cells.

I have used some addition jQuery ($(key).parent().index()) to allow me to identify the row where the cells contain the other criterion.  In jQuery the first row of the table is actually the header row so I need to take that into account when deriving the value of the row index.

[code language="javascript"]
<script>
crm.ready(function(){
var descriptionCol = crm.grids(0).columnIndex("oppo_description");
var stageCol = crm.grids(0).columnIndex("oppo_stage");
crm.grids().filterWhere("oppo_description", "contains", "Deal").exec(function(index, key) {
var rowIndex = $(key).parent().index() -1;
var stageColValue = this.getCellText(rowIndex, stageCol);
if (stageColValue == crm.getTrans("oppo_stage", "quoted")) {
crm.grids(0).setCellAttribute(rowIndex, descriptionCol, "style", "background-color:red");
}
});
});
</script>
[/code]

Comments
  • What about blank values? e.g. the Priority column in your screenshot

  • An individual cell that is blank will still have a predictable structure that can be searched for and identified.

    e.g

    <table><tbody><tr><td class="ROW1"> </td>

    If a cell only contains the 'space' then you know it is a null or blank value.</tr></tbody></table>

  • Good article; It's a pity they didn't build this function into the API

  • Fair comment but the interface is still changing pretty rapidly and there has been a pause while we work on other areas that are fundamental to the API including full CRUD REST.