I can't extract information from a Table Grid field (through script field)

[Camille Lecerf]

Hi,

I am trying to use a scripted field to extract information from a Table Grid Field, following this tutorial from the editor iDalko:

Using scripted fields to extract information from the grid using the table grid API

Yet I get these errors on line 44 and 45:

Could someone help me please? I don't know anything about script...

Regards,

Camille

[Marc Minten _EVS_ · Accepted solution]

Hi,

I think the "error" is only about static type checking you did not type check the grid manager). I think the script will run, even with theses "errors".

Unfortunately the script is not really readable, please provide the full script, I can help you with the static type checking so the "errors" disappear...

[Camille Lecerf]

Hi Marc,

Thank you for your answer, here is the full script:

/**
 * scriptedFieldSum.groovy
 *
 * This is a script for Scripted custom field that calculates sum of all the numbers in specified column of the TGE grid.
 *
 * Configuration:
 * - tgeCustomFieldName - name of the grid custom field that will be used for calculation
 * - columnNameToSum - column name which will be used to get data for sum calculation
 */
import com.atlassian.crowd.embedded.api.User
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.issue.fields.CustomField
import com.atlassian.jira.security.JiraAuthenticationContext
import com.atlassian.jira.user.ApplicationUser
import com.atlassian.plugin.PluginAccessor
import org.apache.log4j.Logger
 
// set up logger
Logger log = Logger.getLogger("com.idalko.scripts");
 
// configuration (update it to match your JIRA settings)
String tgeCustomFieldName = "Demande d'achat NSD";
String columnNameToSum = "total";
 
// find TGE custom field
CustomFieldManager customFieldManager = ComponentAccessor.getOSGiComponentInstanceOfType(CustomFieldManager.class);
CustomField tgeCustomField = customFieldManager.getCustomFieldObjectByName(tgeCustomFieldName);
Long tgeCustomFieldIdLong = tgeCustomField.getIdAsLong();
 
// get current user
JiraAuthenticationContext jiraAuthenticationContext = ComponentAccessor.getOSGiComponentInstanceOfType(JiraAuthenticationContext.class);
Object userObject = jiraAuthenticationContext.getLoggedInUser();
User user = userObject instanceof ApplicationUser ? ((ApplicationUser) userObject).getDirectoryUser() : (User) userObject
 
// read the grid data
PluginAccessor pluginAccessor = ComponentAccessor.getPluginAccessor();
Class dataManagerClass = pluginAccessor.getClassLoader().findClass("com.idalko.jira.plugins.igrid.api.data.TGEGridTableDataManager");
def tgeGridDataManager = ComponentAccessor.getOSGiComponentInstanceOfType(dataManagerClass);
Set<String> columnNames = new HashSet<String>();
columnNames.add(columnNameToSum);
List<Map<String, Object>> gridData = new ArrayList<Map<String, Object>>();
try {
 def callResult = tgeGridDataManager.readGridData(issue.getId(), tgeCustomFieldIdLong, null, columnNames, 0, 10, user);
 gridData = callResult.getValues();
 log.debug("Grid ID=" + tgeCustomFieldIdLong + " content: " + gridData + "\n");
} catch (Exception e) {
 log.debug("Grid ID=" + tgeCustomFieldIdLong + " data cannot be retrieved: " + e.getMessage() + "\n");
}
 
// calculate sum
double sum = 0;
for (Map<String, Object> row : gridData) {
 Object rowValue = row.get(columnNameToSum);
 if (rowValue instanceof Map) {
 rowValue = ((Map) rowValue).get("value");
 }
 
 Number rowNumber = null;
 if (rowValue instanceof Number) {
 rowNumber = (Number) rowValue;
 } else {
 String rowString = rowValue.toString();
 rowNumber = Double.parseDouble(rowString);
 }
 sum += rowNumber.doubleValue();
}
 
return sum;

[Marc Minten _EVS_]

Hi,

