Design an inquiry based on inventory data

For more information: Generic inquiry (SM208000).

You can easily create inquiry windows by using the Generic inquiry (SM208000) window. To design an inquiry, you need to have a general knowledge of data access classes (DACs), through which data can be extracted from the system database tables. For more information about DACs, see the Visma Net Customisation guide.

The following sections describe the process of creating an inquiry that shows the availability of active stock items.

About the design specifications

The inquiry will have a Selection area with the following elements:

• The Warehouse field where you can select a particular warehouse to view its stock items
• The Active only check box you can select to indicate that information about only active items (instead of all items) should be displayed

Choose one of the following ways to arrange these two boxes in the Selection area:

• One column: This will result in two lines, with the second element under the first one.
• Two columns: This will result in one line, with the second element to the right of the first one.

The table with inquiry results will display all the stock items available at the selected warehouse (or at all warehouses, if no warehouse has been selected). This table will have the Stock items title on the UI and the following columns: Item ID, Description, Subitem ID, Warehouse, Base unit, Base price, Quantity on hand, and Available quantity.

About the DACs to be used for the inquiry

All the information about stock items is available through the following basic DACs, which represent specific data from system databases as if it is arranged in tables:

PX.Objects.IN.ItemSite

Contains warehouse-specific information about items: standard cost history, price information, and replenishment parameters.

PX.Objects.IN.INSiteStatus

Contains information about warehouses: the items stored there and their availability information.

PX.Objects.IN.InventoryItem

Holds general information about each stock item: General ledger accounts, subaccounts, kit status (whether the item is a kit), item class, price class, and lot/serial class. This DAC also provides information on the item’s cost, price, and VAT categories.

A simple representation of the sample data in these tables is shown below; the actual tables include more columns than are shown, and no data is shown for columns that are not used in the inquiry being designed in this topic. The table names specified in parentheses are aliases specified for the tables for the inquiry under design (see the Creating a new inquiry section of this topic).

The PX.Objects.IN.ItemSite (ItemSite) table contains the following data

The PX.Objects.IN.INSiteStatus (SiteStatus) table contains the following data

The PX.Objects.IN.InventoryItem (InventoryItem) table contains the following data

The PX.Objects.IN.INSubitem (Subitem) table contains the following data

Create a new inquiry

1. Go to the Generic inquiry (SM208000) window.
2. In the Inquiry title field, type a name for the inquiry, such as Availability by warehouse.
3. In the Sitemap title field, type a title, which is the name of the inquiry.
4. In the Location in the menu field, select Start, to give your inquiry a location. You can choose any location, because your inquiry is independent of structure and will always appear in the Custom views workspace.
5. Type 2 in the Arrange parameters in x columns field.
6. Leave the Select top x records field blank, so you can display all the results.
7. Optional: In the Records per page field, specify the number of records you want to see per page.
8. Optional: Select Number pages if you want to give each results page a page number.
9. On the Tables tab, click anywhere below the last row to add a new row.
10. In the Table name column, search for and select INSiteStatus ( PX.Objects.IN.INSiteStatus ).
11. In the Alias column, type an easy-to-use name (such as SiteStatus ).
12. Repeat Steps 7 through 9 for each table you need for your inquiry.
13. Click . Your inquiry is saved and receives a window ID. You will now find it in the Custom views work space. If you want to place your inquiry in a workspace of your choice, you can duplicate it.

Specify table relations

You need to specify relations between tables so that the system can generate SQL requests to get the required data from the tables involved. On the Relations tab, perform the following steps:

1. In the Table relations table, append a new row. Select SiteStatus as the parent table, left as the join type, and ItemID as the child table. To get the data from both tables for each item ID, do the following in the DataField links for active relations table:

   • Select ItemID in the Parent field column.
   • Select Equals in the Condition column.
   • Select ItemID in the Child field column.

2. In the Table relations table, append a new row. Select SiteStatus as the parent table, left as the join type, and SubItem as the child table. To get information on subitems of stock items, do the following in the DataField links for active relations table:

   • Select SubItemID in the Parent field column.
   • Select Equals in the Condition column.
   • Select SubItemID in the Child field column.

3. In the Table relations table, append a new row. Select SiteStatus as the parent table, left as the join type, and ItemSite as the child table. To get the data from both tables for each stock item stored in each warehouse, do the following in the DataField links for active relations table:

   • Select ItemID as the parent field that equals the ItemID child field.
   • In the next row, specify SiteID as the parent field that equals the SiteID child field.

