Copying Smartlists favorites from one user to another

October 24, 2011

I get this question asked very often and sometimes have to dig up my notes on the subject, so I figured I would just post the SQL script that I use for this.

Smartlist favorites are traditionally stored in the ASIEXP81 table on a user-company basis. It’s important to keep this in mind, as you can tailor this script to copy favorites from one user to another in the same company or the same user across various companies. However, this script replicates all favorites, across all companies for one user to another user.

USE DYNAMICS;
GO
-- remove any records for the destination user prior to synchronizing
DELETE FROM ASIEXP81 WHERE USERID = '<DestinationUserID>';

-- execute synchronization
INSERT INTO ASIEXP81
(ASI_Favorite_Dict_ID
,ASI_Favorite_Type
,ASI_Favorite_Save_Level
,CMPANYID
,USRCLASS
,USERID
,ASI_Favorite_Name
,ASI_Field_Dict_ID_1
,ASI_Field_Dict_ID_2
,ASI_Field_Dict_ID_3
,ASI_Field_Dict_ID_4
,ASI_Field_List_1
,ASI_Field_List_2
,ASI_Field_List_3
,ASI_Field_List_4
,ASI_Search_Type_1
,ASI_Search_Type_2
,ASI_Search_Type_3
,ASI_Search_Type_4
,ASI_Match_Case_1
,ASI_Match_Case_2
,ASI_Match_Case_3
,ASI_Match_Case_4
,ASI_Start_Account_Number_1_1
,ASI_Start_Account_Number_1_2
,ASI_Start_Account_Number_1_3
,ASI_Start_Account_Number_1_4
,ASI_Start_Account_Number_1_5
,ASI_Start_Account_Number_1_6
,ASI_Start_Account_Number_1_7
,ASI_Start_Account_Number_1_8
,ASI_Start_Account_Number_1_9
,ASI_Start_Account_Number_1_10
,ASI_Start_Account_Number_2_1
,ASI_Start_Account_Number_2_2
,ASI_Start_Account_Number_2_3
,ASI_Start_Account_Number_2_4
,ASI_Start_Account_Number_2_5
,ASI_Start_Account_Number_2_6
,ASI_Start_Account_Number_2_7
,ASI_Start_Account_Number_2_8
,ASI_Start_Account_Number_2_9
,ASI_Start_Account_Number_2_10
,ASI_Start_Account_Number_3_1
,ASI_Start_Account_Number_3_2
,ASI_Start_Account_Number_3_3
,ASI_Start_Account_Number_3_4
,ASI_Start_Account_Number_3_5
,ASI_Start_Account_Number_3_6
,ASI_Start_Account_Number_3_7
,ASI_Start_Account_Number_3_8
,ASI_Start_Account_Number_3_9
,ASI_Start_Account_Number_3_10
,ASI_Start_Account_Number_4_1
,ASI_Start_Account_Number_4_2
,ASI_Start_Account_Number_4_3
,ASI_Start_Account_Number_4_4
,ASI_Start_Account_Number_4_5
,ASI_Start_Account_Number_4_6
,ASI_Start_Account_Number_4_7
,ASI_Start_Account_Number_4_8
,ASI_Start_Account_Number_4_9
,ASI_Start_Account_Number_4_10
,ASI_Start_Date_Token_DDL_1
,ASI_Start_Date_Token_DDL_2
,ASI_Start_Date_Token_DDL_3
,ASI_Start_Date_Token_DDL_4
,ASI_Start_Date_1
,ASI_Start_Date_2
,ASI_Start_Date_3
,ASI_Start_Date_4
,ASI_Start_DDL_1
,ASI_Start_DDL_2
,ASI_Start_DDL_3
,ASI_Start_DDL_4
,ASI_String_Start_1
,ASI_String_Start_2
,ASI_String_Start_3
,ASI_String_Start_4
,ASI_End_Account_Number_1_1
,ASI_End_Account_Number_1_2
,ASI_End_Account_Number_1_3
,ASI_End_Account_Number_1_4
,ASI_End_Account_Number_1_5
,ASI_End_Account_Number_1_6
,ASI_End_Account_Number_1_7
,ASI_End_Account_Number_1_8
,ASI_End_Account_Number_1_9
,ASI_End_Account_Number_1_10
,ASI_End_Account_Number_2_1
,ASI_End_Account_Number_2_2
,ASI_End_Account_Number_2_3
,ASI_End_Account_Number_2_4
,ASI_End_Account_Number_2_5
,ASI_End_Account_Number_2_6
,ASI_End_Account_Number_2_7
,ASI_End_Account_Number_2_8
,ASI_End_Account_Number_2_9
,ASI_End_Account_Number_2_10
,ASI_End_Account_Number_3_1
,ASI_End_Account_Number_3_2
,ASI_End_Account_Number_3_3
,ASI_End_Account_Number_3_4
,ASI_End_Account_Number_3_5
,ASI_End_Account_Number_3_6
,ASI_End_Account_Number_3_7
,ASI_End_Account_Number_3_8
,ASI_End_Account_Number_3_9
,ASI_End_Account_Number_3_10
,ASI_End_Account_Number_4_1
,ASI_End_Account_Number_4_2
,ASI_End_Account_Number_4_3
,ASI_End_Account_Number_4_4
,ASI_End_Account_Number_4_5
,ASI_End_Account_Number_4_6
,ASI_End_Account_Number_4_7
,ASI_End_Account_Number_4_8
,ASI_End_Account_Number_4_9
,ASI_End_Account_Number_4_10
,ASI_End_Date_Token_DDL_1
,ASI_End_Date_Token_DDL_2
,ASI_End_Date_Token_DDL_3
,ASI_End_Date_Token_DDL_4
,ASI_End_Date_1
,ASI_End_Date_2
,ASI_End_Date_3
,ASI_End_Date_4
,ASI_End_DDL_1
,ASI_End_DDL_2
,ASI_End_DDL_3
,ASI_End_DDL_4
,ASI_String_End_1
,ASI_String_End_2
,ASI_String_End_3
,ASI_String_End_4
,ASI_Search_From_Str_1
,ASI_Search_From_Str_2
,ASI_Search_From_Str_3
,ASI_Search_From_Str_4
,ASI_Search_To_Str_1
,ASI_Search_To_Str_2
,ASI_Search_To_Str_3
,ASI_Search_To_Str_4
,ASI_Search_Logic_Type
,ASI_Max_Records
,ASI_Sort_Dict_ID
,ASI_Sort_Field
,ASI_Sort_Type
,ASI_Field_Comparison_1
,ASI_Field_Comparison_2
,ASI_Field_Comparison_3
,ASI_Field_Comparison_4
,ASI_Start_Comp_Field_ID_1
,ASI_Start_Comp_Field_ID_2
,ASI_Start_Comp_Field_ID_3
,ASI_Start_Comp_Field_ID_4
,ASI_Start_Comp_Field_Dic_1
,ASI_Start_Comp_Field_Dic_2
,ASI_Start_Comp_Field_Dic_3
,ASI_Start_Comp_Field_Dic_4
,ASI_End_Comp_Field_ID_1
,ASI_End_Comp_Field_ID_2
,ASI_End_Comp_Field_ID_3
,ASI_End_Comp_Field_ID_4
,ASI_End_Comp_Field_Dict_1
,ASI_End_Comp_Field_Dict_2
,ASI_End_Comp_Field_Dict_3
,ASI_End_Comp_Field_Dict_4)
SELECT ASI_Favorite_Dict_ID
,ASI_Favorite_Type
,ASI_Favorite_Save_Level
,CMPANYID
,USRCLASS
,'<DestinationUserID>'
,ASI_Favorite_Name
,ASI_Field_Dict_ID_1
,ASI_Field_Dict_ID_2
,ASI_Field_Dict_ID_3
,ASI_Field_Dict_ID_4
,ASI_Field_List_1
,ASI_Field_List_2
,ASI_Field_List_3
,ASI_Field_List_4
,ASI_Search_Type_1
,ASI_Search_Type_2
,ASI_Search_Type_3
,ASI_Search_Type_4
,ASI_Match_Case_1
,ASI_Match_Case_2
,ASI_Match_Case_3
,ASI_Match_Case_4
,ASI_Start_Account_Number_1_1
,ASI_Start_Account_Number_1_2
,ASI_Start_Account_Number_1_3
,ASI_Start_Account_Number_1_4
,ASI_Start_Account_Number_1_5
,ASI_Start_Account_Number_1_6
,ASI_Start_Account_Number_1_7
,ASI_Start_Account_Number_1_8
,ASI_Start_Account_Number_1_9
,ASI_Start_Account_Number_1_10
,ASI_Start_Account_Number_2_1
,ASI_Start_Account_Number_2_2
,ASI_Start_Account_Number_2_3
,ASI_Start_Account_Number_2_4
,ASI_Start_Account_Number_2_5
,ASI_Start_Account_Number_2_6
,ASI_Start_Account_Number_2_7
,ASI_Start_Account_Number_2_8
,ASI_Start_Account_Number_2_9
,ASI_Start_Account_Number_2_10
,ASI_Start_Account_Number_3_1
,ASI_Start_Account_Number_3_2
,ASI_Start_Account_Number_3_3
,ASI_Start_Account_Number_3_4
,ASI_Start_Account_Number_3_5
,ASI_Start_Account_Number_3_6
,ASI_Start_Account_Number_3_7
,ASI_Start_Account_Number_3_8
,ASI_Start_Account_Number_3_9
,ASI_Start_Account_Number_3_10
,ASI_Start_Account_Number_4_1
,ASI_Start_Account_Number_4_2
,ASI_Start_Account_Number_4_3
,ASI_Start_Account_Number_4_4
,ASI_Start_Account_Number_4_5
,ASI_Start_Account_Number_4_6
,ASI_Start_Account_Number_4_7
,ASI_Start_Account_Number_4_8
,ASI_Start_Account_Number_4_9
,ASI_Start_Account_Number_4_10
,ASI_Start_Date_Token_DDL_1
,ASI_Start_Date_Token_DDL_2
,ASI_Start_Date_Token_DDL_3
,ASI_Start_Date_Token_DDL_4
,ASI_Start_Date_1
,ASI_Start_Date_2
,ASI_Start_Date_3
,ASI_Start_Date_4
,ASI_Start_DDL_1
,ASI_Start_DDL_2
,ASI_Start_DDL_3
,ASI_Start_DDL_4
,ASI_String_Start_1
,ASI_String_Start_2
,ASI_String_Start_3
,ASI_String_Start_4
,ASI_End_Account_Number_1_1
,ASI_End_Account_Number_1_2
,ASI_End_Account_Number_1_3
,ASI_End_Account_Number_1_4
,ASI_End_Account_Number_1_5
,ASI_End_Account_Number_1_6
,ASI_End_Account_Number_1_7
,ASI_End_Account_Number_1_8
,ASI_End_Account_Number_1_9
,ASI_End_Account_Number_1_10
,ASI_End_Account_Number_2_1
,ASI_End_Account_Number_2_2
,ASI_End_Account_Number_2_3
,ASI_End_Account_Number_2_4
,ASI_End_Account_Number_2_5
,ASI_End_Account_Number_2_6
,ASI_End_Account_Number_2_7
,ASI_End_Account_Number_2_8
,ASI_End_Account_Number_2_9
,ASI_End_Account_Number_2_10
,ASI_End_Account_Number_3_1
,ASI_End_Account_Number_3_2
,ASI_End_Account_Number_3_3
,ASI_End_Account_Number_3_4
,ASI_End_Account_Number_3_5
,ASI_End_Account_Number_3_6
,ASI_End_Account_Number_3_7
,ASI_End_Account_Number_3_8
,ASI_End_Account_Number_3_9
,ASI_End_Account_Number_3_10
,ASI_End_Account_Number_4_1
,ASI_End_Account_Number_4_2
,ASI_End_Account_Number_4_3
,ASI_End_Account_Number_4_4
,ASI_End_Account_Number_4_5
,ASI_End_Account_Number_4_6
,ASI_End_Account_Number_4_7
,ASI_End_Account_Number_4_8
,ASI_End_Account_Number_4_9
,ASI_End_Account_Number_4_10
,ASI_End_Date_Token_DDL_1
,ASI_End_Date_Token_DDL_2
,ASI_End_Date_Token_DDL_3
,ASI_End_Date_Token_DDL_4
,ASI_End_Date_1
,ASI_End_Date_2
,ASI_End_Date_3
,ASI_End_Date_4
,ASI_End_DDL_1
,ASI_End_DDL_2
,ASI_End_DDL_3
,ASI_End_DDL_4
,ASI_String_End_1
,ASI_String_End_2
,ASI_String_End_3
,ASI_String_End_4
,ASI_Search_From_Str_1
,ASI_Search_From_Str_2
,ASI_Search_From_Str_3
,ASI_Search_From_Str_4
,ASI_Search_To_Str_1
,ASI_Search_To_Str_2
,ASI_Search_To_Str_3
,ASI_Search_To_Str_4
,ASI_Search_Logic_Type
,ASI_Max_Records
,ASI_Sort_Dict_ID
,ASI_Sort_Field
,ASI_Sort_Type
,ASI_Field_Comparison_1
,ASI_Field_Comparison_2
,ASI_Field_Comparison_3
,ASI_Field_Comparison_4
,ASI_Start_Comp_Field_ID_1
,ASI_Start_Comp_Field_ID_2
,ASI_Start_Comp_Field_ID_3
,ASI_Start_Comp_Field_ID_4
,ASI_Start_Comp_Field_Dic_1
,ASI_Start_Comp_Field_Dic_2
,ASI_Start_Comp_Field_Dic_3
,ASI_Start_Comp_Field_Dic_4
,ASI_End_Comp_Field_ID_1
,ASI_End_Comp_Field_ID_2
,ASI_End_Comp_Field_ID_3
,ASI_End_Comp_Field_ID_4
,ASI_End_Comp_Field_Dict_1
,ASI_End_Comp_Field_Dict_2
,ASI_End_Comp_Field_Dict_3
,ASI_End_Comp_Field_Dict_4
FROM ASIEXP81 WHERE USERID = '<SourceUserID>';

