AnsweredAssumed Answered

Multi-Value fields in an On Demand Environment

Question asked by amy.yarrington on Oct 1, 2010
Latest reply on Oct 6, 2010 by Patrick Cooper
We are on-demand customers running on 12.0.6.

We have a custom attribute on the Project object that is a multi-select field. I need to be able to include that field in portlets and be able to filter on the values. I am really new at Clarity in general (only since April) and I'm trying to absorb as much as I can, but....my brain is starting to hurt! Forgive the newbie question!

I have found a great thread where Senthil gave some instructions on how to write the SQL code to return the values. I understand that pretty well. However, I think I need them to be flattened (comma or semicolon delimited). If I just put the select statements in a query as-is then I get messages about too many dimensions, etc etc which I'm sure is because the query returns one record for each value in the column. In the same thread he offered up a custom function to go with the SQL script that would do the flattening part. But as an On Demand customer, I don't believe I have the ability to create such a function which I assume is on the database side.

So then I found some instructions on writing GEL scripts - I thought I would create a new attribute on the project where I would store the "flattened" values. I was trying to write a process with some GEL script in it to update that field. However, my process hangs on the update step and I have absolutely no feedback as to what might be wrong with it.

I guess my first question is...what is the best way to accomplish what I am trying to do here? (Step by step, please!) If I am headed down the right path, then I need some assistance validating my GEL script (shown below).
<gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:x="jelly:xml"
xmlns:core="jelly:core"
 xmlns:sql="jelly:sql"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:file="jelly:com.niku.union.gel.FILETagLibrary"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
 xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:xog="http://nt2nikudev01/niku/xog">

  <gel:parameter var="XOGusername" default="xoguser"/>
 <gel:parameter var="XOGpassword" default="Clarity1" secure="true"/>    
  <gel:setDataSource dbId="niku"/>    

 <!-- query to get project data for thisProject -->                
 <sql:query var="resultProject">
 SELECT INV.NAME, ATTRIBUTE, CODE , VALUE, LOOKUP.NAME 
FROM INV_INVESTMENTS INV , ODF_MULTI_VALUED_LOOKUPS MUL , 
(SELECT LOOKUP_CODE , NAME FROM CMN_LOOKUPS_V WHERE LANGUAGE_CODE = 'en' AND LOOKUP_TYPE = 'vf_coalition' 
) LOOKUP 
WHERE INV .ID = MUL . PK_ID AND INV . ODF_OBJECT_CODE = 'project' AND MUL .VALUE= LOOKUP . LOOKUP_CODE  AND INV_INVESTMENTS.ID = ${gel_objectInstanceId}
</sql:query>

<!-- start loop to get project data -->
<core:forEach items="${resultProject.rowsByIndex}" trim="false" var="row">
<core:if test="${fcoalition != null}">
<core:set var="fcoalition" value="${fcoalition}; "/>
</core:if>
<core:set value="${row[0]}" var="sProjName"/>
<core:set value="${row[2]}" var="sProjID"/>
<core:set value="${fcoalition}${row[4]}" var="fcoalition"/>
</core:forEach>
   
 <!-- Run XOG inline (not using a file) -->
<soap:invoke endpoint="http://nt2nikudev01/niku/xog" var="result">
<soap:message>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xog="http://www.niku.com/xog">
<soapenv:Body>
<xog:Login xmlns:xog="http://www.niku.com/xog">
<xog:Username>${XOGusername}</xog:Username>
<xog:Password>${XOGpassword}</xog:Password>
</xog:Login>                                        

<!-- start of XOG script -->
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
xsi:noNamespaceSchemaLocation="../xsd/nikuxog_project.xsd">
<Header action="write" externalSource="NIKU" objectType="project" version="12.0.5.5350"/>
<Projects>
<Project name="${sProjName}"
projectID="${sProjID}">
<CustomInformation>
<ColumnValue name="vf_flat_coalition">${fcoalition}</ColumnValue>
</CustomInformation>
</Project>
</Projects>
</NikuDataBus>

<!-- end of XOG script -->                        
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>

</gel:script>

Outcomes