Filter a Link field by foreign variable

[joy11]

I have a custom entity which has a Many-to-One relationship with the Account Entity. My Account Entity has a custom enum of "type" where I have different kinds of accounts I am keeping track of (i.e. Vendor, Customer, Agency)

My custom entity has a link to the accounts where I'd like to select only accounts with the "type" of "Vendor". I've attached a screenshot of the select filter I'd like to have, but I'm not sure how to write it so that it works. I'm assuming it's something similar to what I'm working with already?


I have created a custom view: **This does not work.** \client\custom\src\views\currentProduct\field\vend or.js

Espo.define('custom:views/currentProduct/fields/vendor', 'views/fields/link', function (Dep) {

return Dep.extend({

getSelectFilters: function () {
return {
'account.type': {
type: 'anyOf',
value: 'Vendor',
valueName: this.model.get('accountName'),
}
};
}
});

});



I've attached the custom view in custom/Espo/Custom/Resources/metadata/entityDefs/CurrentProduct.json

{
"fields": {
"vendor": {
"type": "link",
"dynamicLogicVisible": null,
"view": "custom:views/contract/fields/product",
"required": false,
"audited": false,
"tooltip": false
}
},
"links": {
"vendor": {
"type": "belongsTo",
"foreign": "vendor",
"entity": "Account",
"audited": false,
"isCustom": true
}
}
}

[tanya]

Hello

https://stackoverflow.com/questions/44775949/edit-a-view-in-espocrm/44820526#44820526

https://github.com/espocrm/documentation/blob/master/docs/development/custom-views.md

needed path \client\custom\src\views\current-product\fields\vendor.js

definition