GO

In the above script <SourceUserID> and <DestinationUserID> are placeholders for the actual user IDs.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Advertisements

SmartList Builder and creating Calculated Fields with Extender data

July 14, 2011

Just recently, I ran into a case where the partner was creating a SmartList Builder calculated field using data from the RM Open table (RM20101) via an Left Join table operation with the Extender Window Field Numbers. In fact, this is a very typical scenario for a lot of deployments where Extender is used, especially when you cannot use the standard Extender functionality to integrate with out-of-the-box smartlists.

The original SLB calculated field look something like this:

Extender Calculated Field

When the SLB smartlist was deployed, a number of results came back as zero for the records where there was no entry in the Extender Window Field Numbers table, even when the Sales Amount and Current Trx Amount fields had a value in the RM Open File table.

Paying a bit more attention to the issue made me think of how LEFT OUTER JOINs are processed by the Microsoft SQL Server query engine. This is best illustrated with the following example:

RM Open Extender
Customer Number Document Number Sales Amount Current Transaction Amount PT UD Key PT UD Number Total
AARONFIT001 INV3223 200.00 40.00 INV3223 5.00 5.00
ADAMPARK001 INV1020 100.00 20.00 NULL NULL NULL

Note that if Extender data was not entered for INV1020, a left outer join query would produce a NULL value as a result of the join operation. To overcome this situation, we applied the T-SQL ISNULL() function to the Extender field in SmartList Builder. Since SmartList Builder uses pass-through SQL to build each portion of the SELECT statement used to retrieve the records, then this should work just fine. The final calculated field is as follows:

