Initializing rows using the iDalko table grid editor

Anthony Hawkins September 16, 2015

Are there any example(s) of where the rows of the grid editor are initialized by any of the following custom fields?

select list

radio buttons

2 answers

1 vote
francis
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
September 17, 2015

Hello Anthony,

Check out the inline parameters page 

https://wiki.idalko.com/display/TGPD/inline+parameters+method

 

You can write a query like

...
gd.query = select a, b, c, d
           from myTable
           where e = {currentuser:id}
...
 
...
col.mylist.type = list
col.mylist.query = select a, b, c from myTable where e = {customfield:10401}
...

 

Hope this helps,

 

 

Francis

 

 

Anthony Hawkins September 18, 2015

Hey Francis, thank you for your answer. However, I'm trying to do something a little different. I'm trying to take the options from a mutli select list on an issue and initialize a table grid. I want to put each selected item from the custom field in a different row for the table grid. Is there a way to do this?

francis
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
March 11, 2016

Hello Anthony,

 

I missed your comment - sorry about that.  How relevant is it to further the discussion ?

 

Francis

0 votes
francis
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
September 18, 2015

Hi Anthony - given the limited formatting capabilities of a comment, I'm adding a reply to your last remark here ...

 

 

This can be done by configuring gd.query to retrieve the selected values.
An example query (here specific for mysql) would look like following snippet - with 10070 the customfield id 

gd.query = SELECT cfo.customvalue  \
           FROM jira.customfieldoption cfo \
           inner join customfieldvalue cfv  \
                    on cfo.customfield = cfv.customfield  \
                       and cfo.id = cast(cfv.STRINGVALUE as decimal) \
           where cfo.customfield = 10070 \
           and   cfv.issue = {issue:id}

 

When hitting the reload button on the grid, the query will be run again, replacing all rows which have
not been modified.  This behaviour can be adapted with the  https://answers.atlassian.com/questions/29953583 property

Anthony Hawkins September 28, 2015

Hey Francis, the query works, however, I am having trouble viewing the contents of the query inside of my table grid. Your query states that I have to use gd.query. However, I get an error message stating that I need to use gd.query.ds. My table configuration is as follows:

 

############################################
# General grid properties section
#----------------------------------------
# gd.columns is used to define the columns which make up the grid
# gd.tablename and gd.ds are used to specify where the data of the grid should be stored
# It is possible to store the grid data in an external database
#
gd.query.ds = SELECT cfo.customvalue  
           FROM jira.customfieldoption cfo 
           inner join customfieldvalue cfv  
                    on cfo.customfield = cfv.customfield  
                       and cfo.id = cfv.STRINGVALUE 
           where cfo.customfield = 10302 
           and   cfv.issue = {issue:id}


gd.visiblerows=1
gd.visiblerows=auto
gd.columns=Subsystems
gd.query.columns=Subsystems
gd.tablename=Subsystem_table2
gd.ds=jira

############################################
# Column section for the Subsystems column
#----------------------------------------
# Various column properties are used to specify the behaviour
# of the column (such as width, type, etc.)
#

col.Subsystems=Subsystems
col.Subsystems.name=Subsystems
col.Subsystems.type=string

My table grid does not display the results from the query. How can I get it to display the results?

francis
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
September 28, 2015

gd.query.ds is used to specify on what data source the query should run. https://wiki.idalko.com/x/64Lg gd.query is used to specify the query itself https://wiki.idalko.com/x/zoLg So your grid configuration should look like gd.query.ds = jira gd.query = select cfo.customvalue ... (When pretty formatting a query make sure you use the \ line terminator)

Anthony Hawkins September 30, 2015

Hey Francis, I made the changes and now get the following error: "Database error occurred while executing query "null": could not execute query" ############################################ # General grid properties section #---------------------------------------- # gd.columns is used to define the columns which make up the grid # gd.tablename and gd.ds are used to specify where the data of the grid should be stored # It is possible to store the grid data in an external database # gd.tablename=Subsystem_table2 gd.ds=jira_ds gd.query.ds=jira gd.query = SELECT cfo.customvalue \ FROM jira.customfieldoption cfo \ inner join customfieldvalue cfv \ on cfo.customfield = cfv.customfield \ and cfo.id = cfv.STRINGVALUE \ where cfo.customfield = 10302 \ # and cfv.issue = {issue:id} gd.visiblerows=1 gd.visiblerows=auto gd.columns=Subsystems ############################################ # Column section for the Subsystems column #---------------------------------------- # Various column properties are used to specify the behaviour # of the column (such as width, type, etc.) # col.Subsystems=Subsystems col.Subsystems.name=Subsystems col.Subsystems.type=sequence What am i doing wrong?

maschle March 9, 2016

Hi @Francis Martens (iDalko),

I get the same error as Anthony, i want to initialize rows depending on which components are selected in the issue. This is my query:

gd.query.ds=jira
gd.query=select sink_node_id from nodeassociation where association_type = 'IssueComponent' and source_node_id = {issue:id}

We are using PostgreSQL for our database. When I run my query directly against my db it is working. Is there something wrong with {issue:id} ?

Unfortunately I can't enable logging since I don't have access to the server.

Thanks in advance,

Max

francis
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
March 11, 2016

Hi Max,

When creating an issue, the issue id is still tbd.  Therefore you can't use it at that moment.  Could you try to reload the grid (using the reload button on the grid toolbar) once that the issue is created and see what gives

 

Francis

 

maschle March 13, 2016

Hi Francis,

I changed my query to this and now it works even when I select a component during creation of an issue: 

gd.query=select sink_node_id from nodeassociation where association_type = 'IssueComponent' and source_node_id = '{0}'
gd.query.parameters = issue:id

The only thing I changed is to set the parameter "issue:id" outside the query.

Now I changed my query a little bit to get the names of the components instead of the IDs but I get the following error:

 

Validation failed with errors:

  • There is a SQL grammar problem in sql query "select cname from component c inner join (select * from nodeassociation where association_type = 'IssueComponent' and source_node_id = '{0}') as na on c.id = na.sink_node_id;": org.postgresql.util.PSQLException: ERROR: syntax error at or near "limit" Position: 2

 

Do you have any idea why? It says near "limit" Position but I don't even use "limit". The query works fine on my database.

Thank you!

Max

 

francis
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
March 13, 2016

The grid configuration validation check will test the gd.query sql by adding 'limit 1' to the query string and run it.  Could you send this case to support dot tge at idalko dot com, so that we can further track the issue with our JIRA ?

 

Thanks,

 

 

Francis

Suggest an answer

Log in or Sign up to answer