[#7183] CB Auto Actions will not set field value when new user is added via back end

I'm using CB to manage club members/users. Each user is to be assigned a unique Membership Number ( not the User_ID)

I have a CB Auto Action set up with triggers "onAfterUserRegistration" and "onAfterNewUser", No Conditions, Action is "Query", No Parameters

set @next = ( SELECT MAX(`cb_membership_number`) +1 FROM `z5wrt_comprofiler`);
set @last = ( SELECT MAX(`id`) FROM `z5wrt_comprofiler`);
update `z5wrt_comprofiler` set `cb_membership_number`= @next where `user_id` = @last

This should get the next consecutive value for 'cb_membership_number' when the new user is created

This works as expected vie the Joomla Front end but does not work through the back end - Community Builder : User Management - +New

If i add a new user via the back end the field `cb_membership_number` contains a 0 (zero)

The Joomla front end will work with CB Auto Action set up with the trigger set to only "onAfterUserRegistration"

You're trying to guess the next user id, which is not reliable. Simplify it and substitute in the user id as follows.


Your onAfterUserRegistration and onAfterNewUser trigger usages are correct. Ensure Access is set to Everybody and User is set to Automatic. In Joomla #__ will properly replace with your table prefix so you do not need to manually specify it.

Sadly that does not work - Returns error 1093

You can't specify target table '#__comprofiler' for update in FROM clause SQL=UPDATE `#__comprofiler` SET `cb_membership_number` = ( SELECT ( MAX( `cb_membership_number` ) + 1 ) FROM `#__comprofiler` ) WHERE `id` = '6347'

You have to use a query in the from clause to make that work eg

UPDATE `#__comprofiler` SET `cb_membership_number` = ( SELECT ( MAX( `cb_membership_number` ) + 1 ) FROM (select * from `#__comprofiler`) as dummy ) WHERE `id` = ( SELECT MAX( `user_id` ) FROM (select * from `#__comprofiler`) as dummy2 )

But that still won't work when creating a new user via the backend.

So i tried a different approach ... Try updating a different record and check the correct values are calculated.

After trying many variations in ended up with the Following query which works via the back end

update `z5wrt_comprofiler` set `cb_notes`= CONCAT("Test : ",now()," | User ID : ",'[user_id]', " | Member : ", ( SELECT ( MAX( `cb_membership_number` ) + 1 ) FROM (select * from `#__comprofiler`) as dummy ) ) where `user_id` = 733

User ID = the new user account created. - eg 6366
Member = the next consecutive membership number. - eg 3854
These values are concatenated into cb_notes field of an existing record ie user ID 733
"Test : 2017-05-30 14:11:05 | User ID : 6366 | Member : 3854"

When i change the fixed user id from 733 to the CB variable '[user_id]' the query no longer works

update `z5wrt_comprofiler` set `cb_notes`= CONCAT("Test : ",now()," | User ID : ",'[user_id]', " | Member : ", ( SELECT ( MAX( `cb_membership_number` ) + 1 ) FROM (select * from `#__comprofiler`) as dummy ) ) where `user_id` = '[user_id]'

It seems that there is a problem in the timing of the SQL events - An SQL query triggered by "OnAfterNewUser" can not update the record for the new user that triggered the event.

It seems that there is a problem in the timing of the SQL events - An SQL query triggered by "OnAfterNewUser" can not update the record for the new user that triggered the event.

Yes, it most certainly can. Your issue is likely another store is occurring. Do you have any other actions acting on that trigger? The order your actions execute in is very important as a user store done after this query would override whatever changes your query made since the user store has no idea that change was made. Your auto action should be configured exactly as follows.

Global
Triggers: onAfterUserRegistration, onAfterNewUser
Type: Query
User: Automatic
Access: Everybody
Action
Query:
This of course does not work when using Joomla user manager. You must be managing users from CB > User Management. Beyond that I can't really help you any further as we do not provide coding support.

I only have one Auto Action enabled. I have others set up but they are all disabled

I have the settings set up as you have advised and yes i'm adding the user via CB > User Management not Joomla > User

This is looking more like a Bug that needs to be fixed. Even the simplest of queries will not work. The Query below works when a new registration is created via the website front end but fails when a anew user is created via CB > User Management

UPDATE `#__comprofiler` SET `cb_notes` = "test" WHERE `id` = '[user_id]'

The user store done in activateUser is what's overriding it, which shouldn't execute if the user is confirmed, approved, and not blocked. Your alternative is to use onAfterUpdateUser and check that cb_membership_number is empty under the conditions of your action. Your query does not refresh the user object as you're directly modifying the database that's why user stores after the fact erase your changes.