Data Action for read Data Table and return Array

Data Actions
1

Hi All,

i read a lot of topic by i didn't' found the solution for my case.
My target is read a data table through a data action in order to receive ad array of results in base of the filter applied.
The details:
Data Table:
structure
key,Indirizzo,Orario,Telefono,Comune,Provincia

i create a data action with this configuration
input contract:

{
  "title": "Comune",
  "type": "object",
  "properties": {
    "Comune": {
      "type": "string"
    }
  },
  "additionalProperties": true
}

Output contract

{
  "title": "Sportelli",
  "type": "object",
  "properties": {
    "Sportello": {
      "type": "object",
      "properties": {
        "Indirizzo": {
          "type": "string"
        },
        "Orario": {
          "type": "string"
        },
        "Telefono": {
          "type": "integer"
        },
        "Comune": {
          "type": "string"
        },
        "Provincia": {
          "type": "string"
        }
      },
      "additionalProperties": true
    }
  },
  "additionalProperties": true
}

Request URL Template
/api/v2/flows/datatables/#########################/rows?showbrief=false&Comune=${input.Comune}

Configuration : Request:

{
  "requestUrlTemplate": "/api/v2/flows/datatables/#################/rows?showbrief=false&Comune=${input.Comune}",
  "requestType": "GET",
  "headers": {},
  "requestTemplate": "${input.rawRequest}"
}

Configuration : Response:

{
  "translationMap": {
    "Sportello": "$.entities[?(@.Comune == 'Firenze')]"
  },
  "translationMapDefaults": {},
  "successTemplate": "{ \"Sportello\": ${successTemplateUtils.firstFromArray(${Sportello},\"\")} }"
}

If i test i receive this output ( all data are fake)

{
  "Sportello.Telefono": 55062#####,
  "Sportello.key": "01",
  "Sportello.Indirizzo": "Via Dante Alighieri uno",
  "Sportello.Provincia": "Firenze",
  "Sportello.Comune": "Firenze",
  "Sportello.Orario": "Lunedì Venerdì dalle nove alle tredici e dalle quattordici alle diciotto"
}

this return only the first record of data table. the correct result should be 2 records. these will be used into inbound flow.
Moreover the parameter Comune=${input.Comune} added into URL doesn't work fine in fact into json i put Firenze directly into code "Sportello": "$.entities[?(@.Comune == 'Firenze')]"

Someone can help me?

Thanks in advance

2

Your success template includes
firstFromArray
which would only grab the first entry in the array.

What happens if you try to use
Comune=${input.Comune}

--Jason

3

Hi Jason,

i know that the success template includes the firstfromarray because without i am not able to work fine the code. this is why i ask for help.

**at happens if you try to use Comune=${input.Comune}

i receive two different errors:

1) this errore because i include the parameters into URL and i didn't fill it

{
"message": "Substitution values invalid in action config. Reference ${input.Comune} evaluated to null when attempting to render at UrlTemplate[line 1, column 84] A common reason for this error is needing to prepend the variable with 'input.' or 'credentials.'",
"code": "invalid.substitution",
"status": 400,
"messageParams": {},
"contextId": "68e2bc69-d4c5-4cd8-903f-9a75391315bb",
"details": [
{
"errorCode": "ACTION.PROCESSING"
}
],
"errors": []
}

2) if i fill the parameters and set it into translationMap in this way:

{
"translationMap": {
"Sportello": "$.entities[?(@.Comune == '${input.Comune}')]"
},
"translationMapDefaults": {},
"successTemplate": "{ "Sportello": ${successTemplateUtils.firstFromArray(${Sportello},"")} }"
}

i receive this error:

{
"message": "Transform failed to process result using 'successTemplate' template due to error:'Unexpected character ('}' (code 125)): expected a valid value (JSON String, Number, Array, Object or token 'null', 'true' or 'false')\n at [Source: REDACTED (StreamReadFeature.INCLUDE_SOURCE_IN_LOCATION disabled); line: 1, column: 17]'\n Template:'{ "Sportello": ${successTemplateUtils.firstFromArray(${Sportello},"")} }'.",
"code": "bad.request",
"status": 400,
"messageParams": {},
"contextId": "552ba707-a9c7-4071-a222-ce3558a75f4a",
"details": [
{
"errorCode": "ACTION.PROCESSING"
}
],
"errors": []
}

Thanks in advance

4

No One can help me to understand what is wrong?

5

Hi ALL,

no solution for this issue?

Regards

6

Hi @Dune_Dune, I think you may want to change the output contract to return an Array rather than a string. This will then return multiple elements in each of your output elements.
This is based on what I have understood from your question.

7

Hi Vineet,

How would you change the code?
Can helo me?

Regards

8

can you first show how your data looks and how you want to get the data back, with an example. It seems you are nesting your data and that may be the issue.

9

Hi,
My data are in this:

Data Table :
structure

key,Indirizzo,Orario,Telefono,Comune,Provincia
FI00,Via Anfiteatro 5,Lunedi' e Venerdi' otto diciotto,5578XXX80,Firenze,Firenze
FI01,Via Sandro Pertini 4,lunedi e venerdì otto diciotto ,5578XXX54,Firenze,Firenze
FI02,Via Giacomo Matteotti ,Luned' e venerdì otto diciotto,55XXX6655,Firenze,Firenze
PR01,Via Dante Alighieri,Lunedì e Venerdì otto diciotto,5578XXX9,Prato,Prato

the result that i want is:
If i fill the input "Sportello": "$.entities[?(@.Comune == '${input.Comune}')]"
i want receive an array with the record FI00,FI01,FI02 in alternative if i fill the input Key
"Sportello": "$.entities[?( @.key == '${input.key}')]" i want receive a specific record.
Below the example if i hardcode the filter:
"Sportello": "$.entities[?( @.key == 'FI00')]" the result is:

image
image1492×464 13.3 KB

{
"Sportello.Telefono": 5578XXXX0,
"Sportello.key": "FI00",
"Sportello.Indirizzo": "Via Anfiteatro 5",
"Sportello.Provincia": "Firenze",
"Sportello.Comune": "Firenze",
"Sportello.Orario": "Lunedi' e Venerdi' otto diciotto"
}

Regards

10

Hi @Dune_Dune ,

I am not sure if you can setup the data-actions the way you have described.
If you have to retrieve key based data, you don't need to create the data-action for it. It can be done with the "Data Table Lookup" action in Architect.
If you want to retrieve data based on a non-key value, like "Comune" in your example, you can't do that either as data-tables are not searchable the way you want them to be. For this case, you need to retrieve all data from the specific data-table in architect, using array variables as output from the data-action, and then use the FIND built-in functions in architect to search for specific "Comune" data.
Hope this helps.

11

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.