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.
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.
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…
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 ( ).
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).
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”.
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.
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.
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.
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.
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.
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.
You can then adjust the condition to suit your requirements. Click the middle part of the Condition to change the filter type.
Click the value to enter the condition details
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.
You can then choose the type of function you want by clicking on the default type that appears after you select the Aggregate option.
When you’re ready, click the “Update Result” link to see the results of your query/report.
Export Results to Excel
After you update your results an “Export to Excel” link will appear in the title of the Results Panel.