Updating task information displayed for timesheet

[Tiago Abreu]

Hello,

Recently one of our customers asked if we can display their custom ID for the service object, to be displayed in the timesheet screen for example:

<Project name>(project code)
taks
.
.
.

For what i understand this is related to the prtask.prexternalid value, i made a custom script that updates this value to be the same as the inv_investments.code:


<gel:script xmlns:core="jelly:core" xmlns:file="jelly:com.niku.union.gel.FileTagLibrary" xmlns:ftp="jelly:com.niku.union.gel.FTPTagLibrary"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:obj="http://www.niku.com/xog/Object" xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sql="jelly:sql"
xmlns:util="jelly:util" xmlns:x="jelly:xml" xmlns:xog="http://www.niku.com/xog" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<!-- PARAMETERS -->
<gel:setDataSource dbId="niku" var="connect"/>

<core:catch escapeText="false" var="exception">


<!-- Identify all services -->
<sql:query dataSource="${connect}" escapeText="false" var="unit_id">

select id
from niku.INV_INVESTMENTS i
where i.ODF_OBJECT_CODE = 'service'

</sql:query>
<core:forEach items="${unit_id.rowsByIndex}" var="obs">
<core:set value="${obs[0]}" var="serv"/>


<!-- Updates the task external ID for timesheet display code to be equal to investment CODE -->
<sql:update dataSource="${connect}" escapeText="false" var="updreqry3">

update PRTASK
set PREXTERNALID = (select i.CODE
from niku.INV_INVESTMENTS i
where i.ODF_OBJECT_CODE = 'service'
and i.ID = PRTASK.PRPROJECTID)
where PRPROJECTID = ${serv}
and PREXTERNALID is null

</sql:update>

</core:forEach>
</core:catch>
<core:if test="${exception != null}">
<gel:log>ERROR: ${exception.getMessage()}</gel:log>
</core:if>
</gel:script>




Is there any problem in updating the value this way? I have used this in our dev environment and all seems to work correctly.

Thank you!

[Alistair Miller]

Hi Tiago
I've been away from Clarity for a couple of years, but back in the old days running Update SQL to update fields was frowned upon (there were rumours for years that the capability was going to be removed, good to see it hasn't been yet).

As a minimum, the process used to be that you needed to ask Clarity/Broadcom via a support request to approve your script  They won't be responsible for anything that gets broken (understandable) but they may be able to point you at any risks of doing it the way you propose, and they may propose a different way of accomplishing what you need.

From memory there are other fields (Input Type?) that I've seen used to link to a related object (such as an application or service) but as I say, I've been away from Clarity for a couple of years and may be hazy on the details.

The other thing to bear in mind is that the Tasks XOG uses PREXTERNALID as its key, so if you ever XOG tasks in/out, you might need to test that before going into Production.