Examples

This page includes examples for the various chapters of page Advanced filter options.

Dynamic filter values

Look at an example included in the xRM project: In the filter component of ActivityFilter_view, you can filter according to property "Responsible". If you then open the "Value" combo box, you can not only select from the system's Employee records, but you can also choose value "me". This is a dynamic filter value, because it depends on the logged-in ADITO user (Employee).

Example of dynamic filter value "me" (Context "Activity")

Configuration

Activity_entity's EntityField RESPONSIBLE has a Consumer "Employees", which depends on Employee_entity's Provider "Employees". This Provider's property "filterVariablesProcess" includes the core code of the feature:

var res = {
    "$global.user.contactId": translate.text("${FILTER_DYNAMIC_VALUE_ME}")
};

result.object(res);

Employee_entity.Employees.filterVariablesProcess

How to use the filterVariablesProcess?

The result of the filterVariablesProcess can be an arbitrary number of key-value pairs, with

key being the variable whose value is to be used
value being the display value to be used in the client

In the above example, the result means "Show the text 'me' as additional filter value. If the client user selects it, use the value of variable $global.user.contactId as filter value."

Universal usability

This principle can be used with any $global und $sys variable. If required for this purpose, further $sys variables can be ordered from ADITO's development department. But you can also define your own $global variables via vars.set("$global.xxx.yyy", variableValue) in process "autostartNeon".

Technical background

The technical background, why this feature is available in the Provider model is: As you can (optionally) specify a lookupIdfield in the Provider to use a different UID, it must be possible to filter also according to different values.

Hypothetical example:

UID: CONTACTID
lookupIdfield of Provider "#PROVIDER" (the UID will be used) -> In this case, a dynamic filter "my company" requires the CONTACTID of the user's company.
lookupIdfield of Provider "XYZ": ORGANISATIONID -> In this case, a dynamic filter "my company" requires the ORGANISATIONID of the user's company.

We will create an arbitrary example of an extended filter in the client and then learn how to configure the same filter in JDito.

First, click on "Open extended filter" in the filter component of the FilterView of Context "Contact".

Filter configuration in the client

Here is an example of an extended filter, containing 4 filter groups (represented by the lines starting with the all/one switch), each with 0 to 2 filter conditions (represented by the lines with grey background).

The effect of this filter is to show all "Contacts" (= datasets of Person_entity) that are

not inactive AND
- EITHER female and having the last name "Smith"
- OR male and having a last name starting with "Mill"

Filter configuration in JDito

Now, this is how the same filter is configured in JDito (cf. labeled screenshot below):

.Examples of how to use the filter builder pattern

var filterGroup1 = neonFilter.createFilterGroup();
filterGroup1.mergeOperator(neonFilter.MERGE_OPERATOR_AND);

var filterConditionNotInactive = neonFilter.createFilterCondition()
.field("STATUS")
.searchOperator(neonFilter.SEARCH_OPERATOR_NOT_EQUAL)
.contentType(neonFilter.CONTENT_TYPE_TEXT)
.value("Inactive")
.key("CONTACTSTATINACTIVE");

filterGroup1.addFilterCondition(filterConditionNotInactive);

var filterGroup2 = neonFilter.createFilterGroup();
filterGroup2.mergeOperator(neonFilter.MERGE_OPERATOR_OR);
filterGroup1.addFilterGroup(filterGroup2);

var filterGroup3 = neonFilter.createFilterGroup();
filterGroup3.mergeOperator(neonFilter.MERGE_OPERATOR_AND);
filterGroup2.addFilterGroup(filterGroup3);

var filterConditionLastnameSmith = neonFilter.createFilterCondition()
.field("LASTNAME")
.searchOperator(neonFilter.SEARCH_OPERATOR_EQUAL)
.contentType(neonFilter.CONTENT_TYPE_TEXT)
.value("Smith")
.key("Smith");

filterGroup3.addFilterCondition(filterConditionLastnameSmith);

var filterConditionGenderFemale = neonFilter.createFilterCondition()
.field("GENDER")
.searchOperator(neonFilter.SEARCH_OPERATOR_EQUAL)
.contentType(neonFilter.CONTENT_TYPE_TEXT)
.value("Female")
.key("f");

filterGroup3.addFilterCondition(filterConditionGenderFemale);

var filterGroup4 = neonFilter.createFilterGroup();
filterGroup4.mergeOperator(neonFilter.MERGE_OPERATOR_AND);
filterGroup2.addFilterGroup(filterGroup4);

