Thursday, December 12, 2019

Sailpoint IIQ Form - Reading Value from IIQ Database


Below is the sample Form in which most of the value of the field is read from the IIQ Custom 
Table DB . in the previous posts we have seen the sample code for reading the data from IIQ Database.
Create a Table in IIQ Database name VIS_SERVICE_ACCOUNT with below fields

ACCOUNT_ID
APPLICATION_NAME
ACCOUNT_DESCRIPTION
OWNER
INTERACTIVE


    
    
    <?xml version='1.0' encoding='UTF-8'?>
    <!DOCTYPE Form PUBLIC "sailpoint.dtd" "sailpoint.dtd">
    <Form name="Service Account Details Form" type="Workflow">
      <Attributes>
        <Map>
          <entry key="pageTitle" value="Service Account Details Form"/>
        </Map>
      </Attributes>
      <Section>
        <Field displayName="Account Identifier" helpKey="Account ID of Service Account" name="accountID" postBack="true" required="true" type="string">
          <AllowedValuesDefinition>
            <Script>
              <Source>
       import sailpoint.tools.Util;
       import sailpoint.tools.GeneralException;
       import org.apache.log4j.Logger;
    
       Logger logger= Logger.getLogger("vis.form.ServiceAccount.fields");
    
       List itemList = new ArrayList();
                
       try{
    
       String  columnSearch = "ACCOUNT_ID";
       String  tableName = "VIS_SERVICE_ACCOUNT";
       String  conditionColumnName = "ACCOUNT_ID";
       String  sqlStatement ="select "+columnSearch+" from "+tableName;
       Iterator it = context.search("sql:"+sqlStatement, null,null);
    
        while ((null != it) &amp;&amp; it.hasNext()) {
          String i= it.next();
          if(!itemList.contains(i)) {
           itemList.add(i);
          }
        }
       Util.flushIterator(it);
       }
       catch(GeneralException ex){
        logger.error("Error in AppEdit form : " +ex.getMessage());
       }
       return itemList;
              </Source>
            </Script>
          </AllowedValuesDefinition>
        </Field>
        <Field displayName="Application Name" displayType="combobox" helpKey="Application Name of the Service Account" name="applicationName" postBack="true" required="true" type="string">
          <AllowedValuesDefinition>
            <Script>
              <Source>
       import sailpoint.tools.Util;
       import sailpoint.tools.GeneralException;
       import org.apache.log4j.Logger;
    
       Logger logger= Logger.getLogger("vis.form.ServiceAccount.fields");
       List itemList = new ArrayList();
                
       try{
    
       String  columnSearch = "APPLICATION_NAME";
       String  tableName = "VIS_SERVICE_ACCOUNT";
       String  conditionColumnName = "ACCOUNT_ID";
       String  sqlStatement ="select "+columnSearch+" from "+tableName + " where ACCOUNT_ID ='"+ accountID + "'";
                System.out.println("sqlStatement = " + sqlStatement);
       Iterator it = context.search("sql:"+sqlStatement, null,null);
    
        while ((null != it) &amp;&amp; it.hasNext()) {
          String i= it.next();
          itemList.add(i);
        }
       Util.flushIterator(it);
       }
       catch(GeneralException ex){
        logger.error("Error in AppEdit form : " +ex.getMessage());
       }
       return itemList;
              </Source>
            </Script>
          </AllowedValuesDefinition>
          <Attributes>
            <Map>
              <entry key="hidden">
                <value>
                  <Script>
                    <Source>if(accountID==null || accountID ==void){
                      return true;
                    }</Source>
                  </Script>
                </value>
              </entry>
            </Map>
          </Attributes>
        </Field>
        <Field displayName="Account Description" dynamic="true" helpKey="Description of Service Account" name="accountDescription" type="string">
          <Attributes>
            <Map>
              <entry key="hidden">
                <value>
                  <Script>
                    <Source>if(accountID==null || accountID ==void || applicationName==null || applicationName ==void){
                      return true;
                    }</Source>
                  </Script>
                </value>
              </entry>
              <entry key="readOnly" value="true"/>
            </Map>
          </Attributes>
          <Script>
            <Source>
              if(accountID!=null){
         String  columnSearch = "ACCOUNT_DESCRIPTION";
         String  tableName = "VIS_SERVICE_ACCOUNT";
         String  conditionColumnName = "ACCOUNT_ID";
         String  sqlStatement ="select "+columnSearch+" from "+tableName+" where "+conditionColumnName+"='"+accountID+"'"+" and APPLICATION_NAME ='"+applicationName+"'";
         Iterator  itr = context.search("sql:"+sqlStatement, null,null);
         while(itr.hasNext()){
          String accountDescription =  itr.next();
          if(accountDescription!=null){
         return accountDescription;
          }
         }
             }
            </Source>
          </Script>
        </Field>
        <Field displayName="Account Owner" dynamic="true" helpKey="Owner of the Service Account" name="owner" type="sailpoint.object.Identity">
          <Attributes>
            <Map>
              <entry key="hidden">
                <value>
                  <Script>
                    <Source>if(accountID==null || accountID ==void || applicationName==null || applicationName ==void){
                      return true;
                    }</Source>
                  </Script>
                </value>
              </entry>
              <entry key="readOnly" value="true"/>
            </Map>
          </Attributes>
          <Script>
            <Source>         
            if(accountID!=null){
         String  columnSearch = "OWNER";
         String  tableName = "VIS_SERVICE_ACCOUNT";
         String  conditionColumnName = "ACCOUNT_ID";
         String  sqlStatement ="select "+columnSearch+" from "+tableName+" where "+conditionColumnName+"='"+accountID+"'"+" and APPLICATION_NAME ='"+applicationName+"'";
         Iterator  itr = context.search("sql:"+sqlStatement, null,null);
         while(itr.hasNext()){
          String owner =  itr.next();
          if(owner!=null){
         return owner;
          }
         }
              }</Source>
          </Script>
        </Field>
        <Field displayName="Interactive" dynamic="true" helpKey="Service Account is Interactive or Non-Interactive" name="interactive" type="string">
          <AllowedValuesDefinition>
            <Value>
              <List>
                <String>Yes</String>
                <String>No</String>
              </List>
            </Value>
          </AllowedValuesDefinition>
          <Attributes>
            <Map>
              <entry key="hidden">
                <value>
                  <Script>
                    <Source>if(accountID==null || accountID ==void || applicationName==null || applicationName ==void){
                      return true;
                    }</Source>
                  </Script>
                </value>
              </entry>
              <entry key="readOnly" value="true"/>
            </Map>
          </Attributes>
          <Script>
            <Source>
              if(accountID!=null){
         String  columnSearch = "INTERACTIVE";
         String  tableName = "VIS_SERVICE_ACCOUNT";
         String  conditionColumnName = "ACCOUNT_ID";
         String  sqlStatement ="select "+columnSearch+" from "+tableName+" where "+conditionColumnName+"='"+accountID+"'"+" and APPLICATION_NAME ='"+applicationName+"'";
         Iterator  itr = context.search("sql:"+sqlStatement, null,null);
         while(itr.hasNext()){
          String interactive =  itr.next();
          if(interactive!=null){
         return interactive;
          }
         }
              }
            </Source>
          </Script>
        </Field>
      </Section>
      <Button action="back" label="Cancel"/>
    </Form>