Design an inquiry based on inventory data
/visma-net-erp/help/customisation/manage-generic-inquiries/design-an-inquiry-based-on-inventory-data
section
You can easily create inquiry windows by using the Generic inquiry (SM208000) window.
2026-02-19T17:09:06+01:00
# Design an inquiry based on inventory data
You can easily create inquiry windows by using the Generic inquiry (SM208000) window.
For more information: [Generic inquiry (SM208000)](/visma-net-erp/help/customisation/customisation-windows/generic-inquiry-sm208000/).
You can easily create inquiry windows by using the [Generic inquiry (SM208000)](/visma-net-erp/help/customisation/customisation-windows/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
|Site ID|Item ID|ABC code|Pending price|Last price|Pending cost|Last cost|Replenishment parameters|
|---|---|---|---|---|---|---|---|
|RETAIL|Blinds 100mm X1000mm|A|12.5|10|7|6|-|
|WHOLESALE|Blinds 100mm X1000mm|A|12.5|10|7|6|-|
### The PX.Objects.IN.INSiteStatus (SiteStatus) table contains the following data
|Item ID|Subitem|Description|Site ID|Qty. avail.|Qty. on hand|Qty. not avail.|
|---|---|---|---|---|---|---|
|Z00B00250|0 (black)|Blinds 100mmX1000mm|RETAIL|500|100|0|
|Z00B00250|0 (black)|Blinds 100mmX1000mm|WHOLESALE|10|1100|0|
|LCS000011|0 (black)|LSC1 monitor|RETAIL|36|36|0|
|LCS000011|1 (white)|LSC1 monitor|RETAIL|36|36|36|
|LCS000011|2 (brown)|LSC1 monitor|RETAIL|0|0|0|
|LCS000011|0 (black)|LSC1 monitor black|WHOLESALE|8805|64|64|
|LCS000011|1 (white)|LSC1 monitor|WHOLESALE|-300|-300|0|
### The PX.Objects.IN.InventoryItem (InventoryItem) table contains the following data
|Item ID|Subitem|Description|Base unit|Base price|Evaluation method|GL accounts|VAT category|
|---|---|---|---|---|---|---|---|
|Z00B00250|0 black|Blinds 100mmX1000mm|piece|10|0|-|-|
|LCS000011|0 (black)|LSC1 monitor|piece|200|0|-|-|
|LCS000011|1 (white)|LSC1 monitor|piece|250|0|-|-|
|LCS000011|2 (brown)|LSC1 monitor|piece|200|0|-|-|
### The PX.Objects.IN.INSubitem (Subitem) table contains the following data
|Subitem ID|Group mask|Description|Created by|
|---|---|---|---|
|0|-|Black|administrator|
|1|-|White|administrator|
|2|-|Brown|administrator|
## Create a new inquiry
1. Go to the [Generic inquiry (SM208000)](/visma-net-erp/help/customisation/customisation-windows/generic-inquiry-sm208000/) window.
1. In the **Inquiry title** field, type a name for the inquiry, such as `Availability by warehouse`.
1. In the **Sitemap title** field, type a title, which is the name of the inquiry.
1. 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.
1. Type `2` in the **Arrange parameters in x columns** field.
1. Leave the **Select top x records** field blank, so you can display all the results.
1. Optional: In the **Records per page** field, specify the number of records you want to see per page.
1. Optional: Select **Number pages** if you want to give each results page a page number.
1. On the **Tables** tab, click anywhere below the last row to add a new row.
1. In the **Table name** column, search for and select INSiteStatus ( **PX.Objects.IN.INSiteStatus** ).
1. In the **Alias** column, type an easy-to-use name (such as `SiteStatus` ).
1. Repeat Steps 7 through 9 for each table you need for your inquiry.
1. 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.
1. 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.
1. 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.
1. 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.
1. In the **Name** column, specify the name of the inquiry parameter.
1. In the **Display name** column, type `Warehouse`; this is the name of the element the user will use to select a particular warehouse.
1. 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.
1. Select the **From schema** check box and select one of warehouses as the default value.
1. 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.
1. Click **** to append a new row.
1. Type `Active` in the **Name** column.
1. Specify a check box as the schema.
1. Type `Active Only` as the display name.
1. Type `=1` as the default value for the check box.
1. 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.
1. 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.
1. 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.
1. 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**.
1. 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.
1. 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.
1. In the **Object** column, select the DAC that contains the particular column.
1. In the **Data field** column, select the particular DAC column.
1. Specify a schema for the column. Generally, you select the schema used for the column in its DAC.
1. Optional: Specify the width of the column (in pixels) if the values are long strings.
1. 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.
1. 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.
1. Select the column of the specific table whose values should be ordered.
1. Select descending or ascending order.
1. 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.
1. In the bottom left corner, click **Edit menu**.
1. Click **+ Add menu item** in the top menu edit bar. The **Select windows** dialog is displayed.
1. Search for your inquiry title.
1. Select the check box to the left of your inquiry title and click **Add and close**.
1. 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
Tasks
Windows