Row Level Filtering - Proof of Concept

David MeegoOver the years I have seen requests for Row Level Filtering functionality within Microsoft Dynamics GP.

This is functionality where specific users can only see the records or rows in the tables that relate to their division or area.  Most of the time people are told that this is not possible.

Well, saying something is not possible is just raising a challenge to me. So I decided to develop a Dexterity add-on tool that could prove it was possible.  I am attaching the resulting Proof of Concept demos to this posting. It only works for the lesson or sample company and is not available for sale.  It is just to show what is possible.

While it is possible to use Visual Basic for Applications (VBA) to filter records, VBA can only work at the user interface by hiding invalid records.  All the records are still being retrieved from the SQL Server while only valid records are being displayed. This can cause poor performance when a large number of records are being returned and discarded. Dexterity can use a SQL where clause to prevent the invalid records being returned from the server and so maintain the performance while transparently hiding invalid records.

The demo code uses the User Class matrixed with the Customer Classes, Vendor Classes, Item Classes, Employee Classes and SOP Document IDs to restrict what records can be seen. Below is a screenshot of the Setup window:

Row Level Filtering

The concept I used, is to use Dexterity to a FORM_PRE trigger on any window that you wish to apply Row Level Filtering to and in that trigger use the range table where command to apply a SQL where clause to the form's table buffer for the specific table. This limits what is seen by the end user.  You can use a subquery in the where clause to link across multiple SQL tables to create the business rules for the restrictions.
 
You will need a trigger in the primary key field to use a function to look at the table without the where clause and again with the where clause. This would be used to know when the record already exists in the system, but the user does not have access to it.
 
You may also need to add extra triggers to handle the situations where the calls are made to functions and procedures and the form's table buffer is not passed as a parameter.  In these situations you will need to add code to limit the temporary table buffer used inside the procedure or function.  You might need to have access to the source code to be able to achieve this properly.
 
Finally, it would be worth noting that this technique has the following limitations:

  • It does not allow you to filter reports as there are no Dexterity triggers available on reports.
  • It is possible to filter Advanced Lookups searching functionality, but this requires extra development against each lookup window.
  • It would probably be possible to restrict the standard Smartlists, but this would need additional development.  Not sure if Smartlist Builder Smartlists can be restricted.
  • It needs to be coded against every window that you want the restrictions to apply.

The Knowledge Base (KB) articles below explain some of the techniques used:

How to write "Passthrough" SQL statements and "Range Where" clauses in Microsoft Great Plains Dexterity (KB 910129) Secure Link

How to use a "Range Where" clause that is based on more than one table in Dexterity in Microsoft Dynamics GP (KB 922056) Secure Link

The only other technique I have heard about was to handle this all at the SQL level.  You would need to rename the original table and create a view with the same name as the original table.  This view is then limited by your filtering business rules. Note that the views would need to be removed and the tables renamed back to their original names, whenever service packs, hotfixes or version upgrades are installed.

NOTE: This is not an officially released tool and is not supported.  It is made available "as is" and is only functional with the lesson or sample company.

The code and documentation for v8.0, v9.0 & v10.0 is attached at the bottom of the article.

Please add your comments to say what you think of the demo code.

David

RowSecur.zip