In Data filters is a module that allows you to on the control panel, you can 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 To create a new data filter, click Create new 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 |
|---|
Note: It is not possible to change the data model when you have saved the data filter. |
...
Data filter design
Columns, Expand on by and Sort on by affect the Results preview but have no impact on the filter itself. The data filter is written in the Filter field.
| Info |
|---|
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. |
...
| Info |
|---|
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 |
|---|
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. |