Remember, Extender is a valuable tool to capture additional data and enhances the value of SmartList Builder when combined together to deliver reports. Pay special attention when creating calculated fields that rely on information from Extender tables in left join scenarios.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


SmartList Builder "Display as a negative value based on field" option not working as expected

December 14, 2010

Just recently on a newsgroup forum, a partner brought up an issue affecting SmartList Builder. The partner had just recently upgraded the client from version 9 and was testing the Smartlists the customer had built prior to the upgrade to ensure they were still working as expected. In the partner’s own words:

“It was working when we were on GP9, but now we’re on GP2010 SP1 none of my fields set up with Negative Values are showing up with Negative Values. When I run the smartlist from the old GP9 server, amounts are showing negative amounts for Return documents. But when we run the smartlist on GP2010, the amounts are now positive amounts, and when I check the Set Field Options window, they are still set up to “Display as negative based on field” when SOP Type = Return.”

In order to verify this I launched GP 2010 and recreated the SmartList mentioned above by the partner, setting the field option for the Document Amount to display a negative value based on a SOP Type of Return:


Set Field Options window

After saving the SmartList Builder option, I launched SmartList and had it build the newly created option. Once I checked the entry I had just created, effectively Returns were no displaying as negative.



BUG: Returns document amounts not displayed as negative

