Technical question on using primary filters to create cascading select in EspoCRM

[DGorsse]

For the data management system for a humanitarian use case in Morocco (and very likely in the future for other humanitarian use cases too), we are encountering the challenge of implementing cascading select in EspoCRM. We need support to figure out a (potential) new way to do this.

There are two different use cases for this:

1. Coding framework with Types, Categories and Codes. For the Morocco use case, this is a double nested framework with around 7 Types, around 5 Categories per Type, and around 6 Codes per Category. So in total around 200 codes on lowest level.
2. Location-related information with Regions, Provinces and Communes which works in a similar nested structure/hierarchy.

There are multiple ways to try to implement such a functionality: 1) list all the options in three separate fields (not really cascading select and this will leave the user with huge lists; 2) hardcode all the second and third level options as field and use skiplogic to hide (this is cascading select, but you will have to create many fields and many logic); 3) using an entity-approach with primary filters.

How the last one could probably/hopefully work:

1. create an entity per level (so in total 3 new entities)
2. link the 3 level entities in a nested structure (how? upper to lower level; one to many respectively; e.g. one Type record contains many Categories etc.)
3. link the 3 level entities to the main entity
4. add records to the entity level 1 (e.g. for 'type': complaints, questions and observations etc.)
5. add records to entity level 2 which are related to a record of entity level 1 (e.g. for type='question': question about registration, question about distribution etc.)
6. add records to entity level 3 which are related to a record of entity level 2 (e.g. for category='question about registration': question on where to register, question on when I can register etc.)
7. UX: create a record of the the main entity and fill in the following on the detail page:
   1. select a record under type
   2. select a record under category (which only shows the records related to what has been selected under type earlier, using a primary (automatically pre-set) filter
   3. select a record under topic (which only shows the records related to what has been selected under category earlier, using a primary (automatically pre-set) filter

The main question here is how to make this work with a primary filter. What would be the change to be made in the backend so that the above steps 7.2 and 7.3 can show a subselection only of the categories and topics?

Please let me know if a more visual description would help next to this written explanation. The goal is to duplicate the cascading select functionality from KoboToolbox within EspoCRM, to avoid selecting from a large lists of options for users.

Below screenshots give an idea of the type, category and code on the detail layout (screenshot 1). Upon selecting 'questions' as Type, the categories should show a subset of the category options which relate to the earlier inputted Type 'questions' (screenshot 2)


In the attachment there is also the extension that can be uploaded to simulate the above.

[rabii]

It is more of a front-end logic. However if the user is always free to choose and change the Type / Category and then Code why not use a Tree Entity Type similar to knowledge base. on Category side (Parent tree) you will have the ability to nest children e.g Type (Question) Children ( question about registration etc) and then the code would be the entity child that live under both parents (hierarchy) this way you reduce the number of relationships and have only code on the end form entity and on select user can just select the parent on the left side and have all ready.

If you want to keep your logic the you will need to apply there are two ways either you simply use a getSelectFilters on each field (link field Type - Category - Code) Or if you have prebuilt primaryFilters then you can call them using getSelectPrimaryFilterName.

Is it necessary to have Type and Category on the formEntity ?

[yuri]

Select handler applied when selecting a record. Work for link fields and relationship panels. https://docs.espocrm.com/development...#selecthandler

clientDefs > {EntityType} > relationshipPanels > {link} > selectHandler

Create handler applied when creating a record. Work for link fields and relationship panels. https://docs.espocrm.com/development...#createhandler

clientDefs > {EntityType} > relationshipPanels > {link} > createHandler

[DGorsse]

yuri thanks for the explanation. We don't have the skills/capacity in-house to develop this. Are you planning to develop this as a functionality in the future?

[rabii]

What he shared is a the way to filter field links based on relationship. This is already implemented in the core code and a developer can use this to allow select only list of resource related to existing previous link.

[Maarten]

I am giving this one more go, but not yet succeeding. Some help is appreciated.

I placed the functions in
client/custom/src/handlers/select-related/code-by-category.js
client/custom/src/handlers/select-related/category-by-type.js

define('custom:select-handlers/code-by-category', ['handlers/select-related'], Dep => {
    return class extends Dep{

        getFilters(model) {
            const advanced = {};

            let categoryId = null;
            let categoryName = null;

            console.log('getFilters called for code-by-category');
            console.log('Model:', model);

            if (model.get('typeId')) {
                categoryId = model.get('typeId');
                categoryName = model.get('typeName');
                console.log('Found typeId:', categoryId, 'typeName:', categoryName);
            } else {
                console.log('No typeId found on model');
            }

            if (categoryId) {
                advanced.sourceLevel2 = {
                    attribute: 'sourceLevel2Id',
                    type: 'equals',
                    value: categoryId,
                    data: {
                        type: 'is',
                        nameValue: categoryName,
                    },
                };
                console.log('Advanced filter set:', advanced.sourceLevel2);
            } else {
                console.log('No categoryId, advanced filter not set');
            }

            return Promise.resolve({
                advanced: advanced,
            });
        }
    }
});

And for the main entity that has the layout where the type, category and code selects are placed:

in custom/Espo/Custom/Resources/metadata/clientDefs/FeedbackFormTest.json

{
    "controller": "controllers/record",
    "boolFilterList": [
        "onlyMy"
    ],
    "relationshipPanels": {
        "type": {
            "layout": null,
            "selectPrimaryFilterName": null
        },
        "category": {
            "layout": null,
            "selectPrimaryFilterName": null,
            "selectHandler": "custom:handlers/select-related/category-by-type"
        },
        "code": {
            "layout": null,
            "selectPrimaryFilterName": null,
           "selectHandler": "custom:handlers/select-related/code-by-category"
        }
    }
}​

The relationshis in FeedbackFormTest are as follows:



Entity Type has these relationships:


Entity Category has these relationships:


Entity Code has these relationships


When I select a category or code from the select dropdown (when I create a record, or edit a record), I see in the chrome network tab that the js is loaded. But it goes in waiting mode (rotating orange circle), doesn't print the log messages to the console, and doesn't open the select popup.

Can anyone point me to the error(s)?

[yuri]

The AMD module ID is wrong in the define. It does not match the path and the ID set in metadata. I recommend to always omit the ID when using `define`.

define('custom:select-handlers/code-by-category', ['handlers/select-related'], Dep => {

=>

define(['handlers/select-related'], Dep => {

[Maarten]

Thanks yuri that did the trick.