[SOLVED] Query field value persisted in comprofiler?

Use case:

Registered users are assigned to organizational regions, based on their addresses (US state)

Since addresses change adn the assigned region is purely derived data, I recalculate/requery the assigned region each time the profile is created/updated using a Query field type.

I was looking for the query result in comprofiler, but the field name never appeared, so I assume the field is virtual/dynamic, e.g. queried every time the profile is viewed and the specific values for each profile are not persisted in #__comprofiler table.

I built a number of mailing lists AcyMailing) and reports (Reportico) that filter on the region field value.

In Reportico, it's not a big deal, as I have built reporting-specific views at the db level using the same logic as the query field.

Inside Joomla, I have a problem - as the query field value isn't stored in comprofiler, it isn't available to other components that integrate with CB, like AcyMailing.

Is there a solution (using the currently available field plug-ins) that will
1) re-query a field's value on profile edit, and
2) persist the result in the profile as a non-virtual field?

If SQL were allowed in Auto-Actions, I would do it there.
Ideas?
Thanks,
Fred

A query field is just executing your query and outputting the results and has no storage. It will output the results fine through substitutions so if you've access to substitutions in your extension it'll work fine there. SQL can be executed in CB Auto Actions with a Query action.

Not sure how AcyMailing is pulling CB field values, but doesn't sound like they're using getFields API if the query field isn't being output, but maybe CB Content Bot and content.prepare usage will allow you to use substitutions in AcyMailing.

Kyle - thanks for the advice - I'll pursue those options.
You can close this...
Fred

Kyle -
Actually, one follow-on question -
using substitutions, may I use substitutions directly in the SQL of the Auto Action?
e.g.

UPDATE #__comprofiler AS c ......
...
WHERE c.id = [id]

Thanks,
Fred

No, but you can substitute in your query field and store its results to a text field using a Field action if you really need the value stored.