Clarity

I want to add some few hundreds of values to my already existing lookup. Is there any way via which this could be done automatically without entering it manually?

Any response of this would be really helpful! Thanks in advance.

Hi Monica,

Have you considered using XOG to update your lookup?

The changes will be to a file in XML format, which you then have to import in the CA PPM system.

You basically have to use a XOG 'read' file to read/export the existing lookup values, then add your new values to the XML file, then import it back. It will be somewhat manual but not as manual as entering them in the UI

Here are few of the topics that already came up on the communities about it that could help:

XOG newby... can someone help me xog out a lookup 

CA Clarity Tuesday Tip : XOG - controlling the amount of data 

How to XOG Data for a Lookup 

Hope this helps -Nika

Agree with Nika that XOGing new values to a static lookup (especcially when it is not dependend) is the simplest and fastest way if there are 10 or more new values.

Here's a GEL we run at the start of every quarter to automagically update a rolling Fiscal Quarter lookup.

<!-- Update OSUWMC Fiscal Quarter Lookup -->
 <gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
  xmlns:core="jelly:core"
  xmlns:util="jelly:util"
  xmlns:email="jelly:email"
  xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
  xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
  xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
  xmlns:sql="jelly:sql"
  xmlns:jxml="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">

 <core:set var="debug" value="true"/>
 <gel:parameter var="xogUsername" default="not set"/>
 <gel:parameter var="xogPassword" secure="true" default="not set"/>

 <!-- Build the data -->
 <gel:setDataSource dbId="niku"/>
 <sql:query var="data" escapeText="0">
          <![CDATA[SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,-9,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,-9,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,-9,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,-9,GetDate())),1)) NAME
, 'inactive' STATUS
, 'inactivated' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,-6,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,-6,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,-6,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,-6,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter Minus 4 Quarters' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,-3,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,-3,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,-3,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,-3,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter Minus 3 Quarters' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,0,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,0,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,0,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,0,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter Minus 2 Quarters' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,3,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,3,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,3,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,3,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter Minus 1 Quarter' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,6,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,6,GetDate())),1))  CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,6,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,6,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,9,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,9,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,9,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,9,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter Plus 1 Quarter' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,12,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,12,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,12,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,12,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter Plus 2 Quarters' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,15,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,15,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,15,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,15,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter Plus 3 Quarters' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,18,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,18,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,18,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,18,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter Plus 4 Quarters' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,21,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,21,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,21,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,21,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter Plus 5 Quarters' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,24,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,24,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,24,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,24,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter Plus 6 Quarters' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,27,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,27,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,27,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,27,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter Plus 7 Quarters' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,30,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,30,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,30,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,30,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter Plus 8 Quarters' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,33,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,33,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,33,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,33,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter Plus 9 Quarters' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,36,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,36,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,36,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,36,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter Plus 10 Quarters' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,39,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,39,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,39,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,39,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter Plus 11 Quarters' DESCRIPTION
UNION
SELECT
'fy'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,42,GetDate()),1),2)) + '_q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,42,GetDate())),1)) CODE
, 'FY'+ (RIGHT(CONVERT(VARCHAR(8),DATEADD(month,42,GetDate()),1),2)) + ' Q' + (CONVERT(VARCHAR(8),DATEPART(q,DATEADD(month,42,GetDate())),1)) NAME
, 'active' STATUS
, 'Current Fiscal Quarter Plus 12 Quarters' DESCRIPTION]]>
 </sql:query>

 <!--  Get xog URL from application by fpena -->
 <core:invokeStatic className="com.niku.union.config.ConfigurationManager" method="getInstance" var="config"/>
   <core:set var="v_IsLocal">0</core:set>
   <core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getId()}" var="WebServerid"/>
 <core:if test="${WebServerid == 'app'}">
   <core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getAddress()}" var="AddressIp"/>
   <core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getSslEntryUrl()}" var="EntryURL"/>
   <core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getSslPort()}" var="Port"/>
  <core:if test="${EntryURL == 'http://localhost' }">
   <gel:log level="info" message="NULL ADDRESS IS LOCAL... "/>
   <core:set var="v_IsLocal">1</core:set>
   <core:set var="AddressIp">${EntryURL}</core:set>
  </core:if>
 </core:if>
 <core:if test="${WebServerid == 'nsa'}">
   <core:set value="${config.getProperties().getWebServer().getWebServerInstance(1).getAddress()}" var="AddressIp"/>
   <core:set value="${config.getProperties().getWebServer().getWebServerInstance(1).getEntryUrl()}" var="EntryURL"/>
   <core:set value="${config.getProperties().getWebServer().getWebServerInstance(1).getPort()}" var="Port"/>
  <core:if test="${EntryURL == 'http://localhost' }">
   <gel:log level="info" message="NULL ADDRESS IS LOCAL... "/>
   <core:set var="v_IsLocal">1</core:set>
   <core:set var="AddressIp">${EntryURL}</core:set>
  </core:if>
 </core:if>
 <core:if test="${Port != null }">
  <core:set value="${EntryURL}" var="EntryURL"/>
 </core:if>
 <core:if test="${v_IsLocal == 0 }">
  <core:if test="${AddressIp != '' }">
   <core:set value="http://${AddressIp}" var="EntryURL"/>
  </core:if>
 </core:if>
   <core:set value="${config.getProperties().getDirectories().getInstallDir()}" var="NIKUHOME"/>
   <core:set value="${EntryURL}/niku/xog" var="xogURL"/>
   <core:set value="${config.getProperties().getMailServer().getHost()}" var="mailServer"/>

 <!-- Log into xog and get a session ID -->
 <soap:invoke endpoint="${xogURL}" 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>${xogUsername}</xog:Username>
        <xog:Password>${xogPassword}</xog:Password>
       </xog:Login>
      </soapenv:Body>
   </soapenv:Envelope>
  </soap:message>
 </soap:invoke>

 <!-- Checking whether a sessionID is returned. If not, it means that login was unsuccessful -->
 <gel:set asString="true" select="$auth/SOAP-ENV:Envelope/SOAP-ENV:Body/xog:SessionID/text()" var="sessionID"/>
 <core:choose>
   <core:when test="${sessionID == null}">
    <gel:log>Couldn't log in. Please check the hostname/username/password.</gel:log>
   </core:when>
   <core:otherwise>
    <gel:log>Login successful. Session ID is ${sessionID}</gel:log>
   </core:otherwise>
 </core:choose>

