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

6 years 9 months ago - 5 years 9 months ago #294367 by adiehm
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"

Please Log in to join the conversation.

6 years 9 months ago #294397 by krileon
You're trying to guess the next user id, which is not reliable. Simplify it and substitute in the user id as follows.

UPDATE `#__comprofiler` SET `cb_membership_number` = ( SELECT ( MAX( `cb_membership_number` ) + 1 ) FROM `#__comprofiler` ) WHERE `id` = '[user_id]'

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.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

6 years 9 months ago #294416 by adiehm
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.

Please Log in to join the conversation.

6 years 9 months ago #294429 by krileon

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:
UPDATE `#__comprofiler` SET `cb_membership_number` = ( SELECT ( MAX( `cb_membership_number` ) + 1 ) FROM ( SELECT * FROM `#__comprofiler` ) AS cb ) WHERE `id` = '[user_id]'

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.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

6 years 9 months ago #294443 by adiehm
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]'
Attachments:

Please Log in to join the conversation.

6 years 9 months ago #294447 by krileon
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.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.231 seconds

Facebook Twitter LinkedIn