Selection_000 Selection

A selection is a subset of data based on selection parameters and result columns.

How to create a new selection

1. Start by building up your structure with folders.

2. Select the folder to which you want to create a new selection for.

3. Click on the three-point menu to the right and select “New selection”

Selection_001

4. A setting dialog appears to the right where you should choose your data source on which your selection should be based on. The “drop down” menu is based on whether you choose prioritized or non-prioritized data sources. By default, “prioritized” data sources are selected. Select the data source.

sel1

After selecting the data source, your selection template will automatically be created.

By default, the template is empty, which means that you need to add rules and result columns to it.

5. Give your selection a suitable name. Then click outside the entry box, and the name will be saved.

Selection settings

Selection_003

In this setting box, one sees at the top the name it has. You also have two buttons, one to export your selection in order to later be imported to another environment and another button to remove the selection.

(Selection that is related to other selections or flows cannot be deleted.)

  • Locked level

    • Open = Selection is open for changes. Anyone can change this selection.
    • Locked = Selection is locked for everyone except you and administrators.
    • Sealed = Selection is locked for everyone, including you and administrators. It can only be unlocked with help from Sweet Systems.
  • Visible

    • Checked = Selection is visible for everyone.
    • Unckecked = Selection is only visible for you and administrators.

Tree for datasource attributes

In the middle, one sees the structure of the data source’s all attributes. The attributes are grouped in such a way as to make it easy for a user to find what it is looking for. The structure and grouping of the attributes is controlled via the administrative interface.

Selection_004

Above the structure are two tabs. Condition and Result. These control whether you should work with filtering or result columns.

Types of attributes

In this structure for attributes there are different types. We call them data types. Below you can see examples of these data types.

Selection_032

Selection_033 = Alphanumeric (Text).

Selection_034 = Id field

Selection_035 = Lookup (Drop down menu that populates selectable types)

Selection_036 = Numeric (Numerical value such as a person’s age)

Selection_037 = Date (Date values. Populate calendar to pic a date)

Selection_038 = Boolean (A bit value. Could be 1 or 0 /True or False)

Workspace for conditions or result columns

In the right-hand surface, the actual building surface is for your selection. Depending on whether to work with the selection conditions or results, the appearance of this surface changes. Normally you work with both conditions and results.

Conditions are built up in the selection tree with the help of so-called operators that are put together. After that, one can combine the various attributes of the data source which are then released into the selection tree.

Results are built up in a view where you are allowed to change the order of all columns, format or aggregate columns and determine the sort order of the result.

Selection_005

Above the structure are two tabs. Condition and Result. These control whether you should work with filtering or result columns.

Operators

If you have chosen to work with conditions (the tab in the attributes tree) you will see a menu with operators at the top of the workspace, “AND”, “OR”, “NOT IN” and “IN”. By dragging and dropping operators down to the selection tree, one can combine them for how one wants a question to work. Initially, the selection tree has only one node, “AND”. This is the main node that is the basis for your selection.

Selection_006

Below, the various operators describe in detail:

  • AND

Selection_019

Everything that lies within this (almost vertical green line) is to put an “AND” between each criterion, ie all expressions must be fulfilled. The same applies to “NOT IN” and “IN”, ie an “AND” between each criterion.

  • OR

Selection_020

Everything that lies within this (almost vertical blue line) one should put an “OR” between each criterion, ie one of the expressions must be fulfilled.

  • NOT IN

Selection_021

Everything within this works has an “AND” between each expression (closest to the vertical red line). Plus, this is a negation, which means that you want to exclude the result from what is within it. A good expression for NOT IN exclusion is “Not even an oath, may occur

  • IN

Selection_022

Everything within this works has an “AND” between each expression (closest to the vertical yellow line). Plus, this is an inclusion which means that you want to include the results from what is within it. A good expression of IN inclusion is “At least one, may occur

In the picture below you can see how to build a condition tree with operators / blocks. You should always start from the blocks (AND, OR, NOT IN, IN) and look at what is closest to the block’s left vertical line. One can think of that line as a big bracket around everything that lies within it. In the scenario below, there are 4 criteria that must be met (blue arrows).

