This plugin add two sql format for custom fields
$ git clone https://github.com/apsmir/custom_field_sql.git
custom_field_sql
1) Visit Administration->Custom fields. 2) Press the button New custom field. Select format Sql or Sql search. 3) Enter sql query
You can use parameters for sql expression. This may be id of issue %{issue_id} or id of project %{project_id}
You can use any form values as query parameter.
p0='%'+$('#issue_custom_field_values_31').val()+'%'
where
p0 - parameter name
%'+$('#issue_custom_field_values_31').val()+'% - any jquery expression to calculate parameter value
sql_search Query must have field 'value'. This field used be as field value. format: support multiply forms parameters. Parameters must be written in jquery.
Example 1:
"sql expression":
select subject as value, description as label from issues where subject like '%{p0}' and description like '%{p1}'
"sql form params":
p0='%'+$('#issue_custom_field_values_31').val()+'%'
p1='%'+$('#issue_custom_field_values_30').val()+'%'
Simple 2 (for MySQL):
"sql expression":
select subject as value from issues where id = if( ? ='new', id, ?);
"sql form params":
p0=window.location.toString().split('/').pop()
p1=window.location.toString().split('/').pop()
This expression window.location.toString().split('/').pop()
calculate issue id on form. For new issues calculated value = 'new'.
Query in sql search field can be executed by mouse click. Use parameter "search by click" in settings page.
sql_search -this format support sql-query for calculate default value . This query select initial custom field value for new issue from database.
Query can use parameters
view_customize/custom_field_autselect_first_value.js It is script for plugin "view customize" https://www.redmine.org/plugins/view_customize The script allows you to automatically select the first value for a custom field (drop-down list)
1) Delete all custom fields with format Sql. 2) Remove folder redmine_root/plugins/custom_field_sql 3) Restart Redmine