I think your script is correct.

You can ignore these "errors", they are just static type checking warnings , but your script should run correctly...

Did you try it ? Did you get execution errors ?

[Camille Lecerf]

I tried but it does not display anything. I also tried without lines 44 and 45, and I get the field but it equals to zero.

Here is what I get in the Logs :

Time (on server): Wed Jan 30 2019 10:29:58 GMT+0100 (heure normale d’Europe centrale)

The following log information was produced by this execution. Use statements like:log.info("...") to record logging information.

2019-01-30 10:29:58,481 ERROR [customfield.GroovyCustomField]: *************************************************************************************
Script field failed on issue: DANSD-23, field: Montant DA
java.lang.NumberFormatException: For input string: "null"
 at java_lang_Double$parseDouble$1.call(Unknown Source)
 at Script29.run(Script29.groovy:64)

[Marc Minten _EVS_]

The error comes from the line

rowNumber = Double.parseDouble(rowString);

The variable rowString seems to be empty (null) and the method parseDouble throws a exception (error).

What type is your column "total" in your grid ? Do all records have values for this column ?

Maybe you can add some debug in your script ?

[Camille Lecerf]

Total is a number field (col.total.type=number), it is calculated with this formula: col.total.formula = {quant} * {unitprice} * (1-({remise}/100))
Not all records have values for this column.

I think I can't add debug but I am not sure...

[Marc Minten _EVS_]

Ok, if not all records have values, it is safer to write

if (rowValue) { 
 Number rowNumber = null;
  if (rowValue instanceof Number) {
   rowNumber = (Number) rowValue;
  } else {
   String rowString = rowValue.toString();
   rowNumber = Double.parseDouble(rowString);
  }
 sum += rowNumber.doubleValue();
}

[Camille Lecerf]

Does it replace this please ?

Number rowNumber = null;
 if (rowValue instanceof Number) {
 rowNumber = (Number) rowValue;
 } else {
 String rowString = rowValue.toString();
 rowNumber = Double.parseDouble(rowString);
 }
 sum += rowNumber.doubleValue();

[Marc Minten _EVS_]

Yep, you just make sure rowValue contains some data, otherwise the row is ignored...

[Camille Lecerf]

Thank you very much Marc for your answer :) I get a result for all my issue now.

Yet I still have a problem here:

Is that because it is not the same format? How to set my script field with 12 345,67 and not 12,345.67 please? Or maybe it is because of the number of digits?

[Marc Minten _EVS_]

In your script you load only the first 10 lines of the data grid... :-(

Change the 10 in 1000 or somewhat ?

[Camille Lecerf]

Thank you! This was the problem :)

Regards,

Camille

[nallu]

Hi Marc, 

I too have that static type checking error on the scripted field's script that read data from table grid editor. but, the script returns the data correctly. But still it throws an exception and error is getting logged in 'atlassian-jira.log' file. That error just filling the 'atlassian-jira.log' file completely. Huge no.of error. 

Can you please take a look and get me. 

Here is the sample error, 

2019-05-09 07:44:27,101 JIRA-EventThread-2826 ERROR bfarrell 1248x23926368x63 1lnwl23 10.6.20.240 /rest/webResources/1.0/resources [com.idalko.scripts] On Issue Key = CHTR-305 - Gating Details Grid's feature key data cannot be retrieved: User cannot be null

Here is the script, 

/**
* File Information:
* This script is to fetch 'Feature Key' details of an issue from Gating Details field.
* This is associated with the script field 'Feature Key' which is created in 'JIRA ADMINISTRATION (Gear) -> Issues -> Script Fields -> 'Gating Type' in Jira UI.
*
* @since 1th May, 2019
* @version 1.0
* @Last updated 7th May, 2019
*
* Additional Information of 'Feature Key' script field:
** These information are provided in JIRA User Interface while creating script field.
* @Note This field will display aggregation of feature key of an issue
* @Template Text Field(multi-line)
* @Script_file # Provide the path where this script is being stored in JIRA server
* @Preview ROPS-390 (This is Optional and providing an issue key is to just to see the outcome of particular issue as a preview.)
*
* NOTE: This field will not be configured with any screens. This will used only for issue search in Issue Navigator (JQL)
**/

