Velocity Report with calculated lookup value

Patrick Szabo
Patrick Szabo Member, Medical Devices & Life Sciences Solution Posts: 55
Hi all,

we use Jama also for Risk Analysis and just updated to 8.79.1 (self hosted) today. I setup the new lookup matrix feature to calculate P total and the Risk Level. In the previous version, we simply used pickup lists to select the P total and the Risk Level manually.

When I now run our velocity report to export the risk analysis, I only get a number for the calculated lookup value, instead of the text. With just the picklist, the following code worked to get the text value and the associated color:
#set( $prePTotal = "")
	#set( $prePTotal = $velocityReportUtil.getValueForField($r, "p_total", $dateFormat))
		##Get the color it displays in Jama
		###set( $newItem = $contourItemDao.getContourItem($doc.id))
		###set( $customFields = $newItem.getCustomFields())
		#set( $docTypeField = $documentTypeFieldDao.getDocumentTypeFieldByName("p_total", $r.documentType.id))
		#set( $lookupTypeId = $docTypeField.getLookupTypeId())
		#set( $fieldId = $docTypeField.documentField.id)
		#set( $lookupTypeList = [])
		#if( $lookupTypeList.add($lookupTypeId)) #end
		#set( $getLookupIdsReturn = $lookupDao.getLookupIdsByLookupTypes($lookupTypeList))
		#set( $lookupValues = [])
		#foreach( $l in $getLookupIdsReturn)
			#set( $lookup = $lookupDao.getLookup($mathTool.toInteger($l)))
			#if( $lookupValues.add( $lookup)) #end
			##$lookup<br>
			##$lookup.name, $lookup.color, $lookup.description<br>
		#end
		#set( $color = '')
		#foreach( $l in $lookupValues)
			#if( $l.name == $prePTotal)
				#set( $color = $l.color)
				#break
			#end
		#end
Output example:
image
What would I need to change to get the text and the color of the calculated lookup value?

Thanks!

------------------------------
Patrick
------------------------------
Patrick

