A tricky auto action

Okay here's a tricky auto action.

The auto action type is "Field"

I would like to populate/alter a field if one of the conditions (which is another field in the profile is the same as "another" user profile).

For example, User01's cb_department=human-resource and his cb_level=high..

When a new user, say User02 registers, he selects his cb_department=human-resource.. I would like the auto action to fire upon registration and populate User02's cb_level to "high" because his cb_department is the same User01's cb_department.

Meaning that the auto action needs to look for another user or user(s) with a cb_department of the same value and populate the cb_level field..

I hope you understand.. I am working around the value of the field should be something like this:

Field to alter: level (cb_level)
Operator: Set (Field=Value)
Value: [cb:userdata field ="cb_level" cb_department="***is the same as some other user's cb_department***"]

You'll need to use a CB Query Field to perform a database query and find an existing user of a department and grab their level then supply it as the value for your field. Example as follows

Query Field (cb_query):
Field: Level
Operator: Set
Value: [cb:userfield field="cb_query" /]

Thank you so much.. i need to start learning to manipulate the other tools as well.. thanks for showing me the way!

Just another scenario..

if i have multiple conditions, should i write the query the following way.. example below is for 2 conditions (cb_department and cb_unit)

SELECT `cb_level` FROM `#__comprofiler` WHERE `cb_department` = '[cb_department], `cb_unit` = '[cb_unit]' LIMIT 1

You don't separate where statements with a comma, you need to separate them with AND. Please see the below MYSQL documentation and MYSQL tutorial.

MYSQL Documentation: dev.mysql.com/doc/refman/5.6/en/index.html
MYSQL Tutorial: www.w3schools.com/sql/default.asp