apsmir / custom_field_sql

redmine sql custom field

Redmine sql custom field

This plugin add two sql format for custom fields

• sql - format for simple sql-expression.
• sql_search - format for search sql query with form parameters

Compatibility

• Redmine 5.0 or higher

Installation

• Clone or download this repo into your redmine_root/plugins/ folder

$ git clone https://github.com/apsmir/custom_field_sql.git

• If you downloaded this repo, make sure to rename the extracted folder to custom_field_sql
• Restart Redmine

Usage

1. Visit Administration->Custom fields.
2. Press the button New custom field. Select format Sql or Sql search.
3. Enter sql query

SQL fields and parameters

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.

Default value

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

• %{tracker_id}
• %{project_id}

Scripts

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)

Uninstall

1. Delete all custom fields with format Sql.
2. Remove folder redmine_root/plugins/custom_field_sql
3. Restart Redmine

custom_field_sql's Issues

rake aborted! when installing plugin using redmine docker

Hi @apsmir,

First: Thanks for your good work.

When trying out the plugin, I encountered installation problems when using a Redmine Docker environment.

Steps to reproduce:

/opt/redmine/data/redmine/plugins$ git clone https://github.com/apsmir/custom_field_sql.git
/opt/redmine$ docker-compose up

Output:

...
redmine_1  | rake aborted!
redmine_1  | LoadError: cannot load such file -- custom_field_sql/custom_fields/formats/sql
redmine_1  | /usr/local/bundle/gems/polyglot-0.3.5/lib/polyglot.rb:65:in `require'
redmine_1  | /usr/local/bundle/gems/polyglot-0.3.5/lib/polyglot.rb:65:in `require'
redmine_1  | /usr/local/bundle/gems/zeitwerk-2.5.4/lib/zeitwerk/kernel.rb:35:in `require'
redmine_1  | /usr/src/redmine/plugins/custom_field_sql/init.rb:2:in `block in <top (required)>'
redmine_1  | /usr/src/redmine/lib/redmine/plugin.rb:96:in `instance_eval'
redmine_1  | /usr/src/redmine/lib/redmine/plugin.rb:96:in `register'
redmine_1  | /usr/src/redmine/plugins/custom_field_sql/init.rb:1:in `<top (required)>'
redmine_1  | /usr/src/redmine/lib/redmine/plugin_loader.rb:31:in `load'
redmine_1  | /usr/src/redmine/lib/redmine/plugin_loader.rb:31:in `run_initializer'
redmine_1  | /usr/src/redmine/lib/redmine/plugin_loader.rb:108:in `each'
redmine_1  | /usr/src/redmine/lib/redmine/plugin_loader.rb:108:in `block in load'
redmine_1  | /usr/local/bundle/gems/activesupport-6.1.6/lib/active_support/callbacks.rb:427:in `instance_exec'
redmine_1  | /usr/local/bundle/gems/activesupport-6.1.6/lib/active_support/callbacks.rb:427:in `block in make_lambda'
redmine_1  | /usr/local/bundle/gems/activesupport-6.1.6/lib/active_support/callbacks.rb:198:in `block (2 levels) in halting'
redmine_1  | /usr/local/bundle/gems/activesupport-6.1.6/lib/active_support/callbacks.rb:604:in `block (2 levels) in default_terminator'
redmine_1  | /usr/local/bundle/gems/activesupport-6.1.6/lib/active_support/callbacks.rb:603:in `catch'
redmine_1  | /usr/local/bundle/gems/activesupport-6.1.6/lib/active_support/callbacks.rb:603:in `block in default_terminator'
redmine_1  | /usr/local/bundle/gems/activesupport-6.1.6/lib/active_support/callbacks.rb:199:in `block in halting'
redmine_1  | /usr/local/bundle/gems/activesupport-6.1.6/lib/active_support/callbacks.rb:512:in `block in invoke_before'
redmine_1  | /usr/local/bundle/gems/activesupport-6.1.6/lib/active_support/callbacks.rb:512:in `each'
redmine_1  | /usr/local/bundle/gems/activesupport-6.1.6/lib/active_support/callbacks.rb:512:in `invoke_before'
redmine_1  | /usr/local/bundle/gems/activesupport-6.1.6/lib/active_support/callbacks.rb:105:in `run_callbacks'
redmine_1  | /usr/local/bundle/gems/activesupport-6.1.6/lib/active_support/reloader.rb:88:in `prepare!'
redmine_1  | /usr/local/bundle/gems/railties-6.1.6/lib/rails/application/finisher.rb:124:in `block in <module:Finisher>'
redmine_1  | /usr/local/bundle/gems/railties-6.1.6/lib/rails/initializable.rb:32:in `instance_exec'
redmine_1  | /usr/local/bundle/gems/railties-6.1.6/lib/rails/initializable.rb:32:in `run'
redmine_1  | /usr/local/bundle/gems/railties-6.1.6/lib/rails/initializable.rb:61:in `block in run_initializers'
redmine_1  | /usr/local/bundle/gems/railties-6.1.6/lib/rails/initializable.rb:60:in `run_initializers'
redmine_1  | /usr/local/bundle/gems/railties-6.1.6/lib/rails/application.rb:391:in `initialize!'
redmine_1  | /usr/src/redmine/config/environment.rb:16:in `<top (required)>'
redmine_1  | /usr/local/bundle/gems/polyglot-0.3.5/lib/polyglot.rb:65:in `require'
redmine_1  | /usr/local/bundle/gems/polyglot-0.3.5/lib/polyglot.rb:65:in `require'
redmine_1  | /usr/local/bundle/gems/zeitwerk-2.5.4/lib/zeitwerk/kernel.rb:35:in `require'
redmine_1  | /usr/local/bundle/gems/activesupport-6.1.6/lib/active_support/dependencies.rb:332:in `block in require'
redmine_1  | /usr/local/bundle/gems/activesupport-6.1.6/lib/active_support/dependencies.rb:299:in `load_dependency'
redmine_1  | /usr/local/bundle/gems/activesupport-6.1.6/lib/active_support/dependencies.rb:332:in `require'
redmine_1  | /usr/local/bundle/gems/railties-6.1.6/lib/rails/application.rb:367:in `require_environment!'
redmine_1  | /usr/local/bundle/gems/railties-6.1.6/lib/rails/application.rb:533:in `block in run_tasks_blocks'
redmine_1  | Tasks: TOP => db:migrate => db:load_config => environment
redmine_1  | (See full trace by running task with --trace)

