multiple assignedUsers with column enum and primary :)

[item]

Hi,

i try to replicate multiple assignedUsers with column and primary ..
but no luck.

i need column "role" as enum + primary. (else this code have no issue and work with only varchar role field)

No one have idea how do ?

Account.json

{
    "fields": {
        "assignedUsers": {
            "type": "linkMultiple",
            "layoutDetailDisabled": false,
            "layoutMassUpdateDisabled": false,
            "layoutListDisabled": false,
            "noLoad": false,
            "importDisabled": false,
            "exportDisabled": false,
            "customizationDisabled": false,
            "isCustom": true,
            "view": "views/fields/link-multiple-with-columns",
            "columns": {
                "role": "userRole"
            },
            "columnAttributeMap": {
                "role": "userRole"
            },
            "additionalAttributeList": [
                "columns"
            ]
        }
    },
    "links": {
        "assignedUsers": {
            "type": "hasMany",
            "relationName": "accountUser",
            "foreign": "accountsAssigned",
            "entity": "User",
            "audited": false,
            "isCustom": true,
            "additionalColumns": {
                "role": {
                    "type": "varchar",
                    "len": 100
                }
            },
            "additionalAttributeList": [
                "columns"
            ],
            "layoutRelationshipsDisabled": true,
            "columnAttributeMap": {
                "role": "userRole"
            }
        }
    }
}​ 

User.json

{
    "fields": {
        "accountsAssigned": {
            "type": "linkMultiple",
            "layoutDetailDisabled": true,
            "layoutMassUpdateDisabled": true,
            "layoutListDisabled": true,
            "noLoad": true,
            "importDisabled": true,
            "exportDisabled": true,
            "customizationDisabled": true,
            "isCustom": true
        },
        "userRole": {
            "type": "varchar",
            "notStorable": true,
            "exportDisabled": true,
            "disabled": true
        }
    },
    "links": {
        "accountsAssigned": {
            "type": "hasMany",
            "relationName": "accountUser",
            "foreign": "assignedUsers",
            "entity": "Account",
            "audited": false,
            "isCustom": true,
            "additionalColumns": {
                "role": {
                    "type": "varchar",
                    "len": 100
                }
            },
            "additionalAttributeList": [
                "columns"
            ],
            "layoutRelationshipsDisabled": true,
            "columnAttributeMap": {
                "role": "userRole"
            }
        }
    }
}​ 

more info : https://docs.espocrm.com/administrat...assigned-users

[item]

Resolved

"userRole": {
    "type": "enum",
    "options": [
    "R1",
    "R2",
    "R3"
    ],
    "notStorable": true,
    "exportDisabled": true,
    "disabled": true
},​ 

