In my last post, GP2013 R2: Pain or Gain? (Part 1), I highlighted some of the new features you will find in Microsoft Dynamics GP2013 R2. In this post, I will take a look at another great feature called SmartList Designer.

Design a report using SmartList Designer

Both Microsoft Dynamics GP2013 and GP2013 R2 come with a built-in tool called SmartList Designer. With SmartList Designer, you can create or modify your own SmartList from scratch, or based on an existing SmartList, without using SmartList Builder. You can also use SQL views to create your SmartList designer report. Functionality to create calculated fields, formatting currency field relationship between tables, filter to restrict data, and add Go To menu options are also available with SmartList Designer.

To create your SmartList Designer report:

  • Go to Microsoft Dynamics GP > and Select SmartList.

m21

You will see the following screen.

m22

As an example, we will say you want to create Vendor List with EFT information using SmartList designer.

  • Click on New.

m23

  • In the List Name field, type the name of your SmartList, i.e. ‘Vendor with EFT’.
  • From the Product drop-down select ‘Microsoft Dynamics GP’.
  • From the Series drop-down, select ‘Purchasing’.

m24

  • Now click on Microsoft Dynamics GP.
  • Click on Tables.
  • Click on Purchasing.

m25

  • From purchasing select the following table: PM Vendor Master File.
  • Select the columns you want to see in your SmartList.
  • Once done click OK.

m26

  • Once you click OK you will see the following screen:

m27

  •  Select your SmartList and click Modify, as we also want to add the vendors’ EFT information.

m28

  • You will again get the Designer screen. This time click on Microsoft Dynamics GP.
  •  Click on Tables.
  • Click on Company.
  • Click Address Electronic Fund Transfer Master and select the fields you want to add in your report.

m29

As we are using two tables in this report, we need to define the relationship between those two tables based on a common field available in both tables.  In our case the common field is Vendor ID.

  • Click on first TableName column available under ‘Relationship’ grid and select the PM Vendor Master file.
  • From the FieldName column, select Vendor ID.
  • From next column select Left Join.
  • From the TableName2 field select ‘Address Electronic Funds Transfer Master’.
  • From the FieldName2 field select ‘Vendor ID’.
  • Click OK.

Execute your SmartList. You will get you vendor list including EFT information.

Question: This report is giving me all vendors with and without EFT information. Can I restrict this report to show me only my EFT vendor?

Answer: Yes, you can use the Filter option within SmartList designer to filter your report. To do so, follow these steps:

  • Select your SmartList and click on Modify.
  • Click on the FieldName column available under the ‘Filter’ grid. Select ‘Address Electronic Funds Transfer Master ID’.
  •  Under Condition, select ‘Is not’.
  •  Leave the ‘Vale’  field empty.
  • Click OK.
  • Run SmartList and you will get your restricted list.

Stay tuned for my next post!

Please contact us with any questions you have about GP2013 R2. We are here to help!

 


 

This GP Tip was written by:

Mansoor Lakhani
MBA(MIS), MCS, MCTS, MCBMSS, MCBMSP
Senior Business Solutions Consultant
Corporate Renaissance Group

Mansoor LakhaniMansoor Lakhani is an IT professional and Microsoft Certified Technology Specialist with a MBA degree in Management Information System and a Masters degree in Computer Science. Mansoor has over than 23 years of broad experience in several key technical areas, including Database Management, Management Information Systems, ERP Solutions & Implementations, Management Training, Business Intelligence,  LAN/WAN Networks, Server application and configuration, TCP/IP administration, Process implementation, System Analysis, System Development and Troubleshooting and End user Training, Mansoor has been a Senior Business Solutions Consultant, Microsoft Dynamics GP at Corporate Renaissance Group since 2005.