Group management query language

In this post we are going to visit a quite powerful feature in HRM TM portal, group management.

With group management you can set up groups of users, activities and organizations. And use them for different purposes for example showing news and information for a targeted group of users or displaying a list of recommended courses for a specific group of users

In this post we are not going to focus on what you can do with groups but focus on how you can set up groups and the syntax for creating groups.

First go group management, depending of the version you have you will find it under system administrations menu in 4.5 and up and on the start page for system administrators on 4.3 and 4.4. You should then se something like below.

Create a new user group by clicking add.

To start with you can here handle the different name of the group in multiple languages, assign it some id and a brief descriptions on whats you are selecting. At the bottom of the screen is where the interesting stuff is. Here you can build up an expression that selects out the users you want. Lets click on Add Expression

Now the first drop down list contains the different groups we are supporting for this kind of group, in this case a usergroup.

User group contains a list of fields that you can use to filter out users in drop down 2, it is basically the information you will find on employee page. I am not going to cover what every single field means as it depends on you installation. But rather focus on what you can express here. The third dropdown contains commands or logical operators for selecting users and those are

Operator	Description	Example
=	Equal to	role = “5”
<>	Not like	usrid <> 15
<	Smaller than	employmentdate < 2009-10-15
>	Greater than	employmentdate > 2009-01-01
<=	Smaller or equal
>=	Greater or equal
IS	IS a variant of equal for objects	ie. you want to find out all that has NULL as positionid positionid is null or employmentenddate is null will give you everyone that has not quit
IN	IN value must be in a list	positionid in (“5”, “6”) will give you all users with positionid 5 or 6
NOT IN	same as above but negtative selection
LIKE	a text contains the phrase the wildcard character is %	lastname like “%jens%” will give you back Johnsson Jensen Jensen Jens Jenson Jensan ojens
NOT LIKE	same a above

If you swith to freetext editing you can also use built in functions and those are mostly do to with dates

• dateof(datetime): datetime; Creates a datetime with the date from the function argument, and no time.
• timeof(datetime): datetime; Creates a datetime with the time from the function argument, and no date.
• year(datetime): int; Finds the year in the argument.
• quarter(datetime): int; Calculates the quarter of the argument.
• month(datetime): int; Calculates the month number of the argument.
• dayofyear(datetime): int; Calculates the day of year number of the argument.
• day(datetime): int; Calculates the day of month number of the argument.
• week(datetime): int; Calculates the week number of the argument.
• weekday(datetime): int; Calculates the day of week number of the argument.
• hour(datetime): int; Gets the hour of the argument.
• minute(datetime): int; Gets the minute of the argument.
• dayssince(datetime): int; The number of days that has passed since the argument until now (server time).
• monthssince(datetime): int; The number of months that has passed since the argument until now (server time).
• yearssince(datetime): int; The number of years that has passed since the argument until now (server time).
• daysbefore(datetime): int; The number of days from now (server time) until the argument.
• monthsbefore(datetime): int; The number of months from now (server time) until the argument.
• yearsbefore(datetime): int; The number of months from now (server time) until the argument.

This was a quick explanation of the operators in the Agresso HRM query language this you can also find in our WCF integrations services and filter management.

Enjoy

Christian