As part of a SmartList Search series (which can be found here), we will discuss advance search requirements AND using wildcards.
Wildcards are available in the SmartList search. They are a few that I know of… someone once told me there were others but I have yet to figure that out… if I have missed any, leave a comment below and TEACH ME!!!
Continue reading “SmartList Search Options – Advanced Search: Using Wildcards and ANY FIELD”
As part of a SmartList Search series (which can be found here), we will discuss how to use the search window for basic search requirements. Requirements can go beyond basic, don’t worry, we talk about that too!
Each SmartList can be filtered… and they can be filtered by multiple values. Each filter criteria is called a Search Definition. There are 4 available in the SmartList search. For example, if I wanted a list of GL Transactions for account 000-1100-00, I can do that, but I can also add additional criteria. How about GL Transactions for account 000-1100-00 AND only those transactions that posted on May 31, 2017. I can do that too.
Continue reading “SmartList Search Options – Using basic search requirements AND Field comparisons!”
As part of a SmartList Search series (which can be found here), we will discuss the search options available in the search window.
Search options are found at the bottom of the SmartList search window. There are two options available; Maximum Records and Search Type. In addition to these two options, I will also talk about the three buttons below the search options (Clear All, Columns, Order By).
Continue reading “SmartList Search Options – Maximum Records & Search Types (And/Or Logic)”
One of the MANY things that sets Dynamics GP apart from other ERP solutions… SMARTLISTS BABY!!!
Smartlists are a linear list of your data. You can customize them easily but out of the box, there are lists for all transactions, vendors, customers, and TONS of other things.
With the SmartList tool (free/included with Microsoft Dynamics GP) you have the ability to do some valuable filtering of your data… this filtering is made available through data ‘Search’ options.
A simple search example would be, show me all the transactions for Vendor XYZ… and BOOM, there they are… but what if your search is a little more complex that that? Let’s take a look at the options for basic and advanced search functionality.
Continue reading “GP #LifeHacks 163: Smartlists gone WILD!! Search Functionality explained”
Dynamics GP offers a very good tool to create your own Smartlists… Smartlist Designer.
One of the cool things users can do is incorporate custom SQL views into Smartlist through SmartList designer… this can be a HUGE time saver and really gives you the ability to get data you would normally not be able to get in a normal Smartlists.
Below are the steps needed to add a SQL View to your Smartlists.
Continue reading “GP #LifeHacks 137: Add a SQL View to Smartlist using Designer”
As year end quickly approaches, I wanted to share with my followers a very helpful #GPLifeHack that turns your year end wage file data into a Smartlist for easy auditing and review!
There is an ‘edit’ report for W2’s but it’s in long form and difficult to review – plus it is not easily exported to Excel. What exports easily to Excel? SMARTLISTS!!! YEAH THEY DO!
Let’s make this year-end process easier by creating a Smartlist for year end W2 data!
Continue reading “GP #LifeHacks 119: Year End Wage Data in a Smartlist using a SQL View”
Year End is a busy time, especially for those using Dynamics GP Payroll! Below is a SQL Script that can pull the year end wage file data into a smartlist! COOL!!
It is built to pull in records for federal, state, local, special box and the other box. Because of this, employees may have multiple rows if the employee has multiple states, special boxes, or other box data. Using a pivot table cleans this right up! Check it out!
CREATE VIEW [dbo].[Year_End_Wage_Data_Payroll] AS -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ -- Year End Wage Data -- Run Against Company Database -- Only provides data populated by the Year End Closing Routine -- Visit http://lifehacks365.com for more cool GP Stuff! -- Updated by: Shawn Dorward - As of 01/14/2019 -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ SELECT A.RPTNGYR Year1, A.LASTNAME Last_Name, A.FRSTNAME First_Name, A.EMPLOYID Employee_ID, A.ADDRESS1 Address_1, A.ADDRESS2 Address_2, A.Address3 Address_3, A.CITY City, A.STATE State, A.ZIPCODE Zip_Code, A.WGTPCOMP Gross_Wages, A.FEDITXWH Federal_Tax_Withheld, A.SSECWAGS SS_Wages, A.SSTXWHLD SS_Tax_Withheld, A.MCRWGTPS Medicare_Wages, A.MDCRTXWH Medicare_Tax_Withheld, B.STATECD State_Tax_Code, B.STATEWGS State_Wages, B.STATINTX State_Tax_Withheld, C.LOCLCODE Local_Tax_Code, C.LOCLWGES Local_Wages, C.LCLINTAX Local_Tax_Withheld, D.SPCLCODE Special_Code, D.SPCLBNUM Special_Box_Number, D.SPCLAMNT Special_Amount, E.OTHRCODE Other_Code, E.OTHBXNUM Other_Box_Number, E.OTHRAMNT Other_Amount --Payroll Year End Table FROM UPR10101 A -- Adding Payroll Year End State Table left outer join UPR10105 B on A.RPTNGYR = B.RPTNGYR and A.EMPLOYID = B.EMPLOYID -- Adding Payroll Year End Local Table left outer join UPR10106 C on A.RPTNGYR = C.RPTNGYR and A.EMPLOYID = C.EMPLOYID -- Adding Payroll Year End Special Table left outer join UPR10104 D on A.RPTNGYR = D.RPTNGYR and A.EMPLOYID = D.EMPLOYID -- Adding Payroll Year End Other Table left outer join UPR10107 E on A.RPTNGYR = E.RPTNGYR and A.EMPLOYID = E.EMPLOYID go grant select on [Year_End_Wage_Data_Payroll] TO DYNGRP
