Announcement

Collapse
No announcement yet.

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    What is the program flow for an API call ?

    API calls invoke a back end controller which generally then calls for a service class to execute the requested action.

    API call > router > back end controller > service class

    To see an example check this post. https://forum.espocrm.com/forum/deve...3408#post63408

    Comment


    • #17
      How to modify in an "upgrade safe" way the script views/record/search.js

      Click image for larger version

Name:	Capture.PNG
Views:	878
Size:	4.5 KB
ID:	70787


      Check this post for detailed instructions to incorporate custom code changes to the search class without changing any core scripts
      I want to customize espocrm/client/src/views/record/search.js file. Can anyone help me with how to do this? Research: We have espocrm/client/src/views/list.js file which will define the searchView as views/record/search. Similarly, the recordView: 'views/record/list' is defined in theespocrm/client/src/views/list.js. I


      Comment


      • #18
        How are layouts used to render an entity detail display:

        Layouts are json files created through the Administration > Layout Manager facility and tell Espo how to order and render the entity's fields in different display modes.

        For the "detail" display mode, a record (entity) is rendered in one or more panels, each panel containing the fields and organization of these fields specified in the layout file like this:
        Click image for larger version  Name:	Layout Manager.jpg Views:	0 Size:	61.3 KB ID:	78108

        If an entity does not have any detail layout associated with it, like in the case of a new custom entity, Espo will assume a detail layout that contains only the "name" attribute occupying one half of the single row.

        The above information is referred to as "simpleLayout" in the client/src/views/record/detail.js class.

        The "simpleLayout" is processed by the above class through the method getGridLayout() to create a complete specification of not just which fields are displayed where but also which field view classes are to be used to render each field in detail or edit modes, the document element where each field is to be rendered and the model providing the data bound to each field.

        The client/src/views/record/detail.js class takes then the output from the method getGridLayout() and uses it to render the record (entity) in the "middleView" class which is rendered in the "middle" html div where the record's details are displayed in the screen.

        Click image for larger version  Name:	Middle View.png Views:	0 Size:	73.6 KB ID:	78109
        Last edited by telecastg; 01-11-2022, 10:54 PM.

        Comment


        • #19
          How is Layout data retrieved by the Layout Manager ?

          Layout data is stored in json files located under the affected entity's module namespace.

          For example for the "Call" entity, which is defined under the module "Crm", the various layout files (one for each type, like "detail", "detailSmall", "list", "listSmall", etc) are located inside the directory application/Espo/Modules/Crm/Resources/layouts/Call/

          Therefore the detail layout data is stored in the file application/Espo/Modules/Crm/Resources/layouts/Call/detail.json

          Click image for larger version  Name:	Screenshot 2022-01-16 112940.png Views:	0 Size:	114.9 KB ID:	78281


          When a User clicks on the Administration > Layout Manager > Calls > Detail menu option the following actions take place inside Espo

          1) Front end script client/src/layout-manager.js looks for the layout data in cache and if not found, makes an API (Ajax get request) call: Call/layout/detail

          2) Json script application/Espo/Resources/routes.json interprets the instruction and invokes method getActionRead() at Controller class application/Espo/Controllers/Layout.php providing two parameters: $scope = "Call", $name = "detail".

          3) Controller class application/Espo/Controllers/Layout.php invokes method getForFrontend($scope, $name) at Service class application/Espo/Services/Layout.php

          4) Service class application/Espo/Services/Layout.php checks permissions and team membership to determine if the user request requires the retrieval of a specific layout file or the default for all users. If a specific layout is required the Service class invokes method get($scope, $name) at Utils class application/Espo/Core/Utils/Layout.php , otherwise invokes method getDefault($scope, $name) at the same Utils class.

          5) Utils class application/Espo/Core/Utils/Layout.php determines the correct path where the requested layout file exists, reads the file and returns the data as a JSON object to the Service class which passes the same to the Controller class which then passes it to the front end script.
          Last edited by telecastg; 01-16-2022, 08:39 PM.

          Comment


          • item
            item commented
            Editing a comment
            Hello @telecastg,
            i write here because i think it's here

            out-of-box, we have layout "listForAccount" and "listForContact".. it's for bottom panel,
            where we can create this kind of layout for custom entity ? witch files to modify ?
            sample :
            Patient -> bottomPanel = contacts => listForPatient
            Institution -> bottomPanel => contacts => listForInstitution

            so i need bottomPanel to have differents field if it's Patient or Institution.

            Regards

          • telecastg
            telecastg commented
            Editing a comment
            Hello @item,

            I think that the easiest way would be to include all fields in one layout and then use dynamic handler or dynamic logic to hide or display fields based on the type of subject (Patient or Institution)

            To use a custom layout for different subjects, instead of manipulating one layout through dynamic logic or dynamic handler, would require some coding, I'll write the instructions in a separate post so it's easier to find by other users that have the same requirements.

        • #20
          What is the program flow to render a single record(entity) in Espo ?

          Following up on the previous post "How are new records created ?" https://forum.espocrm.com/forum/deve...1398#post61398 the view class client/src/views/record/detail.js is invoked to render the panels and fields which comprise the display to view or edit a single entity.

          This is the program flow executed within client/src/views/record/detail.js to render the record (entity) panels and fields (attributes):

          Click image for larger version  Name:	Screenshot 2022-01-19 110719.png Views:	56 Size:	40.1 KB ID:	78349

          1) After executing the core methods init() and setup() the core method afterSetup() is executed by default.

          2) afterSetup() invokes method build()

          3) method build() verifies whether it is required to render any side panels or bottom (relationship panels) in addition to the record panel ("middle") and then invokes method createMiddleView()

          4) createMiddleView() halts the program flow until the "middle" view is actually crated, then invokes method getGridLayout() and provides as a parameter for it, a callback function that will create a view, extended from the view class client/src/views/record/detail-middle.js which will actually render the record (entity) panels and fields.

          However in order to be able to create the "middle" view above, the information resulting from the execution of getGridLayout() is necessary, and that is why the program execution has to be halted and the instructions to build the "middle" view are encapsulated in a callback function which will be executed after getGridLayout() is done.

          5) getGridLayout() invokes the helper class layoutManager to fetch the entity's layout information (panels and fields layout) stored as a json file (see post above regarding how the layout json files are created and updated manually through the Layout Manager Administration facility) and then provides this data to the method convertDetailLayout()

          6) convertDetailLayout() takes the layout json data (simple layout) , which describes which panels to render and which fields inside those panels to render, and builds a complex object that inserts a field view class for each of the fields specified in the simple layout.

          As an analogy, one can think of the simple layout, which is manipulated through the Layout Manager Admin facility, and the layout for a house that tells you how many rooms the house has and how are distributed and the results provided by convertDetailLayout() as the specifications of how to build each of the rooms.

          7) convertDetailLayout() returns the complete layout data back to getGridLayout() which then executes the callback instruction created at createMiddleView() (number 4 above) to actually render the record (entity) in the "middle" section of the display.
          Last edited by telecastg; 02-02-2022, 02:36 AM.

          Comment


          • #21
            How to create two different layouts for displaying a single entity and apply either layout depending on a value of the entity's (record's) attribute (field)

            Please note that the following example had been extremely simplified, and it is not fully tested nor optimized for highest speed of execution, since the objective is only to show how to create two custom layouts and apply them dynamically, not the actual solution to the hypothetical situation presented.

            Also note that in this implementation, changing the layout requires a display reRender(), every time that the value of the target field changes, so the entity is automatically saved and that interferes with the inline editing and normal saving mechanism, therefore this example must be considered as partially implemented ONLY.

            In the real world, such a simple variation, like the one used in this example (show a different field based on the value of another field), could easily be achieved using dynamic logic or dynamic handler to manipulate the visibility of fields instead of having a custom layout for each case.

            However, using custom layouts for different cases would allow not only to hide or display fields, but to actually have a complete different distribution of fields and panels in the screen like changing templates, so for those interested in such capability this can be a good starting point

            For this example, we assume the existence of an entity called SystemClient, which has, amongst its attributes, an enum field called clientType.

            The field clientType has two possible values: Consumer and Institution and depending on this value, we will want to use a "consumerDetailLayout" or an "institutionDetailLayout" to render the Client record.

            the "consumerDetailLayout" will include a link field to ratings of Medical Institutions called "institutionRatings" and the "institutionalDetailLayout" will include instead a link to a history of medical insurance claims by consumers called "consumerRatings"

            Step 1: create a custom view class with the following content:

            client/custom/src/views/system-client/record/detail.js
            Code:
            Espo.define('custom:views/system-client/record/detail', 'views/record/detail', function (Dep) {
            
                return Dep.extend({
            
                    /* define a default display layout */
                        defaultLayout: [
                            {
                                "rows": [
                                    [
                                        {
                                            "name": "name"
                                        },
                                            false
                                    ],
                                    [
                                        {
                                            "name": "clientType"
                                        },
                                        false
                                    ]
                                ],
                                "style": "default",
                                "label": "Client Data"
                            }
                        ],
            
                        /* define here the specifications for the consumer display,
                         note that you can choose not only what fields are displayed but also the actual placement of the fields within each panel and even having a different number of panels for                 each layout */
            
                        consumerDetailLayout: [
                            {
                                "rows": [
                                    [
                                        {
                                            "name": "name"
                                        },
                                        false
                                    ],
                                    [
                                        {
                                            "name": "clientType"
                                        },
                                        {
                                            "name": "institutionRatings"
                                        }
                                    ]
                                ],
                                "style": "default",
                                "label": "Consumer Data"
                            }      
                        ],
            
                        /* define here the specifications for the institution display */
            
                        institutionDetailLayout: [
                            {
                                "rows": [
                                    [
                                        {
                                            "name": "name"
                                        },
                                        false
                                    ],
                                    [
                                        {
                                            "name": "clientType"
                                        },
                                        {
                                            "name": "consumerRatings"
                                        }
                                    ]
                                ],
                                "style": "default",
                                "label": "Institution Data"
                            }
                        ],
            
                    setup: function() {
                        // invoke the original method from the extend base view class
                        Dep.prototype.setup.call(this);
                        this.detailLayout = this.defaultLayout;
                        // add code to select the appropriate layout based on the value of the field and rebuild the display
                        const self = this;
                        self.listenToOnce(self, "after:render", function(){
                            self.listenTo(self.model, "change:clientType", function() {
                                let clientType = self.model.get("clientType");
                                self.model.save();
                                if(clientType === "Consumer") {
                                    self.detailLayout = self.consumerDetailLayout;
                                } else if (clientType === "Institution") {
                                    self.detailLayout = self.institutionDetailLayout;
                                } else {
            
                                }
                                this.gridLayout = null;
                                self.build();
                                self.reRender();
                            });
                        });
                    }
                });
            });
            Step 2: modify the custom entity's clientDefs script to let Espo know that it should invoke our custom view class to render a detail or edit display and not the default core view class.

            custom/Espo/Custom/Resources/metadata/clientDefs/SystemClient.json
            Code:
            {
                "recordViews": {
                    "detail": "custom:views/system-client/record/detail"
                }
            }
            Last edited by telecastg; 01-23-2022, 04:26 AM.

            Comment


            • #22
              How to display different bottom (Relationship) panels in a Detail view depending on the value of one of the Entity's (Record) field

              Replying to my friend and active contributor item question above, here is how you can toggle between different relationship panels in response to a change of one of the entity's field values.

              In this example, when the client type is "Auditor" there are 2 relationship panels: "Treatments" and "Claims", when the client type is "Consumer" only the panel "Treatments" is displayed and when the client type is "Institution" only the panel "Claims" is displayed.

              Click image for larger version  Name:	Screenshot 2022-01-22 234008.png Views:	0 Size:	33.3 KB ID:	78429

              Click image for larger version  Name:	Screenshot 2022-01-22 234219.png Views:	0 Size:	30.4 KB ID:	78430

              Click image for larger version  Name:	Screenshot 2022-01-22 234400.png Views:	0 Size:	30.0 KB ID:	78431

              To accomplish this, follow the steps below:

              1) Create custom record detail view class client/custom/src/views/system-client/record/detail.js
              Code:
              define('custom:views/system-client/record/detail', 'views/record/detail', function (Dep) {
              
                  return Dep.extend({
              
                      // define the specifications for the auditor bottom (relationship) panel
                      auditorBottomPanelsDetail:
                      {
                          "_delimiter_": {
                              "disabled": true
                          },
                          "treatments": {
                              "index": 0
                          },
                          "claims": {
                              "index": 1
                          }
                      },
              
                      // define the specifications for the consumer bottom (relationship) panel
                      consumerBottomPanelsDetail:
                      {
                          "_delimiter_": {
                              "disabled": true
                          },
                          "treatments": {
                              "index": 0
                          }
                      },
              
                      // define the specifications for the institution bottom (relationship) panel
                      institutionBottomPanelsDetail:
                      {
                          "_delimiter_": {
                              "disabled": true
                          },
                          "claims": {
                              "index": 0
                          }
                      },
              
                      // initialize a layout data container object
                      layoutData: {},
              
                      // point to the custom bottom (relationship) panel view class
                      bottomView: "custom:views/system-client/record/detail-bottom",
              
                      setup: function() {
                          // invoke the original method from the extend base view class
                          Dep.prototype.setup.call(this);
                           // render the appropriate bottom panels for the existing record
                          this.toggleBottomPanel();
                          // add code to switch the bottom panels based on the value of the control field
                          this.listenTo(this.model, "change:clientType", function() {
                              this.toggleBottomPanel();
                          }, this);
                      },
              
                      // define a custom method to create the bottom (relationship) panel view instance
                      createBottomView: function () {
                          var el = this.options.el || '#' + (this.id);
                          this.createView('bottom', this.bottomView, {
                              model: this.model,
                              scope: this.scope,
                              el: el + ' .bottom',
                              readOnly: this.readOnly,
                              type: this.type,
                              inlineEditDisabled: this.inlineEditDisabled,
                              recordHelper: this.recordHelper,
                              recordViewObject: this,
                              portalLayoutDisabled: this.portalLayoutDisabled,
                              layoutData: this.layoutData
                          }, function(bottomView){
                              bottomView.render();
                          });
                      },
              
                      toggleBottomPanel: function () {
                          const clientType = this.model.get("clientType");
                          if(clientType === "Consumer") {
                              this.layoutData = this.consumerBottomPanelsDetail;
                          } else if (clientType === "Institution") {
                              this.layoutData = this.institutionBottomPanelsDetail;
                          } else if (clientType === "Auditor") {
                              this.layoutData = this.auditorBottomPanelsDetail;
                          }
                          this.createBottomView();
                      }
              
                  });
              });
              2) Create custom bottom section view class client/custom/src/views/system-client/record/detail-bottom.js
              Code:
              define('custom:views/system-client/record/detail-bottom', 'views/record/detail-bottom', function (Dep) {
              
                  return Dep.extend({
              
                      // function adapted from views/record/detail-bottom
                      setup: function () {
              
                          this.type = this.mode;
              
                          if ('type' in this.options) {
                              this.type = this.options.type;
                          }
              
                          this.panelList = [];
              
                          this.setupPanels();
              
                          this.layoutData = this.options.layoutData;
              
                          let panelNameList = [];
              
                          this.panelList = this.panelList.filter((p) => {
                              panelNameList.push(p.name);
              
                              if (p.aclScope) {
                                  if (!this.getAcl().checkScope(p.aclScope)) {
                                      return;
                                  }
                              }
              
                              if (p.accessDataList) {
                                  if (!Espo.Utils.checkAccessDataList(p.accessDataList, this.getAcl(), this.getUser())) {
                                      return false;
                                  }
                              }
              
                              return true;
                          });
              
                          if (this.relationshipPanels) {
                              let linkDefs = (this.model.defs || {}).links || {};
                              if (this.layoutData) {
                                  for (const name in this.layoutData) {
                                      if (!linkDefs[name]) {
                                          continue;
                                      }
              
                                      let p = this.layoutData[name];
              
                                      if (!~panelNameList.indexOf(name) && !p.disbled) {
                                          this.addRelationshipPanel(name, p);
                                      }
                                  }
                              }
                          }
              
                          this.panelList = this.panelList.map((p) => {
                              let item = Espo.Utils.clone(p);
              
                              if (this.recordHelper.getPanelStateParam(p.name, 'hidden') !== null) {
                                  item.hidden = this.recordHelper.getPanelStateParam(p.name, 'hidden');
                              }
                              else {
                                  this.recordHelper.setPanelStateParam(p.name, 'hidden', item.hidden || false);
                              }
                              return item;
                          });
              
                          this.panelList.forEach((item) => {
                              item.actionsViewKey = item.name + 'Actions';
                          });
              
                          this.alterPanels();
              
                          this.setupPanelsFinal();
              
                          this.setupPanelViews();
              
                      }
              
                  });
              });
              3) Create custom clientDefs json definition custom/Espo/Custom/Resources/metadata/clientDefs/Test3.json
              Code:
              {
                  "controller": "controllers/record",
                  "recordViews": {
                      "detail": "custom:views/system-client/record/detail"
                  },
                  "boolFilterList": [
                      "onlyMy"
                  ]
              }
              4) Clear cache and rebuild.
              Last edited by telecastg; 01-23-2022, 05:03 PM.

              Comment


              • #23
                How is data persisted in the database when I click the "Save" button in an entity edit view ?

                Click image for larger version  Name:	Screenshot 2022-01-27 143308.png Views:	0 Size:	73.0 KB ID:	78528

                Clicking the "Save" button above triggers the following workflow:
                L Script Method Description
                0 client/src/views/record/detail.js actionSave() Invokes action save()
                1 client/src/views/record/base.js save() Invokes action fetch()
                2 client/src/views/record/base.js fetch() Requests the "view" class for each field in the form
                3 client/src/views/record/detail.js getFieldViews() Returns the "view" class for all fields in all sections in the form
                4 client/src/views/record/detail-middle.js getFieldViews() Returns the "view" classes for the fields in the "middle" section
                4 client/src/views/record/panels/side.js getFieldViews() Returns the "view" classes for the fields in the "side" section
                4 client/src/views/record/panels/bottom.js getFieldViews() Returns the "view" classes for the fields in the "bottom" section
                2 client/src/views/record/base.js fetch() Extracts the data from each field and returns as a "setAttribbutes" object
                1 client/src/views/record/base.js save() Invokes action set()
                2 client/src/model.js set() updates the model attribute values
                1 client/src/views/record/base.js save() Invokes action save()
                2 Backbone.model save() Invokes action sync()
                3 Backbone sync() uses jQuery.ajax to make a RESTful JSON request and returns a jqXHR
                4 application\Espo\Core\Controllers\RecordBase.php postActionCreate() If its a new record, Invokes method create() at the entity's back end Service class
                4 application\Espo\Core\Controllers\RecordBase.php putActionUpdate() If its an update, invokes method update() at the entity's back end Service class
                Last edited by telecastg; 01-29-2022, 09:42 PM.

                Comment


                • #24
                  How to transpose columns to rows in a list view ?

                  Based on a question posted by rem4332 and a hint provided by shalmaxb this is how to transpose columns and rows in a table view:

                  Click image for larger version  Name:	Transpose View Initial.jpg Views:	0 Size:	64.7 KB ID:	78724

                  Click image for larger version  Name:	Transpose View Menu.jpg Views:	0 Size:	72.3 KB ID:	78725
                  Click image for larger version  Name:	Transpose View Transposed.jpg Views:	0 Size:	64.0 KB ID:	78726

                  1) Create a custom list view class to render the target entity's list display: (In this example the entity is ServiceTech)

                  client/custom/src/views/service-tech/record/list.js
                  Code:
                  define('custom:views/service-tech/record/list', 'views/record/list', function (Dep) {
                  
                  return Dep.extend({
                  
                  dropdownItemList: [
                  {
                  name: 'transposeColumnsToRows',
                  label: 'Transpose Columns to Rows'
                  },
                  {
                  name: 'resetTransposition',
                  label: 'Reset Transposition'
                  }
                  ],
                  
                  actionTransposeColumnsToRows: function () {
                  $('tr.list-row').css("display","block");
                  $('tr.list-row').css("float","left");
                  $('td.cell').css("display","block");
                  $('th').css("display","none");
                  },
                  
                  actionResetTransposition: function () {
                  $('tr.list-row').css("display","");
                  $('tr.list-row').css("float","");
                  $('td.cell').css("display","");
                  $('th').css("display","");
                  }
                  
                  });
                  
                  });
                  2) Create custom clientDefs file to let Espo know that the above view class should be invoked when rendering a list of service Techs

                  custom/Espo/Custom/Resources/metadata/clientDefs/ServiceTech.json
                  Code:
                  {
                  "recordViews": {
                  "list": "custom:views/service-tech/record/list"
                  }
                  }
                  3) Clear cache and rebuild
                  Last edited by telecastg; 03-13-2022, 07:48 PM.

                  Comment


                  • telecastg
                    telecastg commented
                    Editing a comment
                    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.

                  • Athensmusic
                    Athensmusic commented
                    Editing a comment
                    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
                    telecastg commented
                    Editing a comment
                    Thanks for pointing it out Athensmusic you are right, I corrected the posting

                • #25
                  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:

                  Click image for larger version

