To Receive CPE Credit Participate in entire webinar Answer - PDF document

Using & Creating SmartLists in Dynamics GP Charles Allen Senior Managing Consultant BKD Technologies callen@bkd.com May 25, 2017 To Receive CPE Credit • Participate in entire webinar • Answer attendance checks & polls when they are provided • If you are viewing this webinar in a group o Complete group attendance form with  Title & date of live webinar  Your company name  Your printed name, signature & email address o All group attendance sheets must be submitted to training@bkd.com within 24 hours of live webinar o Answer attendance checks & polls when they are provided • If all eligibility requirements are met, each participant will be emailed their CPE certificates within 15 business days of live webinar 1

Introduction Charles Allen • Senior managing consultant More than 27 years of experience • • Microsoft MVP Agenda • Introduction • SmartList • SmartList Designer • A Few More Items • Q&A 2

SmartList SmartList • Built-in SmartLists • SmartList Data Sources • Searching Data • Selecting Columns • Creating Favorites • Exporting to Excel • SmartList Options 3

Built-in SmartLists Analytical Company Field Service Financial Accounting* Fixed Assets Human Resources Inventory Manufacturing Payroll Project Accounting Purchasing Sales * Analytical Accounting SmartLists must be created using the AA Options window Data Sources • See Chapter 33 of the Dexterity Dynamics GP Integration guide for details on each SmartList’s data source • Example of information Object type value 13 Constant SMARTLIST_OBJECTTYPE_ACCOUNTSUMMARY Tables GL10110 – GL_Account_SUM_MSTR GL10111 – GL_Account_SUM_HIST GL00100 – GL_Account_MSTR Joins GL10110 left-outer join to GL00100 GL10111 left-outer join to GL00100 4

Searching for Data • Search is like a query • Up to four criteria can be entered • Two types of queries o Match All – all search criteria must be true for any records to appear o Match 1 or More – only one search criteria must be true • A column does not have to be displayed to use it in Search Searching for Data • Match All – all conditions must be true • Good when searching two different fields 5

Searching for Data • Match 1 or More – only one search condition must be true • Good use for searching for two values of same type of data Searching for Data • Use the Any Field option to search ALL columns, not just those displayed 6

Searching for Data • Click the search description under the SmartList to open the Search window Selecting Columns • Two options o Click the Columns button on the ribbon o Click the Columns button from the Search window • Columns can be rearranged • Columns can be renamed – for this SmartList only • Columns can be added/removed 7

Selecting Columns • Click the Columns button on the toolbar • Click Add • Type part of the column name in the Find Next button • Click on one of the fields & type the first letter of the column you’re seeking Selecting Columns – Renaming • Click the Columns button on the toolbar • Click on the column in the Display Name column • Type the name you want to display • Only valid for current SmartList 8

Selecting Columns – Change Order • Click the Columns button on the toolbar • Click on the column you want to move • Click the arrow buttons on the right side of the window Creating Favorites • Save search & column selections • Can be saved to be visible to … o You only o Your user class o The company o The system • Can be used as a Reminder on the home page 9

Creating Favorites • Set the Search criteria • Select the columns • Click the Favorites button • Name the favorite • Select the visibility • Click Add Modifying a Favorite • Make sure you click on the favorite you want to modify • Change the search & columns • Click the Favorites button • Click the Modify button 10

Removing a Favorite • Make sure you click on the favorite you want to remove • Click the Favorites button • Click the Remove button Exporting to Excel • Make sure the status of the SmartList is Completed BEFORE exporting data • Click the Excel button 11

Exporting to Excel • Exported file is in a temporary folder – save to the real folder • Formatting will vary based on GP version or setting in Dex.ini • GP 2013 introduced exporting numeric values with five decimal places • GP 2016 introduced exporting numeric values as numbers • Setting in Dex.ini offers SmartList formatting option Exporting to Excel • SmartList Solutions can be used to manipulate data when exporting to Excel • SmartList Solutions use Excel macros • Example: export data & then create subtotals • Example: export data & format data 12

Exporting to Excel SmartList Options • Used to set default information for SmartLists • Number of records • Columns to display • Order of columns • Default action for double-clicking 13

SmartList Options • Used to set default information for SmartLists • Number of records • Columns to display • Order of columns • Default action for double- clicking SmartList Designer 14

SmartList Designer • Creating a SmartList • Creating an Excel Report • Assigning Security Creating a SmartList • Click on the folder in which the new SmartList should be located • Click the New button on the toolbar 15

Creating a SmartList • Enter a name for the SmartList • Select the table columns for the SmartList • Create calculated columns • Create relationships • Create filters • Create Go To Creating a SmartList 16

Creating a SmartList • Select columns from any table visible within GP • Select columns from SQL views • Format numeric columns Creating a SmartList • Calculated fields can be created • Different types (Numeric, String, etc.) can be created • Use other functions • To add the field to the visible fields, click the OK button on the Calculated Fields window 17

Creating a SmartList • Create relationships between tables • Use Auto Link to create relationships or manually create them Creating a SmartList • Go Tos can be created to provide users the ability to view data in other locations 18

Creating a SmartList • Name the Go To • Select the information for the Go To Creating a SmartList • Multiple Go Tos can be created • A default Go To can be selected 19

Creating a SmartList • A SmartList can be created using an existing SmartList & SmartList favorite • Click on the item to be used & click the New button • The columns displayed in the existing SmartList or SmartList Favorite (2016 only) will be defaulted • Additional columns can be selected Creating an Excel Report • Excel Reports can be created using SmartList Designer • The SmartList must be based on a SQL view • Once a SmartList is created, use the Publish option in SmartList to create the Excel report 20

Creating an Excel Report Creating an Excel Report 21

Creating an Excel Report Assigning Security • New SmartLists • Use the Security Task Setup window to assign security for custom (or any) SmartLists 22

Assigning Security • Excel Reports • Must use SQL Server Management Studio to grant access to the SQL view being used • Modify rpt roles or create custom roles Security for Creating SmartLists • Who can use SmartList Designer? • ADMIN_SYSTEM_SL06* task • IT OPERATIONS MANAGER* role 23

SmartList Designer Security A Few More Items • What you can’t do with SmartList Designer o Summaries o Multicompany o Restrictions based on users o SmartLists including WORK, OPEN & HIST at the same time o Set formats for column types other than numeric types o Create Go Tos tied to specific records 24

Questions BKD Technologies Support Center • Microsoft Dynamics GP – 877.253.7778 GPsupport@bkd.com • Monday–Friday, 8 a.m.–5 p.m. Call Support Center toll-free 25

Stay Connected Follow dynamicsGPinsights.com us on Twitter Join us on LinkedIn At Dynamics GP Insights you can read articles, watch webinars & join View conversations videos on YouTube Continuing Professional Education (CPE) Credits BKD, LLP is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org The information contained in these slides is presented by professionals for your information only. Applying specific information to your situation requires careful consideration of facts & circumstances. Consult your BKD advisor before acting on any matters covered herein or in these seminars 26

CPE Credit • CPE credit may be awarded upon verification of participant attendance • For questions, concerns or comments regarding CPE credit, please email the BKD Learning & Development Department at training@bkd.com Charles Allen | Senior Managing Consultant | 713.499.4629 | callen@bkd.com 27