This bug seems to be confined to Microsoft Dynamics GP 2010 RTM (Build 11.00.1247) and Microsoft Dynamics GP 2010 SP1 (Build 11.00.1524). I could not replicate this issue for version 10.0 SP5 (10.00.1579) or earlier.

As a workaround, you can create a SmartList Builder calculated field to change the sign of the amount being displayed based on the SOP Type, as follows:

Add Calculated Field

NOTE: You can perform a similar CASE statement for any other SmartList you have created based on the field(s) criteria you are using to render the value a negative value.

This problem has been reported to Microsoft and a bug report is being written up to address the issue as of the publishing date of this article. For more information, contact Microsoft Dynamics GP Support.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


Undocumented DEX.INI switch cuts down SmartList export times to Microsoft Office Excel

May 14, 2010

Ok, so why the journalistic type nature of this article? Well, if like many users out there you tend to export tens of thousands of records out of Microsoft Dynamics GP using SmartList and you complain about the export’s performance, then I guarantee you will want to read Patrick Roth’s new article Smartlist: Exports slowly to Excel – Part 1.

In his article, Patrick details an undocumented DEX.INI setting that changes the way records are exported from SmartList into Microsoft Office Excel. The feature was added since release 10 and remains active in release 2010.

SmartlistEnhancedExcelExport=TRUE

