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


Step 1: Create a SQL View and pull this into SmartList Builder
Step 1A: Start the Support Administrator Console, Microsoft SQL Query Analyzer, or SQL Server Management Studio. To do this, use one of the following methods depending on the program that you are using.

Method 1: For SQL Server Desktop Engine
If you are using SQL Server Desktop Engine (also known as MSDE 2000), start the Support Administrator Console. To do this, click Start, point to All Programs, point to Microsoft Administrator Console, and then click Support Administrator Console.

Method 2: For SQL Server 2000
If you are using SQL Server 2000, start SQL Query Analyzer. To do this, click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.

Method 3: For SQL Server 2005
If you are using SQL Server 2005, start SQL Server Management Studio. To do this, click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

Method 4: For SQL Server 2008
If you are using SQL Server 2008, start SQL Server Management Studio. To do this, click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.

Step 1B: Run the following script against the Dynamics database:
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

Step 2. Grant Security to the SQL View by doing the following:
a. Click Microsoft Dynamics GP, point to Tools, point to SmartList Builder, point to Security, and then click SQL Table Security.
b. Check the box for Dynamics under Databases, and then 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. Click Microsoft Dynamcis GP, point to Tools, point to SmartList Builder, and then click 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 then click SQL Server Table.
d. In the Add SQL Table window click Dynamics, and then click the Views radio button.
e. Click Req_History_Doc (or whatever you named the View).
f. Click the plus sign (+) for Key Fields, and then click Field: Requisition_ID
g. Click Save.
h. On the SmartList Builder window select the fields you want on the SmartList, and then check the corresponding Default checkbox.
i. Click Save.

Step 4. Include the changes in SmartList.a. Click Microsoft Dynamics GP, and then click 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 receive a message you could have SmartList already open. Close SmartList and re-launch.
c. In the SmartList Window expand Purchasing, expand the name of your SmartList. and then click the SmartList.