Getting Multiple Records from Web API

Question

I am integrating an employee system with another system that tracks our employee background checks. The Web API returns several records for an employee.

body Dictionary
    LASTNAME "Smith"(Text)
    USER_ID 1234567(Number (Integer))
    FIRSTNAME "Bob"(Text)
BGCDATA List of Dictionary - 3 items
 Dictionary
  SERVICENAME "Fingerprinting"(Text)
  TYPENAME "Background Check Option for School Teachers"(Text)
  BGCDATE "July, 25 2018 00:00:00"(Text)
  BGCSTATUS "Complete"(Text)
  BGCCOMMENTS ""(Text)
 Dictionary
  SERVICENAME "MYB"(Text)
  TYPENAME "EMPLOYEE"(Text)
  BGCDATE "October, 24 2023 08:00:26"(Text)
  BGCSTATUS "Complete"(Text)
  BGCCOMMENTS null(Null)
 Dictionary
  SERVICENAME "MYB"(Text)
  TYPENAME "EMPLOYEE"(Text)
  BGCDATE "April, 19 2017 09:22:39"(Text)
  BGCSTATUS "Complete"(Text)
  BGCCOMMENTS null(Null)

Here is the XSD for the CDT that I am storing the data into.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="urn:com:appian:types:PMSO" targetNamespace="urn:com:appian:types:PMSO">
  <xsd:complexType name="PMSO_BGCData">
    <xsd:annotation>
      <xsd:documentation><![CDATA[Background Check Data]]></xsd:documentation>
    </xsd:annotation>
    <xsd:sequence>
      <xsd:element name="USER_ID" nillable="true" type="xsd:int" />
      <xsd:element name="SERVICENAME" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="TYPENAME" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="BGCDATE" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="BGCSTATUS" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="BGCCOMMENTS" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

I have an expression rule that converts the resultant JSON to the CDT.

a!localVariables(
  if (
    a!isNotNullOrEmpty(ri!result),
    'type!{urn:com:appian:types:PMSO}PMSO_BGCData'(
      USER_ID: ri!result.USER_ID,
      /*FIRSTNAME: ri!result.FIRSTNAME,*/
      /*LASTNAME: ri!result.LASTNAME,*/
      SERVICENAME: ri!result.SERVICENAME,
      TYPENAME: ri!result.TYPENAME,
      BGCDATE: ri!result.BGCDATE,
      BGCSTATUS: ri!result.BGCSTATUS,
      BGCCOMMENTS: ri!result.COMMENTS
     
    ),
    null
  )
)

The problem is that I do not get multiple records, just one. If the resultant is multiple records, I get this from the Expression Rule.

MSO_BGCData
    USER_ID 1234567(Number (Integer))
    SERVICENAME ""(Text)
    TYPENAME ""(Text)
    BGCDATE ""(Text)
    BGCSTATUS ""(Text)
    BGCCOMMENTS ""(Text)

If the resultant API call is a single record, I get this from the Expression Rule.

PMSO_BGCData
    USER_ID 1234567(Number (Integer))
    SERVICENAME "Fingerprinting"(Text)
    TYPENAME "Background Check Option for School Teachers"(Text)
    BGCDATE "July, 25 2018 00:00:00"(Text)
    BGCSTATUS "Complete"(Text)
    BGCCOMMENTS ""(Text)

I am trying to use the USER_ID for each of the nested records. I am not sure that the result is well formed or I am not doing something correctly.

Abhay Dalsaniya · Answer

In the expression rule that converts the resultant JSON to the CDT y ou need to iterate multiple records using forEach().

Chris.Gillespie · Answer

Thank you to everyone that responded to my query. This is how I solved the problem.

I created another CDT table, PMSO_VIRTUS_Compliance that basically combined all of the tables into one.

Here is the XSD

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="urn:com:appian:types:PMSO" targetNamespace="urn:com:appian:types:PMSO">
  <xsd:include schemaLocation="%7Burn%3Acom%3Aappian%3Atypes%3APMSO%7DPMSO_DOCData.xsd" />
  <xsd:include schemaLocation="%7Burn%3Acom%3Aappian%3Atypes%3APMSO%7DPMSO_BGCData.xsd" />
  <xsd:include schemaLocation="%7Burn%3Acom%3Aappian%3Atypes%3APMSO%7DPMSO_TrainingData.xsd" />
  <xsd:complexType name="PMSO_VIRTUS_Compliance">
    <xsd:annotation>
      <xsd:documentation><![CDATA[All Compliance data from VIRTUS]]></xsd:documentation>
    </xsd:annotation>
    <xsd:sequence>
      <xsd:element name="USER_ID" nillable="true" type="xsd:int" />
      <xsd:element name="FIRSTNAME" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="LASTNAME" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="COMPLIANCESTATUS" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element maxOccurs="unbounded" minOccurs="0" name="DOCDATA" type="tns:PMSO_DOCData">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@OneToMany(cascade=CascadeType.ALL, indexed=false)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element maxOccurs="unbounded" minOccurs="0" name="BGCDATA" type="tns:PMSO_BGCData">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@OneToMany(cascade=CascadeType.ALL, indexed=false)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element maxOccurs="unbounded" minOccurs="0" name="TRAININGDATA" type="tns:PMSO_TrainingData">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@OneToMany(cascade=CascadeType.ALL, indexed=false)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

