Initializing rows using the iDalko table grid editor

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

This widget could not be displayed.

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

 

 

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?

Hello Anthony,

 

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

 

Francis

This widget could not be displayed.

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

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?

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)

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?

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

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

 

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

 

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
Community showcase
Posted Tuesday in Jira

What modern development practices are at the heart of how your team delivers software?

Hey Community mates! Claire here from the Software Product Marketing team. We all know software development changes rapidly, and it's often tough to keep up. But from our research, we've found the h...

154 views 1 3
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you