GP Power Tools has the ability to recreate user SQL logins after a server migration using the Database Validation window, or reset multiple user logins using the SQL Login Maintenance window. Both of these methods can use fixed or randomly generated passwords and have the ability to send emails to users with their new password. Also, changing the user’s password from the User Setup window can send an email.

The wording and formatting of these Password Reset emails can be defined in GP Power Tools by your system administrator. You can even use HTML to format the emails as described in the article below:

• #GPPT Using HMTL to format Password Reset Emails

To be able to send emails to your users, you need to ensure that GP Power Tools knows the email addresses of the users. They are entered into the User Setup Additional Information window which can be opened from the User Setup window (if it is not set to automatically open). The sample code in this project shows how you can take the data from an Excel spreadsheet with the User ID and Emails of the users and import it from the clipboard directly into GP Power Tools.

Below shows an example of the User Setup window and the GP Power Tools User Setup Additional Information window.

The User Setup Additional Information window with the User Email Address highlighted.

Below is a screenshot of the USER_EMAIL_IMPORT project showing the components:

Triggers USER_EMAIL_IMPORT and USER_EMAIL_IMPORT2

These triggers add menu entries to the Additional menu when the User Setup (Enter_User_Names) form is open. The first trigger is to Import User Emails via the Clipboard and calls the USER_EMAIL_IMPORT Runtime Execute Script. The second trigger is to Display User Emails and calls the USER_EMAIL_IMPORT SQL Execute Script and displays the results in a SQL Results window with a SQL Goto which allows the user to double click on a row to display that user.

Runtime Execute Script USER_EMAIL_IMPORT

This script will validate that the clipboard contains a tab delimited data set with the first column header being User ID and the second column header being Email. It will then read each row from the data set and call the MBS_UserAddInfo_Set helper function to write the email address into the table used for the User Setup Additional Information window.

Note: This script can easily be altered to import any other of the fields in the User Setup Additional Information window. The data positions for the MBS_UserAddInfo_Set Helper function are 1 = Email; 2 = Active Directory User ID; 3, 4 or 5 = Company ID, Company Name, Intercompany ID; then 6 through 20 are the remaining fields on the window.

Runtime Execute Script USER_EMAIL_GOTO

This script uses the SQL Execute Goto Custom script purpose and will open the User Setup window to the User ID selected in the SQL Results window when selected from the Goto menu or when double clicked.

SQL Execute Script USER_EMAIL_IMPORT

This SQL script is just a select statement to join the User Master table to the Additional User Information table so you can review the email addresses associated with each user in the system.

The archive below also contains an example Excel Spreadsheet showing the expected format of the data.

Note: An issue with the SQL Login Maintenance window has been discovered which means the email modes that use .Net code to send the emails will only send the email to the first user selected. This issue affects email modes: SMTP Server via .Net Addin and Multi-Factor Authentication. This issue has already been fixed in the upcoming Build 31 release.

Importing additional data

To import additional user data, you will need to add additional columns to the spreadsheet with the appropriate column header names. Then you will need to update the USER_EMAIL_IMPORT Runtime Execute script to add the extra columns to the code. You will need to make the following changes:

• In the variable declarations at the top under the local string l_Email; line, add a variable declaration for each additional column.
• At the top of the code add the extra column to the format error checks. Just duplicate the entire if not l_Format_error then … end if; section and change the column header names and column numbers.
• In the if l_Format_error then … end if; section, update the warning statement with the additional column header names.
• In the { Line Fields } section, duplicate the call to the MBS_SQL_Parse_Data_String Helper function. Update column number setting as required and store the data in the variables created in the first step.
• Above the end while; line add in another call to the MBS_UserAddInfo_Set Helper function for the extra columns. Select the correct position number for the data. For example: User Defined 1 is Position 15.
• Note: If adding dates (Positions 19 and 20), just treat them as strings and they will be converted to dates automatically when saved. You can use your local date format or YYYY/MM/DD format.

That’s all folks!!!

Downloading and Installing

Download the example code, import using the Project Setup window (without any project showing, select the path to the xml file and click Import):

• GP Power Tools Settings User_email_import.dbg.zip

The code will be active on next login or after switching companies, or you can use start the triggers manually from the Project Setup window.

More Information

For more information see:

• GP Power Tools Portal: http://winthropdc.com/GPPT
• GP Power Tools Samples: http://winthropdc.com/GPPT/Samples
• GP Power Tools Videos: http://winthropdc.com/GPPT/Videos

Hope you find this example useful.

David

17-Jul-2024: Added details of how to import additional data.

This article was originally posted on http://www.winthropdc.com/blog.