Question

Getting Multiple Records from Web API

Forum|Forum|2 years ago
January 4, 2024
12 replies
0 views

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.


  
    
      
    
    
      
      
        
          @Column(length=255)
        
      
      
        
          @Column(length=255)
        
      
      
        
          @Column(length=255)
        
      
      
        
          @Column(length=255)
        
      
      
        
          @Column(length=255)

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.

web integrations

mathieud0001

From what I can tell, I think you would need to loop through your ri!result.

davidj137213

Could you please add the first structure when there is only one record please?

abhayd3663

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

chrisg0006Author

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


  
  
  
  
    
      
    
    
      
      
        
          @Column(length=255)
        
      
      
        
          @Column(length=255)
        
      
      
        
          @Column(length=255)
        
      
      
        
          @OneToMany(cascade=CascadeType.ALL, indexed=false)
        
      
      
        
          @OneToMany(cascade=CascadeType.ALL, indexed=false)
        
      
      
        
          @OneToMany(cascade=CascadeType.ALL, indexed=false)

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

abhayd3663

Glad to hear that you are able to solve it.

srinivast0001

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
)

harshitb6843

Or

cast(
typeof({'type!{urn:com:appian:types:PMSO}PMSO_BGCData'()}),
ri!result
)

G

gopuh0001

Same issue i had where i am getting multiple data which need to be type casted into recordtype .

Below is the json code coming from Web API

"History":[
{
"Sample1":"1/02/2024",
"Sample2":"123",
"Sample3":"456"
},
{
""Sample1:"2/03/2024",
"Sample2":"789",
"Sample3":"101"
}
]

I need to covert this to multiple data of recordtype.

How can i acheive this with square bracket?

Can you please help me on this?

Sign up

Login with SSO

Login to the community

Login with SSO