Insights | SVA Consulting

Acumatica Tools for Improved Data Retrieval | SVA Consulting

Written by Andrea Hayes | Oct 12, 2021 7:42:11 PM

Are you finding yourself exporting data to Excel for analysis, or searching constantly in Acumatica to get the information you need? Acumatica has easy-to-use features that can optimize data retrieval and presentation.

During a recent webinar, the experts at SVA Consulting discussed how to optimize and expand the use of Acumatica to get more out of your generic inquiries. The intentional focus of the presentation was for the non-technical user. The purpose is to make data easy to access for users who don’t necessarily have a specialty in data science and who aren’t familiar with an SQL database.

Acumatica provides the full suite of integrated business management applications and there are many tools that allow for improved data mining and analysis. Specifically, the tools highlighted during this webinar were generic inquiry sorting and filtering, the Excel connection, and navigation panels.

Generic Inquiry Sorting and Filtering

The objective of the generic inquiry sorting and filtering tool is to allow you to personalize your data. The brilliance is that you can take advantage of these tools right at your fingertips using just the out-of-the-box generic inquiries you already have. No pricey add-ons are needed.

Before we discuss how to use the tool, let’s define generic inquiry. When we refer to the term generic inquiry, we are referring to a data jargon tool term for what resembles an Excel spreadsheet. Just like Excel, generic inquiries have columns of data and each of those columns has pieces of information such as date, order number when looking at sales orders, or customer ID and location when looking at customer data.

Within generic inquiries, there are numerous options for customization. One of the easiest things to do is to drag and drop column headers to change the order of those columns. To execute, when you click and hold the column header, arrows will appear that follow you along the line of columns so you can drop the particular column anywhere in the lineup. You can also eliminate column headers you don’t need and add additional headers. To do this, start by clicking the column configuration button in the upper left-hand corner. Then simply choose the available columns you would like in your inquiry and move them over to the selected columns and click ok. Vice versa to eliminate columns.

As for filtering, there are many options within any generic inquiry in Acumatica. No matter which column header you click on, there are filtering options and those options will change depending on what kind of data is in that column. One particular filtering option in Acumatica related to dates is using flexible parameters. Examples of flexible parameter filter options are week-start and week-end. This filter will change the data depending on the specific week you’re in so there is automatically fresh data for the new week. No more manual filtering is required.

One further example of customizing generic inquiries is the user-defined field. Creating a user-defined field on the customer generic inquiry will allow you to connect sales orders to the region of the customer, for example. If you right-click on a value anywhere in the generic inquiry, you can filter by the cell value and this allows you to perform a pivot against a certain characteristic. This can allow you to, from a sales management perspective, target certain regions for expansion and track sales orders within that region.

To save a filter, in the upper right-hand corner, there is a little floppy disk save option. If you save here, you can assign a filter name for easy access. When saving a filter, there is an option for a shared configuration that will allow the filter to be available to all of your colleagues. So anyone with access to that particular generic inquiry can now access that filter because it’s set up as a shared filter.

The Excel Connection

Another tool that provides easy analysis of data is the Excel connection option available in Acumatica. This tool is valuable for users who have a really high ability within Excel as it allows you to run pivots and charts with the Excel tool while automatically refreshing data from Acumatica. As a result, you are working with the latest and greatest updates to Acumatica automatically with no manual work required.

The first step is to set up an Excel/Acumatica connection. Once the initial connection is established, then you can select “Refresh All” and all new data will automatically populate in the Excel spreadsheet. Then you can easily perform an analysis of your fresh data by using a pivot or chart within Excel. Furthermore, once you add additional data in Acumatica and refresh the spreadsheet, your chart analysis will automatically be updated with the refreshed information.

For further instruction on the Excel connection, there is documentation in Excel itself in the help system. Simply do a search in the help system and you can find further information on how to export transactions.

Navigation Panels

Navigation panels are a fantastic way to gather data all in the same place. Within Acumatica, there are a lot of relationships that exist among documents. For example, with a customer generic inquiry, there are a lot of pieces of data within Acumatica that relate to customers such as sales orders and contacts and the customer card itself. So how do you gather all of that data and put it in the same place for easy access? Navigation panels are the answer.

To start, click on the caret (^) in the upper right-hand corner and you will then be able to see related documents in Acumatica. Each one of the tabs will correspond to a separate navigation panel, bringing another piece of information related to the customer to view and displaying it on the right side of the screen. Available navigation panels within Acumatica are contacts and contact information, the customer card, dashboards (such as sales orders), invoices, opportunities, quotes, and cases.

Navigation panels aren’t just available with customer generic inquiries. You can also have the same concept with projects, invoices and memos, and even journal transactions if you wanted to do a more accounting-based navigation panel. It can also be used for a variety of purposes and interest levels. For example, from a logistics perspective, you can manage shipments and ensure nothing gets stuck in the process. Also from an executive level, you can watch sales orders as they trend up and down with certain customer segments.

To set up navigation panels, the first step is to select your base generic inquiry (before you begin, make sure you have the customizer user role). In the upper right-hand corner, select edit generic inquiry. Once you edit your generic inquiry, you don’t want to edit the out-of-the-box generic inquiry. Remember to edit a copy of it because you don’t want to make any changes you can’t reverse to the actual customer list. If you are using a copied version, it’s easy to just start over and try something again if you make a mistake.

The generic inquiry page can be a little intimidating because there are a lot of options as it relates to the data science element of Acumatica. But setting up a navigation panel is easy as the only tab you need to be concerned with is the navigation tab. In that tab, the Navigation Target is the data you are connecting in the navigation panel. The Navigation Parameters is the connection point. Acumatica needs some information to know what it should show on the navigation panel (an example of a Navigation Parameter is account code). In general, you want your parameter to exactly match what your field is. You can also customize the panel by selecting the window mode and the icon symbol.

Words of Caution

As you are customizing your tools within Acumatica, the experts at SVA Consulting have three areas of caution you should keep in mind:

  • Don’t change the columns in the Excel export
  • Understand security roles and access to the generic inquiry page
  • Uncheck “Make visible to UI” on the generic inquiry page because it’s a possible security risk if it’s checked
PRO TIP
Any time you make a change to a generic inquiry, always check your work. Hit the view inquiry button and then you can see the changes you have made on your generic inquiry.

Conclusion

As you are working through your own version of Acumatica and if you are not seeing side panels or even some of the dashboard widgets, it’s because these features are part of the new functionality that Acumatica adds throughout periodic releases. So if you still require these new features, it’s simply migrating to the newest version of Acumatica to get that functionality.

Lots of information can be connected through Acumatica such as customer data, sales orders, inventory management, projects, CRM, and core accounting. The general concept is getting more information all in the same place that’s easy to access, even for the non-technical user.

If you have questions or require assistance with utilizing these Acumatica tools, contact SVA Consulting at technicalsupport@svaconsulting.com. We would be happy to help.

© 2021 SVA Consulting