Query Tool

Query Tool, Introduction

The Run-a-Restaurant tools use a database to store your information. Whilst there are reports and views that will cater for most your requirements, there may be times when you need information that isn’t readily available. That’s where the Query Tool comes in. The Query Tool gives you access to all your information, direct from the database. You can: filter the information by adding conditions; control the order in which the results are displayed; and use functions such as sum, count and average to transform you results.

EasyQuery Components

The Query Tool consists of three main components – an Entities Panel, Conditions Panel, and Columns Panel. Data will be displayed in the Results Panel. The picture below can help you understand which is what.

Query Tool

Entities and Attributes

While working with the Query Tool, you operate entities and their attributes. They constitute something like a structure of data that you can extract through construction of queries.

An entity is some real-world object, information on which is stored in the database. Examples of entities are as follows: ‘Stock Item’, ‘Supplier’ ‘Menu’ etc. Every entity has an initial set of attributes, or parameters. Thus, attributes for such entity as ‘Stock Item’ can be: Title, Purchase Cost, Supplier ID etc…

Entities Panel

This panel can be used as a visual representation of all entities and their attributes, which we can use to build queries/reports. It can be used to add entity attributes to a Columns Panel (and they will be displayed in the result) or to a Conditions Panel (in order to actually set the query conditions).

You can either move selected attributes to the corresponding panels with a mouse or use buttons Add column and Add condition ( ).

Columns Panel

Using columns panel you can specify the columns which you want to be displayed in your query results. You have a list of lines, each of them represents a column in the query results (in the Results Panel). Every line consists of Expression, which is an attribute, a Title, which serves as a column title in the Results Panel, and buttons – for sorting (on the left), aggregating and deleting (on the right).

Conditions Panel

Here you can specify filtering conditions for data selection. So the result of your query will include only the data that satisfy those conditions. Every line in the Conditions Panel corresponds to a single query condition. Every condition consists of 3 elements – an expression (some entity attribute), an operator, and value(s) (one or more).

We have mentioned attributes above (e.g., ‘Title’ ‘Purchase Price’ and ‘Supplier ID’). Together with values, operators set certain limitations regarding data which is stored under a given attribute. For example, (Title) starts with “B”, (Purchase Price) is greater than $3, (Supplier) is “Food-o-Nomics”.

The Basics

Adding Columns

There are 2 ways to select the columns for your query/report.

Using the Entity Panel, click the checkbox next to the Entity Attributes (fields) that you required. Using this method you can add as many fields as you like all at once. Then click the “Add Columns” icon at the bottom of the Entities Panel to move your selection to the Columns Panel.

Query Tool, Add Columns

The other method of selecting Columns is it select them one at a time from the “Add Column” list at the top of the Columns Panel.

Query Tool, Add Columns

Change Column Order

To change the order in which the Columns appear in your query/report, click on the arrow next to the field you want to move and select from the “Move” options to relocate the field to wherever you want it.

Query Tool, Change Column Order

Change Column Title

To change the title of a column on your query/report, click the default title in the “Title” column of the Columns Panel. You can then change the title as required.

Query Tool, Change Column Title

Sorting Results

To sort your query/report results, click the arrow icon next to the field you want to sort by, select the “Sorting”, then the type of sort you require.

Query Tool, Sorting Results

Adding Filters

Adding a filter is as simple as selecting the field that you want to filter on from the “Add Condition” icon at the top of the Conditions Panel.

Query Tool, Adding a Condition

You can then adjust the condition to suit your requirements. Click the middle part of the Condition to change the filter type.

Query Tool, Change Filter Type

Click the value to enter the condition details

Aggregate Functions

To functions such as sum, average and count are easy to add by clicking the arrow icon next to the field to which you want to apply the function.

Query Tool, Select Aggregate

You can then choose the type of function you want by clicking on the default type that appears after you select the Aggregate option.

Query Tool, Select Aggregate Type

Generate Results

When you’re ready, click the “Update Result” link to see the results of your query/report.

Query Tool, Update Result

Export Results to Excel

After you update your results an “Export to Excel” link will appear in the title of the Results Panel.

Leave a Reply