Enabling Auto-complete on Selectors

In Acumatica we have a very widely used control - Selector. This one allows user to pick a certain object from a potentially long list. In addition to providing a convenient look-up table selectors also allow for manual input - in this case user simply enters the key of the object they wish to chose. To permit faster data entry and reduce the chance for mistakes Acumatica selectors have auto-completion capability, which is very easy to enable.

First of all, assume that you have a selector for Credit Terms, which looks more or less like this:

In the ASPX page definition it is represented by the following code:

<px:PXSelector ID="edTermsID" runat="server" DataField="TermsID" AutoRefresh="True" DataSourceID="ds" />

In this state, if the user starts typing in the selector box nothing specific will happen until they press F3 on the keyboard or click the magnifying glass icon to open the look-up table or proceed to the next field. However, it is very easy to change this - just add the AutoComplete attribute to the selector definition:

<px:PXSelector ID="edTermsID" runat="server" DataField="TermsID" AutoRefresh="True" DataSourceID="ds" AutoComplete="True" />

Once this is done, the selector box will suggest possible choices. This not only means that user will know what to enter - they can easily navigate through the suggestions list using arrow keys on the keyboard and press Enter key to pick the one they look for.

Debugging Acumatica Applications and Inspecting Resulting SQL

When developing your application with Acumatica Framework you might want to inspect the SQL queries, which Acumatica generates from the BQL queries defined in your code. While this can be done with an SQL profiler, there is an easier option. When debugging your application in Visual Studio you can get a fair approximation of the queries standing behind each of your data views and even stand-alone BQL queries. Here's how.

First of all, you need to start debugging your application. If it’s hosted on IIS you do that in several easy steps. Launch Visual Studio with administrator privileges and open your solution. Then go to Debug menu and chose “Attach to process”:

This command will open a dialog letting you select a process to attach to. Here you should find the process of the IIS' application pool that runs your app. IIS pool processes are usually named w3wp.exe and the User Name of the process tells which exact pool it represents. If there are several w3wp.exe processes and you are not sure which one to pick, go to IIS Management Console and locate the pool hosting your app - the name of the pool should be in the User Name of the desired process in the "Attach to process dialog" (in my case that's "IIS APPOOL\ASP.NET v4.0 Classic"). If the User Name is not shown for the w3wp.exe processes that usually means that you are running Visual Studio without admin privileges - restart Visual Studio with appropriate rights. If you are having difficulties with locating w3wp.exe process in the list make sure the checkboxes under the list are checked ("Show processes from all users" and maybe "Show system processes") and also verify that your application is running (just load its login page in the browser).

Once you pick the desired process and click Attach, Visual Studio will go into debug mode - from there you can do lots of things. (You may find additional info about debugging Acumatica Framework applications in documentation. Visit Documentation > Acumatica Framework > Debugging Applications on Partner Portal or in any instance of Acumatica ERP)

The only thing we need to inspect a query is the query itself. These usually come in 3 flavors:

1. Data Views declared as public PXSelect<...> DataViewName variables on the graph level
2. Query variables declared like var query = new PXSelect<...>(this) in the methods of graphs
3. Queries executed without instantiation like var results = PXSelect<...>.Select(this)

For the options 1 and 2 you can easily get the idea of the SQL query that they will run against the database. With option 3 that's not that easy, but you can simply transform it to the option 2 without any impact on the structure of the program, performance or anything else.

To see the approximate SQL query behind a data view place a break point anywhere in a graph where you expect to arrive. Once the breakpoint is hit, in the Locals window (available only in Debug mode. If you can't see it go to Debug > Windows and pick Locals there) expand this (which refers to the current graph) and Raw View inside it. Under that node you should see all the member fields of your graph, including all the data views. For each data view visual studio will show a string representation in the Value column, which shows the query that you are looking for. You can easily copy-paste it to your favorite text editor and check whether it matches your expectations.

To make things cleaner you can also use Visual Studio's Watch window. Just add the name of the desired view in there and you'll have access to it's representation whenever you are in the corresponding graph:

In case you have a query stored in a local variable (option 2) everything is the same, except for the fact that you have to look for it not under this > Raw View, but just in the Locals window - there will be an entry with the name of your query variable.

Even though the queries that you will find this way look like real SQL, note that the actual queries executed by the framework might be slightly different. The ones that you see here serve only for reference purpose - if you need the exact ones, you'll have to use SQL Profiler.