Tag ' queries ' - Page 2 of 2

5th January 20183rd February 2023

Special characters in regexes and how to escape them

Regex – Regular Expression

Cradle provides support for regular expressions, regular expressions are a means to find variable text in places such as:

Queries
Find dialogs
Category recognition strings
Category value validations

For instance, regular expressions (regexes) can be used in queries to find all items in which any frame, or a specific frame, or any of a list of frames, contains text matching the regular expression that you are searching for. For example, if you wanted to find all items containing sequences of capital letters followed by numbers, then the regular expression would be:

[A-Z]+[0-9]+

Whilst you are here, take a look at Why is Requirements Management Essential for your Business?

Special Characters

Some characters have special meanings within regexes these characters are:

Char	Description	Meaning
\	Backslash	Used to escape a special character
^	Caret	Beginning of a string
$	Dollar sign	End of a string
.	Period or dot	Matches any single character
\|	Vertical bar or pipe symbol	Matches previous OR next character/group
?	Question mark	Match zero or one of the previous
*	Asterisk or star	Match zero, one or more of the previous
+	Plus sign	Match one or more of the previous
( )	Opening and closing parenthesis	Group characters
[ ]	Opening and closing square bracket	Matches a range of characters
{ }	Opening and closing curly brace	Matches a specified number of occurrences of the previous

Examples

Finished\? matches “Finished?”
^http matches strings that begin with http
[^0-9] matches any character not 0-9
ing$ matches “exciting” but not “ingenious”
gr.y matches “gray“, “grey”
Red|Yellow matches “Red” or “Yellow”
colou?r matches colour and color
Ah? matches “Al” or “Ah”
Ah* matches “Ahhhhh” or “A”
Ah+ matches “Ah” or “Ahhh” but not “A”
[cbf]ar matches “car“, “bar“, or “far”
[a-zA-Z] matches ascii letters a-z (uppercase and lower case)

Using Special Characters as Literal Characters

If you want to use any of these as literal characters you can escape special characters with \ to give them their literal character meaning.

Example

If you want to match 1+2=3, you need to use a backslash (\) to escape the + as this character has a special meaning (Match one or more of the previous).
To match the 1+2=3 as one string you would need to use the regex 1\+2=3

For further information on using regexes in Cradle see our online help.

22nd November 201723rd August 2022

Can I check for frames that don’t contain digits?

Regex – Regular Expression

A RegEx (Regular Expression) is a pattern matching method often used in text searches and data processing. Cradle supports Regex matches in a number of query elements, so it is possible to perform complex searches on your data. This article gives an example of the use of Regexes in frames.

Example:

The mocked up example describes just one of the ways a user could search the text frames of their items achieve particular results.

Identity	Name	Group	Text
bed1	Single Bedroom	Customer rooms	Single person room of basic standard – en-suite shower
bed2	Double Bedroom	Customer rooms	A 2 person occupancy room, shared bed en-suite shower
bed3	Twin Bedroom	Customer rooms	A 2 person occupancy room, single beds, en-suite shower
bed4	Luxury Bedroom	Customer rooms	A 1 or 2 person occupancy room double bed, en-suite with bath and shower.
bed5	Night Staff Rest	Staff rooms	1 person
store1	Cleaning storeroom

The data in the table above used a Cradle Note called Bedroom and was produced by running the automatic query “BEDROOM – All”. Then the resulting table was published to HTML. There were no regexes in frames applied to the initial query.

Queries with Regexes in Frames

- Simply entering “TEXT” as the Name of the frame to search and running the query will bring back bed1-bed5. The item store1 has no content to compare so can’t match.
- Entering bath in the Regexes field and re-running will return bed4 this is the most basic case the text typed in is simply found as a direct match.
- If you want to find the incomplete items and find those with a blank frame using the Cradle extension of <null> in the Regexes field will return store1 as this has nothing in the frame.
- If we want the query to find those that have not used a digit in the description (This may be important if the output is parsed by some other program) it is possible to use a regex character class [[:digit:]] . This will find all the elements containing a digit ≍ [0-9]. However, if we want the elements that don’t contain numbers, we can use the second Cradle extension of ! to negate the result, hence ![[:digit]] will return bed1 as it used the word ‘single’ rather than a digit for its occupancy value.
- Cradle also allows comma separated logical combinations of regexes in frames. Using the Venn diagram symbol you can select whether these are AND or OR combinations. Switching to AND and then using the regex [[:digit:]],![[:alpha]] would bring back items where the frame contained digits and no alphabetic characters.

screenshots of results using regexes in frames in Cradle — Regexes in Frames

Exploring Regexes

