After selecting a suitable command you will be prompted for further details depending on the command selected.
As an example we will use multiple instances of the SELECT function to select a subset of a file, then the list statement to produce the report.
This example report will be the inventory items that have been sold in the last financial year that have a warehouse location and with days stock equal to zero sorted by item No.
1.Press F5 to invoke TCL (if the user has permission to access TCL)
2.Press assistant and select "SELECT"
3.A list of file names will be displayed and select WAREHOUSE_DETAILS. This is the file name where to information resides for the report. This is usually the same as the form you used to display some of the details required for the report.
4.Because we are performing a select function the "WITH" statement is used to select a subset of the file. Now type in a space then "WITH". You MUST precede each entity with a space.
5.Press the Assistant button to display the available fields for the selected file and scroll down to display the required column. In this example select "WAREHOUSE"
7.Press OK to transfer the selected column to the TCL statement.
8.Enter a space then the equal sign (=) and then one (1) for warehouse 1
9.The resultant statement should be as follows:
a.SELECT WAREHOUSE_DETAILS WITH WAREHOUSE = 1
10.Press Execute to process the TCL statement
11.Once completed, the qty of selected records is displayed in the bottom left hand corner.
12.Repeat item 2 to 8 above resulting in:
a.SELECT WAREHOUSE_DETAILS WITH YTD_UNITS_TOTAL > 0
13.Press Execute to process the TCL statement
14.Repeat item 2 to 8 above resulting in:
a.SELECT WAREHOUSE_DETAILS WITH DAYS_STOCK <= 1
15.Press Execute to process the TCL statement
16.Repeat item 2 to 8 above resulting in:
a.SELECT WAREHOUSE_DETAILS WITHOUT LOCATION = ''
b.In the line above the WITHOUT function is used and the location is a null. This is done by entering two (2) quotation marks.
17.Press Execute to process the TCL statement
18.You will notice that each time a selection is performed on a previous selection the resulting qty is reduced. This is because any subsequent selection is performed on those records previously selected.
19.The above statement could also be entered as one selection as follows:
a.SELECT WAREHOUSE_DETAILS WITH WAREHOUSE = 1 AND WITH YTD_UNITS_TOTAL > 0 AND WITH DAYS_STOCK <= 1 AND WITHOUT LOCATION = ''
Each select process is stored in the query table and can be retrieved by pressing the assistant button and selecting "QUERYTABLE"
Once the selection process is completed the report list needs to be defined as follows:
2.Press assistant and select "LIST"
3.Repeat item 3 above (WAREHOUSE_DETAILS) and press OK
4.Again a list of columns are displayed and select the columns for the report in the order to be printed on the report. In this example is as follows:
5.Press og to transfer the selected columns to the TCL statement
6.Now we are going to sort the result in Item No order by adding:
7.The resulting statement should be as follows:
a.LIST WAREHOUSE_DETAILS DESCRIPTION DAYS_STOCK YTD_UNITS_TOTAL QTY_ON_HAND LOCATION AVERAGE_COST BY ITEM_NO
8.Press execute to create the report.
In summary, the above example uses multiple selects on a file and then use the "LIST" statement to produce a report on the screen. Also any select can be retrieved using the "QUERYTABLE" function.