Name:	Screenshot 2022-02-15 145539.png
Views:	1098
Size:	46.6 KB
ID:	78773

                  Click image for larger version

Name:	Screenshot 2022-02-15 150128.png
Views:	787
Size:	44.9 KB
ID:	78774

                  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
                  Code:
                  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
                  Code:
                  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
                  Code:
                  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
                  Code:
                  {
                      "views": {
                          "list": "custom:views/service-tech/header/list"
                      },
                      "recordViews": {
                          "transpose": "custom:views/service-tech/record/list"
                      },
                      "listViewModeList" : ["list","transpose"]
                  }
                  5) Clear cache and rebuild.

                  Comment


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

                    Comment


                  • #27
                    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)
                    Code:
                    {
                        "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
                    Code:
                    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
                    Code:
                    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
                    PHP Code:
                    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
                    PHP Code:
                    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
                    Last edited by telecastg; 04-07-2022, 06:57 AM.

                    Comment


                    • yuri
                      yuri commented
                      Editing a comment
                      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.

                    • telecastg
                      telecastg commented
                      Editing a comment
                      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 !
                      Last edited by telecastg; 04-22-2022, 10:18 PM.

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

                    Comment


                    • telecastg
                      telecastg commented
                      Editing a comment
                      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
                  Working...
                  X