User update rule with Advanced Query not triggered

[kunal_saxena]

Hi,

I have a requirement to trigger user update rule when the following conditions occur:

• User status is inactivated AND
• User customproperty18 is null or blank AND
• User is a manager of contingent workers

To implement this, I have written the following query under User Update rule > Condition > Advanced Query:

a.statuskey = 0 AND (##a.statuskey isupdated##) AND (a.customproperty18 is null or a.customproperty18='') AND a.id in (select distinct manager from users where employeetype = 'Contingent Worker' and (leavestatus is null or leavestatus=''))

However, the rule is not getting triggered when I test for a user who satisfies the above condition.

Please advise.

Thanks,
Kunal

[NM]

@kunal_saxena what is the trigger condition you have set for the rule?

Is it to trigger when updated via UI or import?

[kunal_saxena]

@NM - When user updated via import

[NM]

@kunal_saxena so user is getting updated via import right?

Change the order and give it a shot 

##a.statuskey isupdated## AND a.statuskey=0 AND (a.customproperty18 is null or a.customproperty18='') AND a.id in (select distinct manager from users where employeetype = 'Contingent Worker' and (leavestatus is null or leavestatus=''))

One more point if above doesn't work

instead of emoloyeetype use employeeType

[Raghu]

@kunal_saxena  try with in condition

a.statuskey = 0 AND (##a.statuskey isupdated##) AND (a.customproperty18 is null or a.customproperty18='') AND a.id in (select distinct manager from users where employeeType in ('Contingent Worker') and (leavestatus is null or leavestatus=''))

[rushikeshvartak · Accepted solution]

Use below query

• Table name first letter should be capital

a.statuskey = 0 AND (##a.statuskey isupdated##) AND (a.customproperty18 is null or a.customproperty18='') AND a.id in (select distinct manager from Users where employeeType = 'Contingent Worker' and (leaveStatus is null or leaveStatus=''))

• You can always check from UI and logs
• 

[kunal_saxena]

Thanks @NM @Raghu @rushikeshvartak for your replies.

The advanced query suggested by @rushikeshvartak worked. User update rule is now triggered for the user.