The selection below provides:

  • Anyone who has an email address.
  • Not optout.
  • Has a positive transaction one of the years 2014, 2015, 2016 for fund accounts.
  • But has not any transaction in 2017 for fund accounts.

Selection_023

The first thing to consider is which one is the primary one you want to produce?

1. Persons that has an active and correct e-mail address (Email control)

2. Persons that has not “opt out” (PersonOptOut not exists)

These criteria generally apply to a person and these only need to be defined once. These are therefore closest to the outermost block (AND).

3. OR. Something that the expressions must be fulfilled. Not all expressions need to be met.

4. NOT IN. Exclude the result from this

Now note that what is closest to the outermost block is points 1–4 and it is an “OCH” between each digit, ie point: 1 AND 2 AND 3 AND 4 must all be satisfied.

If we only look at the OR block that is inside an AND, it is enough that one of the inclusions (IN blocks) is fulfilled. This means that it is enough that a person has a positive transaction on a fund account for one of the years. Remember that an IN block works in the same way as an AND that all expressions must be met.

Drag and drop

By “drag and drop” can build up its operator tree. You select the desired operator box (AND, OR, NOT IN or IN). Holds the mouse button, pulls and releases it inside any of the existing operator blocks. When you pull over an existing operator block, a light gray block lights up in the existing block, which means that you can release your operator there by releasing the mouse button.

Selection_024

Then continue taking a new operator.

Selection_025

The end result could be like this

Selection_026

Summary and analysis

Up to the right of the selection tree are two buttons, summation and analysis.

Selection_007

Selection_010 = Summarizes the whole question. (Total of all parameters together). The fastest way to count the total number for your selection

Selection_051

Selection_011 = Analyzes and summarizes at all levels, including the totals. This can take a little longer to run compared to a regular summation.

Selection_052

Examples of a more difficult question but very common are when eg a person / company has a related object such as bookings, activities, cases or as in this case accounts. For this set, the main entity is Private persons who have a related entity that is accounts. A person can have one or more accounts.

The selection below provides:

- Overall, people should not be optout (denied mailing or the like) and they should have a correct email address

   - The persons will have a positive transaction for fund accounts during the period January 1, 2014 to December 31, 2014

   - OR The persons shall have a positive transaction for fund accounts during the period January 1, 2015 to December 31, 2015

   - OR The persons must have a positive transaction for fund accounts during the period 01-01-2016 to 31/12/2016

- The persons should NOT have a positive transaction for fund accounts during the period January 1, 2017 to December 31, 2017

Selection_053

By analyzing the selection above you can see the number of hits per node. Summarizing the question would only give a result at the top AND level.

Garbage can

If you want to remove a node from the selection tree, you can drag and drop the node on the bin. The discards thrown remain in the bin until you refresh or leave your selection template.

By clicking on the trash can you see what lies in it. You can also pull back thrown nodes to the selection tree. However, no operators are saved down to the garbage can.

Selection_008

To remove a condition or operator block can be done in two ways:

1. Drag and drop to trash. Grab the block / condition and drag and drop it to the trash bin, located at the top right corner.

Selection_027

2. Three-point menu. Click on the three-point menu that is at the far right on a block or condition. In the menu there is a choice at the bottom called “Delete operator” / “Delete conditions”. Click here to delete.

Selection_028

Three point menu

There is a three-point menu in the condition tree at the far right of each block. In this menu you can run functions changing conditions or operator blocks. The functions that can be run are the same that can be run outside this menu.

Selection_029

Depending on whether it is a operator block or a conditional block the content of the three-point menu is changed

Operator block

Selection_030

Condition block

The view of the conditional block depends on different datatypes. (The example below shows datatype - “datetime”)

Selection_031

Action bar

At the bottom is a menu of action buttons. These perform various operations on your selection.

Selection_009

