Selection¶
Selection is like a lookup function where you can view and search for data in available data sources (connections). If your CRM is a data source, you can for example search for “All active customers where type is Company” by using search conditions.
In the selection you can set certain conditions to get the right selection of data, and also present just the data you’re interested in using the result columns. The level of complexity when sorting out your data, and setting rules for what results to display is depending on your needs. It can go from very simple to very complex depending on what conditions and attributes you set.
How to create a new selection¶
1. Create a folder
Start by building up your structure with folders in the navigation tree.
2. Select folder
Select the folder to which you want to create a new selection for.
3. Create new selection OR select from template
Click on the three-point button to the right and click “New selection”
Select either to create new “Without template (blank)” and jump over to step 5, or from an existing template (step 4).
4. Select from template
By selecting from a template, you get the same data source, selection name and all pre-defined conditions and results from the template.
Example of a template - All deals where no conditions are set (to give all results) and the result columns are defined.
5. Choose your datasource
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 list is based on whether you choose prioritized or non-prioritized data sources. By default, prioritized data sources are presented, so if you’re looking for a data source that does not appear in the list, turn off the setting for “prioritized” selections to display all. Select the data source.
After selecting the data source, your selection template will automatically be saved. By default, the template is empty, which means that you need to add rules and result columns to it.
6. Name the selection
Give your selection a suitable name. To save just click outside the settings box, and the name together with your settings will be saved automatically.
7. Publish
To make a selection available outside of Sweet Automation, with an API for example, it needs to be publised. When activating the setting Published you are able to select what groups that are allowed to use the selection.
Note
A selection must for example be published for it to be visible in Sweet Performance as a data source to create a chart or dashboard.
Selection settings¶
The settings of the Selection can be managed upon create, or by using the cogs in the main area of an existing selection, right next to the name of the Selection.
After a selection has been created (when a datasource has been selected), that source cannot be changed. To change datasource a new selection must be created. The settings that can be updated are the Name, Key, Status, Visibility and Publish settings.
In the Settings top bar you have three buttons
- Export your selection in order to later be imported to another environment, or for backup purposes
- Copy the selection to create an exact copy of it, and the last button
- Delete the selection, to remove it entirely
Note
A selection that is related to other selections or flows cannot be deleted.
Statuses of the selection
- 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.
Tip
Only use Sealed after finishing, testing and confirming the selection properly, after sealing it changes cannot be made by you anymore.
Visible
- On = Selection is visible for everyone.
- Off = Selection is only visible for you and administrators.
Use as template
- On = The selection will be available as a template when creating new selections, data source, conditions and result columns will be inherited to the new selection.
- Off = The selection will not be available as a template
Published
- Active = The selection is available for Sweet Automation’s API. When publishing the selection you need to define for what users or user groups it should be available, and what kind for rights they should have.
- Inactive = The selection is not available for Sweet Automation’s API.
- About the API settings
- Propagate to apply the settings fo sub-user groups
- R = the permission to Read (see) data from the selection
- W = the permission to Write (update or overwrite) data to the selection
- E = the permission to Execute (run or get) data from the selection
- A = the permission to Read, Write and Execute (all) data from the selection
Read more about how to set up the API here
Conditions¶
In the main area of the Selection, you will find the data sources and the available attributes for each of them. The attributes are grouped to easier find what the user is looking for. The structure and grouping of the attributes of each datasource is controlled in the Administration and Metadata.
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.
= Alphanumeric = Plain text
= Id field = A unique ID number
= Lookup = Drop down menu that populates selectable types
= Numeric = Numerical value
= Date = Dates
= Boolean = A bit value. Could be 1 or 0 /True or False
Add or set a condition¶
Example: Find all Sweet Form Survey’s created in 2023.
1 Select the condition you would like to use, here CreatedDate, which is a date attribute
2 Select the attribute Between and set the right dates, here January 1st until December 31st
3 Click Add or drag and drop the condition to the right place in your operator tree to the right. By default the operator AND will be applied if no choice is made.
Condition types¶
Condition types you define when you are building a criteria for your selection. Below you see where to find them.
Condition types for “Alphanumeric” values
Condition types for “Numeric” values
Condition types for “date” values
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 types for “boolean” values
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.
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.
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’
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’
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
And in the selection tree
The result of this will be:
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 |
Workspace for conditions and results¶
On the right-hand side of the main area, you adjust and modify your conditions and results after choosing them by using the operators. Depending on whether to work with the selection conditions or results, the appearance of this area changes. Normally you work with both conditions and results (the tab next to Conditions).
Several conditions can be used at the same time, the operators AND, OR, NOT IN and IN can all be combined in order or include or exclude results from your selection datasource. See the chapter below for Operators.
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.
Above the structure are two tabs. Condition and Result. These control whether you work with filtering (conditions) or result columns.
Operators¶
If you have chosen to work with conditions (the tab in the attributes tree) you will see a group of operators at the top right area of the workspace, “AND”, “OR”, “NOT IN” and “IN”. By dragging and dropping values from the selection into the operators tree, one can combine them in order to get the requested results. Initially, the selection tree has only one node, “AND”. This is the main node that is the basis for your selection.
Below are the various operators describe in detail:
- AND
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
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
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
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”
Example
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.
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.
Build the operator tree¶
By “drag and drop” can build up the 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.
Then continue taking a new operator.
Count and analyze¶
Up to the right of the selection tree are two buttons, Count and Analyze.
Count = Summarizes the whole question. (Total of all parameters together). The fastest way to count the total number for your selection
Analyze = Analyzes and summarizes at all levels, including the totals. This can take a little longer to run compared to Count.
Example advanced
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 are Private persons who have one or more accounts, a related entity.
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
By analyzing the selection above you can see the number of hits per node. Counting the question would only give a result at the top AND level.
Delete or restore deleted conditions¶
In the top right corner of the Selection main area, there is a button with a Recycle bin. It can be used to drag conditions, results or operators to remove it. It also works as a place to store removed conditions, but only keeps them until you either leave the Selection, or refresh the page.
Delete a condition If you want to remove a condition from the selection tree, click the Recycle bin on the same row, or drag it to the Recycle bin.
Or
Delete an operator or all conditions in an operator block If you want to remove an operator or a operator block from the selection tree, drag it to the Recycle bin button above, or click the 3-dot button on the operator and click Remove operator.
**Restore deleted ** The deleted conditions can be restored until you refresh or leave your selection template, by clicking the button Recycle bin the deleted conditions can be found and dragged and dropped back to the operator tree.
More menu¶
There is a three-point menu button 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.
Depending on whether it is a operator block or a conditional block the content of the three-point menu is changed
Operator block
Condition block
The view of the conditional block depends on different datatypes. (The example below shows datatype - “datetime”)
Action bar¶
At the bottom is a menu of action buttons. These perform various operations on your selection.
Below are the different actions described in detail
SQL query (advanced)¶
Displays the actual SQL query set against the data source. The query can be edited in the SQL input field, to save the changes use the Save button (disk symbol). To discard the changes, use the Reset SQL button and the changes will be removed. See an advanced description here
Note
If changes are made directly in the SQL, the drag and drop/ simple management for the results, can’t be used anymore.
Scan schema¶
Scans all the result columns from the question and gives detailed information about the data. Displays, for example, the data type and size. (Advanced use only).
History¶
Shows the history, as a log, of all runs against selection. Shows, for example, the number, how it was run, by whom and when.
Actions¶
Actions displays possible actions that can be scheduled and run against the selection. 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.
Result¶
The results tab is the place where you get to configure what result columns will be used in the Selection. It will be the outcome and result when you run a Selection question. Below it’s described how to do it, and how to manage it. By default the ID of the entity is the only result, until you add more attributes yourself.
The result is managed in Selection, access it by clicking the tab Result, next to Conditions. The available result columns of the datasource are displayed in a tree structure.
Add a result column¶
You can add a result to the list by either dragging the attribute over to the result tree and drop it wherever or just double-click your attribute, and it will be added to the bottom of the result tree.
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.
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.
Change “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.
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 , Numeric , Date and Boolena . It is also possible to add custom formats in addition to those that are standard.
No formatting
With formatting
You can read more about the formatters in Condition section: Formatters
Aggregate results¶
You have the opportunity to aggregate the value for the result columns, the field type determine what aggregations can be performed. 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 as an advanced feature.
No aggregation
With aggregation
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 columns. 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 “Sum” but do not show the sum in the result.
Delete result column¶
To remove a column from the result, simply click on the trash to the far right.
Result functions¶
At the top of the head of the result tree is function for how the result is to be delivered
Return TOP number
This can be used if you have a large data set and would like to limit the number of results, you can either choose a fixed number of results, or a percentage of the total results.
Below, the first 100 lines will be returned regardless of whether the result would have more than 100 entries in the result.
Return TOP %
Below, the first 10% will be returned.
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.
Read uncommited
By clicking “Read uncommited” 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 execute and you do not risk locking a data source.
Advanced features¶
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.
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.
Then replace the name with a more appropriate name.
See the result below of your calculated column.
You can do many advanced calculations with this function. For more examples, see the “Best practices” section.
Levels¶
By adding a level to your selection, you have the opportunity to make a new selection above your main selection. See the example below.
Example: In below example an “aggregator” is used to summarize (Sum) the account balance for the owners of a bank account. 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.
See the result of the aggregation below.
After having aggregated (summarized) balances on all persons, add a new “Level” to our main selection.
Click the button with the plus sign 
between the sections (Blue frame) to add a level.
The “1” button 
belongs to your first level selection (Green frame).
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 criteria in an underlying level.
2. Trash can to remove a 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, balance is aggregated for all accounts to a total amount per person. To show only 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".
Then test by analyzing your new level (2)
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.
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 some columns are removed, and adjusted the sort order to descending.
Result of “Level 2”, descending order by “Total balance”.
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. When in the selection result tab, click the button Create virtual view to create it.
Selection sample templates¶
A good feature is the selection sample templates. You can thus build up a library of sample templates that you can use again and again. In order for this to work, the new selection template must be based on the same data source as the old template. Follow the steps below to create new selection based on an existing selection template.
1. Start by creating a new blank selection with the data source you want.
2. Find your old selection sample template in the folder tree, that already has a finished selection. For this to work both selections must use the same datasource.
3. Then bring the mouse pointer and drag and drop the selection template into your new selection tree.
The template that is now in the selection tree has got a yellow background. The yellow background refers to the same as the operator “IN”. IN means include which in this case means that you reuse a selection criterion from an old template.
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”
Now the background color of the condition changes to red. The red background refers to the operator “NOT IN” which means “exclude”, in this case everything that comes from your old template is excluded.
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:
- Select the field “Updated”.
- Select the condition type “greater than”.
- Then drag the field “Created” and release it in the date entry box.
Then use “day and drop” to release your condition in to the selection tree
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. Sometimes when it is a very advanced selection that needs writing, this is an excellent function.
When you are in the “View sql” box there are two buttons on the far right. “Save” and “Delete”
= Save your SQL question
= Reset your SQL 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.
By clicking on the “RESET SQL” button you can reactivate the default tree again.