Getting Multiple Records from Web API

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

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

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!!

Glad to hear that you are able to solve it.