Selection_012 = Run the entire selection and return the result. (Returns only 1000 first lines).

Selection_013 = Exports the entire selection to Excel.

Selection_014 = Displays the actual SQL query set against the data source.

Selection_015 = Scans all the result columns from the question. Displays, for example, the data type and size. (Advanced use only).

Selection_016 = Shows the history of all runs against selection. Shows, for example, the number, how it was run, by whom and when.

Selection_017 = Actions. Displays possible actions that can be scheduled and run against the selection.

Selection_018 = Shows scheduling. For example if the selection is scheduled for an action.

Conditions

To create a condition. Follow the instructions below.

1. Locate your field that you want to select from in the selection tree and select it. The selection appears as a blue frame around the field.

2. Mark the desired condition type (the light blue box contains “equal to”, “not equal to”, “contains” etc.) In this case we choose “Contains”.

3. In the text box, enter the condition value for the field. In this example, the value is “Mari”.

4. In the block at the bottom, your finished condition is now visible.

Selection_039

Your finished condition must now be entered in your condition tree to the right. This is done by drag and drop. Grab the condition and drag and drop it into the condition tree. If you have several operator blocks in your tree, you must release the condition in the correct block.

You can also click the “Add” button to send it over to the selection tree, but then it will always ends up in the root node of the selection tree.

Selection_040

Result after adding one condition to the selection tree.

Selection_044

Condition types

Condition types you define when you are building a criteria for your selection. Below you see where to find them.

Selection_041

Condition types for “Alphanumeric” values

Condition type Example
equal to Is equal to “text”. Example: = “Company AB”
not equal to Is not equal to “text”. Example: <> “Company AB”
contains Contains “text”. Example: contains “%Company AB%”. On this one you can apply% sign to search for content
not contains Contains “text”. Example: does not contain “%Company AB%”. On this one you can apply% sign to search for content
in Show only selectable, means that Sweet Automation automatically lists all possible options that can be selected. Conditions “IN” means that what you choose in the list should be included in the selection.
not in Show only selectable, means that Sweet Automation automatically lists all possible options that can be selected. Conditions “NOT IN” means that what you choose in the list should not be included in the selection.
starts with Starting with “text”. Example: starting with “Company AB%”. On this one you can apply% characters to search for text.
not starts with Does not start with “text”. Example: does not start with “Company AB%”. On this one you can apply% characters to search for text.
ends with Ends with “text”. Example: contains “% Company AB”. On this one you can apply% characters to search for text.
not ends with Does not end with “text”. Example: does not end with “% Company AB”. On this one you can apply% characters to search for text.
is not null Value is empty (NULL)
is null Value is not empty (NOT NULL)

Condition types for “Numeric” values

Condition type Example
equal to Equals “numeric value”. Example: = 1255
not equal to Is not equal to “numeric value”. Example: <> 1255
greater or equal to Is greater or equal to “numeric value”. Example: > = 1255
less or equal to Is smaller or equal to “numeric value”. Example: <= 1255
less than Is less than “numeric value”. Example: < 1255
greater than Is greater than “numeric value”. Example: > 1255
between Is between “numeric value1 and numeric value 2”. Example: xxx BETWEEN 1000 and 2000
not between Is not between numeric value. Example: xxx NOT BETWEEN 1000 and 2000
is not null Value is empty (NULL)
is null Value is not empty (NOT NULL)

Condition types for “date” values

Condition type Example
equal to Equals “numeric value”. Example: = 2019-09-01
not equal to Is not equal to “numeric value”. Example: <> 2019-09-01
greater or equal to Is greater or equal to “numeric value”. Example: > = 2019-09-01
less or equal to Is smaller or equal to “numeric value”. Example: <= 2019-09-01
less than Is less than “numeric value”. Example: < 2019-09-01
greater than Is greater than “numeric value”. Example: > 2019-09-01
between Is between “numeric value1 and numeric value 2”. Example: xxx BETWEEN 2019-05-01 and 2019-11-01
not between Is not between numeric value. Example: xxx NOT BETWEEN 2019-05-01 and 2019-11-01
is not null Value is empty (NULL)
is null Value is not empty (NOT NULL)