<core:forEach items="${data.rows}" trim="true" var="row">
<core:set value="${row.get('CODE')}" var="lookup_code"/>
<core:set value="${row.get('NAME')}" var="lookup_name"/>
<core:set value="${row.get('STATUS')}" var="lookup_status"/>
<core:set value="${row.get('DESCRIPTION')}" var="lookup_description"/>

 <!-- Xog in Updates to the Lookup -->
 <soap:invoke endpoint="${xogURL}" var="updateFinish">
 <soap:message>
  <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
   <soapenv:Header>
    <xog:Auth>
     <xog:SessionID>${sessionID}</xog:SessionID>
    </xog:Auth>
   </soapenv:Header>
 <soapenv:Body>

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_contentPack.xsd">
  <Header action="write" externalSource="NIKU" objectType="contentPack" version="14.3.0.298"/>
  <contentPack update="true">
    <lookups update="true">
      <staticLookup code="OSUWMC_FISCAL_QUARTERS" hiddenAttributeName="lookup_code" sortStyle="alphanumeric" status="active" update="true">
        <nls description="This is a lookup of OSUWMC Fiscal Quarters managed by the OSUWMC Fiscal Quarter Lookup Update scheduled job" languageCode="en" name="OSUWMC Fiscal Quarters"/>
        <lookupValue code="${lookup_code}" enum="0" sortOrder="0" status="${lookup_status}">
          <nls description="${lookup_description}" languageCode="en" name="${lookup_name}"/>
        </lookupValue>
      </staticLookup>
    </lookups>
  </contentPack>
</NikuDataBus>

</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
</core:forEach>

<!-- Log out of xog -->
<soap:invoke endpoint="${xogURL}" var="logoutresult">
 <soap:message>
  <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
   <soapenv:Header>
    <xog:Auth>
     <xog:SessionID>${sessionID}</xog:SessionID>
    </xog:Auth>
   </soapenv:Header>
  <soapenv:Body>
    <xog:Logout/>
  </soapenv:Body>
 </soapenv:Envelope>
 </soap:message>
</soap:invoke>

<!-- comment out below log output once in production  -->
<gel:log> ${xogURL}, ${xogUsername}, ${xogPassword}, ${sessionID}</gel:log>
</gel:script>

I did not test your script and therefore have to ask

Does your script deactivate old obsolete values?

Does it readjust the sort order so that the ones most likely needed are at the top of the list?

Great questions Martti. I had to look up my documentation and review the code to get my head back around it.

The Requirements are for 4 quarters in the past, current quarter, and 12 quarters in the future so this produces a 'rolling wave' of 17 quarters.

• Lines 24 & 25 in the first SELECT statement you'll see this inactivates the 5th oldest quarter (just updating an existing ID)
• Lines 124 through 128 will create the new forecast quarter (new 12th quarter out).
• All the rest are simply getting an update to their description.

We have jobs that keep these 'Fiscal Start Date' and 'Fiscal Finish Date'  attributes in sync with Idea Start & Finish dates. 

Thanks for the more details.

Using a custom lookup allows you to re-use the ID's without actually renaming the fiscal quarters.

Is the lookup used so that old  Fiscal quarters are not renamed with the new ones anywhere is active data?

Was it simpler and safer to use a custom lookup and the process than  dynamic lookup which queries biz_com_perriods and where just the conditions auto-roll?

Hi Martti. Yes, old data is maintained. Dynamic Lookups are great for in-application use by users who are clicking browse in a filter. We have automation tied to this, feed data to different systems, feed the data warehouse without having to understand any of this - and this is just what is implemented today. We have future dreams of automagically building portfolios each quarter. Having persisted values in the datamodel makes all of this easier.