A simple way of testing whether your regex will give you the results you want is to use a test program regex101.com/ is a good example. This allows you to quickly tweak your statement, and run it against various test data.
Cradle uses the PCRE ‘flavour’ internally.
The Cradle help system has further information on regexes

8th November 201714th September 2022

X-Ray Day 2017

X-Ray Day 8th November 2017

X-Rays are a form of electromagnetic radiation or “ray” for short.

When first discovered, these mysterious rays were nothing like anything that had been described before, hence the ‘X’ name has stuck.

X-rays have a wavelength less than 10 nanometres, that is, they are shorter than those of UV, and longer than those of gamma rays. It was a German scientist Wilhelm Röntgen often credited with their discovery.

Looking Inside Cradle

Thankfully there is no need to use X-Rays to look inside your Cradle project.

Information once stored can be recalled by Queries and presented in Views or Forms. Output can be directed to HTML or RTF tables, shown graphically as a Hierarchy diagram, explored by clicking links. Formal publishing to Microsoft® Word documents can be achieved through Document Publisher.

In short there is no mystery to your data once inside Cradle unlike our bones, welded joints or airport suitcases you can easily see your data.

Celebrate on X-Ray Day 2017

Celebrate the genius of a very useful tool in the x-ray machine. Whilst we don’t advise you have an x-ray for fun, you could download another useful tool here!

13th October 201716th October 2017

Parametrics in Document Publisher

Parametrics can be very useful in Document Publisher. It uses a question mark ? as a marker to Document Publisher that this is an option and needs to be filled/selected by the user.

It can allow users to set the Identity/key of a single item or more to be output. This can be used in a hierarchy or with a single item type. Parametrics can also be used to set different properties and options in filters.

Single Parametrics

Using a single Parametric (?) allows a user to publish a template many times but use a different item Identity or Key each time. Note: Using a single parametric in several places in the template will always use the same item identity or key each time.

A parametric can be used at the database/top level or at the field level to set the items to be published. At the top level the parametric is entered for the Identity or Key.

They can also be used in a filter for field to set or restrict the items to be output.

Using a category to detemine if a field tag is output.

In this case the Requirement attribute of Identity will only be output if its Item Status is set to Accepted.

As you can see below, only items with a status of Accepted have both the Identity and Key output for the Requirements.

Named Parametrics

Using a Named Parametric (?named) means a user can have several different parametrics throughout the template and each can be set at time of publishing. So for example the user can set one named parametric for output based on one option of category (e.g. Agreed) and then later in the template use a different named parametric to output based on a different category option (e.g. Rejected).

Named Parameters for 2 different Paragraph Group Tags

During publishing a popup will allow the user to select Agreed for the first Paragraph Group popup:

They then can select Rejected for the second Paragraph Group popup:

The published output will then show the 2 different outputs in the same document.

These named parameters can even be used in several places within the template. The user may want to show the Agreed Requirements and System Requirements in one document. As both item types use the same category (Item Status in our Demo) then the same named parametric can be used for both item types.

Using Named parametrics for both Requirements and System Requirements

Output using the same Named Parameters twice

In the above screenshot, the 2 named Parametrics have been used for Requirements and System Requirements and the output shows all the Agreed items but that there are no Rejected System Requirements.

22nd September 201723rd August 2022

Querying a Range

Categories and Calculations in Range

When running a query it is possible to specify more than just a single value. Categories and calculations with a numeric value can be queried to exist in a range. For example querying a range for categories, or calculations that have a numeric value can be tested for the mathematical values:

Is equal to
is not equal to
Is in range
Is less than or equal to
is less than
is more than or equal to
is more than

It can also be tested against the logical

Is empty
Is not empty

or textually

Contains

menu showing numeric query specifiers — Numeric Query Specifiers

Results

The following screen shot shows the result of running a query against all of the item type and re-running with a range calculation.

The top half of the display shows the calculation having 14^m2 up to 22.5^m2, but the query Mid Floor Area limits the return to those between 15^m2 and 20^m2.

result of running a query witha nd without a range — Querying a Range

5th September 201725th August 2022

How do I see multiple queries at the same time?

Run and Display Multiple Queries Simultaneously

In Cradle it is very simple to see multiple queries!

First of all run your selected Query and this will open in a pane as usual such as shown below:

Now in the Top Right of the Pane you will see a little Arrow pointing right, if you select this you will be able to “Split Top/Bottom” or Split Left/Right” Creating a new pane. In that pane right click and you will have the option to “New Query…” This can be done multiple times. Showing multiple queries in views, multiple items in forms or multiple diagrams As you can see by my example below I have 3 Queries open all with different views and styles.

WorkBench showing multiple queries — Multiple Queries