Patrick further explains the feature trades formatting for performance, so if you are an Excel wizz just looking to get data out so you can do the rest on your own, then this switch is for you.

The DEX.INI is the Microsoft Dynamics GP defaults file containing setup and operating information about Microsoft Dynamics GP. Each line of information, or setting, in the file contains information such as where your files are located, and whether certain functions, such as displaying print dialog boxes, should be performed.

Additional Resources

DEX.INI on this blog – click here
DEX.INI at Dynamics Confessor blog – click here
DEX.INI at Developing for Dynamics GP blog – click here

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


MVP Frank Hamelly on Account Rollups and Organizing SmartLists

May 29, 2009

After a long writing hiatus, MVP Frank Hamelly on his gp2themax blog explains how to setup the often overlooked Accounts Rollup feature which he describes as “not as tasty as fruit rollups, but equally satisfying”.

Frank also explains how to use Microsoft Dynamics GP’s Navigation Pane to organize SmartLists for the end-users. His narrative is based on a requirement posed by the client at an actual project we both had the opportunity to work and collaborate on.

Please be sure to read Frank’s articles as you will find a lot of interesting things from his personal experience.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


How to create a smartlist to show invoices without a physical PDF document

May 6, 2009

Not too long ago, I answered a pretty interesting question on the Dynamics GP newsgroup. The user’s company images signed delivery tickets and store these as PDF documents on a shared location on their network. The PDF document is named after the corresponding invoice number in Dynamics GP, for example, if the invoice number is INV010001, the corresponding image of the delivery ticket would be stored as INV010001.PDF

The user wanted to know if it was possible to create a smartlist to show invoices without a scanned PDF image of the delivery ticket.

Solution

When you think of this problem from the eyes of a user, it seems almost impossible to write a SmartList that would produce the results wanted by the user. But when you breakdown the problem, the real issue is, how do we create a SQL Server view that is able to show whether a file exists for a specific Dynamics GP invoice document.

The solution is to create a SQL Server user-defined function (UDF) that is able to return whether a file exists or not. Then we can incorporate this UDF into our view query. Since views can be addressed from SmartList Builder then we are good to go.

First the UDF.

dbo.IsFileExist


-- Created by Mariano Gomez, MVP

-- Check if the UDF exists and drop
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].
[IsFileExist]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION dbo.IsFileExist
GO

-- Create the UDF
CREATE FUNCTION dbo.IsFileExist(@Path VARCHAR(200), @FileName VARCHAR(200))
RETURNS INT
AS
BEGIN
DECLARE @FullPath VARCHAR(200);
DECLARE @FileExists INT;

-- Check for the back-slash at the end of the path string
IF RIGHT(RTRIM(@Path), 1) '\'
SET @Path = RTRIM(@Path) + '\';

-- concat the path with the file name
SET @FullPath = RTRIM(@Path) + RTRIM(@FileName);

-- run the xp_fileexist system stored proc to retrieve wheter the file exists or not
EXEC master.dbo.xp_fileexist @FullPath, @FileExists OUT;
RETURN @FileExists;
END;
GO

GRANT EXECUTE ON dbo.IsFileExist TO DYNGRP
GO

The above UDF uses the undocumented system stored procedure xp_fileexist which, when executed in its native form, will display information about the file passed in as a parameter. For example:


