Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday: SQL Views vs SQL Script in SmartList Builder


With the release of the new features in SmartList Builder, there is a new option to add SQL Script as a table type. The question has come up as to how you should choose between using a SQL View or a SQL Script. There isn’t a standard set of rules as to which one to use when, so I just wanted to outline some of the differences to help you determine which will be the best option for you in each scenario.

  • Go To: Go To’s will only work with the SQL Script. When entering SQL Script, you can select your Key Field. When you add a view, you cannot select the Key Field. The Key Field is needed for the Go To’s to function.
  • Ease of Use: While both require knowledge of SQL Scripting, using a View also requires knowledge of SQL permissions. With a view you need to be sure you give the GP users appropriate access to the views you create. Both the SQL Views and SQL Script will require this if you are using tables outside of the GP tables.
  • Exporting:  If you are using the Export/Import option in SmartList Builder, the SQL Script option is included in the Export. The SQL View option will export as far as the setup goes, but you need to be sure that you script out the view and create it in the new database before you import the SmartList Builder setup.
  • Performance: There can be differences in performance with each option depending on the scenario. The performance can depend on how the SQL Script or View is written as well as how it is linked to other tables in the SmartList Builder setup. This option may be something where you have to test both options to see which one is the best option for that list.
  • Functionality: The SQL Script option does have limitations to what you can write in the statement. You can only use Select statements and you cannot use “–” to make comments. If you need to include something outside of the select statements, you would need to use the view option.

Interested in learning more about SmartList Builder? Feel free to email us at SLB@eonesolutions.com!


Leave a Comment





RECENT POSTS


Meet eOne Solutions at Directions EMEA 2023!
Connect with eOne Solutions at SuiteWorld 2023!
What is a Data Lake/Data Lake Management?
The Challenges & Benefits of Ad Hoc Reporting
What is Virtual Integration?

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release Salesforce Salesforce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.