Condition types for “Lookup” values

Condition type Example
in Show only selectable. Means that Sweet Automation automatically lists all possible options that can be selected. Terms “IN” means that what you choose in the list should be included in the selection. (This is faster compared to text fields because text fields must scan all possible options.)
not in Show only selectable means that Sweet Automation automatically lists all possible options that can be selected. Terms “NOT IN” means that the selection in the list is not included in the selection. (This is faster compared to text fields because text fields must scan all possible options.)

Condition types for “Id” fields

Condition type Example
is not null Value is empty (NULL)
is null Value is not empty (NOT NULL)

Condition types for “boolean” values

Condition type Example
equal to Equals “numeric value”. Example: x = 1 or x = 0
is not null Value is empty (NULL)
is null Value is not empty (NOT NULL)

Formatters

By clicking on the button eg to the right of the field name, you can see which controls / functions you can use in the field

Selection_042

Example of formatter dialog

Selection_043

Formatters for “string” values

Formatter Description
Number of characters Returns the number of characters in a text field. Ie if you just want results where a specific field is over x number of characters.
Telephone control Simple validation if field is a telephone number. This can be customized as needed. Returns 1 (true) or 0 (false).
SSN/Orgnr control Checks that orgnr or person number is correctly constructed with control digit. Returns 1 (true) or 0 (false).
Email control Easy check if field has correctly constructed email address. Example: %_@%_%.__% This control can be customized as needed. Returns 1 (true) or 0 (false).
Hash Creates a hash value of the input field.

Formatters for “numeric” values

Formatter Description
NullToZero Set “0” instead of NULL. If the value is NULL.

Formatters for “date” values

Formatter Description
Year By putting this function on a date field, the result will return the year. Example 2014-06-25 = 2014.
Quarter By putting this function on a date field, the result will return the Quarter. Example 2014-06-25 = 2.
Month By putting this function on a date field, the result will return the month. Example 2014-06-25 = 6.
Month name By putting this function on a date field, the result will return the month’s name. Example 2014-06-25 = June / June.
Day By putting this function on a date field, the result will return the day. Example 2014-06-25 = 3 (Wednesday, 3rd day of the week)
Weekday By putting this function on a date field, the result will return the current name. Example 2014-06-25 = Wednesday.
Week By putting this function on a date field, the result will return the week number. Example 2014-06-25 = 31.
Week (iso) By putting this function on a date field, the result will return the week number. Example 2014-06-25 = 31. Same as above fixed Swedish standard where the week numbers may differ from the international standard.
Only date If the date field contains a time, only date is returned without time. Example: “2016-12-13 10: 36: 04.080” = “2016-12-13”
Days since Counts number of days since X date to todays date (getdate())

Formatters for “id” fields

Formatter Description
No formatter for Id fields

Formatters for “boolean” values

Formatter Description
To 0/1 Set “0” instead of NULL. If the value is NULL.

Formatters for “Lookup” values

Formatter Description
No formatter for Id fields

Leaves formatter

A leaf formatter is a function that allows one in a condition to submit a dynamic value. That is, a non-static value. A leaf formatter calculates the in-value for each time a question is run.

Selection_045

Leaves values formatter for “string” values

Leaves formatter Description
No formatter for string values

Leaves values formatter for “numeric” values

Leaves formatter Description
Todays date (int) ± X days Convert “Todays date” to a numeric value (int). Example, the date value 2019-05-30 is converted to 20190530

Leaves values formatter for “date” values