Fix working for me:

/opt/redmine/data/redmine/plugins/custom_field_sql# git diff HEAD
diff --git a/init.rb b/init.rb
index 952841b..aeffaaf 100644
--- a/init.rb
+++ b/init.rb
@@ -1,6 +1,6 @@
 Redmine::Plugin.register :custom_field_sql do
-  require 'custom_field_sql/custom_fields/formats/sql'
-  require 'custom_sql_search_hook'
+  require File.expand_path('../lib/custom_field_sql/custom_fields/formats/sql', __FILE__)
+  require File.expand_path('../lib/custom_sql_search_hook', __FILE__)
   name 'Redmine SQL Custom Field Label plugin'
   author 'Alexey Smirnov'
   description 'This is a plugin for Redmine which adds SQL type to Custom Fields'
diff --git a/lib/custom_sql_search_hook.rb b/lib/custom_sql_search_hook.rb
index 0456bfb..1287824 100644
--- a/lib/custom_sql_search_hook.rb
+++ b/lib/custom_sql_search_hook.rb
@@ -1,4 +1,4 @@
-class CustomSqlSearchHookListener < Redmine::Hook::ViewListener
+class CustomSqlSearchHook < Redmine::Hook::ViewListener

   def view_layouts_base_html_head(context={})
     html = "\n<!-- [custom field sql plugin] -->\n"

Error when bulk editing issues

format Sql -> sql request
select subject from issues where id=%id%

in production.log

