Reference: Espo GUI - script map guide, where can I change something ?

[shalmaxb]

Using this as a base I enhanced for my purpose the transposed table: https://forum.espocrm.com/forum/gene...iew#post101166

[shalmaxb]

I resolved all mentioned questions of this post, see next comment. @telecastg: It is quite a time ago, when this was created, but still works in expoCRM 8.x. I am only missing two thing, which I did not get to resolve:

1. How can I display the the tableheader row as first column in the swapped view?
2. In the swapped view, the rows will not be lined up, if the content of any field is varying. The row`s cells will not be at the same position depending on the amount of content, if the content is a list of a multi enum field. The table in this case does not behave like an HTML table, where the complete row height is determined by the field with the largest height. How can I resolve this?

[Paulina]

Hi, I created custom navbar view class in client/custom/scr/views/navbar.js and I have one event which I want to execute on Icon click. It is working fine but then I cant log out from the system. Do you have any ideas why?

define('custom:views/site/navbar', 'views/site/navbar', function(Dep) {
    return Dep.extend({
        afterRender: function() {
            Dep.prototype.afterRender.call(this);

            this.$navbar.find('ul.nav.navbar-nav.navbar-right').append(
                '<a style="cursor: pointer;" class="top-nav-envelope"><i class="fas fa-envelope"></i></a>'
            );
        },

		setup: function() {
            Dep.prototype.setup.call(this);

        },

        events: {
            'click a[class="top-nav-envelope"]': function(e) {
                this.notify('Loading...');

            },
        }


    });


});

[telecastg]

Thank you, I appreciate the suggestions:

I don't believe that sanitizing is necessary In this case, because the only parameter being fed to the query is the list of values from the multi-enum field, which are pre-defined and not modifiable by the user.

However I completely agree that whenever you have user input feeding a query, is very important to "sanitize" the input to avoid malicious SQL injection

Could you please elaborate on how to build an ORM style query, based on a regular SQL query using IDE's autocompletion ?

I couldn't figure out how to use the ORM "language" to construct a query that uses the "IN" statement, is there an example in the code base that i could check ? .

Thanks !

[telecastg]

Hi, I am sorry don't know of any "easy" way to do this and we haven't developed anything similar so I can't provide any code examples

[yuri]

Needs sanitizing parameters before using in raw SQL. E.g. using $entityManager->getPDO()->quote($value).

Recommend using ORM to prevent possible security issues. Building such SQL with ORM is easy, especially if IDE's autocompletion.

[LuckyLouie]

telecastg Thank you for all tutorials.
Is there any easy way to move the dashlist tabs to the menu bar?

[telecastg]

How to filter the options available at a multi-enum field based on the options selected at another multi-enum field in the same entity. TESTED with Espo 7.0.9

This post describes how to accomplish the above functionality that we implemented for an Espo 7.0.9 compatible project.

Please note that that the example does use raw SQL which is discouraged by Espo's developers, however, for our purposes, investing time trying to "translate" the universal SQL to the custom Espo ORM "language" did not provide any advantage.

For those preferring to keep their code strictly adhered to Espo's developers standards, it will be necessary to implement the backend query using Espo's custom ORM.

In our project we have a Pinboard entity that is linked to one or more teams.

Within those teams there will be participants, having one or more specific roles, that will be allowed to read and write on the Pinboard, while all other participants having different roles will have read only rights by default.

We want the ability to choose one or many teams (Participant Teams) that will be able to view the Pinboard and then choose roles (Read Write Roles) that are linked one or more of the selected Participant Teams, and which will be able to read and write on the Pinboard. All other roles will have read only access.

So every time that a Participant Team is added or deleted, the options available for the Read Write Roles selector should adjust accordingly.

Step 1 - Define the multi-enum fields in the Pinboard entity entityDefs file:
custom\Espo\Custom\Resources\metadata\entityDefs\P inboard.json (partial)

{
    "fields": {
        "participantTeams": {
            "type": "multiEnum",
            "view": "custom:views/settings/fields/teams-list",
            "isCustom" : true
        },
        "readWriteRoles": {
            "type": "multiEnum",
            "view: "custom:views/settings/fields/roles-list,
            "isCustom": true            
        }
    }
}

Step 2 - Define the front-end views to render the fields as specified in the entityDefs file:
client/custom/src/views/setings/fields/teams-list.js

define('custom:views/settings/fields/teams-list', ['views/fields/multi-enum'], function (Dep) {

    return Dep.extend({

        setupOptions: function () {
            Espo.Ajax.getRequest('Team/action/list').then(
                function (data) {
                    data.list.sort((a, b) => (a.name > b.name) ? 1 : -1);
                    this.params.options = [];
                    data.list.forEach(function(optionObj){
                        this.params.options.push(optionObj.name);
                    },this);
                    // update the field display after downloading the options from the database
                    this.reRender();
                }.bind(this)
            );
        }

    });
});

client/custom/src/views/settings/fields/roles-list.js

define('custom:views/settings/fields/roles-list', ['views/fields/multi-enum'], function (Dep) {

    return Dep.extend({

        setup: function () {
            Dep.prototype.setup.call(this);
            // update the list of otions availabe every time that the participantTeams field is updated
            this.listenTo(this.model,'change: participantTeams', function() {
                this.setupOptions();
            });
        },

        setupOptions: function () {
            const teamsNames = this.model.get('participantTeams') || [];
            if(teamsNames.length < 1) {
                // do not display any options unless the "participantTeams" field contains at least one selection
                this.params.options = [];
                this.reRender();
                return;
            }
            Espo.Ajax.getRequest('Pinboard/action/fetchTeamsLinkedRoles', {
                teams: teamsNames
            }).then(
                function (fetchedData) {
                    fetchedData.list.sort((a, b) => (a.name > b.name) ? 1 : -1);
                    this.params.options = [];
                    fetchedData.list.forEach(function(optionObj){
                        // this function can be used to further filter the list of roles available for selecting. In this case we don't use any additional filter
                        this.params.options.push(optionObj.name);
                    },this);
                    // update the field display after downloading the options from the Database
                    this.reRender();
                }.bind(this)
            );
        }

    });
});

Step 3 - Implement the back end classes to retrieve the list of roles from the database
custom\Espo\Custom\Controllers\Pinboard.php

namespace Espo\Custom\Controllers;

use Espo\Core\Api\Request;
use StdClass;

class Pinboard extends \Espo\Core\Templates\Controllers\Base
{

    public function getActionFetchTeamsLinkedRoles(Request $request): StdClass
    {
        $teams = $request->getQueryParam('teams');
        $queryResult = $this->getRecordService()->getTeamsRelatedRoles($teams);

        return (object) [
            'list' => $queryResult
        ];
    }
} 

custom\Espo\Custom\Services\Pinboard.php

namespace Espo\Custom\Services;

use PDO;

class Pinboard extends \Espo\Core\Templates\Services\Base
{

    public function getTeamsRelatedRoles($teams): Array
    {
        $sqlString = 'SELECT role.name As `name` FROM `role` ';
        $sqlString.= 'INNER JOIN `role_team` ON role.id = role_team.role_id ';
        $sqlString.= 'INNER JOIN `team` ON team.id = role_team.team_id ';
        $sqlString.= 'WHERE team.name IN (';
        for ($i = 0; $i < count($teams); $i++){
            if($i > 0) {
                $sqlString.= ',"'.$teams[$i].'"';
            } else {
                $sqlString.= '"'.$teams[$i].'"';
            }
        }
        $sqlString.= ') GROUP BY role.name';

        $data = $this->entityManager
            ->getSqlExecutor()
            ->execute($sqlString)
            ->fetchAll(PDO::FETCH_ASSOC);

        return $data;
    }

} 

Step 4 - Clear cache and rebuild the application

[telecastg]

Thanks for pointing it out Athensmusic you are right, I corrected the posting

[Athensmusic]

Hi, don't know why but for me didn't worked as it is.
We needed to add 'src' in the line like below.
client/custom/src/views/service-tech/record/list.js
Thanks

[telecastg]

You're welcome rem4332

[rem4332]

telecastg you are the madness, many dear thanks. Nice feature

[telecastg]

How to create and use a custom button at the top left of the list display, where the kanban button is displayed.

Following item suggestion to use this type of button, to switch between a normal list display and a list where the columns and rows are transposed, as explained in the previous post, these are the steps to create and implement a custom button in this area:





1) Create a custom view class to display the list of records with the columns and rows transposed.
client/custom/src/views/service-tech/record/list.js

define('custom:views/service-tech/record/list', 'views/record/list', function (Dep) {

    return Dep.extend({

        setup: function () {
            Dep.prototype.setup.call(this);
            this.listenTo(this,'after:render', function() {
                $('tr.list-row').css("display","block");
                $('tr.list-row').css("float","left");
                $('td.cell').css("display","block");
                $('th').css("display","none");
            });
        }
    });

});

2) Create a custom view class to render the header area above the list display
client/custom/src/views/service-tech/header/list.js

define('custom:views/service-tech/header/list', 'views/list', function (Dep) {

    return Dep.extend({

        // incorporate the search view that includes the additional icon
        searchView: 'custom:views/service-tech/record/search',

        // fetch the name of the view class to be used to render the collection
        getRecordViewName: function () {
            if (this.viewMode === 'list') {
                return this.getMetadata().get(['clientDefs', this.scope, 'recordViews', 'list']) || this.recordView;
            } else if(this.viewMode === 'transpose') {
                return this.getMetadata().get(['clientDefs', this.scope, 'recordViews', 'transpose']);
            }
            return this.getMetadata().get(['clientDefs', this.scope, 'recordViews', this.viewMode]);
        }

    });

});

3) Create custom view class to render the "search" area in the header section above the list display
client/custom/src/views/service-tech/record/search.js

define('custom:views/service-tech/record/search', 'views/record/search', function (Dep) {

    return Dep.extend({

        // adds the new transpose icon at the top right corner of the list display
        viewModeIconClassMap: {
            list: 'fas fa-align-justify',
            transpose: 'fas fa-align-justify fa-rotate-90'
        }

    });
});

4) Create a custom clientDefs file for the target entity ("ServiceTech" in this example) to invoke the custom view classes created above
custom/Espo/Custom/Resources/metadata/clientDefs/ServiceTech.json

{
    "views": {
        "list": "custom:views/service-tech/header/list"
    },
    "recordViews": {
        "transpose": "custom:views/service-tech/record/list"
    },
    "listViewModeList" : ["list","transpose"]
}

5) Clear cache and rebuild.

[telecastg]

Thanks rabii , good suggestion item, creating a button next to the list / kanban selector requires a slightly different approach, I will make a new post for better understanding.

[item]

Hello @telecastg,

nice .. but for me it's better to put the "button" like "kaban". At the same place of switch from "kaban" "list" "NewName"

Kinds Regards