The site had another API call that included all the data I wanted. So rather than using multiple API calls for each data table, I just created another Integration that pulled in all the data called PMSO_getComplianceDataByUserId.

I then created an expression rule, PMSO_getVIRTUSComplianceFromResult that converted all the data into the new Data Type.

Here is the JSON

/*
If result data is provoided, parse into CDT's
*/
if(
  a!isNotNullOrEmpty(ri!result),
  a!localVariables(
    /* USER ID */
    local!USER_ID: ri!result.USER_ID,
    /* PMSO_DOCData */
    local!docData: if(
      a!isNotNullOrEmpty(ri!result.DOCDATA),
      a!forEach(
        ri!result.DOCDATA,
        'type!{urn:com:appian:types:PMSO}PMSO_DOCData'(
          USER_ID: local!USER_ID,
          DOCRECORDID: fv!item.DOCRECORDID,
          DOCNAME: fv!item.DOCNAME,
          DOCDATE: fv!item.DOCDATE,
          DOCNOTES: fv!item.DOCNOTES
        )
      ),
      {}
    ),
    /* PMSO_BGCData */
    local!bgcData: if(
      a!isNotNullOrEmpty(ri!result.BGCDATA),
      a!forEach(
        ri!result.BGCDATA,
        'type!{urn:com:appian:types:PMSO}PMSO_BGCData'(
          USER_ID: local!USER_ID,
          SERVICENAME: fv!item.SERVICENAME,
          TYPENAME: fv!item.TYPENAME,
          BGCDATE: fv!item.BGCDATE,
          BGCSTATUS: fv!item.BGCSTATUS,
          BGCCOMMENTS: fv!item.BGCCOMMENTS
        )
      ),
      {}
    ),
    /* PMSO_TrainingData */
    local!trainingData: if(
      a!isNotNullOrEmpty(ri!result.TRAININGDATA),
      a!forEach(
        ri!result.TRAININGDATA,
        'type!{urn:com:appian:types:PMSO}PMSO_TrainingData'(
          USER_ID: local!USER_ID,
          TRAININGID: fv!item.TRAININGID,
          TITLE: fv!item.TITLE,
          TRAININGTYPE: fv!item.TRAININGTYPE,
          APPROVED: fv!item.APPROVED,
          LOCATION: fv!item.LOCATION,
          DUE_DATE: fv!item.DUE_DATE,
          START_DATE: fv!item.START_DATE,
          END_DATE: fv!item.END_DATE
        )
      ),
      {}
    ),
    /* PMSO_VIRTUS_Compliance */
    local!compliance: 'type!{urn:com:appian:types:PMSO}PMSO_VIRTUS_Compliance'(
      USER_ID: local!USER_ID,
      FIRSTNAME: ri!result.FIRSTNAME,
      LASTNAME: ri!result.LASTNAME,
      COMPLIANCESTATUS: if(
        a!isNotNullOrEmpty(ri!result.COMPLIANCEDATA),
        ri!result.COMPLIANCEDATA.COMPLIANCESTATUS,
        null
      ),
      DOCDATA: local!docData,
      BGCDATA: local!bgcData,
      TRAININGDATA: local!trainingData
    ),
    local!compliance
  ),
  null
)

From the interface, I called the Integration and the Expression Rule using a button on the Interface.

Here is the JSON

  a!buttonWidget(
                        label: "Check Virtus Status",
                        icon: "user-check",
                        iconPosition: "START",
                        saveInto: rule!PMSO_getComplianceDataByUserId(
                          userid: local!record['recordType!PMSO Speaker.fields.virtusUserId'],
                          onSuccess: {
                            a!save(local!results, fv!result.body),
                            a!save(
                              local!VirtusCompliance,
                              rule!PMSO_getVIRTUSComplianceFromResult(local!results)
                            )
                          },
                          onError: {
                            a!save(
                              local!VirtusErrorMessage,
                              fv!error.message
                            ),
                            a!save(local!results, null),
                            a!save(local!VirtusCompliance, null)
                          }
                        ),
                        submit: false(),
                        style: "SOLID",
                        color: "ACCENT",
                        showWhen: and(
                          a!isNotNullOrEmpty(local!record['recordType!PMSO Speaker.fields.{69d56df9-d130-457f-a1cb-ab2d01c6aec9}virtusUserId']),
                          local!record['recordType!PMSO Speaker.fields.virtusCertified'] < today() - 1827
                          )
                        
                      )
                    },

This simplified my Process Model greatly!!

Srinivas Thota · Answer

Hi, while type casting the dataset or list data dictionary we can either use looping of using combination of cast function with CDT. As you type casting of list of dataset. you can use either looping or you can use " ?list " in your existing code , please refer the below cast( 'type!{urn:com:appian:types:PMSO}PMSO_BGCData?list', ri!result )