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 Last Modified Field for Smart Refresh
In order to retrieve only "new" data, the inquiry must include a last-modified field. Velixo Classic automatically detects whether a field qualifies as a Smart Refresh candidate based on specific naming and type rules. If no qualifying field is found, the Smart Refresh option will appear greyed out.
Field Detection Rules
Velixo Classic (and NX) will recognize a field as a Smart Refresh candidate only when both of the following conditions are met:
-
Field name — The name must contain the substring "modif" or "stamp" (case-insensitive). For example,
LastModifiedDateTime,LastModifiedOn,LastModifiedDate, orTimeStampwould all be detected. A name likeModDateTimeNXTestwould not be detected because it contains neither "modif" nor "stamp". -
Field type — The field type must be DateTime, DateTimeOffset, or Binary.
Where the Field Name Comes From
Velixo reads the field name from the Caption column in your Acumatica Generic Inquiry settings. If the Caption is left blank, Velixo falls back to the Data Field column instead.
Recommendation: Setting an explicit Caption is more reliable than leaving it blank. When Caption is not set, Acumatica may modify the actual field name in the OData feed (for example, to avoid naming conflicts when fields are sourced from different tables or views), which can cause Smart Refresh detection to fail unexpectedly.
Examples of Working Captions
The following Caption values will be detected as Smart Refresh candidates (because they contain "modif" or "stamp"):
-
LastModifiedDate -
LastModifiedOn -
LastModifiedDateTime -
TimeStamp
If the Caption is left blank and the underlying Data Field name contains "modif" or "stamp" (e.g., GLTran_lastModifiedDateTime), it will also be detected.
Important: Full Refresh Required After Changes
After changing a Caption in the Acumatica GI settings, you must perform a Full Refresh in Velixo before the updated field name is picked up. Until a Full Refresh is done, Smart Refresh will continue to reference the previous field name and may produce errors.
Once Smart Refresh has been selected and the Key Fields and last-modified 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 your report will be returned by the grouped generic inquiry and can be shown in your report.