parsing salesforce report analytics api results

sidjayawardena — Wed, 11 Sep 2024 11:27:42 GMT

i have created a custom action in the SF connector to read a salesforce report

the report data is contained in an array called datacells inside an array called rows

i have put the response body view and the raw json below

what is the easiest way for me to parse this into a list so i can import to an invoices in an external system?

i tried creating a for...each loop on rows but workato errors out if i try to refer to data cells[x].label

i can for each on rows and then for each on data cells but then i can't tell where i am without a variable to track where i am which seems kludgy

the response body looks like this in workato

the raw json looks like this

{
"body": {
"attributes": {
"describeUrl": "/services/data/v42.0/analytics/reports/xxxx/describe",
"instancesUrl": "/services/data/v42.0/analytics/reports/xxxx/instances",
"reportId": "xxxx",
"reportName": "Invoices Import",
"type": "Report"
},
"allData": true,
"factMap": {
"T_T": {
"rows": [
{
"dataCells": [
{
"label": "Billing Company Inc.",
"value": "xxxx"
},
{
"label": "Customer Name Ltd.",
"value": "xxxx"
},
{
"label": "-",
"value": null
},
{
"label": "Customer Name Ltd.",
"value": "xxxx"
},
{
"escapedLabel": "Customer Number",
"label": "Customer Number",
"value": "Customer Number"
},

...

"dataCells": [
{
"label": "Billing Company 2 Inc.",
"value": "xxxx"
},
{
"label": "Customer Name 2 Ltd.",
"value": "xxxx"
},
{
"label": "-",
"value": null
},
{
"label": "Customer Name 2 Ltd.",
"value": "xxxx"
},
{
"escapedLabel": "Customer Number 2",
"label": "Customer Number 2",
"value": "Customer Number 2"
},

...

Re: parsing salesforce report analytics api results

beatrice-talens — Wed, 16 Oct 2024 05:16:41 GMT

Hello, can you provide more details on your use case? Do you want to loop through a nested array? You may check this sample recipe if this is what you require. Here's the link.

Re: parsing salesforce report analytics api results

shivakumara — Wed, 16 Oct 2024 13:42:20 GMT

Hi @sidjayawardena ,
Here is my thoughts on the requirement.
1. Use Parse Json in sample document provide with above data, and for the actual document "map the actual data coming from source"
2. Use the create list initialize or you can map directly to incoming parsed value from step_1. Please refer below images for the reference.

Thanks and Regards,
Shivakumara Avadhani

topic parsing salesforce report analytics api results in Workato Pros Discussion Board

parsing salesforce report analytics api results

Re: parsing salesforce report analytics api results

Re: parsing salesforce report analytics api results