Leaves formatter Description
Todays date Returns today’s date and time. Example: ”2016-12-13 10: 36: 04.080”
Todays date ± X days This is based on the current date where you then set + or - number of days. Tex Today’s date - 180 days. This results in the date on which the criterion is based is 180 days back in time, counting from todays date.
Todays date ± X month This is based on the current date where you then set + or - number of months. Tex Today’s date - 6 months. This results in the date on which the criterion is based is 6 months back in time, counting from todays date.
Todays date ± X year This is based on the current date where you then set + or - number of years. Tex Today’s date - 2 years. This results in the date on which the criterion is based is 2 years back in time, counting from todays date.
Todays date no time Returns today’s date without time. Example: “2016-12-13”
Process date Returns a date from a table called “MD.ProcessTrack”. By submitting a key, such as integration.erp.customer, you get the latest date for this key. This table and key is controlled by two procedures MD.SetProcessTrackStart and MD.SetProcessTrackEndDate. These two use local time zone. Instead, if you want to use UTC dates, run with MD.SetProcessTrackStartUTCDate. You can also force the end date by using MD.SetProcessTrackEndDateForce or MD.SetProcessTrackEndDateForceUTCDate.

Clarification about “Process date”

Process date is depending on date values from a table called “MD.ProcessTrack”. The “process track” table is controlled via two procedures that add and update data in this table. The procedures is MD.SetProcessTrackStart and MD.SetProcessTrackEndDate. Both procedures require an input parameter in the form of a key that you choose. For example: An integration is starting. Before the integration starts you can set a start date via procedure MD.SetProcessTrackStart and the key = “integration.erp.customer”

exec MD.SetProcessTrackStart ‘integration.erp.customer’

Selection_046

The integration continues and when it is finished with a phase it is dax to put an end to this phase. This is done via MD.SetProcessTrackEndDate and the same key.

exec MD.SetProcessTrackEndDate ‘integration.erp.customer’

Selection_047

What happens then is that the value in the date field “LastStartDate” is moved to the date field “LastEndDate”, for the same key. In this way you do not get any slip in the integration, ie nothing that ends up between the chairs

Selection_048

And in the selection tree

Selection_050

The result of this will be:

Selection_049

Note that the date value for “integration.erp.invoices” is NULL in the table. If the value is NULL, the function still returns a date set to “1900-01-01”.

Leaves values formatter for “Id” fields

Leaves formatter Description
No Leaves formatter for Id fields

Leaves values formatter for “boolean” values

Leaves formatter Description
No Leaves formatter for boolean values

Leaves values formatter for “lookup” values

Leaves formatter Description
No Leaves formatter for lookup values

Result

By clicking over to results by clicking on the blue block or clicking on “Results” in the middle section. Here are the result columns that are in the template. By default, it is only the Id that is the basis of your data source. In this example, “PersonId”.

Selection_054

The middle section looks just like the same as when standing on “Condition”. That is, the structure of the data source’s all attributes.

To bring in more results columns, it is easy to do this by “drag and drop” or by “double-clicking” the columns in the middle section.

Drag and drop

Pick your attribute and drag it over to the result tree and drop it.

Selection_055

Double clicks

Just double-click your attribute, and it will fly over to the result tree.

Selection_056

Renaming fields

If you want to rename the result columns, you do this very easily by clicking with the cursor on the existing text. Then it lights up and you can change the name.

Selection_074

Column order

In order to change the order in which the columns should be located, one can easily change these by grasping the symbol for the field to the far left and pulling it up or down to the position that you wish it to lie on.

Selection_057

Action bar

At the bottom is a menu of action buttons. These perform various operations on your selection.

(A bit more detailed about Action bar than in the overall description).

Selection_009

Selection_012 = Run the entire selection and return the result. (Returns only 1000 first lines).

Selection_059

Selection_013 = Exports the entire selection to Excel.

Selection_065

Result exported to Excel

Selection_066

Selection_014 = Displays the actual SQL query set against the data source.

Selection_060

Selection_015 = Scans all the result columns from the question. Displays, for example, the data type and size. (Advanced use only).

Selection_061

Selection_016 = Shows the history of all runs against selection. Shows, for example, the number, how it was run, by whom and when.

Selection_062

