RSMUS.comChances are when you began using Microsoft Dynamics GP and loaded the chart of accounts, you may not have thought about populating the GL Segment Descriptions which are stored in a separate table called the Segment Description Master. Populating your segment descriptions is useful when setting up new GL accounts using the Mass Modify feature and for reporting purposes in Management Reporter. Unfortunately, Integration Manager does not insert or update the Segment Description Master table, and the Table Import tool does not have update capabilities. Luckily, you can use the Data Import tools in SQL to accomplish this task by creating a temporary table and then joining it to the GL40200 to process the update. Below are the steps:
- Create a SmartList in Dynamics GP from the Financial > Accounts folder. Use the default * Accounts SmartList, and remove all the columns. Add the column called Main Account Segment. Export the results to Excel, and change the name of the column Main Account Segment to SGMNTID. Then insert 2 additional columns for the segment number and segment description using the headers SGMTNUMB and DSCRIPTN as shown below. Column headers must be exact in order to sync with the fields in the SQL tables. Enter the segment number of your main account in the first column and copy it down to each cell in the spreadsheet. For example, if the main account is segment 2, enter a 2 for the SGMNTNUMB column for each line. Then populate the DSCRIPTN column with the segment descriptions you want to update in GP. See example below:
Save the file as csv with the name SegmentDescriptions.
- In SQL Server Management Studio make a backup of GL40200 table by running the following script against the company database:
- You will now proceed to create the temporary table called [SegmentDescriptions]. First expand the databases, and right click on the company database. Then go to Tasks > Import Data to open the SQL Server Import/Export Wizard.
Click Next. - Select Flat File Source for the data source. Browse out and select the file called [SegmentDescriptions] you saved in step 1. Mark the checkbox to display column names in the first row.
- Highlight Preview to preview your file. Double check the column headers to make sure they match exactly to the fields in the GL40200 table. If any corrections are necessary, make the changes to the file, and restart the import wizard. If the preview of the data is correct, click Next.
- The destination and server name will default in the window. Select Use SQL Server Authentication, and enter the sa user credentials. Select the company database to import the file. Click Next.
- Review the Select Source Tables and Views window, and make sure the table name is [SegmentDescriptions] with no spaces. You can also click the Preview button to preview the data. Click Next.
- Accept the default to run the package file immediately, and click Next.
- Verify the selections made in the wizard and click Finish.
- Review the results of the import for any errors, messages, and the number of rows affected. If you receive any errors, review your source file, make the necessary corrections, and re-import the file. Otherwise click the Close button.
- Run the script below against the company database to update the GL40200 from the SegmentsDescriptions table created in the wizard.
- Run the script below against the company database to review the updated contents of the GL40200 table:
Replace the X with the number that represents the main account segment.The results should contain the segment descriptions from your source file in the DSCRIPTN field. - If you are satisfied with the results, you can delete the temporary SegmentDescriptions table and the backup of the GL40200 table you created by running the following scripts against the company database:
If you’re looking for quick and easy business solutions for your Microsoft Dynamics GP system, RSM offers access to Certified Microsoft Professionals, help desk and phone support, knowledge and experience with third party products and dedicated account management. Please contact our professionals for more information at erp@rsmus.com or by phone at 855.437.7202.
by Nancy Hogan for RSM