import com.atlassian.crowd.embedded.api.User;
import com.atlassian.jira.component.ComponentAccessor;
import com.atlassian.jira.security.JiraAuthenticationContext;
import com.atlassian.jira.user.ApplicationUser;
import com.atlassian.plugin.PluginAccessor;
import org.apache.log4j.Logger;

// Disabling the cache
enableCache = {-> false}

if(issue.getIssueType().getName() == 'Epic')
{
// set up logger
Logger log = Logger.getLogger("com.idalko.scripts"); //Uncomment this only if you would not like to log the error

//Get current Key
def currKey = issue.getKey();

// Table Grid Editor 'Gating Details' field details
String tgeCustomFieldId = "customfield_17001";
String columnNameToSum = "feature_key";

// Get 'Gating Details' custom field object & Id
def customFieldManager = ComponentAccessor.getCustomFieldManager();
def gatingDetailsField = customFieldManager.getCustomFieldObject(tgeCustomFieldId);
Long tgeCustomFieldIdLong = gatingDetailsField.getIdAsLong();

// get current user
JiraAuthenticationContext jiraAuthenticationContext = ComponentAccessor.getOSGiComponentInstanceOfType(JiraAuthenticationContext.class);
Object userObject = jiraAuthenticationContext.getLoggedInUser();
User user = userObject instanceof ApplicationUser ? ((ApplicationUser) userObject).getDirectoryUser() : (User) userObject

// read the grid data
PluginAccessor pluginAccessor = ComponentAccessor.getPluginAccessor();
Class dataManagerClass = pluginAccessor.getClassLoader().findClass("com.idalko.jira.plugins.igrid.api.data.TGEGridTableDataManager");
def tgeGridDataManager = ComponentAccessor.getOSGiComponentInstanceOfType(dataManagerClass);
Set<String> columnNames = new HashSet<String>();
columnNames.add(columnNameToSum);
List<Map<String, Object>> gridData = new ArrayList<Map<String, Object>>();

// Defining an array to store Feature Key
def featureKey = [];
Long id = issue.id;

try {
def callResult = tgeGridDataManager.readGridData(id, tgeCustomFieldIdLong, null, columnNames, 0, 10, user);
gridData = callResult.getValues();

if(gridData) {
gridData.each {
featureKey.add("(${it['feature_key']})")
}
}
//log.error("On Issue Key = ${currKey} - Gating Details Grid's content - Feature Key: " + gridData + "\n"); //Uncomment this only if you would like to print the content in error log
} catch (Exception e) {
log.error("On Issue Key = ${currKey} - Gating Details Grid's feature key data cannot be retrieved: " + e.getMessage() + "\n");
}

if(featureKey)
{
return featureKey.unique().join(', ').toString()
}
else
{
return null
}
}

[Marc Minten _EVS_]

??It seems there is an problem with your "user" ? Dis you check the right value is retrieved in it ?

I did not go in details in your code to retrieve your "user" object, but it seems unnecessary complex to me.

I would simply write

ApplicationUser userId = ComponentAccessor.getJiraAuthenticationContext().getLoggedInUser()
User user = userId?.getDirectoryUser()

[farid peymandoost]

Hi Marc, 

Unfortunately I've got the same problem as Camille, I tried your suggestion about the "rowValue" condition, but still the result is fixed to zero, do you have got any Idea? 

by the way I used the exact code form Using scripted fields to extract information from the grid using the table grid API and only changed the required field names.

Regards

[Marc Minten _EVS_]

Ok, but without some more info ("the same problem..."), difficult to estimate what goes wrong :-)