Data filters is a module that allows you to create fixed filters and queries against all data in the system (all data available via our public API). This is an advanced module that requires good technical skills.
Create a data filter
1. Go to the control panel and click Data filters in the General column.
2. Click Create new filter to create a new data filter. To edit an existing one, click the name of the filter in the list.
3. Enter a name, select a data model and click Save.
Note: It is not possible to change the data model when you have saved the data filter.
Info: The data model is described on the right of the page under Model. Click Explore API to view all models and extensions (the entire API).
Data filter design
Columns, Expand on and Sort on affect the Results preview but have no impact on the filter itself. The data filter is written in the Filter field.
Info: All data extracted via the API is always filtered on the permissions of the user logged in. The preview of the result is updated regularly when changes are made to the fields.
Columns
If this is blank, all columns in the model are selected. To view only specific columns in the result, enter the columns and separate them with commas.
Example: "Subject, Description".
Expand on
You can select here the relations on which the model is to be expanded. If you extend on a relation, you can type "RelationName/ColumnName" in the Columns field to select columns from the extension.
Example: "Responsible, Department".
Sort on
You can select here the columns on which the selection is to be sorted and the direction in which they are to be sorted.
Example: "Subject DESC, CreatedDateTime ASC"
Filter
This is where you enter the filter query to be used to filter your selection.
- The filters must be separated with either "and" or "or".
- Similarity is checked using the "=" character. The characters "<=", ">=", "<", and ">" are supported for comparison of data. The operators "contains" and "in" are also supported.
- "contains" is used as follows: Subject contains "kjell"
- "in" is used as follows: Id IN (1,2,3,4)
- By all means use brackets to distinguish the filters from one another, as shown in the example. If the list does not show what is expected, try putting brackets around things to clarify what you are asking for.
Example: "(Subject contains "kjell" AND Cause contains "hi") OR (Id IN (1,2,3,4)) OR (IncidentDateTime <= '2013-12-31')"
Info: Saved values do not always reflect what is displayed in the API when filtering on JSON-formatted fields. For example, a value may be saved as null (JSON field not populated), but the value in the API is false (due to the default value). To retrieve all values that are null or false, you can then query Feltnavn != true instead of Feltnavn = false.
Functions and variables
| Name | Purpose | Parameters | Example |
|---|---|---|---|
CurrentUserId() | Returns the ID for the user logged in, can be used as follows: | None | ResponsibleCoworkerId = CurrentUserId() |
CurrentDepartmentIds() | Returns IDs for all departments for the user logged in, can be used as follows: | None | DepartmentId IN CurrentDepartmentIds() |
Now() | Current time, can be used as follows: | None | PlannedStartDate > Now() |
CurDate() | Today's date, with no time, can be used as follows: | None | PlannedStartDate > CurDate(). The difference is that this has a time of 00:00:00, unlike Now(), which also has the current time. |
CurrentUsername() | Current username | None |
|
CurrentFullName() | The current user's full name | None |
|
CurrentGroupIds() | The current user's groups | None | Find employees who share some of my groups: GroupIds IN(CurrentGroupIds()) |
CurrentFunctionIds() | The current user's functions/roles. | None | Find employees who share some of my functions: FunctionIds IN(CurrentFunctionIds()) |
| CurrentCustomerIds() | List of Customers I'm participant on. Only for external users. | CustomerId IN CurrentCustomerIds() | |
| CurrentSupplierIds() | List of Suppliers I'm participant on. Only for external users. | SupplierId IN CurrentSupplierIds() | |
hasMember | Filter on data where an employee is a participant | Comma-separated list of employee IDs | Find data where I or employees with ID 2 and 3 are participants: hasMember(currentUserId(), 2, 3) |
currentUserIsMember | Filter on data where I am a participant. Quick function for HasMember(currentuserid()) | None | currentUserIsMember() |
currentUserIsMemberWithFunctions | Filter on data where I am a participant with a particular role/function | Comma-separated list of roles | currentUserIsMemberWithFunctions(1,2) |
| currentCustomerIsMember | Filter on data where a customer is participant, and I'm participant on the customer. Only for external users. | currentCustomerIsMember() | |
| currentSupplierIsMember | Filter on data where a supplier is participant, and I'm participant on the supplier. Only for external users. | currentSupplierIsMember() | |
| currentCompanyIsMember | Filter on data where a supplier or customer is participant, and I'm participant on one of the selected the suppliers/customers. Only for external users. | currentCompanyIsMember() | |
date | Purpose calculate a dynamic date | 1: Text | For example, you can write date(1. January), date(01.01.2016), date(now), date(first day of this year), etc. Example for retrieving records in the current year: RegisteredDateTime >= date(first day of this year) and RegisteredDateTime < date(first day of next year). Note that "less than first day next year" is used, because you also have to count records at 23:59:59 on 31.12. |
substringof | Text containing | 1: Text, 2: Field | Find all where subject contains "Vindu": substringof('Vindu', Subject) |
endswith | Text ending in | 1: Text, 2: Field | Find all where subject ends with a full stop: endswith(Subject, '.') |
startswith | Text starting with | 1: Field, 2: Text | Find all where subject starts with "Vindu": startswith(Subject, 'Vindu') |
length | Length of a text | 1: Field | Find all where subject is 5 characters long: length(Subject) = 5 |
indexof | Position of first text match | 1: Field, 2: Text | Find all where text 'vindu' is at character 8 in the subject: indexof(Subject, 'vindu') = 8 |
substring | Get excerpt of a text based on position | 1. Field, 2: Start, 3: Length | Find all where text 'vindu' is at position 3-6: substring(Subject,3,4) = 'vindu' |
notin | Is not among | Unlimited number of parameters | Find all that are not of type 5 or 7: TypeId NOTIN(5,7) |
in | Is among | Unlimited number of parameters | Find all of type 1, 2 and 3: TypeId IN(1,2,3) |
search | Searches all text fields | 1: Text | Find all containing the text 'vindu' in a text field: search(vindu) |
childrenof | Get child objects for an object | 1: Field, 2: ID | Find all registered with DepartmentId 1 or subtype: childrenof(DepartmentId, 1). Not supported in all modules. |
hastags | Filter on tags | Unlimited number of IDs | Find all registered with tag 1 or 5: hastags(1,5) |
not | Negative filter | Filter | Find all without tag 4: NOT hastags(4) |
year | Get year from a date | 1: Date | Find all registered in a specific year: YEAR(RegisteredDateTime) = 1972 |
month | Get month from a date | 1: Date | Find all registered in January (regardless of year): MONTH(RegisteredDateTime) = 1 |
day | Get day from a date | 1: Date | Find all registered on the first of every month (regardless of date): DAY(RegisteredDateTime) = 1 |
hour | Get hour from a date | 1: Date | HOUR(RegisteredDateTime) = 12 |
minute | Get minute from a date | 1: Date | MINUTE(RegisteredDateTime) = 59 |
second | Get second from a date | 1: Date | SECOND(RegisteredDateTime) = 59 |
round | Round numbers | 1: Number | Find hours containing decimal places: ROUND(TotalHours) != TotalHours |
floor | Round down numbers | 1: Number | Find all hour registrations where the number of hours is rounded down and is 10: CEILING(TotalHours) = 10 |
ceiling | Round up numbers | 1: Number | Find all hour registrations where the number of hours is rounded up and is 10: CEILING(TotalHours) = 10 |
Info: It is not possible at present to use functions in column definition, only for filtering. If you need more variables or functions, register n suggestion for improvement in the Customer Portal.