Query Wizard - Examples

Copy/Move

Scenario: It is necessary to have the table SalesTable between the tables CustConfirmJour and DlvMode.

You can add the table SalesTable to the table CustConfirmJour. However, this would provide a structure in which tables are at the same level as the parent table which could cause issues in relation to joins and ranges.

A better approach is to have a nested structure and not have tables at the same level.

You can move the table DlvMode by using the Copy/Move button.

You can use the Arrow up and Arrow down to adjust the order of the output.

Group By

1. Navigate to Lasernet > Reports and select your query report.

2. Open the Query wizard and select Next to navigate to the Tables form.

3. Select the query or Subquery you would like to use Group by on, expand the Properties menu, and select Yes for the Group by field.

When you enable the Group by function on a Query, each field that is part of a Query with Group by enabled requires a Group by function.

Ensure to remove the RecId fields from your tables when using Group by. This is because using Group by on the RecId will cause the Connector to create a new group for every RecId it encounters in the data. Since RecIds are meant to be unique, you will get every line returned, and grouping will not occur properly.

Group by Function

The Group by function, Group by is applied to the following fields:

• InventTrans.ItemId

• InventDim.InventColorId

• InventDim.InventSizeId

• EcoResSize.DisplayOrder

The detail records for table InventTrans will be grouped for each unique combination of the GroupBy fields: ItemId, InventColorID, InventSizeID, and DisplayOrder.

The following is an example where multiple detail records with ItemID M0030, color White, size 6, and display order 106 on the left-hand side are grouped into a single detail record on the right-hand side.

Sum Function

The Sum function is applied for the grouping of InventTrans.Qty.

This will calculate the sum of the field Qty for each ItemId group.

Using the example where multiple detail records with Item ID M0030, color White, size 6, and display order 106 are grouped into a single detail record, the Qty fields for the two records on the left-hand side are summed in the Qty field on the right-hand side.

The RowNumber function is applied for the field InventDim.InventColorId.

The InventTrans.ActivityNumber field is used as a placeholder for the RowNumber function. A placeholder field does not return the field value, but the value defined by applying the Group by function.

RowNumber returns the number of rows within a partition. Numbering rows begin with 1 and start over again once InventDim.InventColorId changes (the OVER Partition by property). The row numbers are ordered by the OVER order by field, InventDim.InventColorId. This can be used for patterns in the Lasernet Developer application.

Using the example where a query is using Group by and contains two records with the Item ID M0030 and color White (as seen on the left-hand side), the RowNumber field provides a row number of 1 to the first record and 2 to the second (as seen on the right-hand side).

Group by 'Top Records'

It is possible to select TOP(X) including the option to prepare a dialog field and link the value from the dialog field with the TOP(DialogField).

To do so, follow these steps:

1. Create a Query containing the required output. In the following example, the fields ItemId and dataAreaId are used for the grouping, and the Qty is used for the sum.

All fields are selected from the table CustPackingSlipTrans, but any tables and fields could also be joined and used.

The sorting ensures a descending order of the Qty (Quantity).

2. Create a dialog field such as the following:

3. Create a dialog field such as the following:

4. Link the Dialog field to the Top records within the Query wizard.

5. Run the report and select the Number of records.

The output will look as follows: