How to set a field based on a query with an auto action?

Hi!
I would like to update a custom CB field based on a select query with an auto action. But I don't know how to use the result of the select (e.g. a date) in the field-action.

Could you please give me some hints how to proceed? Can I store the result of the query in a variable and use it to fill the field?

Thanks a lot!

Frank

A query select field should already be stored in the database so you shouldn't need to store it twice. If you want to display it in a Custom HTML field you'd just substitute it in as shown in our substitution tutorial below.

www.joomlapolis.com/documentation/279-community-builder/tutorials/18353-using-substitutions-throughout-cb

Aside from that am unsure what you're asking. Are you wanting to update Field A while conditioning against Field B with Field B being your query select?

Hi!
Thanks for your quick reply.
The select is not on a field but I need to select a specific date for each user with a bit more complex statement with joins over a couple of tables.
That date I would like to store into a field and show it in CB to the user. I also want to store it in order to reuse the field for other purposes.

Thanks!

Best,
Frank

So you need to set a fields value with the result of custom code or custom SQL? If SQL you should be able to use a Query action and directly update _comprofiler table. If code then a Code action with Method set to PHP can be used to set values on $user then $user->store() it to save the values.

The field action has no support for setting a field based off a custom SQL unless you create a Query fieldtype, add your SQL there, then substitute it into the auto action to be stored into a field.

This brings up the question of does this really need stored in the database? For example does it need to be searchable? If not it maybe better to just use a Query field and query for whatever information in your other tables that you're needing.