tutorial:working_with_lists

This is an old revision of the document!


3.11. Working with lists

Whenever you perform a search in mSupply resulting in more than one item being found, you will be presented with a list of records (be they items, names, contacts or another list) that matches the criteria you entered.

mSupply uses two types of list display. Shorter lists, such as those you see when you view an invoice, or view the stock for an item employ a list with a striped background. These lists have resizable and moveable columns, and have an indicator to show the sorted column. Their use is discussed in the next chapter. In this chapter we are concerned with tools for handling lists of names and items and transactions, which run into hundreds of thousands or millions of records.

When these lists are displayed, a series of buttons are present along the top of the window, as shown:

Customising the columns viewed: Clicking the Customise button in the toolbar will display this window:

On the right is a list of columns you are currently using.

On the left is a list of available columns. To add a column to the view, click on the item in the left-hand list, then click the right–pointing button in between the lists. You can control-click to add multiple columns at the same time.

To remove a column select the column(s) to remove from the right-hand list, and click the left-pointing button to move the columns back to the available list. You can not remove the first column in chosen list, as it is a hidden key column to identify each row uniquely.

Renaming columns: By clicking twice on a field name in the right hand list you can change the column title.

Column widths: The easiest way to set column widths is to drag the dividers between the columns in the list view..

Note that if the total column width is too wide for the window, a horizontal scroll bar is added to the list allowing you to view columns to the right of those displayed.

Choosing the column order: To change the sequence of the columns in a list, drag a column header to a new location. This is done when viewing the Item list window, not in the Customise columns window.

Viewing or editing a particular record: To view or edit a record, double-click the row in the list you wish to view. A new window will open showing the details of the record you clicked.

Ordering (Sorting) lists: Ordering a list is the same as sorting it. Frequently a column may be ordered by clicking on the column heading. The column will be sorted in ascending order. Ascending means:

  • A > Z (upper and lowercase are not considered different)
  • 1 > 2 > 3 for numbers
  • earlier dates to later dates

Descending means the opposite of Ascending .

Note that ordering a list does not add or remove any records from the list. It simply changes the order in which the records are displayed. Should you require two (or more) sort parameters, clicking the Order by button shows the Order by window:

lists-4.jpeg

The Available Fields list will reflect the type of records you are viewing. The list shown is for Items . To order by a particular field, double-click it in the left-hand list; double-clicking on the “flags” and “item name” fields results in this window:

lists-5.jpeg

(You can also drag fields to the right, or click the right-pointing arrow after highlighting the field on the left)

You can order by multiple fields by adding further fields to the right-hand list.

To change to ascending/descending ordering: Each item in the “Ordered by Fields” list has a small triangle to the right. If pointing up, the ordering will be ascending. If pointing down, descending.

To change the direction, click on the triangle.

To remove a field: Highlight the field in the right-hand column, then click the red single left-pointing arrow between the two lists. The double left-pointing arrow will clear the “Ordered by Fields” list

Using formulae: If you want to order by a formula, click the Add formula button. Here the formula window is shown, and the formula “Length([items]code)” has been entered.

lists-6.jpeg

Using this formula would allow you to identify items whose item code is shorter/longer than your organisation's specifications.

Click the OK button to close the window.

Once the details are entered, click the Order by button to order the list.

You can press the print icon to send the list to Excel. The list will display in Excel with the column headings you have selected.

In the item list shown above there is a Find button.

This button shows a simple find window where you can enter the start of the item name or code.

Complex finds: The window shown above has a Complex find button. This allows you to perform a search using multiple criteria. Clicking this button shows the query editor

Within this window there are four areas from which you have to choose or set a value for each line of a query.

  1. Available fields,from where you make your selection
  2. Operands, from where you select the one appropriate
  3. Value, where you enter your specific criterion
  4. For queries with more than one criterion, click Add line , .then ….
  5. Click the appropriate conjunction.

For example, if you want to find all items whose default pack size is greater than ten and whose code starts with “A” these are the required steps:

  1. Click on “default_pack_size” in the available fields list.
  2. Click on “is strictly greater than” in the Comparisons list
  3. Enter “10” (no quotes) in the value field.
  4. Click the Add line button
  5. Scroll down the available fields lists then click on “code” in the available fields list.
  6. Click on “is” in the Comparisons list
  7. Enter “a@” (no quotes) in the value field.
  8. Click the Query button to perform the query.

Before step 8, the window would look like this

The upper area of the query editor shows a summary of the query that is about to be performed.

Note that the wildcard character “@” has been used following “a” to find all items whose code has “a” as the first character

This example will return a list of items where both the search criteria are met, because the conjunction And was used. If you wanted to find items that matched only one of the criteria entered, you would click on the second line in the large upper panel to select it, and then click on the Or button where the conjunctions are displayed.

Saving and Using saved queries: If you have spent time on developing a particular query , and you are likely to use the same query in the future, click the Save button. You will be asked to choose a name and location to save the query. The saved file is very small.

When a blank query window is first shown, you can re-use a previously saved query by clicking the Load… button and locating the query file.

  • Last modified: 2019/10/21 19:21
  • by Emily Porrello