Announcement

Collapse
No announcement yet.

Tutorial - Control values of a multiple link depending on another multiple link

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

  • Tutorial - Control values of a multiple link depending on another multiple link

    THIS TUTORIAL IS ONLY APPLICABLE TO ESPO VERSIONS BEFORE 6

    Introduction


    This tutorial describes the steps necessary to control the list of options displayed for a multiple link field (used when entities are related in a many-to-many relationship) based on the existing options selected for another multiple link field.

    Please note that while adapting this tutorial to your application should not require extensive coding, it is not an installable extension at this point. It does require to create the front-end and back-end scripts described here, create/ modify metadata json files, and requires a basic understanding of creating entities and relationships (through the Admin panel is OK), basic javascript and PHP coding knowledge, and basic familiarity with Espo's architecture and database structure.

    This tutorial is not suitable for users who might prefer not to do any customization beyond what is already available via the Administration UI.

    Use case description

    In this example, dealing with a property rental business, an entity called "WorkOrder" is used to control the execution of maintenance work required to satisfy a customer request.

    When a "WorkOrder" is received, a Maintenance Coordinator determines the "Skill" (another entity) or list of skills required to complete the work (represented in a field "skills" of the "WorkOrder").

    After those skills are selected from a list of skills that the organization has defined, then the Maintenance Coordinator needs choose from a list of service technicians ("ServiceTech" entity), one or more that posses one or more of the required skills, and assign those technicians to the work order.

    For example: If a work order requires the skills "plumbing" and "painting", only those service technicians that posses plumbing or painting skills will be listed for the user to choose from.

    The above entities and relationships can be defined in the Admin > Entity Manager panel are as follows:

    "WorkOrder", linked to "Skill" in a many-to-many relationship

    "Skill", linked to "ServiceTech" in a many-to-many relationship

    When you create the above entities and relationships, Espo generates the necessary tables and the database schema will look like the diagram below:
    Please note the labels in red color assigned to each table in the image above, these are names that we will use to refer to each table further in this tutorial.

    Step 1
    After the appropriate entities and relationships have been created, open the script custom/Espo/Custom/Resources/metadata/entityDefs/WorkOrder.json (generated by Espo when you created the entities and relationships described above), look for the section "fields" sub-section "serviceTechs" and add the line "view": "custom:views/fields/link-multiple-conditional" as shown below.

    This line will tell Espo to use the custom class client/custom/src/views/fields/link-multiple-conditional.js (shown in Step 3 below) to render the field "serviceTechs" when displaying the "WorkOrder" record in detail view.

    Code:
    "fields": {
    
        // there might be some other code here describing other fields
    
        "serviceTechs": {
            "type": "linkMultiple",
    [COLOR=#f39c12][B]"view": "custom:views/fields/link-multiple-conditional",[/B][/COLOR]
            "layoutDetailDisabled": false,
            "layoutMassUpdateDisabled": false,
            "importDisabled": false,
            "noLoad": false,
            "isCustom": true
        }
    
        // there might be some other code here describing other fields
    }
    Step 2
    Open the script custom/Espo/Custom/Resources/metadata/clientDefs/ServiceTech.json. (This script should have also been generated by Espo when you created the entities and relationships described above) and add the code below:
    Code:
    {
        // there might be some other code here describing other client definitions
    
        "modalViews" : {
    [B][COLOR=#f39c12]"select": "custom:views/modals/select-records-filtered"[/COLOR][/B]
        },
    
        "linkFilter" : {
            "[B][COLOR=#c0392b]modelTable[/COLOR][/B]": "work_order",
            "[COLOR=#c0392b][B]modelCriteriaTable[/B][/COLOR]": "work_order_skill",
            "[COLOR=#c0392b][B]criteriaTable[/B][/COLOR]": "skill",
            "[COLOR=#c0392b][B]criteriaTargetTable[/B][/COLOR]": "skill_service_tech",
            "[COLOR=#c0392b][B]targetTable[/B][/COLOR]": "service_tech",
            "[COLOR=#c0392b][B]modelKey[/B][/COLOR]": "id",
            "[COLOR=#c0392b][B]modelReferenceKey[/B][/COLOR]": "work_order_id",
            "[COLOR=#c0392b][B]criteriaKey[/B][/COLOR]": "id",
            "[COLOR=#c0392b][B]criteriaReferenceKey[/B][/COLOR]": "skill_id",
            "[COLOR=#c0392b][B]targetKey[/B][/COLOR]": "id",
            "[COLOR=#c0392b][B]targetReferenceKey[/B][/COLOR]": "service_tech_id"
        }
    }
    This code is telling Espo two things:

    a) To use a custom view class client/custom/src/views/modals/select-records-filtered.js (shown in Step 4 below) when rendering a list of "Service Techs" to choose from in a modal view that will open when the user wants to add a Service tech to the Work Order entity.

    b) To use the specifications under the section "linkFilter" to build the SQL statement that will filter the list of Service Techs available depending on the skill or skills required by the Work Order. Notice how the schema red labels used in the image above are used in this section to define the actual names and fields of the database tables.

    Step 3
    Create a custom field class client/custom/src/views/fields/link-multiple-conditional.js using the code below:
    Code:
    Espo.define('custom:views/fields/link-multiple-conditional', 'views/fields/link-multiple', function (Dep) {
    
        return Dep.extend({
    
            setup: function() {
                // console.log("custom link-multiple-conditional this.model = ",this.model);
                this.nameHashName = this.name + 'Names';
                this.idsName = this.name + 'Ids';
                this.foreignScope = this.options.foreignScope || this.foreignScope || this.model.getFieldParam(this.name, 'entity') || this.model.getLinkParam(this.name, 'entity');
                if ('createDisabled' in this.options) {
                    this.createDisabled = this.options.createDisabled;
                }
                var self = this;
                if (this.mode == 'search') {
                    var nameHash = this.getSearchParamsData().nameHash || this.searchParams.nameHash || {};
                    var idList = this.getSearchParamsData().idList || this.searchParams.value || [];
                    this.nameHash = Espo.Utils.clone(nameHash);
                    this.ids = Espo.Utils.clone(idList);
                } else {
                    this.copyValuesFromModel();
                }
                this.listenTo(this.model, 'change:' + this.idsName, function () {
                    this.copyValuesFromModel();
                }, this);
                this.sortable = this.sortable || this.params.sortable;
                this.iconHtml = this.getHelper().getScopeColorIconHtml(this.foreig nScope);
                if (this.mode !== 'list') {
                    this.addActionHandler('selectLink', function () {
                         // display modal list of entities that can be linked
                        self.notify('Loading...');
                        var viewName = this.getMetadata().get('clientDefs.' + this.foreignScope + '.modalViews.select') || this.selectRecordsView;
                        this.createView('dialog', viewName, {
                            scope: this.foreignScope,
                            parentId : this.model.id,
                            createButton: !this.createDisabled && this.mode !== 'search',
                            filters: this.getSelectFilters(),
                            boolFilterList: this.getSelectBoolFilterList(),
                            primaryFilterName: this.getSelectPrimaryFilterName(),
                            multiple: true,
                            createAttributes: (this.mode === 'edit') ? this.getCreateAttributes() : null,
                            mandatorySelectAttributeList: this.mandatorySelectAttributeList,
                            forceSelectAllAttributes: this.forceSelectAllAttributes
                        }, function (dialog) {
                            dialog.render();
                            self.notify(false);
                            this.listenToOnce(dialog, 'select', function (models) {
                                this.clearView('dialog');
                                if (Object.prototype.toString.call(models) !== '[object Array]') {
                                    models = [models];
                                }
                                models.forEach(function (model) {
                                    self.addLink(model.id, model.get('name'));
                                });
                            });
                        }, this);
                    });
                    this.events['click a[data-action="clearLink"]'] = function (e) {
                        var id = $(e.currentTarget).attr('data-id');
                        this.deleteLink(id);
                    };
                }
            }
    
        });
    });
    Note the line // console.log("custom link-multiple-conditional this.model = ",this.model); in the script above. This will display a message in the browser's console if un-commented to help with trouble shooting. If displayed it will signal that the script is being called properly so the code entered in Step 1 is working fine.

    Because of the 10,000 character limit for postings in this forum, this tutorial will continue in Part 2 https://forum.espocrm.com/forum/deve...-multiple-link and Part 3 https://forum.espocrm.com/forum/deve...p/60642-part-3
    Last edited by telecastg; 01-18-2023, 07:09 AM.

  • #2
    Wouldn't it be better to just triple post telecastg ? Splitting in 3 threads might force you to run into 3 thread to reply in the future. I delete this post if you plan to triple post.

    Comment


    • telecastg
      telecastg commented
      Editing a comment
      That's a good idea, thanks, will do next time to avoid confusion for users that have seen the other threads.
      Last edited by telecastg; 07-20-2020, 06:30 PM.

    • tothewine
      tothewine commented
      Editing a comment
      you could also create a new thred and modify the others to point to it

  • #3
    Hi telecastg, Thank you so much for posting this solution. I know you must have spent a while on it. I attempted to follow the post and was unsuccessful in getting it to work. I created the entities and correct relationships, but every time I run the Work Orders entity, it breaks. Weirdly, it also breaks my menu logo.
    I tried it on two different systems and had the same result. I think this is an amazing upgrade and the way that you are using it is exactly the way what I need to do. I was wondering if it is possible to do this with users in edit (small-list) view? It would be super cool to be able to have some logic for when someone clicks edit in the entity (in list view) and then the filter only shows the techs that have the related skills. I know that this is taking allot of your time and I appreciate it. If you can assist me, I would be willing to hire you to get this to work. I am in California. Thanks again and have a great evening,

    Comment


    • espcrm
      espcrm commented
      Editing a comment
      Might be stupid of me to ask, but did you follow the guide in all 3 threads/posts? I haven't tried this yet so cannot provide too much insight.

  • #4
    great! I always wondered how to implement such a thing

    Comment


    • #5
      Hi Redwood

      If the Work Order detail display is not working initially at all (you select a record in list view and the detail display fails), first I suggest that you open the browser console (right click on the browser, click "inspect" and then click on the tab "console") and see if there are any errors being thrown.

      If no error messages appear, then try the following trouble shooting actions:

      1.1 Uncomment the line // console.log("custom link-multiple-conditional this.model = ",this.model); in the script client/custom/src/views/fields/link-multiple-conditional.js
      1.2 save the script
      1.3 clear cache and rebuild the Espo instance
      1.4 open the browser console if necessary
      1.5 open the WorkOrder record in detail view.

      The message "custom link-multiple-conditions this.model = " (and the contents of the WorkOrder model object) should appear in the console.

      If it does, it means that the custom multi-link field is being properly called, so the WorkOrder entityDefs metadata file (Step 1) is OK.

      If it doesn't then you need to go back and double check WorkOrder entityDefs and check for possible mispellings to make sure that you are pointing to an existing script (most likely) or that you don't have the permissions in your server to access the script.

      If the breakdown occurs when you are editing the field ServiceTech and you click the down chevron icon ^ to select a tech, then do the following:

      2.1 Uncomment line // console.log("select-records-filteres.js params = ",params); at client/custom/src/views/modals/select-records-filtered.js
      2.2 save the scripit
      2.3 clear cache and rebuild Espo
      2.4 open the browser console if necessary
      2.5 open the WorkOrder record in detail view
      2.6 edit the field "ServiceTech" and click the down chevron icon to select service tech entity.

      The message "select-records-filteres.js params = " and a list of values that should correspond to the values saved in the metadata file custom/Espo/Custom/Resources/metadata/clientDefs/ServiceTech.json under the section "linkFilter" should be displayed, if not it means that the script client/custom/src/views/modals/select-records-filtered.js is not being requested from the script client/custom/src/views/fields/link-multiple-conditional.js , check both files to make sure that their contents match the tutorial example.

      Hopefully this will steer you in the right direction.

      In regards to your idea of the ServiceTech being automatically limited to those matching a set of skills when a WorkOrder is request in the list view, this is already happening. The implementation performs the filtering when you try to select an additional ServiceTech.

      Comment


      • #7
        Actually, if you can tell me what the relationship is for WorkOrder to Service Techs, I think that is where I am getting messed up. My workorder JSON does not have serviceTechs field.

        Comment


        • #8
          The schema original is attached at the bottom of this post, hopefully it can be seen.

          It didn't include the relationship between WorkOrder and ServiceTech to keep the diagram simple and I thought that I had communicated that the WorkOrder could have many ServiceTechs working on it, but I realize that this might have been confusing, sorry.

          To be clear, the relationship between WorkOrder and ServiceTech is many-to-many realtionship. A WorkOrder can have one or many ServiceTechs linked to it and a ServiceTech can be linked to one or many WorkOrders.

          Here is the revised database schema diagram:
          Click image for larger version  Name:	MultiLink to Multilink Schema.jpg Views:	0 Size:	71.0 KB ID:	60881

          When you create these entities and this relationship through the Admin GUI, Espo will create 3 tables in the dabase:
          a) work_order table
          b) service_tech table
          c) service_tech_work_order table linking the two entities

          After creating the above links, you will need to edit the WorkOrder entityDefs JSON to specify that the "serviceTechs" field requires the "custom:views/fields/link-multiple-conditional" for rendering.

          custom/Espo/Custom/Resources/metadata/WorkOrder.json
          Code:
          {
              "fields": {
              "serviceTechs": {
                  "type": "linkMultiple",
                  "view": "custom:views/fields/link-multiple-conditional",
                  "layoutDetailDisabled": false,
                  "layoutMassUpdateDisabled": false,
                  "importDisabled": false,
                  "noLoad": false,
                  "isCustom": true
              },
          
              // other field definitions
          
              },
              "links": {
                  "serviceTechs": {
                  "type": "hasMany",
                  "entity": "ServiceTech",
                  "foreign": "workOrders",
                  "audited": false
              },
          
              // other link definitions    
          }
          One last word of caution, please check your database to confirm that the relationship tables names match the names specified for them in Step 2 and if necessary modify the names in Step 2 to match the actual table names.
          Last edited by telecastg; 07-25-2020, 06:33 AM.

          Comment


          • #9
            Hello, On line 26 of the above step 3 I believe has an error. This is a question? should this line be
            this.iconHtml = this.getHelper().getScopeColorIconHtml(this.foreig nScope);

            In the above snippet it is

            this.iconHtml = this.getHelper().getScopeColorIconHtml(this.foreig nScope);

            Thank you for the diagram. that helped allot. I am still debugging but very determined to get this to work.

            Have a great weekend,

            Comment


            • #10
              Hi,

              Originally posted by Redwood View Post
              On line 26 of the above step 3 I believe has an error. This is a question? should this line be
              this.iconHtml = this.getHelper().getScopeColorIconHtml(this.foreig nScope);

              In the above snippet it is

              this.iconHtml = this.getHelper().getScopeColorIconHtml(this.foreig nScope);
              The extra space between "this.foreignScope" and "this.foreig nScope" is a bug in the forums markup, it should read this.foreignScope

              I suggest that you try un-commenting the mentioned "console.log" lines as recommended to see if the program flow is working properly in the front end and the correct parameters are being passed.

              If you think that there is a back-end issue, you can insert some debugging code in the PHP scripts to check the program flow too. For more about debugging PHP see this post. https://forum.espocrm.com/forum/deve...gging-php-code

              Have a great weekend too.

              Comment


              • #11
                Hi Telecastg, I am getting an error on select-records-filtered. Any pointers would be great. I really appreciate your help. Console error below.
                at XMLHttpRequest.xmlhttp.onreadystatechange (eval at _execute (loader.js:94), <anonymous>:102:47)

                Comment


                • telecastg
                  telecastg commented
                  Editing a comment
                  Un-comment the line // console.log("select-records-filteres.js params = ",params); in select-records-filtered.js, rebuild Espo and see the contents of the "params" object in the console.

                  The values for the properties "modelTable", "modelCriteriaTable", "criteriaTable", etc MUST match the actual names of your tables and fields in your database. (See the original diagram to see what those labels correspond to) Check the database with PHPAdmin or similar tool to confirm that the correct names for tables and fields are stored in the "params" object.

                  If not, go back to custom/Espo/Custom/Resources/metadata/clientDefs/ServiceTech.json and make the necessary adjustments to the section "linkFilter"
                  Last edited by telecastg; 07-25-2020, 06:28 AM.

              • #12
                Following your suggestion, IT looks like I am getting the right fields. Here is the output.
                select-records-filteres.js params = {action: "getOptionListFilter", modelKeyValue: "5f1aa7367f1f5a452", modelTable: "work_order", modelCriteriaTable: "work_order_skill", criteriaTable: "skill", …}
                VM460:1 Uncaught SyntaxError: Unexpected end of JSON input
                at JSON.parse (<anonymous>)
                at XMLHttpRequest.xmlhttp.onreadystatechange (eval at _execute (loader.js:94), <anonymous>:102:47)
                xmlhttp.onreadystatechange @ VM457:102
                XMLHttpRequest.send (async)
                setup @ VM457:127
                initialize @ bull.js:276
                Backbone.View @ backbone-min.js:1224
                child @ backbone-min.js:1884
                child @ backbone-min.js:1884
                child @ backbone-min.js:1884
                child @ backbone-min.js:1884
                child @ backbone-min.js:1884
                (anonymous) @ bull.js:114
                (anonymous) @ bull.js:98
                (anonymous) @ loader.js:100
                _executeLoadCallback @ loader.js:99
                proceed @ loader.js:126
                (anonymous) @ loader.js:133
                (anonymous) @ loader.js:169
                (anonymous) @ loader.js:100
                _executeLoadCallback @ loader.js:99
                proceed @ loader.js:126
                (anonymous) @ loader.js:133
                (anonymous) @ loader.js:169
                (anonymous) @ loader.js:100
                _executeLoadCallback @ loader.js:99
                proceed @ loader.js:126
                (anonymous) @ loader.js:133
                load @ loader.js:268
                require @ loader.js:154
                define @ loader.js:132
                root.define.Espo.define @ loader.js:440
                eval @ VM459:29
                _execute @ loader.js:94
                (anonymous) @ loader.js:341
                j @ jquery-2.1.4.min.js:2
                fireWith @ jquery-2.1.4.min.js:2
                x @ jquery-2.1.4.min.js:4
                (anonymous) @ jquery-2.1.4.min.js:4
                load (async)
                send @ jquery-2.1.4.min.js:4
                ajax @ jquery-2.1.4.min.js:4
                load @ loader.js:322
                (anonymous) @ loader.js:161
                require @ loader.js:160
                define @ loader.js:132
                root.define.Espo.define @ loader.js:440
                eval @ VM458:29
                _execute @ loader.js:94
                (anonymous) @ loader.js:341
                j @ jquery-2.1.4.min.js:2
                fireWith @ jquery-2.1.4.min.js:2
                x @ jquery-2.1.4.min.js:4
                (anonymous) @ jquery-2.1.4.min.js:4
                load (async)
                send @ jquery-2.1.4.min.js:4
                ajax @ jquery-2.1.4.min.js:4
                load @ loader.js:322
                (anonymous) @ loader.js:161
                require @ loader.js:160
                define @ loader.js:132
                root.define.Espo.define @ loader.js:440
                eval @ VM457:1
                _execute @ loader.js:94
                (anonymous) @ loader.js:341
                j @ jquery-2.1.4.min.js:2
                fireWith @ jquery-2.1.4.min.js:2
                x @ jquery-2.1.4.min.js:4
                (anonymous) @ jquery-2.1.4.min.js:4
                load (async)
                send @ jquery-2.1.4.min.js:4
                ajax @ jquery-2.1.4.min.js:4
                load @ loader.js:322
                require @ loader.js:154
                root.require.Espo.require @ loader.js:423
                eval @ VM198:430
                _getViewClass @ bull.js:96
                create @ bull.js:110
                (anonymous) @ bull.js:843
                createView @ bull.js:828
                eval @ VM284:32
                dispatch @ jquery-2.1.4.min.js:3
                r.handle @ jquery-2.1.4.min.js:3
                Show 6 more frames

                Comment


                • #13
                  It looks like I am getting a loader.Could not load content for http://localhost/client/src/loader.js (HTTP error: status code 404, net::ERR_HTTP_RESPONSE_CODE_FAILURE)http://localhost/client/src/loader.js Not sure what the problem is but if you have any ideas. It would be greatly appreciated. Thank you again!!

                  Comment


                  • #14
                    Sorry I should have added that it is happening when I attempt to choose a service_tech

                    Comment


                    • #15
                      It looks like the data received by the ajax call from the entry point is not a properly formatted json string.

                      Insert this line: console.log("select-reocrds-filtered.js ajax response = ",xmlhttp.responseText); in client/custom/src/views/modals/select-records-filtered.js right in between lines:
                      if (xmlhttp.status === 200) { and var filterList = JSON.parse(xmlhttp.responseText); like this:

                      if (xmlhttp.status === 200) {
                      console.log("select-reocrds-filtered.js ajax response = ",xmlhttp.responseText);
                      var filterList = JSON.parse(xmlhttp.responseText);
                      .....


                      Then check the console to read what custom/Espo/Custom/EntryPoints/ConditionalLinkMultiple.php is returning (most likely and error message) which is why the error VM460:1 Uncaught SyntaxError: Unexpected end of JSON input is being thrown off.

                      Additionally un-comment lines:
                      // $GLOBALS['log']->debug('ConditionalLinkMultiple Entry Point payload:', [$payload]);
                      // $GLOBALS['log']->debug('ConditionalLinkMultiple Entry Point sql:', [$sql]);
                      at custom/Espo/Custom/EntryPoints/ConditionalLinkMultiple.php and turn on the debugging mode in config.php (see this post https://forum.espocrm.com/forum/deve...gging-php-code to see how this is done) and check Espo's backend error log at data/logs/{today's log}

                      Don't forget to rebuild after these changes

                      The first log entry will confirm what input is being received by the entryPoint (which should be the same as the one shown in the console) and the second line will print the actual SQL command that fetches the list of serviceTech id's.

                      You can test this statement by copy and paste to execute in phpAdmin.

                      The log will also display any other errors so you can check and investigate further if necessary.
                      Last edited by telecastg; 07-25-2020, 07:28 PM.

                      Comment

                      Working...
                      X