Ability to link entities with filters

[murugappan]

Currently, we can link an entity with another entity using the relationship option. When selecting the field in the parent entity, we get a modal showing the short list of the rows of the linked entity. The modal is more of smaller version of the larger list view of the linked entity. If the linked entity has different type of records, e.g medication, sanitizers etc, we will have to use the "three vertical dots" to filter the type. This gives rise to frequent mistakes because the user could select the "medication" type instead of the "sanitizers" without using the filter option. This frequently happens as there are too many keystrokes.

An example:

Let me give an example, which hopefully will make it clearer:

1. We have entity called "Prescriptions" which contains all the medical prescriptions issued to patients.
2. One field in the "Prescriptions" entity is linked to a relationship to the Accounts entity. The Accounts entity contains all the medical providers such as Clincs and Hospitals.
3. In our country, long term prescriptions can only be issued by Hospitals.
4. Based on (3) above, when we create a prescription we need to link to the hospital that issued it.
5. To achieve (4), the user will select a provider from the modal short list view of Accounts which initially lists all the rows (clinics and hospitals) in the Accounts entity.
6. Frequently the users enter the short name into search filter box (with magnifying glass) and selects the wrong the provider, a clinic by mistake.

Feature Request:

to resolve the problem, it will be great if we could define a filter in the relationship. By doing this, when the user executes step 5 above, the user will get only a list of hospitals. Agreed, the user could still select the wrong hospital but helps reduce the number of mistakes.

What i an not sure of:

I am not sure if this can be achieved using workflow. I tried but could not set it up even a tiny close.

[emillod]

Hello murugappan
i don't think so. It's possible to code custom filters for list in Prescriptions entity, which will show only hospitals after he click on this small arrow, next to linked field, BUT he'll be able to get rid of this filter, and which is even worst, he'll be able to write name of the company in this field and unfortunatelly, by default, this field will also let him to search in whole related entity, not only in "hospitals".

I already reported this to EspoCRM developers, because it's common issue. Same thing is in opportunities i think, you can choose account and choose only contacts assigned to that account, BUT only if you're using list. If you use field, without opening panel, you'll be able to search in all contacts.

I think we could develop that, but i'm not sure is there a point for that, because it should be done globally for EspoCRM.

[murugappan]

emillod, espcrm

Hi

Fully agree on this. Its a common issue. In nearly all business applications, we use this cascading drop-down commonly. I am sure you know that the industry has come to see the need and now there are many providing such components in their web templates. Especially the one where city->state-postcode->country cascading. Fabrika provides this feature wonderfully well. To achieve our needs, the developers may need to provide this sort of cascading filters, This feature will also resolve the tricky part in my request for have lookup DB Tables as the alternative to enum (which bloats the the table in DB).

This may be an important setback in achieving a excellent product award (hehehe...). By the way, guys i use Fabrikar.com Customer Application Builder for Joomla CMS for CRUD modelling which i use to test functionality and then pass on to my developers to do. The documentations for Fabrikar are outdated but still applicable. See the attached example.

[espcrm]

Not sure why I was pinged but this now pique my interest. I'm looking at their documentation and your image... look like it might be a slowly learning curve here. Can't seem to find anything about CRUD/API though

https://fabrikar.com/forums/index.ph.../special/pages

Not much utube video tutorial either... guess reading is the only option

[murugappan]

hi espcrm ,

The first part is the shortfall in espocrm we face now. When we create entities which contain addresses, we are unable to select a State that is related to a Country. The State field is currently free typed. Huge amounts of mistakes are made here especially when using the address for mailing item to clients, That when a cascading dropdown feature linked to lookup tables will help. This need is more linked to the subject of this post. I roped you into this because we were involved in discussion on lookup tables and this sort of extends that topic, just in case you would be interested.

Second part relating to Fabrikar.com is more related to how we is to develop CRUD models, especially related to the cascading dropdown. More of a reference only.

[espcrm]

Certainty is, thank you for very much. Always on look out for thing that I would need right now or in the future. But bit occupied at the moment and for a few more weeks possibly. Don't have much time to read forum lately.

[emillod]

murugappan you can look at our extension which provide customized address field with list of countries instead of varchar. https://devcrm.it/address

[murugappan]

Hi, Thank you for your advise and link. I checked it. It is enum implementation and not a cascading dropdown. That will be difficult to implement without the help of Espocrm developers as it involves external tables for look up.