Dynamics 365 Finance
Category
Sep 29, 2023
Published date
Text
Article Type
AI Summary
- The article discusses the use of GP Power Tools custom RW Functions to execute SQL queries and return data to a report.
- The article explains that while the original methods described in the article are still functional, there are newer and faster methods available in later GP Power Tools builds.
- The original methods use SQL Execute scripts without a where clause to limit the data returned, and custom RW Functions that manipulate the SQL query in code, potentially leaving the code open to SQL Injection or regional date and time format issues.
- The data is passed in and out of the custom RW Function scripts using the MBS_Param_Get and MBS_Param_Set Helper Functions, resulting in multiple reads and writes to the DUOS table and the execution of the SQL query each time.
- The current method improves performance by using Parameter Lists to pass values for the SQL where clause, SQL Lookup for the Parameter List, and Memory Parameters to pass variables into and out of the Runtime Execute scripts, avoiding the need for string datatype conversion and physical tables.
- The new method further enhances performance by running the SQL query only once for each header or line record, storing the returned data in Memory parameters, and returning the desired column each time the custom RW Function is called.
- The new method also includes an additional Runtime Execute script to remove Memory parameters after the report has printed or when the key fields for the header or line record change.
- The article provides further information on downloading and installing the example code and handling required datatype conversions for Line level RW functions.
Registered users can view the full text for FREE!
Sign In Now!