Clarity

Expand all | Collapse all

Update custom field from Project Task

Jump to Best Answer
  • 1.  Update custom field from Project Task

    Posted 02-21-2017 12:03 PM

    Hello Everyone,

    I created a query that allows me to give a single line, it's the task that contains the field that i want to change (boolean = "true").
    This is my Gel script with the XOG that i'm not sure if it's correct or not ...

     

     

    <customScript languageCode="gel">

    <scriptText>
    <gel:script
    xmlns:core="jelly:core"
    xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
    xmlns:jxml="jelly:xml"
    xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:sql="jelly:sql"
    xmlns:util="jelly:util"
    xmlns:x="jelly:org.apache.commons.jelly.tags.xml.XMLTagLibrary"
    xmlns:xog="http://www.niku.com/xog"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">


    <!-- ***********************************
    PARAMETRES
    ************************************ -->

    <!-- Params -->
    <!-- BD CONNECTION ** -->
    <gel:setDataSource dbId="Niku" />
    <!-- -->
    <core:set value="NA" var="hostClarity" />
    <core:set value="clarity" var="loginClarity" />
    <core:set value="passw2016" var="passClarity" />
    <!-- connection parameter -->
    <core:catch var="exception">
    <sql:query var="req"><![CDATA[
    Select
    NVL ((SELECT X_ATT_VALUE
    FROM ODF_CA_X_SO_TECH_CONF
    WHERE X_ATT_ATTRIBUTE_ID = 'hostClarity'), 'NA') hostClarity
    From Dual
    ]]></sql:query>
    </core:catch>
    <core:choose>
    <core:when test="${exception != null}">
    <gel:log category="SQL" level="ERROR" message="[-] SQL error : ${exception}" />
    </core:when>
    <core:otherwise>
    <core:if test="${req.rowCount !=0}">
    <core:forEach items="${req.rowsByIndex}" trim="true"
    var="confRow">
    <core:if test="${confRow[0] != null}">
    <core:set value="${confRow[0]}" var="hostClarity" />
    </core:if>
    </core:forEach>
    </core:if>
    </core:otherwise>
    </core:choose>
    <core:choose>
    <core:when test="${loginClarity.compareTo('NA') == 0}">
    <gel:log category="HOST" level="ERROR"
    message="[-] Connexion parameter ERROR (loginClarity not set)." />
    </core:when>
    <core:otherwise>
    <gel:log category="HOST" level="info"
    message="Xog user found: ${loginClarity}" />
    </core:otherwise>
    </core:choose>

    <core:choose>
    <core:when test="${passClarity.compareTo('NA') == 0}">
    <gel:log category="HOST" level="ERROR"
    message="[-] Connexion parameter ERROR (passClarity not set)." />
    </core:when>
    <core:otherwise>
    <gel:log category="HOST" level="info"
    message="Xog user password found: ${passClarity}" />
    </core:otherwise>
    </core:choose>

    <!-- ** Connexion Clarity server ** -->
    <gel:log category="XOG" level="info" message="[-] connecting to ${hostClarity}..."/>

    <!--****************************************
    LOGON XOG
    ****************************************-->
    <soap:invoke endpoint="${hostClarity}/niku/xog" var="auth">
    <soap:message>
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
    <soapenv:Header/>
    <soapenv:Body>
    <xog:Login>
    <xog:Username>${loginClarity}</xog:Username>
    <xog:Password>${passClarity}</xog:Password>
    </xog:Login>
    </soapenv:Body>
    </soapenv:Envelope>
    </soap:message>
    </soap:invoke>

    <gel:set asString="true" select="$auth/soapenv:Envelope/soapenv:Body/xog:SessionID/text()" var="sessionID"/>
    <core:choose>
    <core:when test="${SessionID == null}">
    <gel:out>Log in impossible. Vérifiez le hostname/username/password.</gel:out>
    <gel:log>Log in impossible. Vérifiez le hostname/username/password.</gel:log>
    <core:set value="true" var="stepError"/>
    </core:when>
    <core:otherwise>
    <gel:out>Login succeed. Session ID = ${SessionID}</gel:out>
    <gel:log>Login succeed. Session ID = ${SessionID}</gel:log>
    </core:otherwise>
    </core:choose>

    <!-- ** query ** -->
    <sql:query var="data"><![CDATA[
    select invp.id, name , prname, prIsMilestone,xtd_export_fsp
    from odf_ca_task od inner join PRTASK pr on pr.prID = od.id
    inner join inv_projects inv on inv.prid = pr.prprojectid
    inner join inv_investments invp on inv.prid=invp.id
    where od.id = '5872042'
    ]]>
    </sql:query>


    <gel:log category="QUERRY" level="info" message="${data.rowCount}" />

    <core:set value="${data.rows[0].xtd_export_fsp}" var="xtd_export_fsp"/>
    <core:set value="true" var="xtd_export_fsp"/>

    <gel:log category="XOG" level="info"
    message="Debut du parsing de la variable check" />

    <gel:parse var="val_xml">
    <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_project.xsd">
    <Header action="write" externalSource="NIKU" objectType="project" version="13.3.0.286"/>
    <Projects>
    <Project projectID="${data.rows.0.id}" name="${data.rows.0.name}">
    <Tasks>
    <Task name="${data.rows.0.prName}" taskID="${data.rows.0.prExternalID}">
    <CustomInformation>
    <ColumnValue name="xtd_export_fsp">${xtd_export_fsp}</ColumnValue>
    </CustomInformation>

    </Task>
    </Tasks>
    </Project>
    </Projects>
    </NikuDataBus>
    </gel:parse>
    <gel:log category="QUERRY" level="info" message="${data.rows.0.xtd_export_fsp}" />
    <gel:log category="QUERRY" level="info" message="${data.rows.0.prName}" />
    <gel:log level="INFO">After forming the XML structure</gel:log>
    <gel:set asString="true" select="$val_xml" var="userBodyContent"/>
    <gel:log level="INFO">’User body is ‘${userBodyContent}</gel:log>
    <gel:log category="XOG" level="info" message="${val_xml}" />


    <!--XOG SENDING -->
    <gel:out>Session ID = ${SessionID}</gel:out>
    <gel:log category="XOG" level="info"
    message="Envoi du XOG pour l'affectations de colonne" />
    <core:catch var="exception">
    <soap:invoke endpoint="${hostClarity}/niku/xog" var="output">
    <gel:log category="XOG" level="info" message="${output}" />
    <soap:message>
    <soapenv:Envelope>
    <soapenv:Header>

    <xog:Auth>
    <xog:SessionID>${sessionID}</xog:SessionID>
    </xog:Auth>

    </soapenv:Header>
    <gel:log level="INFO">Session id is ${sessionID}</gel:log>
    <soapenv:Body>
    <gel:include select="$val_xml" />
    </soapenv:Body>
    </soapenv:Envelope>
    </soap:message>
    </soap:invoke>
    <gel:set asString="true" select="$auth/soapenv:Envelope/soapenv:Body/xog:SessionID/text()" var="sessionID"/>
    <core:set value="0" var="exception" />
    </core:catch>
    <core:choose>
    <core:when test="${exception != 0}">
    <gel:log level="error" message="[-] Error while sending XOG" />
    <gel:log category="SOAP" level="info" message="${exception}" />
    </core:when>
    <core:otherwise>
    <gel:set asString="true"
    select="$output//XOGOutput/ErrorInformation/Exception/text()"
    var="DocErrTxt1" />
    <gel:log category="XOG" level="info" message="${DocErrTxt1}" />
    <gel:set asString="true"
    select="$output//XOGOutput/Records/Record/ErrorInformation/Exception/text()"
    var="DocErrTxt2" />
    <gel:log category="XOG" level="info" message="${DocErrTxt2}" />
    <core:choose>
    <core:when test="${DocErrTxt1 != null || DocErrTxt2 != null}">
    <gel:log category="SOAP" level="error"
    message="Erreur lors de l'association de colonne !!" />
    <gel:set asString="true"
    select="$output//XOGOutput/Records/Record/ErrorInformation/Description/text()"
    var="DescErr" />
    <gel:log category="XOG" level="info" message="${DocErrTxt1}" />
    <gel:log category="XOG" level="info" message="${DescErr}" />
    <gel:log category="XOG" level="info" message="${DocErrTxt2}" />
    <gel:set asString="true" select="$val_xml" var="xogIn" />
    <gel:log category="XOG" level="info" message="$xogIn" />
    </core:when>
    <core:otherwise>
    <gel:log category="SOAP" level="info"
    message="Affectation de colonne réussie !!" />
    <gel:set asString="true" select="$output" var="docOut"/>
    <gel:log category="XOG" level="info" message="${docOut}"/>
    </core:otherwise>
    </core:choose>
    </core:otherwise>
    </core:choose>
    </gel:script>
    </scriptText>
    </customScript>

    _

    my custom field doesn't updated with new value (xtd_export_fsp = "true") . any suggestions/remarks?



  • 2.  Re: Update custom field from Project Task

    Posted 02-21-2017 12:49 PM

    Just looking at the xml part, project id needs to be the code not the id

    <Project projectID="${data.rows.0.id}"

     

     



  • 3.  Re: Update custom field from Project Task

    Posted 02-21-2017 12:59 PM

    Hello Andrew,

    Thanks for your response. I did   <Project projectID="${data.rows.0.code}" name="${data.rows.0.name}"> but nothing when i see the log its says that Project Object update failed even it seems that Xog output is correct ...

    This is my XOG output :

     

    [XOG]<?xml version="1.0" encoding="UTF-8"?>
    <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_project.xsd">
      <Header action="write" externalSource="NIKU" objectType="project" version="13.3.0.286"/>
      <Projects>
        <Project name="27435 Contib -  Dossier ProEr" projectID="75184">
          <Tasks>
            <Task name="test" taskID="c_t_test">
              <CustomInformation>
                <ColumnValue name="xtd_export_fsp">true</ColumnValue>
              </CustomInformation>
            </Task>
          </Tasks>
        </Project>
      </Projects>
    </NikuDataBus>



  • 4.  Re: Update custom field from Project Task

    Posted 02-22-2017 03:09 AM

    Just looking at a XOG script I wrote previously, it is:

          <Project name="${v_rows.name}" projectID="${v_rows.code}">
            <Tasks>
              <Task finish="${v_rows.schedule_finish}" fixed="false" name="${v_rows.prname}" openForTimeEntry="true" outlineLevel="${v_rows.prwbslevel}"
                percComp="${v_rows.percent_complete}" start="${v_rows.schedule_start}" status="${v_rows.task_status}" taskID="${v_rows.task_id}">
       <CustomInformation>
           <ColumnValue name="is_late_lu">${v_rows.is_late_lu}</ColumnValue> 
       </CustomInformation>
              </Task>
            </Tasks>
          </Project>

     

    From memory, you need to include a lot more attributes for the TASK, rather than the Task Name and Task ID for the XOG to work.



  • 5.  Re: Update custom field from Project Task

    Posted 02-22-2017 06:43 AM

    Hello Roland,

    I did exactly the same attributes as you, but still invalid xml for me this is my Gel script updated...

    <customScript languageCode="gel">

    <scriptText>
    <gel:script
    xmlns:core="jelly:core"
    xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
    xmlns:jxml="jelly:xml"
    xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:sql="jelly:sql"
    xmlns:util="jelly:util"
    xmlns:x="jelly:org.apache.commons.jelly.tags.xml.XMLTagLibrary"
    xmlns:xog="http://www.niku.com/xog"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">



    <!-- ***********************************
    PARAMETRES
    ************************************ -->


    <!-- Params -->
    <!-- BD CONNECTION ** -->
    <gel:setDataSource dbId="Niku" />
    <!-- -->
    <core:set value="NA" var="hostClarity" />
    <core:set value="clarity" var="loginClarity" />
    <core:set value="passw2016" var="passClarity" />
    <!-- connection parameter -->
    <core:catch var="exception">
    <sql:query var="req"><![CDATA[
    Select
    NVL ((SELECT X_ATT_VALUE
    FROM ODF_CA_X_SO_TECH_CONF
    WHERE X_ATT_ATTRIBUTE_ID = 'hostClarity'), 'NA') hostClarity
    From Dual
    ]]>
    </sql:query>
    </core:catch>
    <core:choose>
    <core:when test="${exception != null}">
    <gel:log category="SQL" level="ERROR" message="[-] SQL error : ${exception}" />
    </core:when>
    <core:otherwise>
    <core:if test="${req.rowCount !=0}">
    <core:forEach items="${req.rowsByIndex}" trim="true"
    var="confRow">

    <core:if test="${confRow[0] != null}">
    <core:set value="${confRow[0]}" var="hostClarity" />
    </core:if>
    </core:forEach>
    </core:if>
    </core:otherwise>
    </core:choose>
    <core:choose>
    <core:when test="${loginClarity.compareTo('NA') == 0}">
    <gel:log category="HOST" level="ERROR"
    message="[-] Connexion parameter ERROR (loginClarity not set)." />

    </core:when>
    <core:otherwise>
    <gel:log category="HOST" level="info"
    message="Xog user found: ${loginClarity}" />

    </core:otherwise>
    </core:choose>

    <core:choose>
    <core:when test="${passClarity.compareTo('NA') == 0}">
    <gel:log category="HOST" level="ERROR"
    message="[-] Connexion parameter ERROR (passClarity not set)." />

    </core:when>
    <core:otherwise>
    <gel:log category="HOST" level="info"
    message="Xog user password found: ${passClarity}" />

    </core:otherwise>
    </core:choose>

    <!-- ** Connexion Clarity server ** -->
    <gel:log category="XOG" level="info" message="[-] connecting to ${hostClarity}..."/>

    <!--****************************************
    LOGON XOG
    ****************************************-->

    <soap:invoke endpoint="${hostClarity}/niku/xog" var="auth">
    <soap:message>
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
    <soapenv:Header/>
    <soapenv:Body>
    <xog:Login>
    <xog:Username>${loginClarity}</xog:Username>
    <xog:Password>${passClarity}</xog:Password>
    </xog:Login>
    </soapenv:Body>
    </soapenv:Envelope>
    </soap:message>
    </soap:invoke>

    <gel:set asString="true" select="$auth/soapenv:Envelope/soapenv:Body/xog:SessionID/text()" var="sessionID"/>
    <core:choose>
    <core:when test="${SessionID == null}">
    <gel:out>Log in impossible. Vérifiez le hostname/username/password.</gel:out>
    <gel:log>Log in impossible. Vérifiez le hostname/username/password.</gel:log>
    <core:set value="true" var="stepError"/>
    </core:when>
    <core:otherwise>
    <gel:out>Login succeed. Session ID = ${SessionID}</gel:out>
    <gel:log>Login succeed. Session ID = ${SessionID}</gel:log>
    </core:otherwise>
    </core:choose>

    <!-- ** query ** -->
    <sql:query var="data"><![CDATA[
         select od.id, prExternalID task_id , invp.id project_id, name project_name ,code project_code, prname task_name, prIsMilestone,xtd_export_fsp, prFinish,prStart, prIsFixed,is_open_te,prwbslevel pr_level, pr.prpctcomplete pr_ccomplete, prStatus status
    from odf_ca_task od inner join PRTASK pr on pr.prID = od.id
    inner join inv_projects inv on inv.prid = pr.prprojectid
    inner join inv_investments invp on inv.prid=invp.id
    where od.id = '5873020'
    ]]>

    </sql:query>


    <gel:log category="QUERRY" level="info" message="${data.rowCount}" />

    <core:set value="${data.rows[0].xtd_export_fsp}" var="xtd_export_fsp"/>
    <core:set value="true" var="xtd_export_fsp"/>

    <gel:log category="XOG" level="info"
    message="Debut du parsing de la variable check" />


    <gel:parse var="val_xml">
                             <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_project.xsd">
                   <Header action="write" externalSource="NIKU" objectType="project" version="6.0.11"/>
                   <Projects>
                    <Project name="${data.rows.0.project_name}" projectID="${data.rows.0.project_code}">
            <Tasks>
              <Task finish="${data.rows[0].prFinish}" fixed="false" name="${data.rows[0].task_name}" openForTimeEntry="true" outlineLevel="${data.rows[0].pr_level}"
                percComp="${data.rows[0].pr_ccomplete}" start="${data.rows[0].prStart}" status="${data.rows[0].status}" taskID="${data.rows[0].task_id}">

       <CustomInformation>
           <ColumnValue name="xtd_export_fsp">${xtd_export_fsp}</ColumnValue>
       </CustomInformation>
              </Task>
            </Tasks>
          </Project>
           </Projects>
                             </NikuDataBus>
    </gel:parse>
    <gel:log category="QUERRY" level="info" message="${data.rows.0.xtd_export_fsp}" />
    <gel:log category="QUERRY" level="info" message="${data.rows.0.prName}" />
    <gel:log level="INFO">After forming the XML structure</gel:log>
    <gel:set asString="true" select="$val_xml" var="userBodyContent"/>
    <gel:log level="INFO">’User body is ‘${userBodyContent}</gel:log>
    <gel:log category="XOG" level="info" message="${val_xml}" />


    <!--XOG SENDING -->
    <gel:out>Session ID = ${SessionID}</gel:out>
    <gel:log category="XOG" level="info"
    message="Envoi du XOG pour l'affectations de colonne" />

    <core:catch var="exception">
    <soap:invoke endpoint="${hostClarity}/niku/xog" var="output">
    <gel:log category="XOG" level="info" message="${output}" />
    <soap:message>
    <soapenv:Envelope>
    <soapenv:Header>

    <xog:Auth>
    <xog:SessionID>${sessionID}</xog:SessionID>
    </xog:Auth>

    </soapenv:Header>
    <gel:log level="INFO">Session id is ${sessionID}</gel:log>
    <soapenv:Body>
    <gel:include select="$val_xml" />
    </soapenv:Body>
    </soapenv:Envelope>
    </soap:message>
    </soap:invoke>
    <gel:set asString="true" select="$auth/soapenv:Envelope/soapenv:Body/xog:SessionID/text()" var="sessionID"/>
    <core:set value="0" var="exception" />
    </core:catch>
    <core:choose>
    <core:when test="${exception != 0}">
    <gel:log level="error" message="[-] Error while sending XOG" />
    <gel:log category="SOAP" level="info" message="${exception}" />
    </core:when>
    <core:otherwise>
    <gel:set asString="true"
    select="$output//XOGOutput/ErrorInformation/Exception/text()"
    var="DocErrTxt1" />

    <gel:log category="XOG" level="info" message="${DocErrTxt1}" />
    <gel:set asString="true"
    select="$output//XOGOutput/Records/Record/ErrorInformation/Exception/text()"
    var="DocErrTxt2" />

    <gel:log category="XOG" level="info" message="${DocErrTxt2}" />
    <core:choose>
    <core:when test="${DocErrTxt1 != null || DocErrTxt2 != null}">
    <gel:log category="SOAP" level="error"
    message="Erreur lors de l'association de colonne !!" />

    <gel:set asString="true"
    select="$output//XOGOutput/Records/Record/ErrorInformation/Description/text()"
    var="DescErr" />

    <gel:log category="XOG" level="info" message="${DocErrTxt1}" />
    <gel:log category="XOG" level="info" message="${DescErr}" />
    <gel:log category="XOG" level="info" message="${DocErrTxt2}" />
    <gel:set asString="true" select="$val_xml" var="xogIn" />
    <gel:log category="XOG" level="info" message="$xogIn" />
    </core:when>
    <core:otherwise>
    <gel:log category="SOAP" level="info"
    message="Affectation de colonne réussie !!" />

    <gel:set asString="true" select="$output" var="docOut"/>
    <gel:log category="XOG" level="info" message="${docOut}"/>
    </core:otherwise>
    </core:choose>
    </core:otherwise>
    </core:choose>
    </gel:script>
    </scriptText>
    </customScript>

    Can i see your script to have an idea of whats wrong ... Thanks a lot



  • 6.  Re: Update custom field from Project Task

    Posted 02-22-2017 09:33 AM

    The date fields need to be formatted correctly, and  given you may be using  non-english environment, I won't guess what format you should be using.  As Andrew suggests, XOG out a TASK record to see what the XOG output looks like and try to XOG it back in.  Once that is working, then modify your Gel script to replicate the XOG.



  • 7.  Re: Update custom field from Project Task
    Best Answer

    Posted 02-22-2017 03:39 AM

    As Roland has said you don't have all the mandatory fields for this to work

    you need to have "outlineLevel" in the task (wbs)

     

    <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_project.xsd">
      <Header action="write" externalSource="NIKU" objectType="project" version="13.3.0.286"/>
      <Projects>
        <Project name="27435 Contib -  Dossier ProEr" projectID="75184">
          <Tasks>
            <Task name="test" taskID="c_t_test" outlineLevel="1">
              <CustomInformation>
                <ColumnValue name="xtd_export_fsp">true</ColumnValue>
              </CustomInformation>
            </Task>
          </Tasks>
        </Project>
      </Projects>
    </NikuDataBus>

     

    I'd recommend always building the XOG and testing first, before scripting the gel  

    saves the inevitable headaches with bugs and problems.



  • 8.  Re: Update custom field from Project Task

    Posted 02-22-2017 10:00 AM

    Does the user that you are running the XOG through have the correct access rights in the application - i.e. the ability to update projects/tasks through XOG (this is a distinct access right you would need to explicitly grant).

     

    (This is just a GUESS - as others have pointed out, you might be able to do all this in a much simpler process not involving GEL or failing that you need to try to debug things one-bit-at-a-time - its VERY hard for us to look at a big GEL script and try to work out where the problem is - its much easier for us to help if you have narrowed the problem down to a specific area)



  • 9.  Re: Update custom field from Project Task

    Posted 02-22-2017 08:03 AM

    If this GEL is to be used within process as custom action and if task object is linked to process, then you can go with system action and set this value to True.