var filterConditionLastnameMill = neonFilter.createFilterCondition()
.field("LASTNAME")
.searchOperator(neonFilter.SEARCH_OPERATOR_STARTSWITH)
.contentType(neonFilter.CONTENT_TYPE_TEXT)
.value("Mill")
.key("Mill");

filterGroup4.addFilterCondition(filterConditionLastnameMill);

var filterConditionGenderMale = neonFilter.createFilterCondition()
.field("GENDER")
.searchOperator(neonFilter.SEARCH_OPERATOR_EQUAL)
.contentType(neonFilter.CONTENT_TYPE_TEXT)
.value("Male")
.key("m");

filterGroup4.addFilterCondition(filterConditionGenderMale);

📝 Note: The above code is simplified. In practice, of course, you would reference KeywordEntries not directly, but via the usual JDito methods, e.g.,

.key($KeywordRegistry.contactStatus$inactive()) instead of .key("CONTACTSTATINACTIVE")

.value(KeywordUtils.getViewValue($KeywordRegistry.contactStatus(), $KeywordRegistry.contactStatus$inactive())) instead of .value("Inactive")

initFilterProcess

Example task

Here is an example task, which can be used as pattern: When opening the FilterView of Context Person (titled "Contact" in the client), we want to see only persons, whose last name includes the letter "M". This requires the following code (save the existing code first!):

.Person_entity.initFilterProcess

import { neon, neonFilter, result, vars } from "@aditosoftware/jdito-types";

if (vars.get("$sys.presentationmode") === neon.CONTEXT_PRESENTATIONMODE_FILTER)
{
    var recordState = vars.get("$sys.recordstate");
    if (recordState != neon.OPERATINGSTATE_SEARCH)
    {
        var filter = neonFilter.createFilterGroup()
        .mergeOperator(neonFilter.MERGE_OPERATOR_AND)
        .addFilterCondition(neonFilter.createFilterCondition()
            .field("LASTNAME")
            .key("M")
            .value("M")
            .searchOperator(neonFilter.SEARCH_OPERATOR_CONTAINS)
            .contentType(vars.get("$property.LASTNAME.contentType"))
        );
    result.string(filter.toString());
    }
}

💡 Tip: Find more information about the difference between "operating state" and "record state" in chapter System state.

Example from xRM

Here is another example, from the xRM project. This code makes sure that only "active" company datasets are shown:

.Organisation_entity.initFilterProcess (fragment)

var filter;

(...)

if (vars.get("$sys.presentationmode") === neon.CONTEXT_PRESENTATIONMODE_FILTER)
{
    var statusInactive = $KeywordRegistry.contactStatus$inactive();

    filter = neonFilter.createFilterGroup()
        .mergeOperator(neonFilter.MERGE_OPERATOR_AND)
        .addFilterCondition(neonFilter.createFilterCondition()
            .field("STATUS")
            .key(statusInactive)
            .value(KeywordUtils.getViewValue($KeywordRegistry.contactStatus(), statusInactive))
            .searchOperator(neonFilter.SEARCH_OPERATOR_NOT_EQUAL)
            .contentType(neonFilter.CONTENT_TYPE_TEXT)
            );
}

result.string(filter);

filterConditionProcess

.Example of filterConditionProcess evaluating value selection via combo box

// the first part of the array returned by filterValuesProcess, e.g., a UID
var rawvalue = vars.get("$local.rawvalue");

// the relational operator coded as Integer number (non-unique!), e.g. "2"
var operator = vars.get("$local.operator");

// the relational operator as special character to be used in SQL statements, e.g. ">"
var operator2 = vars.get("$local.operator2");

// the relational operator as cleartext in String format, e.g. "NOT_EQUAL"
var comparison = vars.get("$local.comparison");

// useful logging for understanding the above variables
// -> just try different values and relational operators
//    and inspect the log output
logging.log("--------------------> rawvalue = " + rawvalue);
logging.log("--------------------> operator = " + operator);
logging.log("--------------------> operator2 = " + operator2);
logging.log("--------------------> comparison = " + comparison);

// Example:
// Assuming that ANOTHERTABLE has been used in filterValuesProcess,
// so ANOTHERTABLE.ANOTHERTABLEID is here given in rawvalue
// and now used for filtering MYTABLE via its column ANOTHERTABLE_ID

var myPrimaryId = rawvalue;
var sqlCondition = "";