exec master..xp_fileexist 'c:\boot.ini

… produces the following results:


File Exists File is a Directory Parent Directory Exists
----------- ------------------- -----------------------
1 0 1

(1 row(s) affected)

Now that we have the UDF function in place, we can prepare the view to be used with SmartList Builder.

dbo.vShowSOPAttachments


-- Created by Mariano Gomez, MVP
CREATE VIEW dbo.vShowSOPAttachments AS
WITH SOP_CTE (SOPNUMBE, SOPPDF) AS (
SELECT SOPNUMBE, RTRIM(SOPNUMBE) + '.PDF' FROM SOP10100
UNION ALL
SELECT SOPNUMBE, RTRIM(SOPNUMBE) + '.PDF' FROM SOP30200
)
SELECT SOPNUMBE, SOPPDF, dbo.IsFileExist('C:\', SOPPDF) AS [File Exists] FROM SOP_CTE;
GO

GRANT SELECT ON dbo.vShowSOPAttachments TO DYNGRP

Note the call to the dbo.IsFileExists UDF. With the view in place you can follow standard SmartList Builder procedures to implement and deploy the view. For information managing security and changes to SmartList Builder smartlist click here.

Until next post!

MG.-
Mariano Gomez, MIS
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/


Changing SQL Server views for SmartList Builder smartlists

January 22, 2009

One of the valuable tools that Microsoft has released is SmartList Builder for Dynamics GP. The concept is very simple: if the standard out-of-the-box Smartlist does not cut it for you, well, you build your own! Furthermore, SmartList Builder does allow for some nifty stuff, such as working with Microsoft SQL Server views and (custom) tables. Custom tables are not the focus of this article.

If you can’t get your smartlist done with all the SmartList Builder query building capabilities, then create your own views of the data in Microsoft SQL Server, grant access to them in SQL Server, then, grant security to them in SLB, and use them.

But what if after deploying your smartlists, users request more columns of information that you did not include in your original SQL Server views, and now you need to make those changes and affect your previous work of art?

Solution

The solution may be easier than you think, but please do not delete your existing SmartList Builder and recreate all that work again! It’s not necessary! These simple steps will illustrate how to change your SQL Server views and make these changes affect your existing SmartList Builder objects.

1) First, create your SQL Server view (If you already have a view skip to step 5), i.e:


/* Mariano Gomez, MVP
Sample code to illustrate changes in SQL Server view-based smartlist
*/
IF OBJECT_ID('dbo.querySomething') IS NOT NULL
DROP VIEW dbo.querySomething
GO

CREATE VIEW dbo.querySomething AS
SELECT JRNENTRY, TRXDATE, ACTINDX, DEBITAMT from GL20000
GO

GRANT SELECT ON dbo.querySomething TO DYNGRP
GO

NOTE: This is a trivial example to illustrate the solution. Views should and must be used when you have exhausted all possibilities in SmartList Builder.

2) Grant security to the new object in SmartList Builder in GP. Go to Microsoft Dynamics GP > Tools > SmartList Builder > Security > SQL Table Security

3) Create a smartlist with the new view, marked all columns in the Default list

4) Build the smartlist by going to Microsoft Dynamics GP > SmartList. Click on Yes to build SmartList.

5) Now, go back to SQL Server Management Studio and add a new column to the view, as follows:


/* Mariano Gomez, MVP
Sample code to illustrate changes in SQL Server view-based smartlist
*/
ALTER view [dbo].[sampleGLforSLB] AS
SELECT JRNENTRY, TRXDATE, ACTINDX, DEBITAMT, CRDTAMNT from GL20000
GO

NOTE: In this case I added the CRDTAMNT (credit amount) column.

6) Go back to SmartList Builder, open the smartlist and click on the Edit Selected Table button to open the Add SQL Table window.

All that’s needed is to click on the Save button, nothing else! Surprisingly, this is not documented anywhere in the manuals. Now the CRDTAMNT column shows up in the fields list on the right pane. Then click on the Default check mark to add to the default list of columns to be displayed, or manually add the column in your smartlist after rebuilding with the changes.

7) Now, click on the Save button in the SmartList Builder window.

8) Open SmartList to build the modified smartlist with the new changes.

Hope these simple steps help you with modifying your views and getting your SmartList Builders to work smoothly with the changes.

Until next post!

MG.-
Mariano Gomez, MIS, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/