Selection_017 = By clicking on the button “Actions” you can perform an action on the result that your question has. By default, there are no actions enabled for selection templates. Instead, one usually uses flows to perform actions. An action could be a send E-mail or SMS to the target audience. The disadvantage of running actions directly on a sample template is that you do not know what happens after the action is completed. An action that is run via a flow can later be captured responses etc to see what happened and that you save the result that the action ran on.

Selection_063

Selection_018 = Shows scheduling. For example if the selection is scheduled for an action.

Selection_064

Result “Sort order”

To sort, select the column you want to sort and click in either:

  • ASC - Ascending: From A to Z, From Least to Largest
  • DESC - descending: From Ö to A, From Largest to Least

If you have chosen to sort by several columns in the same selection, the sort order is sorted out in the order in which the columns are in the result tree.

In this case it is: Order by Firstname asc, Lastname desc. The priority is from top to bottom / left to right.

Selection_067

Format results

For each result column there are opportunities to format the value. One is dependent on the type of field in question. There are 4 different types of fields that you apply this to Text Selection_033, Numeric Selection_036, Date Selection_037 and Boolena Selection_038. It is also possible to add custom formats in addition to those that are standard

Selection_068

No formating

Selection_070

With formating

Selection_069

You can read more about the formatters in Condition section: Formatters

Aggregate results

For a result column you have the opportunity to aggregate the value. The aggregation is controlled by the type of field in question. The same as for formatting function is the text field, numeric field or date field that controls which aggregation function is possible to apply. There is also the possibility of adding own aggregation functions in addition to those that are by default

Selection_072

No aggregation

Selection_071

With aggregation

Selection_073

Formatters for “string” values

Aggregator Description
Count Counting rows

Formatters for “numeric” values

Aggregator Description
Avg Calculates the average
Sum Sums the value
Count Counting rows
Max Calculate the maximum value
Min Calculate the maximum value

Formatters for “date” values

Aggregator Description
Count Counting rows
Max Calculate the maximum value
Min Calculate the maximum value

Formatters for “boolean” values

Aggregator Description
Count Counting rows

Formatters for “id” fields

Aggregator Description
Count Counting rows

Visibility

Among the result columns you can also choose to hide a column. That is, the column should be included in the selection but not shown in the result. This is useful if, for example, you want to sort by a column that should not appear in the result. Ex. sort by the aggregated column “Balance” but not show the balance in the result.

Selection_075

Delete result column

To remove a column from the result, simply click on the trash to the far right.

Selection_076

Result functions

At the top of the head of the result tree is function for how the result is to be delivered

Selection_077

Return TOP number

Below, the first 10 lines will be returned regardless of whether the result would have more than 10 entries in the result.

Selection_078

Return TOP%

Below, the first 10% will be returned.

Selection_079

Distinct

Below, only unique lines will be returned. That is, if the result should contain duplicates, these will be filtered off. However, this feature can affect the performance of your selection.

Selection_080

Read uncommited

By clicking in “Read uncommited” means that the selection will not generate locks against your data source. However, this may mean that there is a risk of not getting the latest information from certain data, eg if someone simultaneously saves information about a person when you run your question. However, it is recommended to run with Read uncommited because it can speed up the issue and you do not risk locking a data source.

Selection_081

Calculated fields

At the bottom left, under the result columns. There is a plus button. Click on this to add a calculated field. This function can be used to concatenate or reformat result columns.

Selection_082

Adds a new calculated column. It will first take the name “Column_x”, this could be renamed to something better. There is a text box that can be opened when clicking there. Here you enter the syntax to make a calculation. Common SQL code works in this. (This is a bit more advanced so it requires a little more knowledge on just sql to use this function).

But in this example I want to merge two of my result columns. First name and Last name.

Selection_083

Then replace the name with a more appropriate name.

Selection_084

See the result below of your calculated column.

Selection_085

You can do many advanced calculations with this function. For more examples, see the “Best practices” section.

Advanced features

Levels

By adding a level to your selection is meant that you have the opportunity to make a new selection above your main selection. See the example below.

