Setting up Smart Refresh for Generic Inquiries (Classic)

Applies to:

• Velixo Classic
• Acumatica, CEGID, Haufe x360, JAMIS Prime, MYOB Advanced

TABLE OF CONTENTS

• Overview
• Configuring Generic Inquiries Options
  • Specify Key Fields
  • Select Smart Refresh
  • Specify the LastModifiedOn Field
• Limitation with Grouped Generic Inquiries

Overview

Generic inquiries provide a powerful tool for retrieving data from your ERP.  Generic Inquiries offer a way to extract the specific data you need and present it for analysis.

Because generic inquiries can return a great deal of data, it can be important to control the amount of data that you attempt to transfer from your ERP to Excel.  The Velixo GIFILTER function, for example, helps with that control.

A further method to help with the performance of GI-based reports is to use the Velixo Smart Refresh feature for generic inquiries.  Under most conditions, rather than retrieving the entire dataset within a generic inquiry, we can configure the Velixo functions to only retrieve new data whenever a report is refreshed.

Configuring Generic Inquiries Options

The Generic Inquiries portion of the Options window allows us to modify those options that are specific to the Generic Inquiries we have referenced from within our workbook:

When we click the kebab (3 dots) icon next to our Generic Inquiry, and select Edit...

... Velixo will display the options associated with that specific Generic Inquiry:

As you can see, by default, generic inquiries are configured for Full Refresh (which will retrieve the full data set of the inquiry each time the report is refreshed).

Let's configure this inquiry for Smart Refresh.

Specify Key Fields

Our first step in setting up Smart Refresh is to specify the Key Fields for this inquiry.  This does require knowledge of the underlying data retrieved by the inquiry and how our inquiry is configured.

In our case, the key fields for the AR-Invoices and Memos inquiry are ReferenceNbr and Type.  Combined, these two fields can uniquely identify each record within the resulting dataset.

Each generic inquiry is different.  You may need to contact your system administrator for help in identifying the key fields for your specific inquiry.

Select Smart Refresh

Next, select the radio button for Smart Refresh:

Specify the LastModifiedOn Field

Notice that this added a new section to the window: Last Modified Field for Smart Refresh.  In order to have the ability to retrieve only "new" data, the inquiry must include a LastModifiedOn field of some sort.  These are a standard field in your ERP and are often named LastModifiedDateTime (but can vary, as is shown above).  If your generic inquiry does not include on of these fields, you will need to update the definition of the inquiry to include that field.

Once Smart Refresh has been selected and the Key Fields and LastModifiedOn field have been specified, we can click the OK button.

Now, when this specified generic inquiry is accessed from your report, instead of returning the entire dataset to your report, it will only return any new data (to be combined with the data previously retrieved) - making your reports as performant as possible.

Limitation with Grouped Generic Inquiries

When employing Grouping within a generic inquiry...

... there are certain things to keep in mind if you use that inquiry within your Velixo report.

Because of the way grouped generic inquiries are accessed, there are some differences when using them with the Smart Refresh feature. 

By its nature, a grouped generic inquiry considers all data and already returns a full set of that data.  

As a result, when a Smart Refresh is applied to a grouped generic inquiry, the inquiry considers the request as asking for new data only (with no consideration for the previous data).

So, when a Smart Refresh is done on a grouped generic inquiry, the inquiry will return only that new data.  There are times when this is convenient.  However, if what you are trying to accomplish is to ADD new data to your already existing data, this method will not provide that.

In order to combine previous data with new data from a grouped generic inquiry, Full Refresh is required.

In this way, all the data necessary for you report will be returned by the grouped generic inquiry and can be shown in your report.