switch(comparison) {
    case "EQUAL":
        sqlCondition = newWhere("MYTABLE.ANOTHERTABLE_ID", myPrimaryId, SqlBuilder.EQUAL());
        break;
    case "NOT_EQUAL":
        sqlCondition = newWhere("MYTABLE.ANOTHERTABLE_ID", myPrimaryId, SqlBuilder.NOT_EQUAL());
        break;
    case "ISNULL":
        sqlCondition = "MYTABLE.ANOTHERTABLE_ID IS NULL";
        break;
    case "ISNOTNULL":
        sqlCondition = "MYTABLE.ANOTHERTABLE_ID IS NOT NULL";
        break;

    default:
        sqlCondition = "1 = 2";
}

result.string(sqlCondition);

.Example of filterConditionProcess evaluating value input via free text (no filterValuesProcess required in this case)

var myUserInput = vars.get("$local.rawvalue");

// operator selection is ignored here

var myFilterCondition = newWhere(
  "MYTABLE.MYCOLUMN",
  myUserInput,
  SqlBuilder.EQUAL());

result.string(myFilterCondition);

groupQueryProcess

Example in xRM

Example in xRM: groupQueryProcess of Phase_filter (Phase_filterExtention), a FilterExtension of the "db" RecordContainer of Salesproject_entity ("Opportunity").

As you can see, this FilterExtension is not used for filtering (which you can additionaly do via the EntityField PHASE). Its purpose is to enable grouping of Opportunity datasets by PHASE and still having them in the correct alphabetical order.

This is the configuration of Phase_filter:

Implementing a `groupQueryProcess` - step by step

Step-by-step explanation of how to implement a groupQueryProcess:

Add a new FilterExtension.
Check property isGroupable (otherwise, the grouping option is not visible in the client)
Set groupedRecordField: This is the EntityField by which the grouping will be done.
Set titleRecordField: This is the EntityField that will later be used as displayValue for the groups. As in this property a string can be entered, you can alternatively set a placeholder string and replace it later, e.g., by a join or (if it is not too long) subselect/caseWhen statement - see groupQueryProcess of Phase_filter.
groupQueryProcess: Here, you are free to do what is required, as long as the result is a suitable SQL string that includes a "group by" clause.

Example: `Phase_filter` and its variables

You may have a look at the groupQueryProcess of Phase_filter to learn the approach. Here you have access to different useful variables (see chapter FilterExtensionSet). Variables used in Phase_filter are:
- $local.condition: The condition that is given by filter and filterConditionProcess. If present, the condition needs to be appended to the SQL (see Phase_filter)
- $local.count: Boolean indicating if the process is executed to calculate the count or for loading the data itself. If only the count is needed, the SQL should, for performance reasons, select only something like "1". If the data is to be loaded, take the column list (see below) and replace the placeholder text for the displayValue (if required, see Phase_filter)
- $local.columnlist: string with columns, separated by comma (order: groupedRecordField, titleRecordField [, n aggregate fields])

FilterExtensionSet

FilterExtensionSet can be generated as follows:

Open an Entity in the Navigator, right-click on its RecordContainer, and choose "Add Filter Extension Set" from the context menu. Enter a name of your choice.

Example: Managing grades of trainees

Here is an example of how to configure a FilterExtensionSet: Given we want to manage trainees, including their performance at school (grades in English, German, and math).

This example of a FilterExtensionSet is to demonstrate the 3 options to load the values (directly from the database; dropdown with filter values from filterValuesProcess; dropdown with filter values from Consumer) and the grouping.

In the filterConditionProcess, in turn, we again have 3 options that are quite common with FilterExtensions: A boolean evaluation (yes/no), and two evaluations with type TEXT (one of it simple, and one more complex).

All defined filter and groupings in the set are directly refering to the same table as the RecordContainer does - therefore, the examples are a little bit "artificial", but nevertheless comparably easy to understand, as you do not have to deal with subqueries etc.

📝 Note: This example, for itself, is not meant as "best practice", but it demonstrates well how to handle a FilterExtensionSet, without having the need to call complex functions or generate/require complex SQL queries.

Now, first, we set up a Context "Trainee", including a "Trainee_entity" with several EntityFields. And we include this Context in a new menu group in the Global Menu. Furthermore, we create a database table and connect it with the Entity and its EntityFields. Most of this preparatory work can be realized via Liquibase: Please update your ADITO project, including its database, using the corresponding Liquibase and .aod files in Appendix "Trainee example". Then, everything will be prepared to continue with the following paragraphs.

In the project tree, double-click on Trainee_entity and unfold its RecordContainer "db" in the Navigator window. There, if you unfold the node "FilterExtensions", you see a FilterExtensionSet named "example_filterSet":