For other, the problem in my case, when multiple assigned .. out-of-the-box assignedUser is not avaible on layout. (i need assignedUser, it's ours bpm best choose).
So i have change relationName .. then i can access assignedUser and assignedsUsers with column

[rabii]

this is how i could approach this just like espocrm dev team style:

Under custom entityDefs of Account.json add necessary fields and relationships:

{
"fields": {
    "assignedUsers": {
        "type": "linkMultiple",
        "view": "custom:views/account/fields/assigned-users",
        "columns": {
            "role": "accountRole"
        },
        "additionalAttributeList": ["columns"],
        "primaryLink": "user",
        "orderBy": "name"
    },
    "userRole": {
        "type": "enum",
        "notStorable": true,
        "layoutMassUpdateDisabled": true,
        "layoutDetailDisabled": true,
        "layoutAvailabilityList": ["listForUser"], // optional
        "customizationDefaultDisabled": true,
        "customizationRequiredDisabled": true,
        "customizationIsSortedDisabled": true,
        "customizationAuditedDisabled": true,
        "customizationReadOnlyDisabled": true,
        "where": {
            "=": {
                "leftJoins": ["users"],
                "whereClause": {
                    "usersMiddle.role": "{value}"
                },
                "distinct": true
            },
            "<>": {
                "whereClause": {
                    "id!=s": {
                        "from": "AccountUser",
                        "select": ["accountId"],
                        "whereClause": {
                            "deleted": 0,
                            "role": "{value}"
                        }
                    }
                }
            },
            "IN": {
                "leftJoins": ["users"],
                "whereClause": {
                    "usersMiddle.role": "{value}"
                },
                "distinct": true
            },
            "NOT IN": {
                "whereClause": {
                    "id!=s": {
                        "from": "AccountUser",
                        "select": ["accountId"],
                        "whereClause": {
                            "deleted": 0,
                            "role": "{value}"
                        }
                    }
                }
            },
            "LIKE": {
                "leftJoins": ["users"],
                "whereClause": {
                    "usersMiddle.role*": "{value}"
                },
                "distinct": true
            },
            "IS NULL": {
                "leftJoins": ["users"],
                "whereClause": {
                    "usersMiddle.role": null
                },
                "distinct": true
            },
            "IS NOT NULL": {
                "whereClause": {
                    "id!=s": {
                        "from": "AccountUser",
                        "select": ["accountId"],
                        "whereClause": {
                            "deleted": 0,
                            "role": null
                        }
                    }
                }
            }
        },
        "view": "custom:views/account/fields/user-role",
        "customizationOptionsDisabled": true
    }
},
"links": {
    "assignedUsers": {
        "type": "hasMany",
        "entity": "User",
        "foreign": "accounts",
        "additionalColumns": {
            "role": {
                "type": "varchar",
                "len": 50
            }
        },
        "columnAttributeMap": {
            "role": "userRole"
        }
    }
}
}​ 

Create custom field views:

1 - create user role field custom view:

define('custom:views/account/fields/user-role', ['views/fields/enum'], function (Dep) {

    return Dep.extend({

        searchTypeList: ['anyOf', 'noneOf'],

        setup: function () {
            this.params.options = this.getMetadata().get('entityDefs.User.fields.accountRole.options');
            this.params.translation = 'User.options.accountRole';

            Dep.prototype.setup.call(this);
        },
    });
});​ 

2 - create assigned users field custom view:

define('custom:views/opportunity/fields/assigned-users',
['views/fields/link-multiple-with-columns-with-primary'], function (Dep) {
    return Dep.extend({});
}); 

under custom entityDefs of User.json entity add necessary fields and links:

{
    "fields": {
        "accountRole": {
            "type": "enum",
            "notStorable": true,
            "options": ["", "Manager", "Editor", "validator"],
            "layoutMassUpdateDisabled": true,
            "layoutListDisabled": true,
            "layoutDetailDisabled": true,
            "customizationRequiredDisabled": true,
            "customizationIsSortedDisabled": true,
            "customizationAuditedDisabled": true,
            "customizationReadOnlyDisabled": true,
            "where": {
                "=": {
                    "whereClause": {
                        "accountsMiddle.role": "{value}"
                    },
                    "leftJoins": ["accounts"],
                    "distinct": true
                },
                "<>": {
                    "whereClause": {
                        "id!=s": {
                            "from": "AccountUser",
                            "select": ["userId"],
                            "whereClause": {
                                "deleted": false,
                                "role": "{value}"
                            }
                        }
                    }
                },
                "IN": {
                    "leftJoins": ["accounts"],
                    "whereClause": {
                        "accountsMiddle.role": "{value}"
                    },
                    "distinct": true
                },
                "NOT IN": {
                    "whereClause": {
                        "id!=s": {
                            "from": "AccountUser",
                            "select": ["userId"],
                            "whereClause": {
                                "deleted": false,
                                "role": "{value}"
                            }
                        }
                    }
                },
                "LIKE": {
                    "leftJoins": ["accounts"],
                    "whereClause": {
                        "accountsMiddle.role*": "{value}"
                    },
                    "distinct": true
                },
                "NOT LIKE": {
                    "leftJoins": ["accounts"],
                    "whereClause": {
                        "accountsMiddle.role!*": "{value}"
                    },
                    "distinct": true
                },
                "IS NULL": {
                    "leftJoins": ["accounts"],
                    "whereClause": {
                        "accountsMiddle.role": null
                    },
                    "distinct": true
                },
                "IS NOT NULL": {
                    "whereClause": {
                        "id!=s": {
                            "from": "AccountUser",
                            "select": ["userId"],
                            "whereClause": {
                                "deleted": false,
                                "role": null
                            }
                        }
                    }
                }
            },
            "view": "custom:views/user/fields/account-role"
        }
    },
    "links": {
        "accounts": {
            "type": "hasMany",
            "entity": "Account",
            "foreign": "assignedUsers",
            "columnAttributeMap": {
                "role": "accountRole"
            }
        }
    }
}​ 

finally create the account role field custom view for the user

define('custom:views/user/fields/account-role', ['views/fields/enum'], function (Dep) {

    return Dep.extend({
        searchTypeList: ['anyOf', 'noneOf'],
    });
});​ 

and that is it you have a custom field assigned users on account with role and primary functions.

Just sharing in case someone needs this in future

[esforim]

How did I missed this, reading your comments of 'feature' it look like this need to be on my Task list!

[czcpf]

This is nice work. I’ve seen stuff like this in the code base and don’t understand what this is all doing. When you have time, I would greatly appreciate a detailed description of what the fields and links described above in the entity defs. for User.json and Account.json are doing in laymen’s terms. I’m sure other developers new to espocrm would appreciate that as well. For example, is the primary goal here to be able to have a field on the GUI where you can select a link and right next to it specify other fields associated with the link? Can similar things be done with a “link field” instead of only “link-multiple” fields ?

[rabii]

yeah that is what is the purpose of this code. is to allow to choose multiple users on accounts (choose meaning linking) and at the same time be able to assign a role for each user on each account. this is a many to many relationship with ability to use primary user. this is similar to what is implemented already on the opportunity and contacts. i am planning on writing blog post on espocrm dev tips so once that is done i will share the link to a more detailed blog on how to customise espocrm

[czcpf]

That would be awesome! I want to do stuff like the above code but I don't quite understand what is happening and which db table/entity/link has the extra field (ie. role, userRole, etc.). It looks quite powerful, but unfortunately difficult to understand or how to implement in other entities.

[esforim]

Guys, with the documents it say this: https://docs.espocrm.com/administrat...ssigned-users/

Make sure that you chose link names that do not already exist in the system.

I follow the documents and create multi Assigned user for Task.

Now I want to do it for another entity, do I still use the same "assignedUsers" name or I need to use something else like "assignedUsers2".
So question is, I need to use the same name or each Entity must have different name?

[rabii]

you can still use the same name because assignedUsers will be added to the entity (Task) therefore if you want to have the same concept applied to (MyEntity) then it should work fine as long as the name doesn't exist already then it should be fine.

[esforim]

Thank rabii, I see a few thread with this error and worry. I'll give it a try and hopefully don't get same issue.

Example: https://forum.espocrm.com/forum/gene...age2#post94113