Comments

  • Patrick Szabo
    Patrick Szabo Member, Medical Devices & Life Sciences Solution Posts: 55
    edited December 2022
    I found now a workaround which works fine, but it is just a workaround. Would be nice if there would be a direct possibility to get the correct values and colors.
    Here the code which works for us:
    #set( $riskLevel = "")
    #set( $riskLevelString = "")
    #set( $color = '')
    #set( $riskLevel = $velocityReportUtil.getValueForField($r, "estimated_risk_level_lu", $dateFormat))
    	
    #if( $riskLevel == "892")
    	#set( $riskLevelString = "Low")
    	#set( $color = '00CC66')
    #elseif( $riskLevel == "893")
    	#set( $riskLevelString = "Medium")
    	#set( $color = 'FF9933')
    #elseif( $riskLevel == "894")
    	#set( $riskLevelString = "High")
    	#set( $color = 'FF0000')
    #else
    	#set( $riskLevelString = "Undetermined")
    	#set( $color = 'FFFFFF')
    	#end
    <td class=xl88 width=90 rowspan=$riskRowSpan style='background:#$color;mso-pattern:#$color none;align:left;width:68pt'>$!riskLevelString</td>
    Fortunately, the numbers (e.g. 892, 893,...) always correlate with the same text. Otherwise this would not work like that.

    ------------------------------
    Patrick
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 12-20-2022 08:23
    From: Patrick Szabo
    Subject: Velocity Report with calculated lookup value

    Hi all,

    we use Jama also for Risk Analysis and just updated to 8.79.1 (self hosted) today. I setup the new lookup matrix feature to calculate P total and the Risk Level. In the previous version, we simply used pickup lists to select the P total and the Risk Level manually.

    When I now run our velocity report to export the risk analysis, I only get a number for the calculated lookup value, instead of the text. With just the picklist, the following code worked to get the text value and the associated color:
    #set( $prePTotal = "")	#set( $prePTotal = $velocityReportUtil.getValueForField($r, "p_total", $dateFormat))		##Get the color it displays in Jama		###set( $newItem = $contourItemDao.getContourItem($doc.id))		###set( $customFields = $newItem.getCustomFields())		#set( $docTypeField = $documentTypeFieldDao.getDocumentTypeFieldByName("p_total", $r.documentType.id))		#set( $lookupTypeId = $docTypeField.getLookupTypeId())		#set( $fieldId = $docTypeField.documentField.id)		#set( $lookupTypeList = [])		#if( $lookupTypeList.add($lookupTypeId)) #end		#set( $getLookupIdsReturn = $lookupDao.getLookupIdsByLookupTypes($lookupTypeList))		#set( $lookupValues = [])		#foreach( $l in $getLookupIdsReturn)			#set( $lookup = $lookupDao.getLookup($mathTool.toInteger($l)))			#if( $lookupValues.add( $lookup)) #end			##$lookup<br>			##$lookup.name, $lookup.color, $lookup.description<br>		#end		#set( $color = '')		#foreach( $l in $lookupValues)			#if( $l.name == $prePTotal)				#set( $color = $l.color)				#break			#end		#end
    Output example:
    image
    What would I need to change to get the text and the color of the calculated lookup value?

    Thanks!

    ------------------------------
    Patrick
    ------------------------------
    Patrick
  • Bridget Dowling
    Bridget Dowling Member, Medical Devices & Life Sciences Solution, Jama Validation Kit (JVK) + Functional Safety Kit (FSK) Posts: 9
    edited January 19

    Hi

    Looks like I am encountering the same issue. Just wondering if anyone has an explanation or understanding of these values. 

    I am getting 1786 for Low, 1785 for medium and 1784 for high

    Thanks

    Bridget

    ------------------------------
    Bridget Dowling
    Analog Devices Int
    Limerick
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 12-22-2022 23:25
    From: Patrick Szabo
    Subject: Velocity Report with calculated lookup value

    I found now a workaround which works fine, but it is just a workaround. Would be nice if there would be a direct possibility to get the correct values and colors.
    Here the code which works for us:
    #set( $riskLevel = "")#set( $riskLevelString = "")#set( $color = '')#set( $riskLevel = $velocityReportUtil.getValueForField($r, "estimated_risk_level_lu", $dateFormat))	#if( $riskLevel == "892")	#set( $riskLevelString = "Low")	#set( $color = '00CC66')#elseif( $riskLevel == "893")	#set( $riskLevelString = "Medium")	#set( $color = 'FF9933')#elseif( $riskLevel == "894")	#set( $riskLevelString = "High")	#set( $color = 'FF0000')#else	#set( $riskLevelString = "Undetermined")	#set( $color = 'FFFFFF')	#end<td class=xl88 width=90 rowspan=$riskRowSpan style='background:#$color;mso-pattern:#$color none;align:left;width:68pt'>$!riskLevelString</td>
    Fortunately, the numbers (e.g. 892, 893,...) always correlate with the same text. Otherwise this would not work like that.

    ------------------------------
    Patrick
    ------------------------------

    Original Message:
    Sent: 12-20-2022 08:23
    From: Patrick Szabo
    Subject: Velocity Report with calculated lookup value

    Hi all,

    we use Jama also for Risk Analysis and just updated to 8.79.1 (self hosted) today. I setup the new lookup matrix feature to calculate P total and the Risk Level. In the previous version, we simply used pickup lists to select the P total and the Risk Level manually.

    When I now run our velocity report to export the risk analysis, I only get a number for the calculated lookup value, instead of the text. With just the picklist, the following code worked to get the text value and the associated color:
    #set( $prePTotal = "")	#set( $prePTotal = $velocityReportUtil.getValueForField($r, "p_total", $dateFormat))		##Get the color it displays in Jama		###set( $newItem = $contourItemDao.getContourItem($doc.id))		###set( $customFields = $newItem.getCustomFields())		#set( $docTypeField = $documentTypeFieldDao.getDocumentTypeFieldByName("p_total", $r.documentType.id))		#set( $lookupTypeId = $docTypeField.getLookupTypeId())		#set( $fieldId = $docTypeField.documentField.id)		#set( $lookupTypeList = [])		#if( $lookupTypeList.add($lookupTypeId)) #end		#set( $getLookupIdsReturn = $lookupDao.getLookupIdsByLookupTypes($lookupTypeList))		#set( $lookupValues = [])		#foreach( $l in $getLookupIdsReturn)			#set( $lookup = $lookupDao.getLookup($mathTool.toInteger($l)))			#if( $lookupValues.add( $lookup)) #end			##$lookup<br>			##$lookup.name, $lookup.color, $lookup.description<br>		#end		#set( $color = '')		#foreach( $l in $lookupValues)			#if( $l.name == $prePTotal)				#set( $color = $l.color)				#break			#end		#end
    Output example:
    image
    What would I need to change to get the text and the color of the calculated lookup value?

    Thanks!

    ------------------------------
    Patrick
    ------------------------------