Creating a SmartList through SmartList Builder that will include the Status field values of the Requisition Management Audit History Document table

Article
08/30/2011

Have you ever wanted to create a SmartList on the Requisition Management tables that would include the values of the Status field in the Requisition Document table? In this post I will provide you some quick ways to do this.

Step 1: Create a SQL View and pull this into SmartList Builder.

Here is a simple SQL view I created to pull the ReqMgmtAuditHistoryDocument, ReqMgmtAuditHistoryLines, and GL00105 (Account Index Master).

*I included the Account Number from the GL00105 in my View. You could just pull the Account Index and follow Jake's steps in the following Blog post to convert the Account Index to the actual Account Number blogs.msdn.com/b/dynamicsgp/archive/2011/08/23/quick-tips-smartlist-builder.aspx

CREATE VIEW Req_History_Doc
AS

SELECT RD.DocumentID Requisition_ID,
       RL.ItemDescription Title,
       RD.OriginatingName Created_by,
       RD.CreatedDate Created_Date,
       CASE RD.Status
              WHEN 1 THEN 'Created'
              WHEN 2 THEN 'Pending Final Approval'
              WHEN 3 THEN 'Rejected'
              WHEN 4 THEN 'Pending Transfer to PO'
              WHEN 5 THEN 'Transferred to PO'
              WHEN 6 THEN 'Voided'
        END Status,
       RD.LastToModifyName Modified_by,
       RD.ModifiedDate Modified_Date,
       RL.Item Item_Number,
       RL.Vendor Vendor,
       RL.PurchaseOrderDocument PO_Number,
       RL.PurchaseAccount Account_Index,
       GL.ACTNUMST Account,
       RL.RequiredBy Required_by_Date,
       RL.UnitPrice Price,
       RL.Quantity QTY,
       RL.UnitOfMeasure Unit,
       RL.ExtendedPrice Extended_Price

FROM   ReqMgmtAuditHistoryDocument RD

LEFT OUTER JOIN
ReqMgmtAuditHistoryLines RL
ON RL.DocumentID = RD.DocumentID
Join TWO..GL00105 GL on RL.PurchaseAccount = GL.Actindx

You will run this against the Dynamics Database.

Step 2. Grant Security to the SQL View by doing the following:
a. Go to MDGP | Tools | SmartList Builder | Security | SQL Table Security.
b. Check the box for Dynamics under Databases and select the Views radio button. (Dynamics should be marked by default along with the Company database).
c. Scroll down and check the box for Req_History_Doc.
d. Click Ok.

Step 3. Create the SmartList in SmartList Builder.
a. Go to MDGP | Tools | SmartList Builder | SmartList Builder.
b. Enter information in the following fields:
SmartList ID
SmartList Name
Item Name
Product: Microsoft Dynamics GP
Series: Purchasing

c. Click the plus sign (+) for Tables and select SQL Server Table.
d. In the Add SQL Table window highlight Dynamics and select the Views Radio button.
e. Select Req_History_Doc (or whatever you named the View).
f. Click the plus sign (+) for Key Fields and select Field: Requisition_ID
g. Click Save.
h. On the SmartList Builder window select the fields you would like on the SmartList and check the corresponding Default checkbox.
I. Click Save.

Step 4. Include the changes in SmartList.
a. Go to SmartList (MDGP | SmartList).
b. Click Ok to the following message:

SmartList Builder has detected changes to be made. Do you want to make these changes now?
*Note if you do not get a message you could have SmartList already open. You will need to close SmartList and re-launch.

c. In the SmartList Window expand Purchasing | Expand the name of your SmartList | Click the SmartList.

Enjoy,

Andy S.

Creating a SmartList through SmartList Builder that will include the Status field values of the Requisition Management Audit History Document table

Additional resources