4. Click .

Configure inquiry parameters

To configure the elements for inquiry parameters, do the following:

1. On the Parameters tab, click to append a row to the table.
2. In the Name column, specify the name of the inquiry parameter.
3. In the Display name column, type Warehouse; this is the name of the element the user will use to select a particular warehouse.
4. In the Schema field column, select a schema. If no schema is specified, a simple text field will be used on the inquiry window. We recommend that you select as a schema the Warehouse column from the respective table.
5. Select the From schema check box and select one of warehouses as the default value.
6. If you have chosen to arrange parameters in two columns, in the Column span field, type the number of columns the Warehouse text field will span.
7. Click to append a new row.
8. Type Active in the Name column.
9. Specify a check box as the schema.
10. Type Active Only as the display name.
11. Type =1 as the default value for the check box.
12. Click .

Specify conditions

By using the Conditions tab, you can specify the conditions that determine the data (records) to be displayed. The inquiry parameters used in conditions appear in square brackets; for example, [Warehouse] stands for the first parameter of the inquiry (the ID of the warehouse selected by the user), and [Active] designates the value of the Active only check box, which the user can select to view only active items.

For this example, we want to select stock items matching the following conditions, based on user selections:

• If a user selects a particular warehouse: Items stored at this warehouse
• If the user doesn’t select any warehouse: Items stored at all warehouses
• If the user selects the Active only check box: Items with the Active status
• If the user clears the Active only check box: Items with any status

To implement these conditions, perform the following steps:

1. Enter the following: a single opening parenthesis, the SiteID column of the SiteStatus table ( SiteStatus.SiteID ) in the Data field column, the Equals condition, [Warehouse] as Value 1, and Or as the operator.
2. In the next row, enter [Warehouse] in the Data field column, Is null as the condition, and a single closing parenthesis. Select And as the operator to join another condition.
3. In the next row, enter the following: double opening parentheses, [Active] in the Data field (the value of the Active only parameter), the Equals condition, =1 as the Value 1, a single closing bracket, and And as the operator.
4. In the next row, select InventoryItem.ItemStatus ( ItemStatus of the StockItem table) as the data field, the Equals condition, the From schema check box, Active as Value 1, a single closing parenthesis, and the operator Or.
5. In the next row, select [Active] as the data field, select Equals as the condition, type =0 as the Value 1, and add a single closing parenthesis.
6. Click .

Configure the results area

On the Results grid tab, select the column from the involved DACs to display data. To add a column to the resulting table, perform the following steps:

1. Click to add a new row.
2. In the Object column, select the DAC that contains the particular column.
3. In the Data field column, select the particular DAC column.
4. Specify a schema for the column. Generally, you select the schema used for the column in its DAC.
5. Optional: Specify the width of the column (in pixels) if the values are long strings.
6. Select the Visible check box if you want this column to be visible in the table by default. Clear the check box if the column will be an optional column that can be brought up by users as needed.
7. Click .

Sorting results

To sort the results, select one column and specify how the values should be ordered in the respective column. To do this, on the Sort order tab, do the following:

1. Click to append a new row.
2. Select the column of the specific table whose values should be ordered.
3. Select descending or ascending order.
4. Click .

You can speed up a generic inquiry if you define a custom sorting order that optimises a database query. For example, such order may include less fields than a default sorting order, which contains keys for all tables added in the generic inquiry.

Preview the inquiry

To preview the inquiry you have created, click View inquiry on the window toolbar. To return to the Generic inquiry window, click Back (or the equivalent action) on the toolbar of your browser.

Add the new inquiry to a workspace you choose

In Visma Net, user-defined inquiries are automatically added to the workspace called Custom views. You can also add tem to a workspace of your choice, for example you can add customer ledger-specific inquiries to the Customer ledger workspace.

1. Go to the workspace you want to add the inquiry to.
2. In the bottom left corner, click Edit menu.
3. Click + Add menu item in the top menu edit bar. The Select windows dialog is displayed.
4. Search for your inquiry title.
5. Select the check box to the left of your inquiry title and click Add and close.
6. In the bottom left corner of the window, click Exit the Edit menu. The inquiry will now be displayed both in your selected workspace and in Custom views.

Related pages

Concepts

• About side panels

Tasks

• Enable the side panel

Windows

• Generic inquiry (SM208000)