I am getting requests about this subject almost from all my customers, therefore we at Dynamics Innovations have decided to develop an add-on for Dynamics GP to fulfill this request a month ago and my development team is currently in the process of developing the final touches on the utility.
The add-on is basically works on creating new Dynamic lookup that replaces traditional Dynamics GP lookups, the same lookup is used for all kind of data inside Dynamics GP and the rows are filter dynamically at runtime based on user privileges which to be setup by creating a specific view for each user/role per each business entity and the system will display only data from the linked view:
By achieving this, the system administrator will be able to customize columns names and columns to be displayed by using SQL views or direct SQL queries and rows to be displayed, and the lookup will automatically filter rows using "like/contains" operator based on the user selection and based on the clicked column:
The data load were enhanced to load data into .Net data grid that pulls rows from database directly into the user interface, 250,000 records were pulled within less than 7 seconds:
In addition to the lookup functionality we have developed a routine that validates user inputs inside GP texts and will make sure that the user selected data is a part of the user query:
The development team is currently working to enhance the navigation buttons as it might violates our security setup.
We been able to automate all the above processes and hanged with a couple of issues, the first is reports and the other is the smart list, and been able to handle smart list security by linking users view with smart list views using smart list builder, so each user will have his smart list configured, not an easy task but doable.
The last part is our challenge for the time being, we cannot figure out a solution for filtering report writer reports using available algorithms, we tried to pass restrictions fields to the reports using VBA but this wasn't a sustainable solution, we still didn't give up but we might not be able have this done.
UPDATE: The tool has been released and published over the company website without affecting the Reports, we left this for the IT people to manage, download your copy now from the link below:
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85