1. EasyQuery: What’s That?
...
| Excerpt | ||
|---|---|---|
| ||
The Advanced Search is able to search on nearly any specific field or field combinations in a DVI file and returning nearly any field in the result. |
Advanced Search: What’s That?
It is always good when there are just a few search parameters, and when one can perform a query with a single text line (for example,
...
Full Text Search). Nevertheless, in most cases, you need to conduct search while operating several parameters at the same time. For example, “Find
...
all male files that has blue trousers, an Omega watch and the appendix removed.” In order to specify
...
the conditions of such a query, you will need more than just one text line.
In such cases,
...
Advanced Search would be much of a help. It is a universal query builder UI for data search and filtering. It will allow you setting the search conditions of almost any difficulty, while using exclusively natural language, terms and expressions of which are familiar to user.
...
2. EasyQuery Components.
...
| Info |
|---|
When you first create an Advanced Search, the conditions from the current File Search is automatically replicated into your search, so that it i.e. start out with predefined conditions selecting Active, Unidentified, AM or PM files and the selected Container if not all. This makes it easier to include general conditions without specifying these one by one while starting each new Advanced Search. |
Advanced Search Components
The Advanced Search user interface consists of three main components –
...
a File Attributes Panel, Search Conditions panel, and Result Columns
...
NB: SQL Panel is not a component of the EasyQuery UI. It is displayed here exclusively to demonstrate what a particular query would look like in SQL (Structured Query Language). You will not normally see this panel in most applications which use EasyQuery.
Each component can be used separately. For example it’s possible to use only Conditions Panel for data filtration while the columns are defined internally inside application.
About entities and attributes
...
panel. The picture below shows the general layout, but the panels can be resized by dragging the horizontal and vertical separators:
File Attributes
While working with Advanced Search, you operate file attributes. They constitute something like the field structure of a DVI file that one 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: ‘Customer,’ ‘Order,’ ‘Product,’ etc. Every entity has an initial set of attributes, or parameters. Thus, attributes for such entity as ‘Customer’ can be: Name, Address, Country, and others.
Entities Panel
File Attributes Panel
This panel can be used as a visual representation of all
...
attributes/fields of a DVI file, which we can use to build queries. It can be used to add entity attributes to a Result Columns
...
panel (and they will be displayed in the result) or to a Search 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 ( 
).
...
...
Main branches
- The File branch contains general, administrative attributes of a File
- The AM branch contains all fields that are special to an AM/MP file
- The PM branch contains all fields that are special to a PM/UB or PUI file
- The Common branch contains all fields that exists in all file kinds
In general, it is a very bad idea to select checkboxes at a high level, as it will automatically also select all attributes within the underlying branches, effectively leading to a system overload. Instead, the checkboxes at a high level can be used to ensure that everything within the underlying branches is unchecked when the higher-level checkbox is unchecked.
The buttons are used as:
 | Select all attributes (never use this - will be removed) |
 | Remove selection from all attributes |
 | Add selected attributes to the Result Columns panel to include them in the search result |
 | Add selected attributes to the Search Conditions panel to be able to specify |
