Clarity

I am doing a basic validation on the projects on the below fields, and i want to display the specific list of errors in a Custom object attribute screen .

Project is a 'Program'

Project is a 'Template'

Project is 'Inactive'Project status 'Unapproved'

Project Financial Status Is Not 'Open'

Project Region 'Europe'

how can i check each condition for each projects which are available in custom object and display the error message in Custom object's attribute.

Could someone help me on this.

Thank you in advance.

Thenmozhi K

In your query block, you need include the error information based on your business requirements, then you need load the data into the custom objects.

For Example: Project Manager is null, Project Financial Status is Not Open

Sample Query:

SELECT I.CODE, I.NAME, (CASE WHEN I.MANAGER_ID IS NULL THEN 'No Project Manager' END || , || 

CASE WHEN P.STATUS != 'O' THEN 'Project Financial Status is Not Open' END) ERROR_DESC

FROM INV_INVESTMENTS I JOIN PAC_MNT_PROJECTS P ON I.ID = P.ID

Thanks

Senthil

Hi Senthil,

Thanks for your support!!!

1 ) I tried to execute your code, it works fine and how to use/call  "ERROR_DESC"  and display the result in gel custom object.

2 ) When i execute my code (copied below), i'm getting positive result in gel log. But it's getting overwrite other projects as well.Below is the screen shot in the custom object.

 <core:catch var="exception1">

        <sql:query var="invcode">

select distinct inv.id "PROJECT_ID",inv.code,invp.IS_PROGRAM,invp.IS_TEMPLATE,inv.IS_ACTIVE "PROJECT_ACTIVE",inv.STATUS "PROJECT_STATUS",

                pac.STATUS "FINANCIAL_STATUS",prt.PRISTASK,pac.LOCATIONID

                from

    INV_PROJECTS invp, INV_INVESTMENTS inv, PAC_MNT_PROJECTS pac,ODF_CA_PROJECT odf, PRTASK prt,odf_ca_ne_actual_if_his2 odfh

                Where invp.PRID=inv.ID

                  and pac.ID=inv.ID

      and odf.ID=pac.ID

      and prt.PRPROJECTID=odf.id

      and odf.Z_RGN =4

      and odf.z_lead_comp in(38,39)

                  and (pac.DEPARTCODE Like 'NESAS%' OR pac.DEPARTCODE Like 'NTCE%')

    and inv.code=odfh.Z_PROJECT_ID

</sql:query>

    </core:catch>

    <core:if test="${exception1 != null}">

        <core:file append="true" trim="false" omitXmlDeclaration="true" name="${ErrLog}">

${TS} - Project Extraction failed

