Part 3 - Control values of a multiple link depending on another multiple link

[telecastg]

This is the final part of the tutorial to Control values of a multiple link depending on the values of another multiple link, you can see Parts 1 and 2 here:
https://forum.espocrm.com/forum/deve...-multiple-link
https://forum.espocrm.com/forum/deve...-multiple-link

Step 5
Create the back-end entry point class custom/Espo/Custom/EntryPoints/ConditionalLinkMultiple.php that will receive the ajax call specified in the code described in Step 4 of this tutorial https://forum.espocrm.com/forum/deve...-multiple-link, will execute an sql command on the database and will return a list of entity ids to use as the filter for displaying the list of available options for the multi link field.

namespace Espo\Custom\EntryPoints;

use \Espo\Core\Exceptions\NotFound;
use \Espo\Core\Exceptions\Forbidden;
use \Espo\Core\Exceptions\BadRequest;


class ConditionalLinkMultiple extends \Espo\Core\EntryPoints\Base
{
    public static $authRequired = true;

    // default action
    public function run()
    {
        // convert the POST JSON input received into a PHP associative array
        $requestPayload = file_get_contents("php://input");
        $payload = json_decode($requestPayload, true);
        // $GLOBALS['log']->debug('ConditionalLinkMultiple Entry Point payload:', [$payload]);
        $action = $payload["action"];

        if(!$action) {
            throw new BadRequest('ConditionalLinkMultiple.php "action" parameter is missing');
        }

        switch ($action) {
            case 'getOptionListFilter':
                // build the sql command from inputs received
                $modelKeyValue = $payload['modelKeyValue'];
                $modelTable = $payload['modelTable'];
                $modelCriteriaTable = $payload['modelCriteriaTable'];
                $criteriaTable = $payload['criteriaTable'];
                $criteriaTargetTable = $payload['criteriaTargetTable'];
                $targetTable = $payload['targetTable'];
                $modelKey = $payload['modelKey'];
                $modelReferenceKey = $payload['modelReferenceKey'];
                $criteriaKey = $payload['criteriaKey'];
                $criteriaReferenceKey = $payload['criteriaReferenceKey'];
                $targetReferenceKey = $payload['targetReferenceKey'];
                $targetKey = $payload['targetKey'];
                $sql = 'SELECT '.$targetTable.'.'.$targetKey;
                $sql.= ' FROM '.$targetTable;
                $sql.= ' INNER JOIN '.$criteriaTargetTable.' ON '.$targetTable.'.'.$targetKey.' = '.$criteriaTargetTable.'.'.$targetReferenceKey;
                $sql.= ' INNER JOIN '.$criteriaTable.' ON '.$criteriaTable.'.'.$criteriaKey.' = '.$criteriaTargetTable.'.'.$criteriaReferenceKey;
                $sql.= ' INNER JOIN '.$modelCriteriaTable. ' ON '.$criteriaTable.'.'.$criteriaKey.' = '.$modelCriteriaTable.'.'.$criteriaReferenceKey;
                $sql.= ' INNER JOIN '.$modelTable.' ON '.$modelTable.'.'.$modelKey.' = '.$modelCriteriaTable.'.'.$modelReferenceKey;
                $sql.= ' WHERE '.$modelTable.'.'.$modelKey.' = "'.$modelKeyValue.'"';
                $sql.= ' GROUP BY '.$targetTable.'.'.$targetKey;
                // $GLOBALS['log']->debug('ConditionalLinkMultiple Entry Point sql:', [$sql]);
                // execute the sql command
                $pdo = $this->getEntityManager()->getPDO();
                $data = $pdo->query($sql)->fetchAll();
                $response = [];
                foreach($data as $row){
                    $response[] = $row['id'];
                }
                // return data set
                echo(json_encode($response));
            break;

            default:
                //code to be executed if $action is different from all labels;
        }
    }

} 

Notice that this script also contains 2 commented statements that can be used for trouble shooting to make sure that the data passed by the ajax call is correct and that the final sql statement is correct.

For more on debugging PHP code see this post https://forum.espocrm.com/forum/deve...gging-php-code

Step 6

The final step is to clear cache and rebuild the Espo instance

This tutorial does not cover the layout specifications for the main ("Work Order") entity, which will depend on your preferences, but in our case the modified Work Order entity detail display looks like this:

[Redwood]

OMG, IT WORKS. Telecastg. THANK YOU SO MUCH. I found an error in the PHP at custom/Espo/Custom/EntryPoints/ConditionalLinkMultiple.php. It had one extra closing bracket and that was the problem) (line 67). It is working great now!!!

This is the most useful tutorial on this site and I hope the moderators include the ability in future versions.
THANK YOU AGAIN!!

[telecastg]

You're very welcome :-) and thank you so much for pointing out the error, I edited the script to remove the extra closing bracket

[Redwood]

Hi Telecastg, I have used your solution to filter users. The question I have is where could you put in a if-else statement to only call the (in my case) Users filter when in Workorder? As it is now, users cannot be assigned to for example meetings or calls. If there is not practical way to put in a If then filter, is there a way to sync two entities (for example Service Techs and Users) ? If so I can move the filter back into Service Techs and the the problem would be solved.

I want to use the users entity because it would be difficult to use the scheduler and impossible for users to be able to edit their skills.

I hope there is a way to call the filter when someone is only in the Workorder and not in any other part.

Let me know your thoughts on this and again, THANK YOU!!

[telecastg]

Try changing the following code in client/custom/src/views/fields/link-multiple-conditional.js :

FROM:

var viewName = this.getMetadata().get('clientDefs.' + this.foreignScope + '.modalViews.select')  || this.selectRecordsView;

TO:

if(this.model.urlRoot === '[B][COLOR=#c0392b]WorkOrder[/COLOR][/B]') {
    var viewName = this.getMetadata().get('clientDefs.' + this.foreignScope + '.modalViews.select') || this.selectRecordsView;
} else {
    var viewName = 'views/modals/select-records';
}

By doing this you are telling Espo to use the default view ("views/modals/select-records") to render a list of ServiceTechs (or Users in your case) instead of the custom view specified in in ServiceTech (or User) clientDefs metadata file.

Please note that you are hard coding the name of the entity where the special filtering applies ("WorkOrder"). If you decide to use a different name you will need to hard code the actual entity class name in the script.

[Redwood]

I tried your solution, it did not allow other entities to see the users. I think because I had to put the filter in the entity def, it over rides the standard view. I really appreciate all of your help with this. I think once it works, it is going to be great though. If you have any other ideas, please let me know. AND THANK YOU!!

[telecastg]

I suggest that you stick with the "ServiceTech" or other 100% custom entity and link it to "User" in a one-to-one relationship instead of trying to customize the core User entity views or filters.

Once you do that, then all the fields in User will be available for the ServiceTech entity and all the fields in ServiceTech will be available for the User