Dynamic Filter per Table

Managing data access effectively is crucial in any ERP system. With Dynamic Validation per Table, iDempiere offers a powerful and flexible way to control data visibility at a system-wide level or based on specific users and roles. Similar to PostgreSQL’s Row Level Security (RLS), this feature ensures that users only see the relevant information while maintaining security and compliance.

---

---

What is Dynamic Filter per Table?

The purpose of `Dynamic Validation per Table` is to offer a more flexible way to restrict access to iDempiere data widely, or also based on user or role. This feature is similar to PostgreSQL’s Row Level Security (RLS) capabilities.

Use Case examples

• Filter order records to display only sales documents where the logged-in user is the sales representative.
• Restrict business partner records so the purchase manager sees only vendors, excluding customers and employees.
• Hide employee records from non-administrators to comply with data privacy laws.
• Ensure employees working in one warehouse cannot access documents from another warehouse.
• Allow many users to access vendor invoices, but restrict invoices with specific charges (e.g., Salary, Consultation Charges, Bonus, or Loan) to finance users only.

To see a more detailed and technical explanation of some of these use cases, visit the wiki.

How to Configure Dynamic Filters per Table

Using this approach provides a more flexible, SQL-based way to restrict access to tables while leveraging context variables such as login details, preferences, accounting, and predefined roles.

When multiple records apply to a session, all WHERE clauses are combined using AND.

For example, you can define rules based on:

• Table + Tenant – Rules that apply on C_BPartner in GardenWorld.
• Table + System – Rules that apply on C_BPartner for all tenants.

Additionally, the Role and User fields help define rule restrictions:

• Role empty, User empty – Applies to everyone for the specified table and tenant.
• Role filled, User empty – Applies to all users logged in with this role.
• Role empty, User filled – Applies only to the specified user.
• Role filled, User filled – Applies only to the specified user when logged in with that role.

Alternative approaches to restricting access

1. Use Role/User => Org Access as a way to restrict access. Problem statement: This is an all-or-nothing solution. If a user/role does not have access to an Org, they can see ‘nothing’ in that Org.
2. Use Role Data Acces as a way to restrict access. Problem statement: The configuration is static, and it can be verbose (many records) to accomplish a goal
3. Use Window Tab and Field => Tab => SQL Where field as a way to restrict access. Problem statement: this solution can be a lot of work and can lead to repetitive logic. There are hundreds of windows with thousands of tabs in iDempiere, and you could easily be in a situation where you make mistakes and show the wrong data to the wrong role.

Why Dynamic Validation per Table is just right: it strikes a nice balance between flexibility and being concise. 

This wiki page does a good job of providing more information and practical examples.

Conclusion

Dynamic table filtering enhances security, improves user experience, and simplifies access control without requiring extensive coding. By leveraging this feature, businesses can implement tailored data access rules to meet their operational and regulatory needs efficiently.

Do you have further questions? Join our community forums to share your experiences and learn from other users.