${TS} - ERROR : ${exception1}

               </core:file>

    </core:if>

    <core:if test="${invcode.rowCount &gt; 0}">

                <gel:log>Project_Count : ${invcode.rowCount}</gel:log>

        <core:forEach items="${invcode.rows}" trim="true" var="projFile">

            <core:set var="projectcode" value="${projFile.code}" />

                                                <core:set var="PROJECT_ID" value="${projFile.PROJECT_ID}" />

                                                <core:set var="PROJECTIS_PROGRAM" value="${projFile.IS_PROGRAM}" />

                                                <core:set var="PROJECTIS_TEMPLATE" value="${projFile.IS_TEMPLATE}" />

                                                <core:set var="PROJECT_ACTIVE" value="${projFile.PROJECT_ACTIVE}" />

                                                <core:set var="PROJECT_STATUS" value="${projFile.PROJECT_STATUS}" />

                                                <core:set var="FINANCIAL_STATUS" value="${projFile.FINANCIAL_STATUS}" />

                                                <core:set var="PRISTASK" value="${projFile.PRISTASK}" />

                                                <core:set var="LOCATIONID" value="${projFile.LOCATIONID}" />

 <gel:log>Project_CODE      : ${projectcode}</gel:log>

                                                <!--gel:log>ProjectID         : ${PROJECT_ID}</gel:log>

                                                <gel:log>IS_PROGRAM        : ${PROJECTIS_PROGRAM}</gel:log>

                                                <gel:log>IS_TEMPLATE       : ${PROJECTIS_TEMPLATE}</gel:log>

                                                <gel:log>PROJECT_ACTIVE    : ${PROJECT_ACTIVE}</gel:log>

                                                <gel:log>PROJECT_STATUS    : ${PROJECT_STATUS}</gel:log>

                                                <gel:log>FINANCIAL_STATUS  : ${FINANCIAL_STATUS}</gel:log>

                                                <gel:log>PRISTASK          : ${PRISTASK}</gel:log>

                                                <gel:log>LOCATIONID        : ${LOCATIONID}</gel:log-->

                <core:choose>

                                                 <core:when test = "${PROJECTIS_PROGRAM == 1}">

                    <sql:update var="Failure_Records1"> update odf_ca_ne_actual_if_his2  set z_error_info = 'Project should not be a Program'</sql:update>

                                                                                <gel:log>Program Query has been updated in Actual History Screen</gel:log>            

                                                 </core:when>

             <core:when test="${PROJECTIS_TEMPLATE == 1}">

                    <sql:update var="Failure_Records2"> update odf_ca_ne_actual_if_his2  set z_error_info = 'Project should not be a Template'</sql:update>

                                                                                <gel:log>Template Query has been updated in Actual History Screen</gel:log>

                                                  </core:when>

             <core:when test="${PROJECT_ACTIVE == 0}">

                    <sql:update var="Failure_Records3"> update odf_ca_ne_actual_if_his2  set z_error_info = 'Project should not be Inactive'</sql:update>

                                                                                <gel:log>Project Active Query has been updated in Actual History Screen</gel:log>

                                                  </core:when>

                                                 <core:when test="${PROJECT_STATUS != 1}">

                    <sql:update var="Failure_Records4"> update odf_ca_ne_actual_if_his2  set z_error_info = 'Project is not "Approved" Status'</sql:update>

                                                                                <gel:log>Project Status has been updated in Actual History Screen</gel:log>

                                                  </core:when>

                                                 <core:when test="${FINANCIAL_STATUS!='O'}">

                    <sql:update var="Failure_Records5"> update odf_ca_ne_actual_if_his2  set z_error_info = 'Project Financial Status is Not Open'</sql:update>

                                                                                <gel:log>Financial Status Query has been updated in Actual History Screen</gel:log>

                                                  </core:when>

             <core:when test="${PRISTASK == 0}">

                    <sql:update var="Failure_Records6"> update odf_ca_ne_actual_if_his2  set z_error_info = 'Task Not Available'</sql:update>

                                                                                <gel:log>Task Query has been updated in Actual History Screen</gel:log>

                                                  </core:when>

             <core:when test="${LOCATIONID == null}">

                    <sql:update var="Failure_Records7"> update odf_ca_ne_actual_if_his2  set z_error_info = 'Project Location is NULL'</sql:update>

                                                                                <gel:log>Location Query has been updated in Actual History Screen</gel:log>

                                                  </core:when>

                                                <core:otherwise>

                                                                                <!--sql:update var="Success_Records"> update odf_ca_ne_actual_if_his2  set z_error_info = 'Project details are valid'</sql:update>

                                                                                <gel:log>Project is validated and updated in Actual History Screen</gel:log-->

                                                </core:otherwise>

                  </core:choose>

   </core:forEach>

</core:if>

None of your SQL update statements contain a where clause - so they would update all instances of your custom object with the message each time.

(Arguably you should not be doing SQL updates anyway, you should update the data via XOG which would protect you from issuing statements that "break" the data model)

This is very confusing ; there appears to be 2 different conversations going on related to the same problem making it hard to follow what is going on; can we try to keep a problem in a single discussion topic otherwise it just gets lost and people will be dis-inclined to help I think.

Displaying Error message in custom object attribute using GEL 

Concatenate all the errors description in single field(custom object)