Design an inquiry based on inventory data

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.

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

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.
    

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 .

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 .

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 .

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 .

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 .

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.