Advanced queries

From WMS Help

Wildcards

When looking for records in the WMS, you can use wildcards to help you find exactly what you are looking for. There are two types:

  • % matches zero or more characters.
e.g: %AF returns all the records that end with AF.
e.g: X% returns all the records that start with X.
e.g: %X% returns all the records that contain X in the current field.
  • _ (underscore) matches exactly one character.
Can be used in place of any letter or number.
e.g: X_ returns the records with XA, XB, X1, X2, etc but not XAB, XB1.

NOTE: To type the underscore character press the Shift and the Minus key located next to the 0 (zero).

Sorting Results

Changing the sorting of the results

When using the enquiry or audit forms you may want to sort the results in a way that is different then originally intended. This can be accomplished in the following way.

  1. Enter the screen of your choice, e.g: fe303.
  2. Press F7 to enter the query mode.
  3. Type H for the Site.
  4. Type X% for the Location.
  5. Type :a (colon followed by letter a) in the Product field.
  6. Press F8 and a new screen should appear (see picture on the right).
  7. Type the words order by :a.
  8. Click on the OK button to execute the query or the Cancel button to return to the form.

Your results will now appear sorted by the column the :a appears in.

NOTE: By default the results will be shown in ascending order. To change it to descending use the keyword desc. For example, order by :a desc.


Date Ranges

Date filters

You can use date ranges to filter the results of the query forms that contain date fields.

Take for example the form FO212 (View/Update Delivery Orders). To find out all orders that need to be dispatched after tomorrow.

  1. Type in the Client, it is required for the query to work.
  2. Type % in the Order Number to indicate that you are interested in any/all orders.
  3. Type :a (colon followed by letter a) in the Despatch Date field.
  4. Press F8 and a new screen should appear (see picture on the right).
  5. Type the words :a > sysdate +2.
  6. Click on the OK button to execute the query or the Cancel button to return to the form.

Your results will now appear showing only the deliveries to be deispatched starting from two days from now.


Here are some more examples:

  • :a < sysdate -1
Shows orders before today.
  • :a < sysdate and :a > sysdate -7
Shows orders in the last seven days.
  • :a < sysdate and :a > sysdate -7 order by :a desc
Show orders in the last seven days and the results are sorted in descending order.