We would like to inform you about a potential issue that you may encounter in ERP.net. This issue is related to custom properties where filtering for allowed values is specified in the Allowed Values Filter XML field.
What is the issue?
During certain events, such as generating an Accounting Voucher, when a value of a custom property needs to be populated in the document, you may encounter an error like the following:
Error, while executing business rule 'Property Values.ValueIdValidation'.
Query 'Cm_Company_Locations' does not support filter by table 'Cm_Companies.'.
This error occurs when non-standard filtering for the domain system is specified in the Allowed Values Filter XML field of a custom property. This means that the filters are applied to fields from a reference table rather than fields from the main table. The main table refers to the entity that is filled in the Allowed Values Entity Name field.
For example:
If Allowed Values Entity Name field is set to Products, the Allowed Values Filter XML field should contain filtering criteria only for fields from the Products table.
What to do if you encounter such an error
- Check for properties that may cause the problem: Verify if a filter is set in the Allowed Values Filter XML field of these properties.
- Review the applied filtering: Ensure that the filtering applied is not for fields from reference tables.
- Correct the filtering: If filtering is applied to fields from a reference table, modify the filtering to use fields from the main table instead. Below you can find examples for correct and incorrect filtering.
If you are still unable to resolve the issue, please create a support ticket on our ticketing platform and our team will assist you.
Examples of correct and incorrect filtering
Correct Filtering (by main table’s field):
Allowed Values Entity Name = Companies - Locations
Allowed Values Filter XML = Companies – Locations.Company = 'Test Ltd'
Incorrect Filtering (by reference table’s field):
Allowed Values Entity Name = Companies - Locations
Allowed Values Filter XML = Companies.Company = 'Test Ltd'
Steps we are taking to prevent incorrect filtering
Currently, the system allows incorrect filters to be applied because the navigator, accessible through the three dots in the Allowed Values Filter XML field, permits filtering by fields from reference tables.
In Service Pack 2 of version 24, we implement a change to ensure that the filtering can only be set through a navigator that allows filtering exclusively by fields from the main table.
This adjustment aims to prevent the possibility of setting incorrect filters in the future, improving the reliability and usability of the filtering feature within ERP.net.
If you have any further questions or need assistance, please do not hesitate to contact our support team.
0 Comments