Now we will configure this FilterExtensionSet's properties step-by-step, along with some explanations as code comments. Furthermore, reading the different properties' property description will help you to understand the example.

First, make sure that property "filtertype" is set to BASIC. (The other option, "EXTENDED", would mean that the FilterExtensionSet's features are only available via "Open extended filter conditions".)

📝 Note: The following code snippet is only required for making the example work properly. It is not directly related to the basics of a FilterExtensionSet.

.The code of the valueProcess of ContainerName_param of Consumer "KeywordGenders"

import { result } from "@aditosoftware/jdito-types";
import { $SalutationKeywords } from "SalutationKeywords_registry";

result.string($SalutationKeywords.personGender());

filterFieldsProcess

.The code of "example_filterSet"'s property filterFieldsProcess

import { result } from "@aditosoftware/jdito-types";
import { KeywordUtils } from "KeywordUtils_lib";
import { $SalutationKeywords } from "SalutationKeywords_registry";

//no local variables available

var filterFields = [
    //No dropdown
    {
        name: "FILTER_GRADEENGLISH",
        title: "Grade English entered?",
        contentType: "BOOLEAN",
        isGroupable: true,
        
        groupedRecordField:"CASE WHEN TRAINEE.GRADEENGLISH IS NOT NULL THEN 1 ELSE 0 END",
        titleRecordField:"CASE WHEN ISNULL(TRAINEE.GRADEENGLISH) = 0 THEN 'Yes' ELSE 'No' END",
    },
    {
        name: "FILTER_GRADEGERMAN",
        title: "Grade German entered?",
        contentType: "BOOLEAN",
        isGroupable: true,
        
                groupedRecordField:"CASE WHEN TRAINEE.GRADEGERMAN IS NOT NULL THEN 1 ELSE 0 END",
        titleRecordField:"CASE WHEN ISNULL(TRAINEE.GRADEGERMAN) = 0 THEN 'Yes' ELSE 'No' END",
    },
    {
        name: "FILTER_GRADEMATH",
        title: "Grade math entered?",
        contentType: "BOOLEAN",
        isGroupable: true,
        
                groupedRecordField:"CASE WHEN TRAINEE.GRADEMATH IS NOT NULL THEN 1 ELSE 0 END",
        titleRecordField:"CASE WHEN ISNULL(TRAINEE.GRADEMATH) = 0 THEN 'Yes' ELSE 'No' END",
    },
    
    //dropdown => uses filterValuesProcess (only for this one)
    {
        name: "FILTER_GRADE",
        title: "Grade",
        contentType: "TEXT",
        hasDropDownValues: true,
        isGroupable: false,
    },
    
    //dropdown => uses Consumer in current entity
    {
        name: "FILTER_GENDER",
        title: "Gender",
        contentType: "TEXT",
        hasDropDownValues: true,
        isGroupable: true,
        consumer: "KeywordGenders",
        groupedRecordField: "TRAINEE.GENDER",
        titleRecordField: KeywordUtils.getResolvedTitleSqlPart($SalutationKeywords.personGender(), "TRAINEE.GENDER")
    }

];

result.string(JSON.stringify(filterFields));

filterValuesProcess

.The code of "example_filterSet"'s property filterValuesProcess

import { logging, result, vars } from "@aditosoftware/jdito-types";

let filter = JSON.parse(vars.getString("$local.filter"));

let values = [];
switch(filter.name){
    case "FILTER_GRADE":
        values = [
            ["5", "excellent"],
            ["4", "good"],
            ["3", "satisfactory"],
            ["2", "less than satisfactory"],
            ["1", "unsatisfactory"]
        ]
        break;
}

result.object(values);

filterConditionProcess

.The code of "example_filterSet"'s property filterConditionProcess

import { logging, result, vars } from "@aditosoftware/jdito-types";
import { newWhere, SqlBuilder } from "SqlBuilder_lib";

//all possible local variables
//let columnPlaceholder = vars.get("$local.columnPlaceholder");
//let columntype = vars.get("$local.columntype");
//var comparison = vars.get("$local.comparison");
//let condition = vars.get("$local.condition");
//let conditionHaving = vars.get("$local.conditionHaving");
//let isAggregateCondition = vars.get("$local.isAggregateCondition");
//let name = vars.get("$local.name");
//let operator = vars.get("$local.operator");
//let operator2 = vars.get("$local.operator2");
//let placeholder = vars.get("$local.placeholder");
//let rawvalue = vars.get("$local.rawvalue");
//let value = vars.get("$local.value");

let rawValue = vars.get("$local.rawvalue");
let comparison = vars.get("$local.comparison");

//e.g. Trainee_entity.example_filterSet.FILTER_GRADEMATH
let name = vars.get("$local.name"); 
let filterName = name.split(".")
    .pop(); // e.g. FILTER_GRADEMATH
let column = "TRAINEE." + name.split("_")
    .pop(); //e.g. GRADEMATH

let cond = newWhere();

switch (filterName) 
{
    case "FILTER_GRADEENGLISH":
    case "FILTER_GRADEGERMAN":
    case "FILTER_GRADEMATH":
        {
            let nullOperator = "IS NULL";
            switch (comparison) 
            {
                case "EQUAL":
                    nullOperator = rawValue == 1 ? "IS NOT NULL" : "IS NULL";
                    break;
                case "NOT_EQUAL":
                    nullOperator = rawValue == 1 ? "IS NULL" : "IS NOT NULL";
                    break;
                case "ISNULL":
                    nullOperator = "IS NULL";
                    break;
                case "ISNOTNULL":
                    nullOperator = "IS NOT NULL";
                    break;
            }

            cond.and(column + " " + nullOperator);
        }
        break;
    case "FILTER_GRADE":
        {
            let operator = null;

            switch (comparison) 
            {
                case "EQUAL":
                    cond.and(
                        newWhere("TRAINEE.GRADEENGLISH", rawValue, SqlBuilder.EQUAL())
                            .or("TRAINEE.GRADEGERMAN", rawValue, SqlBuilder.EQUAL())
                            .or("TRAINEE.GRADEMATH", rawValue, SqlBuilder.EQUAL())
                    );
                    break;
                case "NOT_EQUAL":
                    cond.and(
                        newWhere("TRAINEE.GRADEENGLISH", rawValue, SqlBuilder.NOT_EQUAL())
                            .or("TRAINEE.GRADEGERMAN", rawValue, SqlBuilder.NOT_EQUAL())
                            .or("TRAINEE.GRADEMATH", rawValue, SqlBuilder.NOT_EQUAL())
                    );
                    break;
                case "ISNULL":
                    cond.and(
                        newWhere("TRAINEE.GRADEENGLISH IS NULL")
                            .and("TRAINEE.GRADEGERMAN IS NULL")
                            .and("TRAINEE.GRADEMATH IS NULL")
                    );
                    break;
                case "ISNOTNULL":
                    cond.and(
                        newWhere("TRAINEE.GRADEENGLISH IS NOT NULL")
                            .and("TRAINEE.GRADEGERMAN IS NOT NULL")
                            .and("TRAINEE.GRADEMATH IS NOT NULL")
                    );
                    break;
            }
        }
        break;
        
    case "FILTER_GENDER":
        {
            let operator = null;

            switch (comparison) 
            {
                case "EQUAL":
                    cond.and(column, rawValue, SqlBuilder.EQUAL());
                    break;
                case "NOT_EQUAL":
                    cond.and(column, rawValue, SqlBuilder.NOT_EQUAL());
                    break;
                case "ISNULL": 
                    cond.and(column + " IS NULL");
                    break;
                case "ISNOTNULL":
                    cond.and(column + " IS NOT NULL");
                    break;
            }
        }
        break;
}

logging.log(JSON.stringify({
    rawValue,
    comparison,
    name,
    cond: cond.toString()
}, null, "\t"));

result.string(cond.toString());

groupQueryProcess

.The code of "example_filterSet"'s property groupQueryProcess

import { logging, result, vars } from "@aditosoftware/jdito-types";
import { newWhere, SqlBuilder } from "SqlBuilder_lib";

//all possible local variables
//let columnlist = vars.get("$local.columnlist");
//let columns = vars.get("$local.columns");
//let columntype = vars.get("$local.columntype");
//let condition = vars.get("$local.condition");
//let contenttype = vars.get("$local.contenttype");
//let count = vars.get("$local.count");
//let fieldname = vars.get("$local.fieldname");
//let grouped = vars.get("$local.grouped");
//let groupedlist = vars.get("$local.groupedlist");
//let name = vars.get("$local.name");
//let order = vars.get("$local.order");

var sql = new SqlBuilder()

if (vars.get("$local.count")) // TRUE if the count of the records is needed
{
    sql.select("1");
} 
else 
{
    let columnlist = vars.get("$local.columnlist");

    sql.select([columnlist]);
}

sql.from("TRAINEE");

let condition = vars.get("$local.condition");
if(condition != "  ")
{
    sql.where(condition);
}

let grouped = vars.get("$local.grouped");
sql.groupBy(grouped);

sql.orderBy(grouped);

result.string(sql.toString());

Further examples

Further, more complex examples of FilterExtensionSets are included in the ADITO xRM project. For example, a FilterExtensionSet named "Attribute_filter" is included in the RecordContainer of several Entities, e.g., in Organisation_entity and in Person_entity. This FilterExtensionSet allows the client user to filter the Entity's datasets according to the attributes assigned to them (e.g., the attribute "Loyalty" of Organisation/Company or Person/Contact datasets).

Available local variables

The following "$local" variables can, amongst others, be accessed in the code of a FilterExtensionSet's properties:

Name	Description
`$local.count`	TRUE if the count of the records is needed
`$local.columnlist`	String with the columns (and expressions) expected to be returned by the query
`$local.condition`	The (filter) condition that's being used (if used in a grouping, then it includes the group hierarchy); see example in appendix ("$local variables")
`$local.groupedlist`	String with the columns (and expression) used for grouping
`$local.order`	String that contains the order expression how the grouped items have to be sorted
`$local.name`	String value of the "name" property, if a filterField was returned by the filterFieldsProcess; every filterField has its unique name

useConsumer

If you are already familiar with FilterExtensions, please note that the "useConsumer" functionality (see chapter FilterExtension above) is also available for FilterExtensionSets. You can configure it via the JSON config object that is set in the result of the filterFieldsProcess: Simply add attribute "consumer" and set the Consumer's name as its argument. Here is a universal code example:

.Example of result of filterFieldsProcess relating to a Consumer

var myConfig = [];
(...)
    myConfig.push({
        name: (...),
        title: (...),
        contentType: (...),
        hasDropDownValues:  (...)
        isGroupable:  (...),
        groupedRecordField:  (...),
        titleRecordField:  (...),
        consumer: "MyConsumerName",
        (...)
    });

myConfig = JSON.stringify(myConfig);
result.string(myConfig);

groupQueryProcess

Grouping via groupQueryProcess is also available for FilterExtesionSets. The approach is similar to the groupQueryProcess of FilterExtensions. The difference is only that the required properties "groupedRecordField" and "titleRecordField" are filled in the filterFieldsProcess. In the groupQueryProcess the usual $local variables are available ($local.columnlist, $local.condition, $local.groupedlist, etc.), and you can use them to build your SQL statement that makes the grouping.

You may study an example in xRM, e.g., groupQueryProcess of ClassificationGroup_filter, a FilterExtensionSet of several Entities, e.g., of Organisation_entity or Salesproject_entity.

Consumer filter

Example included in the ADITO xRM project:

In Context "Person" (titled "Contact" in the Global Menu), you have the option to filter contact persons according to the topic of a connected Activity:

This was made possible by the following steps:

In the RecordContainer, the ConsumerMapping for Consumer "Activities" was initialized. ("Click to initialize" in the property sheet)

In the property sheet of this ConsumerMapping, the following properties were set:
- filtertype: EXTENDED
- isFilterable: true
- filterConditionProcess:

import { result, vars  } from "@aditosoftware/jdito-types";
import { newSelect, newWhere, SqlBuilder } from "SqlBuilder_lib";

let subselect = newSelect("CONTACT.PERSON_ID")
    .from("CONTACT")
    .join("ACTIVITYLINK", "ACTIVITYLINK.OBJECT_ROWID = CONTACT.CONTACTID")
    .and("ACTIVITYLINK.OBJECT_TYPE", "Person")
    .join("ACTIVITY", "ACTIVITY.ACTIVITYID = ACTIVITYLINK.ACTIVITY_ID")
    .where(vars.get("$local.condition"));

let sql = newWhere("PERSONID", subselect, SqlBuilder.IN());

result.string(sql.toString());

EntityRecordsRecipe

Usage in "openContextWithRecipe"

The following example will help you to understand how the EntityRecordsRecipe works. This code opens the FilterView of Person_entity, with the datasets

restricted to persons whose last name starts with letter "B"
without (excluding) the persons "Frank Baer" and "Christine Burger"

Test it with an Action

You may include this code, e.g., in the onActionProcess of a test Action of Person_entity. (see chapter "Actions")

.Person_entity.TestActionGroup.testAction.onActionProcess

// Definition of test filter that is restricting records
// to those with LASTNAME starting with letter "B"
var myFilterCondition = neonFilter.createFilterCondition()
.field("LASTNAME")
.searchOperator(neonFilter.SEARCH_OPERATOR_STARTSWITH)
.contentType(neonFilter.CONTENT_TYPE_TEXT)
.key("B");
var myFilter = neonFilter.createFilterGroup()
.addFilterCondition(myFilterCondition);

// Definition of array holding UIDs (CONTACTIDs)
// of "Frank Baer" and "Christine Burger"
var myUidList = ["701569b7-d791-4682-89a1-bf26682187af", "a38a19f6-6255-47b0-bbea-138bae2271c4"];

// Definition of EntityRecordsRecipeBuilder
var myEntityRecordsRecipe = neonFilter.createEntityRecordsRecipeBuilder()
.entity("Person_entity")
// applying filter
.filter(myFilter)
// excluding records of "Frank Baer" and "Christine Burger"
.uidsExcludelist(myUidList);

// opens PersonFilter_view with multiple record (e.g., "Carl Bush")
neon.openContextWithRecipe("Person", "PersonFilter_view", myEntityRecordsRecipe,
    neon.OPERATINGSTATE_SEARCH, null, false);

Example of uidsIncludelist

Here is a modified example, without using method filter, resulting in exactly 2 records ("Frank Baer" and "Christine Burger"):

.Person_entity.TestActionGroup.testAction2.onActionProcess

var myEntityRecordsRecipe = neonFilter.createEntityRecordsRecipeBuilder()
.entity("Person_entity")
// restricting to records of "Frank Baer" and "Christine Burger"
.uidsIncludelist(myUidList);

Combining filter and "include list"

And here is an example that combines a filter with a "include list" (resulting in only 1 record, "Christine Burger"):

.Person_entity.TestActionGroup.testAction3.onActionProcess

// Definition of test filter that is restricting records
// to those with LASTNAME starting with letter "B"
var myFilterCondition = neonFilter.createFilterCondition()
.field("FIRSTNAME")
.searchOperator(neonFilter.SEARCH_OPERATOR_STARTSWITH)
.contentType(neonFilter.CONTENT_TYPE_TEXT)
.key("C");
var myFilter = neonFilter.createFilterGroup()
.addFilterCondition(myFilterCondition);

// Definition of array holding UIDs (CONTACTIDs)
// of "Frank Baer" and "Christine Burger"
var myUidList = ["701569b7-d791-4682-89a1-bf26682187af", "a38a19f6-6255-47b0-bbea-138bae2271c4"];

// Definition of EntityRecordsRecipeBuilder
var myEntityRecordsRecipe = neonFilter.createEntityRecordsRecipeBuilder()
.entity("Person_entity")
// applying filter
.filter(myFilter)
// restricting to records of "Frank Baer" and "Christine Burger"
.uidsIncludelist(myUidList);

// opens PersonFilter_view with 1 record ("Christine Burger")
neon.openContextWithRecipe("Person", "PersonFilter_view", myEntityRecordsRecipe,
    neon.OPERATINGSTATE_SEARCH, null, false);

Usage in "LoadEntity"

EntityRecordsRecipe can also be used to define the records to load via "LoadEntity" (see chapter "LoadEntity"): Simply specify an EntityRecordsRecipe instance as parameter of the LoadRowsConfig's method fromEntityRecordsRecipe. Here is an example using the same EntityRecordsRecipe as in the example code of the previous chapter:

.Person_entity.TestActionGroup.testAction4.onActionProcess

var myEntityRecordsRecipe = (...) // see previous chapter

var myLoadRowsConfig = entities.createConfigForLoadingRows()
// can be skipped, as it is included in EntityRecordsRecipe
//.entity("Person_entity")
.fields(["FIRSTNAME", "LASTNAME"])
.fromEntityRecordsRecipe(myEntityRecordsRecipe)
     
var myRows = entities.getRows(myLoadRowsConfig);

// [{"FIRSTNAME":"Carl", "LASTNAME":"Bush"}]
logging.log(JSON.stringify(myRows));

Usage in customized methods

In principle, there are almost no limits for integrating EntityRecordsRecipe also in customized methods. In fact, your application's performance can be significantly improved if you use it whenever required, or if you refactor your existing customized code with respect to EntityRecordsRecipe.

Here is an example of how an integration can look like in a customized code (just as pattern):

.Example pattern for using EntityRecordsRecipe in a customized method

var attributeValue = (...);

var activeStatusFilter = neonFilter.createFilterCondition()
.field("STATUS")
.searchOperator(neonFilter.SEARCH_OPERATOR_EQUAL)
.key($KeywordRegistry.contactStatus$active())
.contentType(neonFilter.CONTENT_TYPE_TEXT);

var affectedContactsRecordsRecipe = neonFilter.createEntityRecordsRecipeBuilder()
.entity("Person_entity")
.filter(activeStatusFilter)
.parameters({"NoCommRestriction_param": "EMAIL"});

AttributeRelationUpdateUtils.addAttribute(
  $AttributeRegistry.deliveryTerm(),
  "Person",
  affectedContactsRecordsRecipe,
  attributeValue);

📝 Note: The argument of method .filter can either be a filter object (as shown in the above example), but it could also be a JSON filter object (stringified) instead. Method .filter only works, if also method .entity is used (unlike parameters, uids, etc.).

Further example

This is an example code of a test Action of Person_entity, opening the FilterView again, restricted to the selected records (i.e., all records of the FilterView with checkboxes checked by the client user).

.Person_entity.TestActionGroup.testAction5.onActionProcess

var myEntityRecordsRecipeAsJSON = vars.get("$sys.selectionsRecordsRecipe");

// logging the selected records
logging.log("------> " + myEntityRecordsRecipeAsJSON);

neon.openContextWithRecipe("Person", "PersonFilter_view", myEntityRecordsRecipeAsJSON,
    neon.OPERATINGSTATE_SEARCH, null, false);

Now, to be more exact, $sys.selectionsRecordsRecipe holds the EntityRecordsRecipe not as EntityRecordsRecipeBuilder object, but as JSON string.

Converting JSON string into EntityRecordsRecipeBuilder object

You can simply convert this JSON string into an EntityRecordsRecipeBuilder object, by specifying it as parameter of the create method:

var myEntityRecordsRecipe = neonFilter.createEntityRecordsRecipeBuilder(vars.get("$sys.selectionsRecordsRecipe"));

Subsequently, you may modify this object (e.g., by calling methods filter or uidsExcludelist- see previous chapter) and use it for any purpose.

Example: Notifications

In the xRM project' Notification_entity, you can find an example of the usage of EntityRecordsRecipe. In the client, a "select all" button is available, allowing the user to select all Notification records (and, if required, unselect single records afterwards) - including those that will appear not before you scroll down or to the next "page".

After selecting Notification records, the user can change their state all at once. In the corresponding Actions' onActionProcesses the EntityRecordsRecipe approach is used. See also the functionality of Notification_lib and the ADITO platform methods notification.xxx, e.g.

notification.updateUserNotificationsStateBulk, which in turn requires method
notification.createUpdateStrategy

Dynamic filter values​

Configuration​

How to use the filterVariablesProcess?​

Universal usability​

Technical background​

FilterGroup and FilterCondition​

Filter configuration in the client​

Filter configuration in JDito​

initFilterProcess​

Example task​

Example from xRM​

filterConditionProcess​

groupQueryProcess​

Example in xRM​

Implementing a groupQueryProcess - step by step​

Example: Phase_filter and its variables​

FilterExtensionSet​

Example: Managing grades of trainees​

Creating Consumer for gender-related field​

filterFieldsProcess​

filterValuesProcess​

filterConditionProcess​

groupQueryProcess​

Further examples​

Available local variables​

useConsumer​

groupQueryProcess​

Consumer filter​

EntityRecordsRecipe​

Usage in "openContextWithRecipe"​

Test it with an Action​

Example of uidsIncludelist​

Combining filter and "include list"​

Usage in "LoadEntity"​

Usage in customized methods​

Further example​

Converting JSON string into EntityRecordsRecipeBuilder object​

Example: Notifications​

Dynamic filter values

Configuration

How to use the filterVariablesProcess?

Universal usability

Technical background

FilterGroup and FilterCondition

Filter configuration in the client

Filter configuration in JDito

initFilterProcess

Example task

Example from xRM

filterConditionProcess

groupQueryProcess

Example in xRM

Implementing a `groupQueryProcess` - step by step

Example: `Phase_filter` and its variables

FilterExtensionSet

Example: Managing grades of trainees

Creating Consumer for gender-related field

filterFieldsProcess

filterValuesProcess

filterConditionProcess

groupQueryProcess

Further examples

Available local variables

useConsumer

groupQueryProcess

Consumer filter

EntityRecordsRecipe

Usage in "openContextWithRecipe"

Test it with an Action

Example of uidsIncludelist

Combining filter and "include list"

Usage in "LoadEntity"

Usage in customized methods

Further example

Converting JSON string into EntityRecordsRecipeBuilder object

Example: Notifications