Auto Action not working as expected

4 months 1 week ago - 4 months 1 week ago #316404 by adinet
Hi Team, I have been trying to implement an auto action to update some fields in CB but it isn't working as expected.
Could you please have a look and let me know what I am doing wrong.
We have created text fields in CB that are being populated from another table to create read only fields to show in CB lists.
The format of the scripts need to be as in the images as the fields in the tables are integers and need to represent actual text results.
Global

There are no conditions
The Script in the Actions window is only one of a few. This script changes the value of say 1 to Active and so on.

Action

Output is none
Uses is 0

Params

I am currently running an update script in the database but require these fields to update automatically.


The List looks like this-

I have tried creating triggers in the database but they wont allow updating the table comprofiler.
So I'm back to trying to figure out how to make Auto Actions work with this.

I also have the query plugin installed and tried to create a query to just show a value in the list for a year of subscription but all entries where the same.
As this query to show which year the subscription was valid for.
SELECT
    (
        CASE WHEN ourtable_osmembership_subscribers.plan_subscription_from_date 
        
        BETWEEN '2018-03-01 00:00:00' AND '2019-03-01 00:00:00' THEN '2018 - 2019' 
        
        WHEN ourtable_osmembership_subscribers.plan_subscription_from_date 
        
        BETWEEN '2019-03-01 00:00:00' AND '2020-03-01 00:00:00' THEN '2019 - 2020'
        
        WHEN ourtable_osmembership_subscribers.plan_subscription_from_date 
        
        BETWEEN '2020-03-01 00:00:00' AND '2021-03-01 00:00:00' THEN '2020 - 2021'
        
 ELSE 'unknown'
    END
)
FROM
    ourtable_osmembership_subscribers
INNER JOIN ourtable_comprofiler  ON ourtable_osmembership_subscribers.user_id = ourtable_comprofiler.user_id  
WHERE ourtable_comprofiler.user_id = ourtable_osmembership_subscribers.user_id

but it only shows the value 2018 - 2019 in the lists

Kind Regards Adrian
Attachments:
4 months 1 week ago #316417 by krileon
When do you need the auto action to take place as your trigger list doesn't make a lot of sense. You're acting on the auto action general url, auto action users url, after Joomla profile save, and after CB profile update (backend only).

If you don't need this information actually stored in the users profile and just need it for display purposes then I highly suggest just using CB Query Field to query for and display it on profile without having to deal with any storage at all. Using the query you provided above I've adjusted it to be dynamic based off the profile displayed in the below which should provide this for you.

SELECT
    (
        CASE WHEN `plan_subscription_from_date` 
        
        BETWEEN '2018-03-01 00:00:00' AND '2019-03-01 00:00:00' THEN '2018 - 2019' 
        
        WHEN `plan_subscription_from_date` 
        
        BETWEEN '2019-03-01 00:00:00' AND '2020-03-01 00:00:00' THEN '2019 - 2020'
        
        WHEN `plan_subscription_from_date` 
        
        BETWEEN '2020-03-01 00:00:00' AND '2021-03-01 00:00:00' THEN '2020 - 2021'
        
 ELSE 'unknown'
    END
)
FROM `ourtable_osmembership_subscribers`
WHERE `user_id` = '[user_id]'


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Templates - CBSubs - Hosting - Forge - Incubator - GroupJive
--
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 EST to 4:00 PM EST. 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.
The following user(s) said Thank You: nant
Moderators: beatnantkrileon
Time to create page: 0.436 seconds

Facebook Twitter LinkedIn