For further information on Multiple Queries click here

14th June 201714th June 2017

Column Sorting in WorkBench

Column Sorting

WorkBench can perform column sorting in Table and Document view styles. This can easily be performed by clicking on a column heading.

Sort Options

Selecting a column heading will display four sort options. When one of these options is chosen the items will be sorted and then displayed based on that selection.

Sort ascending – Case insensitive

The first sort option will sort the items from A to Z, based on the item’s value for the selected column. This is done regardless of whether the characters are upper-case or lower-case.

Sort descending – Case insensitive

For the second sort option the items are returned from Z to A, based on the item’s value for the selected column. Again, as this option is case insensitive the returned items can be upper-case or lower-case.

Sort ascending – Case sensitive

The third sort option will sort the items from A to Z; but this time all upper-case items are returned first:

Once there are no more upper-case items the remaining items are then displayed a to z:

Sort descending – Case sensitive

The final sort option will sort the items from z to a; meaning all lower-case items are shown first:

Once there are no more lower-case items the remaining items are displayed Z – A:

Note:

This sorting only re-arranges the current data brought back in the current query. It does not retrieve new data from the database. In order to retrieve data from the database in a particular order, then one of the database key fields must be selected in the Sort by: field of the query. However, because the sorting occurs on the data in the table it does have the advantage of being able to order the table by frame content or other non key fields.

9th May 20174th February 2019

Using Parametric Queries

Parametric Queries in WorkBench and Web Access

Parametric queries prompt you at runtime for values for certain parameters prior to the query being run. They are supported for Identity, Name, Key, Level and free-format categories (including picklists).

You are prompted for values when the query is run from the following places:

Query Details dialog
Definitions sidebar
Phase tree
As part of a report
When performing an export by query
A start page

From the Query dialog press the arrow button next to the field you want to set the prompt for and select “Choose at Runtime“. This produces the “Runtime Parameter Definition” dialog where you can setup the parametric query prompt:

Screenshot to show prompt when running parametric queries — Prompt shown when running parametric queries

You can also specify more than one parametric in the query dialog. This can prove very useful in simplifying the interaction in not having to use the full query details window.

When you run a parametric query the prompt will be shown. An example is shown below:

Prompt shown when running a parametric query — Parametric Query Prompt

Parametric queries are supported in both WorkBench and Web Access.

For further information please see the Cradle online help.

Article Updated 04/02/2019 – Added further details

27th April 2017

Querying on Dates

Filter Query Results Based On Date

When running a basic item query all items of that type that the user is allowed to see are returned. As a project grows this number may become quite large, it therefore becomes necessary to add additional filtering criteria, such as the date. Querying on dates allows you to filter by the modification or creation date of an item.

Screenshot showing querying on dates providing creation date filtering — Querying on Dates

Relative and Absolute Dates

You may have a good enough memory to remember it is the item you created on the 24th that you want to return. Choosing ‘Specify:‘ from the drop-down will enable a date picker. However, it is much more likely that you’ll want a range of dates, as you want to run a query each Thursday to find the new ‘issues’ raised in the last week. Relative dates allow you to do this, choosing ‘Start of last week’ and ‘Today‘ would give the data required. The benefit of the relative date specification is that the query can be saved. It can then be run next week to give a new set of values.

Specific Dates

An item’s creation or modification date may not be something that you can directly control. It certainly can not be set in the future. If a data entry clerk enters all emails as items on a Friday they’ll all have the same creation date. Equally updates will change the modification date making an old item appear more recent. Categories can be set to hold abstract data of type Date, this would allow a value such as “Review Date” to be set, and then queried upon. This data is based on a user input value and not the automatic dates recorded by Cradle.

Availability

This filtering functionality is available wherever queries can be run. This includes WorkBench, Web Access, C_IO command line tool and the Cradle API.

19th April 2017

Configuration Management – Submit by Query

Too Many Items to Select and Submit?

At some points in a project’s lifecycle, a large number of items need to be submitted. These may be too numerous to ‘select‘ and ‘Submit‘, Cradle offers ‘Submit by Query‘ option. This allows submission of all the items (subject to access rights) that match a query to be moved to the review stage in the configuration management system.

Highlighting methods of submitting items for review — Submit Items

Check Before Submission

Using the ‘Show Items’ button allows the user to run a report that will detail which items the current settings will submit. In this way users can ensure that the correct items are going to be processed before creating all the approval records and changing the status of the items being submitted to that of ‘Under Review‘. For further information see the article How to Submit Items for Review Based on a Query. Also see further information on Cradle’s Configuration Management System in this article.. Configuration Management features are also available in Web Access.