SmartList in Microsoft Dynamics GP is a gem. It’s wonderful! Talk about incredibly powerful reporting in simple to read rows and columns you can sort, filter, print and export to Excel. WIN!
However, that’s where it stops for a lot of people. Don’t stop there! You can do so much more with it!
Turn those SmartLists into a tool that’s more than just reporting. Use it for driving processes, shortcuts to other screens to create transactions or to raise exceptions or tasks that need to be done — all right from good ‘ol SmartList!
Then, if you’re really cool, add these SmartLists to your GP home screen that show in the Reminders section. One click access to things that need to be viewed, addressed, dealt with, whatever. YES!
The example case today: CREDIT HOLDS. This is the most FAVORITE THING IN THE WHOLE WIDE WORLD for any finance, sales or accounting professional. A custom SmartList is being created from a SQL view. Then a favorite is going to be created called “Customers on Credit Hold” that will be accessible from the user’s Reminders section on the GP home screen. From there, they can see all transactions on credit hold, view details and take action.
This process solution uses the following features in GP and two third-party addons:
- Sales Transaction Entry
- Sales Process Holds
- SmartList (obviously)
- SmartConnect from eOne
- SmartList Builder from eOne
- A SQL view
Scenario 1: Customer Exceeds Their Credit Limit and Process Hold Applied
When a new order is being entered and if the customer has exceeded their credit limit, you get the nice prompt: “This transaction exceeds the customer’s credit limit. Do you want to continue?”
As soon as the user hits “Yes”, GP has been configured to automatically place a process hold called “CREDIT” on the transaction.
(Thank you Microsoft for putting the stop sign back on the Sales Transaction Entry screen.)
Scenario 2: Customer is Placed on Hold
The specific requirements were to stop anything from shipping or being invoiced if the customer is placed on Hold AND allow for removing the process holds at a granular level.
Once the user checks the Hold checkbox on the Customer Maintenance screen and saves, SmartConnect kicks in and places a process hold on ALL pending sales transactions. This is a simple map that watches for any changes to the RM00101 table and checks for Hold to equal 1. If so, the map assigns the CREDIT process hold to all open sales transactions (order, invoice, return, back order).
Simple.
The SQL view to make the SmartList is simple too: every open sales transaction that has any process hold applied to it. Why any process hold and not just the CREDIT process hold? So the SmartList is reusable for other people besides the finance team! NICE!
SELECT SOPType, DocumentNumber, DocumentType, DocumentDate, CustomerNumber, CustomerName, PONumber, DocumentAmount, DocumentID, ProcessHoldID, IsRemoved, Remove, RestrictionType, RestrictionTypeCustomer, RestrictionTypeProcessHold, InsertButton, ProcessButton
FROM
(SELECT a.SOPTYPE AS SOPType, RTRIM(a.SOPNUMBE) AS DocumentNumber, CASE a.soptype WHEN 2 THEN 'Order' WHEN '3' THEN 'Invoice' WHEN 4 THEN 'Return' WHEN 5 THEN 'Back Order' END AS DocumentType, a.DOCDATE AS DocumentDate, RTRIM(a.CUSTNMBR) AS CustomerNumber, RTRIM(a.CUSTNAME) AS CustomerName, RTRIM(a.CSTPONBR) AS PONumber, RTRIM(a.DOCID) AS DocumentID, a.DOCAMNT AS DocumentAmount, RTRIM(b.PRCHLDID) AS ProcessHoldID, b.DELETE1 AS IsRemoved, 1 AS Remove, 2 AS RestrictionType, 4 AS RestrictionTypeCustomer, 16 AS RestrictionTypeProcessHold
FROM dbo.SOP10100 AS a WITH (nolock) INNER JOIN dbo.SOP10104 AS b WITH (nolock) ON a.SOPTYPE = b.SOPTYPE AND a.SOPNUMBE = b.SOPNUMBE
WHERE (a.SOPTYPE IN (2, 3, 4, 5)) AND (a.VOIDSTTS = 0))
AS ProcessHolds
After reading the query, I’m sure you’re asking yourself what does this part mean?
1 AS Remove, 2 AS RestrictionType, 4 AS RestrictionTypeCustomer, 16 AS RestrictionTypeProcessHold
Read on!
A new SmartList in SmartList Builder is created — I just called it “Process Holds” and the table is a SQL script (the view above).
HERE IS WHERE THE MAGIC STARTS. We could just stop here, save it and publish it but THAT’S NO FUN. We’re going to take this up a notch!
In the GoTo menu, we can add shortcuts to do stuff to the data returned in our SmartList! Look at all of the options!
Of course, to start, we want to give the option to view the transaction or the customer, so the built-ins for viewing a sales transaction and customer are used.
The user has two options for removing the hold: on the single transaction highlighted or all of the transactions showing for the customer listed. So, we’re going to create a GoTo that opens the Sales Holds Processing window and fills in the information for the user!
For a single transaction:
To break it down:
- Open the Sales Process Holds window
- Set the Process Hold ID field to the process hold showing on the screen
- Set the Add or Remove radio buttons to Remove (this is why there is “1 as Remove” in the SQL view!)
- Set the Document Range drop-down to Document Number (this is why there is “2 AS RestrictionType” in the SQL view!)
- Set the starting document number to the Document Number column in the SmartList
- Set the ending document number to the Document Number column in the SmartList
- Click the Insert >> button
For a group of transactions:
To break it down:
- Open the Sales Process Holds window
- Set the Process Hold ID field to the process hold showing on the screen
- Set the Add or Remove radio buttons to Remove (this is why there is “1 as Remove” in the SQL view!)
- Set the Document Range drop-down to Customer Number (this is why there is “4 AS RestrictionType” in the SQL view!)
- Set the starting customer number to the Customer Number column in the SmartList
- Set the ending customer number to the Customer Number column in the SmartList
- Click the Insert >> button
- Set the Document Range drop down to Process Hold ID (this is why there is “16 AS RestrictionTypeProcessHold” in the SQL view!)
- Set the starting process hold ID to the Process Hold column in the SmartList
- Set the ending process hold ID to the Process Hold column in the SmartList
- Click the Insert >> button
Since the search scope is so broad at the customer level, there’s another restriction added to look just for the process hold assigned.
Whew!
If the user wants to remove just one transaction from hold, they select GoTo and pick Remove Hold (This Transaction Only). Everything fills in about the selected transaction!
If the user wants to remove all of the customer transactions from hold, they select GoTo and pick Remove Hold (All Transactions for This Customer). Everything fills in about the selected customer!
All the user has to do is hit the Process button.
Wow. WOW. WOWWWWW!!! Look how easy it is for the user! One thing to mention again: this isn’t limited to just process holds related to credit issues. This can be used for ANY kind of process hold. Let SmartList take away some of the clicks and manual work to get things done.