Result Columns
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
...
. Every line
...
consists the Attribute name (i.e. Day), a Title (i.e. DOB Day), which serves as a column title in the Results Panel, and buttons –
...
for ordering, deleting and aggregating columns (on the left) and sorting (on the
...
...
right).
Managing columns
When you click the button in front of each column specification, you get the option to move/delete the column or change its type from simple to grouped aggregate. The aggregation is currently not working.
Sorting the result
When you click the None link at the end of each column specification, you get to option to sort this column Ascending or Descending. If you sort on multiple columns, the first sorted column from top to bottom (left to right on the paper) will have highest priority and so on.
Adding an extra column
Columns can be added from the File Attribute panel or from the 
button.
You have to option to enter a search word, and then only attributes regarding this keyword will be visible in the branches.
Search Conditions
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
...
file attribute), an operator, and value(s) (one or more).
...
Attributes, together with operators and values set certain limitations regarding data which is stored under a given attribute. For example, “(Date) is equal to 23 Jul 2014,” “(Quantity) is greater than 35,” “(
...
Weight) is less than
...
100,” and “(Name) starts with A.”
Basic queries: hot to make them with EasyQuery
1) Display Table Data
Let us imagine that we need a table, which contains following data: names of our customer companies along with countries of their origin, names of categories in which they ordered products, and a total sum of orders each of them made.
So, we select following entities and their attributes to add them in the Columns Panel:
- Customer -> Company Name
- Customer -> Country
- Product -> Category
- Order -> Total Sum
Clicking on the Execute button displays our query result just as we need it.
2) Table Data with Aggregated Columns
EasyQuery allows to aggregate data for each column in the query result. For example, we may need a total quantity (amount) of the orders for every company.
We are going to add the following columns:
- Customer -> Company Name
- Customer -> Country
- Order -> Order ID (we need an attribute of the Order entity to calculate the amount of orders)
- Click on the button and select the "Count" function
- Change the title for last column to "Order Count" - just to represent its purpose.
We get the following result:
3) Table Data with Sorting
Let us use the previous example, in which, as a result, we get columns with data on:
- names of customer companies,
- countries of their origin,
- amount of products they paid for.
First steps are the same as in the previous example:
- Customer -> Company Name
- Customer -> Country
- Order -> Order ID
- We make the last line active and select Count after clicking on the Function button
This time, let us sort data by the number of orders, in the descending order. To do this, we
- make the line Count of Order ID active, click on the sorting icon and select Descending
Results are displayed according to the number of orders made by every company, in the descending order.
4) Filtering Data 1 (One Condition)
We often deal with situations when, for example, of all the customer companies, we must select only those which have purchased goods worth 450 or more. In cases like this we should use the Conditions Panel for data sorting.
Let us do a sample query with the following data displayed: customer companies, countries of their origin, and total sum of their purchases. So, in the Columns Panel we select:
- Customer -> Country,
- Customer -> Name,
- Order -> Total sum.
Now we switch to the Conditions Panel and:
- add Order -> Total sum,
- change the operator 'is equal to' to 'is greater than or equal to,'
- and enter 450 in the value slot, as it is shown in the picture below.
It will look like this:
5) Filtering Data 2 (Several Conditions)
Often we have not one, but several conditions for filtering data. For example, we need a list containing information on:
- customer companies from Germany,
- whose purchases are worth more than 450,
- and those purchased products are in the 'Dairy products' category.
Firstly, we need to specify that, if (by default) every new condition is an additional limitation to the existing ones, they are connected by a conjunction 'and.' In this particular case, it absolutely suits us, but we will show you how to change this (when necessary) a little bit later.
Thus, when building a query, we indicate first which columns to display, through the Columns Panel:
- Customer -> Company name,
- Customer -> Country,
- Order -> Total sum,
- Product -> Category,
then we add the following to the Conditions Panel:
- Order Total sum -> "is greater than" -> 450
- and: Product Category -> "is equal to" -> Dairy products
- and: Customer Country -> "is equal to" -> Germany
See how it looks below:
6) Filtering Data 3 (Several Conditions with AND and OR Conjunctions)
Let us suppose that the query conditions in the example 5) have changed to the following:
We need a list of customer companies which:
- purchased a total of $450 worth of products
- in the Diary category,
- and their postal code contains 4 or 5.
In the Columns Panel, we select:
- Customer -> Company name,
- Order -> OrderID (f = Count),
- Product -> Name
- We keep 'all' in the Conditions Panel's topmost line ("Select records where all of the following apply")
- Using a [+] button, we add a condition: Order -> Total sum -> "is greater than" -> 450
- and another condition: (and) Product -> Category -> "is equal to" -> Dairy Products
- Next to the first line at the top, we add a group of conditions with the help of a symbol {+} and leave 'any' in a newly formed line.
Consistently, after "and any of the following apply," we add two conditions, by clicking a [+] button:
- Customer -> Postal code -> "is equal to" -> 4
- (or) Customer -> Postal code -> "is equal to" -> 5
It will look this way:
7) Enabling/Disabling of Conditions
Let us assume that we are interested in a particular product - Aniseed Syrup. All we need to know is the list of companies that either have paid for their order, or have made an order starting from the first day of the year.
Therefore, we indicate the following in the Columns Panel:
- Customer -> Company name,
- Order -> Freight (f = Sum),
- Product Name (we will use it only to illustrate the example better).
In the Conditions Panel, we will take a set of steps:
- Product -> Product -> "is equal to" Aniseed Syrup
- We create a group of conditions by clicking {+} and using 'any' in "and any of the following apply"
- Order -> Paid -> "is true"
- (or) Order -> Date -> "after (special)" -> First day of the year
Below is the visual representation of our steps:
And the result we get:
It can happen so that we will be needing only total sums of orders made after beginning of the year, or right the opposite - only orders which have been paid for (with no time restrictions).
There is an option of hiding particular conditions specifically for cases like this one. To the right of a condition itself, you will see a 'hide condition' button - [o]. By clicking on it, we get a broader range of data we want displayed:
The result is slightly different from the previous one:
Enable or Disable Conditions
The checkbox in front of each line allows you to easily enable (checked) or disable (unchecked) any of your conditions so that you don't need to delete a condition in order to temporarily see which records it excludes.
Composite searches
Sometimes, you would like to include 'OR' conditions in your query. An example is seen below, where two query lines are grouped in a "bracket" or predicate with an any clause.
So, a result line is included if the Status File kind is any of AM or PM (effectively being an OR between sub-conditions):
If you click on the 
button (or any other numbered button), you get the option to Open a new bracket, which creates a new group/predicate below and within the current button.
You may also Add a new condition either after the current group/predicate (at the same level), or Delete the whole group including all sub-conditions.
Finally, you may move the group up or down in the total list of conditions.
The group/predicate can change function if you click on i.e. the 
link:
You can choose between:
- all - corresponding to an AND between conditions
- not all - corresponding to an OR where one of the conditions must fail
- any - corresponding to a full OR between conditions where one of the conditions must succeed
- none - corresponding to a NOT ( AND ) where all of the conditions must fail
| Anchor | ||||
|---|---|---|---|---|
|
Let us imagine that we want to search for files where the AM or PM person is wearing either blue or grey trousers.
First, we need to find the Item number of the item to search for. We are looking for trousers, so according to the form, it is item 308. The search value for trousers is then "S308".
| Note |
|---|
When entering search values for items (jewelry, clothing, watch... any type of item described in a grid-like structure), you must use the item number from the form prepended with an "S", so skirt become "S303" and trousers become "S308" and so on. |
Item as a condition
Expand the Common branch in File Attributes panel and continue expanding down to F300 Clothing Items. Do not set any checkmarks on your way!
Then, check the Item row. It will also set all checkmarks above it. If you make any errors, just remove the checkmark next to Common, and all your selections in that branch will disappear.
Finally, click the Add Condition button to add the Item attribute to the Search Conditions panel
This will add another query line (marked 
), and your Search Conditions panel should now look like:
Specifying the item condition value
From above, you are directed to 
to enter the value to search for. In order to look for trousers, enter S308 as defined in the introduction.
If you want to look for multiple clothing items, these can be specified as a list, changing the operator to is in list and specifying multiple item numbers separated by a , (comma):
Colour as a condition
You want to specify multiple colours, blue or grey. In an ideal world, we could use the is in list operator and specify blue,grey as the value, but that would only result in items that have been specified as being exactly blue or grey, without any other words around it. It may be the case for well-structured new 2013+ files where the colour constraints have been expressed on the form, but we recommend using this approach instead:
The best way to search for a word is normally to use the contains operator, but because we want to specify either blue OR grey, we need to open a bracket to group these two different queries. Click the button and choose Open new bracket.
It will create a new line 
with an predicate (equal to an OR between conditions within the group), and it will copy the condition content of into the bracket as an 
line.
You don't want to continue querying the Item, but wants to change it to Colour.
Click the 
button to bring up the field selector, type colour in the search field to reveal old branches that contain a colour field, and expand the Common, Effects and Clothing Items branches to choose the colour of clothing items that we are interested in.
Change the operator from starts with to contains to search for the colour anywhere in the Colour field sentence
and within the field, type blue as the value.
In order to add the next condition within the group you click the button and choose to Add new condition.
It will add a copy of your line, and you then just need to enter grey as the value.
Finally, you want to see most of the entered field values in your result, so return to the File Attributes panel,
check the fields/columns you want to include and click to add these to the Result Columns panel.
You will end up with a condition and column list like this:
When you then click 
, you will get a list of results.
| Warning |
|---|
We only return the first 100 matches in order to save resources on client and server. Please refine and add conditions to your query to get a result list of less than 100 lines. This limit will be adjusted in future versions, and a line counter or other message that the result is incomplete will be added, as well as the ability to print the result list. |
Whenever you execute the search, you get a new tab of results, so that you can compare different results.
| Anchor | ||||
|---|---|---|---|---|
|
If you start a new Advanced Search, the currently selected Container in the Administrative part of Simple Search will be automatically added to your default query:
This will also happen after you Clear an ongoing Advanced Search.
You can add an extra condition If you need to include multiple Containers:
Just click the [4.1] (or whatever it is numbered in your case) button in front of 'Container links' and choose to Add new condition.
Then click the [click here] link and choose the relevant Container short name from the list.
Repeat the procedure for each extra Container to include in your search.
Relevant Attributes
Be aware that you use different field attributes for Conditions and Columns. Use the 'Container CONDITION' attribute in Conditions but 'Container Short Name' or 'ContainerName' to display the container in the Result Columns.
Re-using searches
Your queries can be saved and then loaded again at a later stage to be able to repeat certain searches over time.
Save search
When you are satisfied with the query and the results you are producing, find the ribbon menu in the upper left corner and click 
.
It will ask for a search name, and you can type any text you like:
Finally, click 
to save the complete search.
Load search
If a search has been saved (and the Advanced Search feature has been closed and reopened), the 
button has been enabled.
When you click , you are presented with all the searches that you have saved on this machine.
| Info |
|---|
For the time being, searches are stored individually for each user on each client machine. We will later provide the option to share searches between machines and between users. |
Highlight the search in question and click to load it, then so get the result.
Delete search
If you click the 
in front of a search name in the load list, you may delete it.
Related articles
| Filter by label (Content by label) | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
| Page Properties | ||
|---|---|---|
| ||
|