RKL eSolutionsBlogs

Disable Auto Load of Business Insights Explorers and Lookup Views in Sage 500 ERP

 

Many customers experience issues with performance and often times they do not take advantage of a simple change that would benefit the overall Sage 500 ERP system performance.  This change applies to the default settings for BIEs and Lookup Views to prevent auto-loading data when launched.  In many cases, this is a huge benefit as some views/lookups would default to return thousands, hundreds of thousands or in extreme cases millions of rows.  The likelihood that the user truly needs all of this data is typically low.

Use of Filters

It would be far more efficient for the user to first identify filter criteria prior to searching for a result data set.  This in turn would result in only a subset of records being returned to the user.  Below is an example of an explorer view where the user has provided this filtering criteria.

A training tip that comes out of this:  You will want to ensure that you let users know that they are not going to get data without clicking the refresh or search button.  This is something they quickly adapt to.

Figure 1:  Example of a Business Insights View with a filter applied.  The result set is greatly reduced.

Sage 500 Business Insights View

Sage 500 User Tasks to Update Settings

Users have two tasks available to disable the auto load of data on launch for individual BIEs or Lookup Views.

Using the Sage 500 ERP Maintain Lookup Views task you can change each lookup view one-by-one to uncheck the “Load Data Immediately” setting.  Unchecking this option prevents the Lookup view from returning records until the user clicks the Refresh button to populate the grid. 

Figure 2: Maintain Lookup Views task

Sage 500 Maintain Lookup Views task

Figure 3: Example of the Lookup View after disabling Load Data Immediately (data does not auto load)

Sage 500 disabling Load Data Immediately

Figure 4: Clicking the refresh button on the Lookup View will display the data

Sage 500 Look up WIndow

Using the Sage 500 ERP Maintain Business Insights Views / Context Menus task you can change each BIE one-by-one to uncheck the “Allow Load at Startup” setting.  Unchecking this value will make it such that data will not auto load initially. 

Figure 5: Maintain Business Insights Views / Context MenusSage 500 Maintain Business Insights Views

System Administrator Update All BIE Views and Lookup Views

Now that we have reviewed how to make these changes via the user interface, there is a much more efficient way for a system administrator to update all BIEs and Lookup Views for your Sage 500 ERP installation.  This can be done by a user that has access to the database and can execute some basic update statements.  Below are the commands to accomplish these updates.  Please note that the values are different based on whether you want to disable auto-load in the BIEs or the Lookup Views. 

Sage 500 BIE Views and Lookup Views-1We Can Help You With a System Performance Review

Read Consultant John Puttmans Blog Series on Using Extended Events to Performance Tune in Sage 500.

Full Troubleshooting Series

Parsing an Extended Event Attach_Activity_ID into Component Parts with the String_Split Function

Using Extended Events to Performance Tune Sage 500 - Overview

IDENTIFYING THE SAGE 500 TASK NAME AS SEEN IN SQL SERVER

CREATE AN EXTENDED EVENTS SESSION FOR RECORDING ALL THE ACTIVITY UNDER A TASK

Running the XE Session and Brief View of Events

EXTRACT THE XE DATA FROM THE EVENT FILE AND LOAD IT INTO A SQL TABLE

ADD HELPER COLUMNS TO THE XE TABLE

ANALYZE THE DATA FOR LONG-RUNNING SINGLE QUERIES

Using Extended Events to Performance Tune Sage 500 – Part 7

Please reach out to the RKL Support team.

Tagged With: Sage 500, How-To
Russ Griffith

Written by Russ Griffith

Questions? Need Support?
Get in Touch