Espo.define('custom:views/current-product/fields/vendor', 'views/fields/link', function (Dep) {

in entityDefs field view has to be the same as in definition 'custom:views/current-product/fields/vendor'

[joy11]

I'm sorry, I posted wrong entitydefs above.

I have this in my custom/Espo/Custom/Resources/metadata/entityDefs/CurrentProducts.json :

{
"fields": {
"vendor": {
"type": "link",
"dynamicLogicVisible": null,
"view": "custom:views/currentProduct/fields/vendor",
"required": false,
"audited": false,
"tooltip": false
}
},
"links": {
"vendor": {
"type": "belongsTo",
"foreign": "vendor",
"entity": "Account",
"audited": false,
"isCustom": true
}
}
}


this does not work either. I think that my vendor.js isn't quite right? I'm not sure how to write it to only filter and display the accounts with type = "Vendor" (type is an enum added to accounts)

[tanya]

using current-product instead of currentProduct is important. Check in browser log, if your view was read or you get 404 error

[joy11]

You were right. It wasn't reading until I changed it to current-product. I am not getting any errors now and it's closer because something shows up as a filter. It still doesn't act quite like I thought it would.

I've attached a screenshot of how the filter shows up currently. My enum from accounts is called "type" with label "Account Type". I'm not sure how to reference that value. This is what I have in the vendor.js view for that field.

getSelectFilters: function () {
return {
'accountType': {
type: 'anyOf',
value: 'Vendor',
valueName: this.model.get('accountName'),
}
};
}

[joy11]

Is there a spot where I could just enter the mysql query to pull the accounts marked as vendors and have them selectable in the link drop-down? I'm trying to work within the parameters of the program, but this seems like it should be something very easy and is giving me problems for way too long.

This is what I want to be able to select from the linked field: SELECT `id` FROM `account` WHERE `deleted` = 0 AND `type` LIKE 'Vendor'
Anyone know how to do this?

[tarasm]

You can use the code;

$accountList = $entityManager->getRepository('Account')->where(array(    
   'type' => 'Vendor',    
))->find(); 

[joy11]

I've been working with custom views that are .js and the custom entityDefs that are .json
I'm not sure where to put this php so it is called/used. Do I create a function of vendorFilter in a Custom/SelectManagers/CurrentProducts/CurrentProducts.php
then envoke that on the clientDefs? I've tried so many different things and read so many different forums looking for hints that I don't know what direction is correct.

Will this allow me to populate the link's select field?

[tarasm]

I think, you can add to a filter to an Account: custom/Espo/Custom/SelectManagers/Account.php. Examples located at application/Espo/Modules/Crm/SelectManagers/Account.php

[joy11]

So if I create: custom/Espo/Custom/SelectManagers/Account.php

<?php
namespace custom\Espo\Custom\SelectManagers\Account;

class Account extends \Espo\Modules\Crm\SelectManagers\Account
{
protected function filterVendor(&$result)
{
$result['whereClause'][] = array(
'type' => 'Vendor'
);
}
}

The above code is similar to what was in the application/Espo/Modules/Crm/SelectManagers/Account.php

I'm not sure how to call this function from my entity, CurrentProduct, for the linked field "vendor" which is linked to the Account Entity. I have 25,000+ Accounts and 75 of them are "Vendors". I only want the 75 Vendors to come up in the select or when typed in the field in my CurrentProduct entity's vendor field.

All of the other examples I have seen are only for filtering other entities that already have an association with an account (like contacts etc) instead of filtering from a value defined in the account entity already. I've viewed every post in the forums which even remotely dealt with filters, but i'm not getting it to work. I'm not sure if I need to work with the view and entityDef or the SelectManager and the clientDef of my CurrentProduct entity, or now maybe the Account.php?

[joy11]

I've tried to just focus on one part at a time now. I know that I can get the value to filter in the select by using the custom view and the entityDef. I'll worry about the text complete after I get the select menu to work.

I'm able to get the words "Account Type" to show up in the selection menu for the Vendor link. It does not pick up any of the other information set in the js however and I'm unable to select Vendor. I am able to add the "Account Type" filter from the add fields and then select vendor that way, but I want to preset it.

I'm assuming it is because I don't have this portion formed correctly, but any hint would be appreciated.

getSelectFilters: function () {
return {
'Account Type': {
type: 'anyOf',
value: 'Vendor',
}
};
}

[tarasm]

It's pretty easy. Used Chrome DevTools to find the correct search parameters.

getSelectFilters: function () {            
            return {
                'type': {
                    type: 'in',
                    value: ['Vendor']
                }
            };            
        },

Tested. It works fine.

[joy11]

tarasm Thank you! This works!
I figured it was something easy with the right syntax or key words. I would have never thought the type would be 'in'. Now I will try to tackle the text filter.

[Etienne]

Hello,

I have a similar problem.
I click on opportunity and i click on "add opportunity". I can select a account (ex: my_account) and when I click on contact, a panel "contact" open with a filter "account - equals - my_account"

I would like to do the same with my proper entity "Testbase"

I created an entity "Testbase" like "contact".
I created a file \custom\Espo\Custom\Resources\metadata\entityDefs\ opportunity.json

{
"fields": {
"testbases": {
"type": "linkMultiple",
"view": "custom:views/opportunity/fields/Testbase",
"layoutDetailDisabled": false,
"layoutMassUpdateDisabled": false,
"importDisabled": false,
"noLoad": false,
"isCustom": true
}
},
"links": {
"testbases": {
"type": "hasMany",
"relationName": "testbaseOpportunity",
"foreign": "opportunities",
"entity": "Testbase",
"audited": false,
"isCustom": true
}
}
}

I created \client\custom\src\views\opportunity\fields\testba se.js

Espo.define('custom:views/opportunity/fields/Testbase', 'views/fields/link-multiple-with-role', function (Dep) {

return Dep.extend({

getSelectFilters: function () {
if (this.model.get('accountId')) {
return {
'account': {
type: 'equals',
field: 'accountId',
value: this.model.get('accountId'),
valueName: this.model.get('accountName'),
}
};
}
},

getCreateAttributes: function () {
if (this.model.get('accountId')) {
return {
accountId: this.model.get('accountId'),
accountName: this.model.get('accountName')
}
}
}

});

});

now, when i click on "new opportunity" -> I select account (my_account) -> I click on Testbase, i have this.

I forgot somethings ?

thank you

Etienne

[Etienne]

Hello,

I have a similar problem.
I click on opportunity and i click on "add opportunity". I can select a account (ex: my_account) and when I click on contact, a panel "contact" open with a filter "account - equals - my_account"

I would like to do the same with my proper entity "Testbase"

I created an entity "Testbase" like "contact".
I created a file \custom\Espo\Custom\Resources\metadata\entityDefs\ opportunity.json

{
"fields": {
"testbases": {
"type": "linkMultiple",
"view": "custom:views/opportunity/fields/Testbase",
"layoutDetailDisabled": false,
"layoutMassUpdateDisabled": false,
"importDisabled": false,
"noLoad": false,
"isCustom": true
}
},
"links": {
"testbases": {
"type": "hasMany",
"relationName": "testbaseOpportunity",
"foreign": "opportunities",
"entity": "Testbase",
"audited": false,
"isCustom": true
}
}
}

I created \client\custom\src\views\opportunity\fields\testba se.js

Espo.define('custom:views/opportunity/fields/Testbase', 'views/fields/link-multiple-with-role', function (Dep) {

return Dep.extend({

getSelectFilters: function () {
if (this.model.get('accountId')) {
return {
'account': {
type: 'equals',
field: 'accountId',
value: this.model.get('accountId'),
valueName: this.model.get('accountName'),
}
};
}
},

getCreateAttributes: function () {
if (this.model.get('accountId')) {
return {
accountId: this.model.get('accountId'),
accountName: this.model.get('accountName')
}
}
}

});

});

now, when i click on "new opportunity" -> I select account (my_account) -> I click on Testbase, i have this.

I forgot somethings ?

thank you

Etienne

[rabii]

Hey Etienne,

Did you managed to sort it out ? i am trying to achieve same thing but i can't figure out how to do it ?

any help would be much appreciated.

Many thanks

Rabii