In my basic question, I choose an “aggregator” to summarize (Sum) the balance for my people. That is, merge the balance of all accounts that a person has. For example, if a person has 3 accounts, the balance for all 3 accounts will be summed up to one row per person, and not 3, one for each account.

Selection_086

Se the result of the aggregation below.

Selection_087

After having aggregated (summarized) balances on all persons, we should now add a “Level” to our main selection. Click the button with the plus sign Selection_089 between the sections (Blue frame) to add a level. The “1” button Selection_090 belongs to your main selection (Green frame).

Selection_088

Selection_091

1 Your new level. It gets the number “2”. If you add another level, it will get the number “3”. You can easily jump between these levels if you want to see what lies behind or change a criterion in an underlying level.

2 Trash can to remove a added level.

3 Trees with selectable attributes. These attributes come from the result columns in your main question. You can now use this in the same way as before, ie add conditions and adjust among your result columns.

Make a new condition on you new level

In this example, I have aggregated balances for all accounts to a total amount per person. Now I just want to produce people who have more than 10,000,000. Do this acc. below:

1. Select "Total balance"
2. In the drop down for the condition types you choose "greater or equal to"
3. In the box to the right, type "10000000"
4. Then use "drag and drop" or "Add" to add the new criteria to your selection tree for "level 2".

Selection_092

Then test by analyzing your new level (2)

Selection_094

Note that in level two you have only 4988 people left based on your main question. This means that there are 4988 people who have a total balance of over 10,000,000.

To see how many people are in the origin, just click Level “1” and analyze that level to compare.

Selection_093

Adjust your results columns in your new level. Same as before, you can remove column, make new aggregations and adjust sort order for the result.

In this case I have removed some columns, and adjusted the sort order for ” to descending

Selection_095

Result of “Level 2”, descending order by “Total balance”

Selection_096

Virtual view

Creating and using virtual views is an advanced feature that is only available to administrators. A virtual view like this can be an advanced selection template that can then be reused in the admin tool when building new data sources.

Selection_097

Templates in templates

A good feature to use is to reuse sample templates. You can thus build up a library of sample templates that you can use again and again. In order for this to be possible, my new selection template is based on the same data source as the old template. Do this:

1 Start by creating a new blank selection template against the data source you want.

2 Find your old selection template that already has a finished selection.

3 Then bring the mouse pointer and drag and drop the selection template into your new selection tree.

Selection_098

The template that is now in the selection tree has got a yellow frame. The yellow frame means the same as the operator “IN”. IN means include which in this case means that you reuse a selection criterion from an old template.

Selection_099

You can easily change your condition on this template by using the 3-point menu to the right. Click on it and well another card type, eg “not .in”

Selection_100

Now the frame changes color to red. The red frame has the same meaning as “NOT IN” which means “excludera”, in this case everything that comes from your old template is excluded.

Selection_101

Terms between columns

There is also a function for setting conditions between two columns in a sample template. For example, I want to bring out all the people who have been updated after being created in the system. Among the selection fields for person I have two fields. One for Created date and one for updated date:

1. Select the field "Updated".

2. Select the condition type "greater than".

3. Then drag the field "Created" and release it in the date entry box.

Selection_102

Then use “day and drop” to release your condition in to the selection tree”

Selection_103

Then try using the “show sql” button to see your terms.

Selection_104

Use your own SQL code

If you need to write your own sql code, there are no obstacles to it. However, it is recommended that you create an empty template and reuse names etc. from the result columns that already exist. If you need to write your own sql code, there are no obstacles to it. However, it is recommended that you create an empty template and reuse names etc. from the result columns that already exist. Sometimes when it is very advanced selection that needs writing, this is an excellent function.

Selection_105

When you are in the “View sql” box there are two buttons on the far right. “Save” and “Delete”

Selection_106 = Save your question

Selection_107 = Reset your question

Once you have saved your custom sql question and clicked on the “Save” button, this selection tree will be disabelled. This means that you can no longer use the usual feature to build your selection as long as you use your own sql.

Selection_108

By clicking on the “RESET SQL” button you can reactivate the default tree again.