[SOLVED] CB Auto Action to update a field in another table of the DB

Hi Kyle,

Why is onCPayUserStateChange not listed in the drop down options or is it by any other name, I can't find it?

If I manually copy and paste onCPayUserStateChange then is comes and after I select it both User State Change and onCPayUserStateChange in the specified order are entered. Should I only use onCPayUserStateChange or should I leave both?

What is the difference between User State Change and onCPayUserStateChange?

Thank you

Why is onCPayUserStateChange not listed in the drop down options or is it by any other name, I can't find it?

It's listed as "User State Change". The trigger labels are more user friendly.

If I manually copy and paste onCPayUserStateChange then is comes and after I select it both User State Change and onCPayUserStateChange in the specified order are entered. Should I only use onCPayUserStateChange or should I leave both?

You only need 1. It's adding the existing value plus your custom value. It's a bit weird behavior, but that's how select2 does it (probably fixed in next select2, but waiting for it to stabilize more).

What is the difference between User State Change and onCPayUserStateChange?

None, it's the same trigger. Once is just the pre-existing trigger option and the other is the one you added (it's a tags usage).

It works with the "Before Draw Subscription" trigger, but not with "User State Change".

CBSubs SQL Actions doesn't do the job either.

Let me explain what am I trying to do:

I have a Free plan and a VIP plan.

First:

I need the value of a CB text field called "cb_vip" to be always 0 if the user is not a VIP member and 1 if the user is a VIP.

In my Free plan's CB Fields integration settings I have this:

Field "cb_vip"
Operator "Set: Field=Value"
Value "0"
Remove value on plan deactivation "No"(I've tried changing this to yes but it doesn't make any difference)

In my VIP plan's CB Fields integration settings I have this:

Field "cb_vip"
Operator "Set: Field=Value"
Value "1"
Remove value on plan deactivation "Yes"
Value on plan deactivation "0"

The value of the "cb_vip" field is not changed to 0 on VIP plan deactivation unfortunately, it changes to empty row, that's why I first need a CB Auto Action to change the value to 0.

Here is what is working currently, like I've said earlier:

Type: Field
Trigger: Before Draw Subscription
User: Automatic
Access: Everybody
Conditional: [cb_vip] Empty
Action:
Field [cb_vip]
Operator Set (Field=Value)
Value 0

If I change this to:

Type: Field
Trigger: User State Change
User: Automatic
Access: Everybody
Conditional: [cb_vip] Empty
Action:
Field [cb_vip]
Operator Set (Field=Value)
Value 0

It doesn't change the value to 0, but again to empty row (nothing is inside) and I can't use this field for list or other sorting. I need this field to have a value and not be empty.

Second:

Then, when I make sure that the "cb_vip" field is either 0 or 1 I need to update the corresponding field in my other table with the same value.

This is working currently:

Type: Query
Trigger: Before Draw Subscription
User: Automatic
Access: Everybody
Action: Query:
UPDATE `#__myothertable`
SET `vip_222` = (SELECT `cb_vip`
FROM `#__comprofiler`
WHERE `#__comprofiler`.`user_id` = `#__myothertable`.`222_id` AND `#__comprofiler`.`cb_vip` = 1);

UPDATE `#__myothertable`
SET `vip_222` = 0
WHERE `vip_222` != '1';

The "User State Change" trigger is not working again in the above action. Why?

Please tell me if there is any better way of achieving what I'm after?

Thank you,
Best regards,
ricco

You need to use User State Change for both of your actions. If you change a fields value for the user object with a Field action then a second action acts on that field then you need to enable reload user. Example as follows.

Action 1: Change field value
Action 2: Execute database query

They should be ordered as such in your auto actions. Action 2 should have Reload User enabled under its Parameters tab to ensure the new field value is picked up reliably.

The reason for this is CB Auto Actions can not know which variables are references. The only for it to do that is you have to specify which variables should be treated as references under Parameters. The easier solution in this case is to just have your second action reload the user object.

This should not be an issue if you're using CBSubs SQL Actions as there's no need to check a field value in it since their subscription would be setting them to VIP anyway.

Your query should also be updating 1 row. Not your entire table. That's going to lock an entire table. Adjust the where statement so the individual user updates as needed.

Based off the information you've provided I'd just do the below in CBSubs and let CBSubs easily handle the entire scenario. Including reversal.

Plan > Integrations > CB Fields
Field: VIP
Operator: Set
Value: 1
Remove value on plan deactivation: Yes
Value on plan deactivation: 0

Plan > Integrations > SQL actions
Activation: Deactivation:

I will try it now and report.

Thank you Kyle.