ctionView::Template::Error (undefined method `id' for #<Array:0x000001254dd24a10>):
    196:    <% custom_fields.each do |custom_field| %>
    197:      <p>
    198:        <label><%= custom_field.name %></label>
    199:        <%= custom_field_tag_for_bulk_edit('issue', custom_field, @issues, @issue_params[:custom_field_values][custom_field.id.to_s]) %>
    200:      </p>
    201:      <% if i == split_on %>
    202:        </div><div class="splitcontentright">

Field dont show values on issues filter --Me too

I have the same problem tat #15

Do you have a workaround for solve the problem?

The problem its with thw sql fielt type but not with the sql search type.

Thankyou in advance and thank you for the very good plugin.

Regards

Exception during filtering

Query:
select subject ||' ('||coalesce(c1.value,'')||')' from issues
LEFT JOIN custom_values c1 ON c1.customized_id=issues.id AND c1.custom_field_id=21
where tracker_id = 9 and status_id = 2
select '---- User beafore ----'
union all
union all
select distinct c2.value from custom_values c2 where c2.custom_field_id=31
and c2.value not in (
select subject ||' ('||coalesce(c1.value,'')||')' from issues
LEFT JOIN custom_values c1 ON c1.customized_id=issues.id AND c1.custom_field_id=21
where tracker_id = 9 and status_id = 2)
;

Give exception:
<field_name> cannot be blank.

No results are displayed.

Create custom connection to a different DB

From what I can tell this plugin only allows to send queries to the redmine database, would it be possible to instead get results from a different database with different IP?

How to use this feature like a SELECT2 style?

Thanks for this plugin.
How can I use this feature like a SELECT2 style?
My Select command returned more than 1000 records.
It is hard to find correct record. Is it possible to search correct record by typing some letters in field?

Best Regards

Configuration for external DB?

Great work on this plugin. It looks like it will solve a lot of problems.

Can you give some guidance about how to set up connection to query external databases? I need to connect to an Oracle DB to extract info, and the format is not obvious.

Thanks!

%issue_id% parameter for sql

Hello,

thank you for your great work.

Consider adding this code in sql.rb in formatsdirectory:

              if object.issue_id.nil?
                sql = sql.gsub('%issue_id%', 'null')
              else
                sql = sql.gsub('%issue_id%', object.issue_id.to_s)
              end

It's very useful to fill dropdown with specific values in Time Tracking Entries.

S.

More then one column in select statement

Hi, In the case when sql query have 2 or more columns field have just first column (ok), but it does not work correctly - the value change (especially when second column in few rows are null).
As i wrote at redmine.org - useful plugin.
Regards,
KI

Hover fields in redmine turn red

After installing the plugin, all "Hover" fields in our redmine started to turn red in all projects. Here is an example image:

[Feature] query an external DB

Hello, I am searching for a way to get data from an external MS SQL database. Is it possible to add this feature?

Error when installing plugin

Hello,
I'm getting the following error when installing the plugin. Can anyone advise? Redmine 5

redmine@ibcopenproject:$ cd plugins/
redmine@ibcopenproject:/plugins$ git clone https://github.com/apsmir/custom_field_sql.git
Cloning into 'custom_field_sql'...
remote: Enumerating objects: 256, done.
remote: Counting objects: 100% (256/256), done.
remote: Compressing objects: 100% (116/116), done.
remote: Total 256 (delta 79), reused 219 (delta 60), pack-reused 0
Receiving objects: 100% (256/256), 27.41 KiB | 400.00 KiB/s, done.
Resolving deltas: 100% (79/79), done.
redmine@ibcopenproject:/plugins$ cd ..
redmine@ibcopenproject:$ bundle exec rake redmine:plugins:migrate RAILS_ENV=production --trace
** Invoke redmine:plugins:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
rake aborted!
LoadError: cannot load such file -- custom_field_sql/custom_fields/formats/sql
/var/lib/gems/3.0.0/gems/polyglot-0.3.5/lib/polyglot.rb:65:in require' /var/lib/gems/3.0.0/gems/polyglot-0.3.5/lib/polyglot.rb:65:in require'
/var/lib/gems/3.0.0/gems/zeitwerk-2.6.8/lib/zeitwerk/kernel.rb:38:in require' /opt/redmine/plugins/custom_field_sql/init.rb:2:in block in <top (required)>'
/opt/redmine/lib/redmine/plugin.rb:96:in instance_eval' /opt/redmine/lib/redmine/plugin.rb:96:in register'
/opt/redmine/plugins/custom_field_sql/init.rb:1:in <top (required)>' /opt/redmine/lib/redmine/plugin_loader.rb:31:in load'
/opt/redmine/lib/redmine/plugin_loader.rb:31:in run_initializer' /opt/redmine/lib/redmine/plugin_loader.rb:108:in each'
/opt/redmine/lib/redmine/plugin_loader.rb:108:in block in load' /var/lib/gems/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/callbacks.rb:427:in instance_exec'
/var/lib/gems/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/callbacks.rb:427:in block in make_lambda' /var/lib/gems/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/callbacks.rb:198:in block (2 levels) in halting'
/var/lib/gems/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/callbacks.rb:604:in block (2 levels) in default_terminator' /var/lib/gems/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/callbacks.rb:603:in catch'
/var/lib/gems/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/callbacks.rb:603:in block in default_terminator' /var/lib/gems/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/callbacks.rb:199:in block in halting'
/var/lib/gems/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/callbacks.rb:512:in block in invoke_before' /var/lib/gems/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/callbacks.rb:512:in each'
/var/lib/gems/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/callbacks.rb:512:in invoke_before' /var/lib/gems/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/callbacks.rb:105:in run_callbacks'
/var/lib/gems/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/reloader.rb:88:in prepare!' /var/lib/gems/3.0.0/gems/railties-6.1.7.2/lib/rails/application/finisher.rb:124:in block in module:Finisher'
/var/lib/gems/3.0.0/gems/railties-6.1.7.2/lib/rails/initializable.rb:32:in instance_exec' /var/lib/gems/3.0.0/gems/railties-6.1.7.2/lib/rails/initializable.rb:32:in run'
/var/lib/gems/3.0.0/gems/railties-6.1.7.2/lib/rails/initializable.rb:61:in block in run_initializers' /usr/lib/ruby/3.0.0/tsort.rb:228:in block in tsort_each'
/usr/lib/ruby/3.0.0/tsort.rb:350:in block (2 levels) in each_strongly_connected_component' /usr/lib/ruby/3.0.0/tsort.rb:431:in each_strongly_connected_component_from'
/usr/lib/ruby/3.0.0/tsort.rb:349:in block in each_strongly_connected_component' /usr/lib/ruby/3.0.0/tsort.rb:347:in each'
/usr/lib/ruby/3.0.0/tsort.rb:347:in call' /usr/lib/ruby/3.0.0/tsort.rb:347:in each_strongly_connected_component'
/usr/lib/ruby/3.0.0/tsort.rb:226:in tsort_each' /usr/lib/ruby/3.0.0/tsort.rb:205:in tsort_each'
/var/lib/gems/3.0.0/gems/railties-6.1.7.2/lib/rails/initializable.rb:60:in run_initializers' /var/lib/gems/3.0.0/gems/railties-6.1.7.2/lib/rails/application.rb:391:in initialize!'
/opt/redmine/config/environment.rb:16:in <top (required)>' /var/lib/gems/3.0.0/gems/polyglot-0.3.5/lib/polyglot.rb:65:in require'
/var/lib/gems/3.0.0/gems/polyglot-0.3.5/lib/polyglot.rb:65:in require' /var/lib/gems/3.0.0/gems/zeitwerk-2.6.8/lib/zeitwerk/kernel.rb:38:in require'
/var/lib/gems/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/dependencies.rb:332:in block in require' /var/lib/gems/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/dependencies.rb:299:in load_dependency'
/var/lib/gems/3.0.0/gems/activesupport-6.1.7.2/lib/active_support/dependencies.rb:332:in require' /var/lib/gems/3.0.0/gems/railties-6.1.7.2/lib/rails/application.rb:367:in require_environment!'
/var/lib/gems/3.0.0/gems/railties-6.1.7.2/lib/rails/application.rb:533:in block in run_tasks_blocks' /usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/task.rb:281:in block in execute'
/usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/task.rb:281:in each' /usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/task.rb:281:in execute'
/usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/task.rb:219:in block in invoke_with_call_chain' /usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/task.rb:199:in synchronize'
/usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/task.rb:199:in invoke_with_call_chain' /usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/task.rb:243:in block in invoke_prerequisites'
/usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/task.rb:241:in each' /usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/task.rb:241:in invoke_prerequisites'
/usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/task.rb:218:in block in invoke_with_call_chain' /usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/task.rb:199:in synchronize'
/usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/task.rb:199:in invoke_with_call_chain' /usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/task.rb:188:in invoke'
/usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/application.rb:160:in invoke_task' /usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/application.rb:116:in block (2 levels) in top_level'
/usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/application.rb:116:in each' /usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/application.rb:116:in block in top_level'
/usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/application.rb:125:in run_with_threads' /usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/application.rb:110:in top_level'
/usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/application.rb:83:in block in run' /usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/application.rb:186:in standard_exception_handling'
/usr/share/rubygems-integration/all/gems/rake-13.0.6/lib/rake/application.rb:80:in run' /opt/redmine/bin/rake:4:in <top (required)>'
/var/lib/gems/3.0.0/gems/bundler-2.4.13/lib/bundler/cli/exec.rb:58:in load' /var/lib/gems/3.0.0/gems/bundler-2.4.13/lib/bundler/cli/exec.rb:58:in kernel_load'
/var/lib/gems/3.0.0/gems/bundler-2.4.13/lib/bundler/cli/exec.rb:23:in run' /var/lib/gems/3.0.0/gems/bundler-2.4.13/lib/bundler/cli.rb:492:in exec'
/var/lib/gems/3.0.0/gems/bundler-2.4.13/lib/bundler/vendor/thor/lib/thor/command.rb:27:in run' /var/lib/gems/3.0.0/gems/bundler-2.4.13/lib/bundler/vendor/thor/lib/thor/invocation.rb:127:in invoke_command'
/var/lib/gems/3.0.0/gems/bundler-2.4.13/lib/bundler/vendor/thor/lib/thor.rb:392:in dispatch' /var/lib/gems/3.0.0/gems/bundler-2.4.13/lib/bundler/cli.rb:34:in dispatch'
/var/lib/gems/3.0.0/gems/bundler-2.4.13/lib/bundler/vendor/thor/lib/thor/base.rb:485:in start' /var/lib/gems/3.0.0/gems/bundler-2.4.13/lib/bundler/cli.rb:28:in start'
/var/lib/gems/3.0.0/gems/bundler-2.4.13/exe/bundle:45:in block in <top (required)>' /var/lib/gems/3.0.0/gems/bundler-2.4.13/lib/bundler/friendly_errors.rb:117:in with_friendly_errors'
/var/lib/gems/3.0.0/gems/bundler-2.4.13/exe/bundle:33:in <top (required)>' /opt/redmine/bin/bundle:4:in load'
/opt/redmine/bin/bundle:4:in `

'
Tasks: TOP => redmine:plugins:migrate => environment

What is the use case for this?

This plugin interests me, but I wonder about the specific use case, and how it improves over the standard custom fields. Any chance you can provide examples?

examples with pics

your plugin so nice
but we need more example with pics to know more about this plugin
and im try to install it in redmine 5.0.2 ruby 3.1.2

redmine@redmine:~/5.0.2$ bundle exec rake redmine:plugins:migrate RAILS_ENV=production
rake aborted!
LoadError: cannot load such file -- custom_field_sql/custom_fields/formats/sql
/home/redmine/5.0.2/plugins/custom_field_sql/init.rb:2:in `block in <top (required)>'
/home/redmine/5.0.2/lib/redmine/plugin.rb:96:in `instance_eval'
/home/redmine/5.0.2/lib/redmine/plugin.rb:96:in `register'
/home/redmine/5.0.2/plugins/custom_field_sql/init.rb:1:in `<top (required)>'
/home/redmine/5.0.2/lib/redmine/plugin_loader.rb:31:in `load'
/home/redmine/5.0.2/lib/redmine/plugin_loader.rb:31:in `run_initializer'
/home/redmine/5.0.2/lib/redmine/plugin_loader.rb:108:in `each'
/home/redmine/5.0.2/lib/redmine/plugin_loader.rb:108:in `block in load'
/home/redmine/5.0.2/config/environment.rb:16:in `<top (required)>'
/home/redmine/.rbenv/versions/3.1.2/bin/bundle:25:in `load'
/home/redmine/.rbenv/versions/3.1.2/bin/bundle:25:in `<main>'
Tasks: TOP => redmine:plugins:migrate => envir
```onment
(See full trace by running task with --trace)

Enable "Link values to URL" and "Text formatting"

This plugin offers some great new possibilities! E.g. creating drop-down lists with values from other database tables.

Unfortunately, the output of the label will not be rendered as HTML.
Consider the case, where I want to create a dropdown menu of all DMS files and then generate a clickable link to the document.

my query is:
select name as value, CONCAT('<a href="http://172.16.32.41/dmsf/files/', id, '/view">', name, '</a>') as label from dmsf_files

Result:
The HTML anchor tag is not rendered.

Could you please add an option for this?

How to make the first element selected automatically in drop-down list?

Hi there. I am new in RoR and Redmine, and trying to get through it. As i understood, sql returns the array of results of sql expression. So here is the question : for example, my resultig arrays have only one element in themselves. How to set this one element to be selected automatically? In sql.rb in formats directory i saw def possible_values_options and i understand how it works, but need some guidance with my problem.

Error if I try to use a variable in a request

I get an error if I try to use a variable in a request.

@id=%id%
select subject from issues where id=@id

ActionView::Template::Error (Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@id=1331
select subject from issues where id=@id' at line 1: @id=1331
select subject from issues where id=@id):
     8: <% i = 0 %>
     9: <% split_on = (custom_field_values.size / 2.0).ceil - 1 %>
    10: <% custom_field_values.each do |value| %>
    11:   <p><%= custom_field_tag_with_label :issue, value, :required => @issue.required_attribute?(value.custom_field_id) %></p>
    12: <% if i == split_on -%>
    13: </div><div class="splitcontentright">
    14: <% end -%>

How can variables be used?

Field dont show values on issues filter

Hi, when I select a custom field of type sql, named OS (CAST/SGG), its values didnt appear on combobox.

And it has values:

New Issue page cannot use %id%

Let's say SQL expression: select subject from issues where id=%id%
If the issue exists, EDIT ISSUE is OK.
If create a new issue, it's Internal error

ActionView::Template::Error (Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1: select subject from issues where id=):
19: <% i = 0 %>
20: <% split_on = (lr_values.size / 2.0).ceil - 1 %>
21: <% lr_values.each do |value| %>
22:

<%= custom_field_tag_with_label :issue, value, :required => @issue.required_attribute?(value.custom_field_id) %>

23: <% if i == split_on %>
24:

25: <% end %>

plugins/custom_field_sql/lib/custom_field_sql/custom_fields/formats/sql.rb:15:in possible_values_options' lib/redmine/field_format.rb:162:in possible_custom_value_options'
lib/redmine/field_format.rb:613:in select_edit_tag' lib/redmine/field_format.rb:578:in edit_tag'
app/helpers/custom_fields_helper.rb:91:in custom_field_tag' app/helpers/custom_fields_helper.rb:120:in custom_field_tag_with_label'
app/views/issues/_form_custom_fields.html.erb:22:in block (2 levels) in _9a8a4f882f5988e4bc110ca6763fd21b' app/views/issues/_form_custom_fields.html.erb:21:in each'
app/views/issues/_form_custom_fields.html.erb:21:in block in _9a8a4f882f5988e4bc110ca6763fd21b' app/views/issues/_form_custom_fields.html.erb:1:in each'
app/views/issues/_form_custom_fields.html.erb:1:in _9a8a4f882f5988e4bc110ca6763fd21b' app/views/issues/_attributes.html.erb:83:in block in _31d901be448779fb8bcc513422c492e7'
app/helpers/application_helper.rb:1365:in labelled_fields_for' app/views/issues/_attributes.html.erb:1:in _31d901be448779fb8bcc513422c492e7'
app/views/issues/_form.html.erb:50:in block in _80d472f4a750771d2125048d87bbdde3' app/helpers/application_helper.rb:1365:in labelled_fields_for'
app/views/issues/_form.html.erb:1:in _80d472f4a750771d2125048d87bbdde3' app/views/issues/new.html.erb:13:in block in _e80235d30eb35dd73c2a3f21753346e8'
app/helpers/application_helper.rb:1358:in labelled_form_for' app/views/issues/new.html.erb:5:in _e80235d30eb35dd73c2a3f21753346e8'
app/controllers/issues_controller.rb:122:in block (2 levels) in new' app/controllers/issues_controller.rb:121:in new'