Integrationpublic

Integration

Get Involved. Join the Conversation.

This is a public Forum  public

Topic

    Fernando Paes
    Issue with Array in JSONAnswered
    Topic posted September 16, 2019 by Fernando PaesBronze Medal: 1,250+ Points, tagged Adapters, Cloud, Integration, Mapping, REST 
    67 Views, 7 Comments
    Title:
    Issue with Array in JSON
    Summary:
    Arrays not being handled correctly with in Json request
    Content:

    Hello,

    I hope you can help me with the following. I have an integration where I need to push Opportunities to JDEdwars via standard REST adapter.

    The Json request have some section that are arrays, but is creating some issues: formActions, gridRowInsertEvents.

    {
        "token": "***",
        "formServiceAction": "U",
        "deviceName": "CRM",
        "formName": "***",
        "formActions": [{
                "gridAction": {
                    "gridID": "1",
                    "gridRowInsertEvents": [{
                        "gridColumnEvents": [{
                                "command": "SetGridCellValue",
                                "value": "175516",
                                "columnID": "14",
                                "columnDescription": "CRM Row Id"
                            },
    .....
                        ]
                    }
                ]
                }
            },
            {
                "command": "DoAction",
                "controlID": "12"
            }
        ]
    }
     

     The issue comes when OIC builds the request and send it over to JDE. It ignores the [] for the formActions section and gridRowInsertEvents sections, and also the {} that enclose command and controlID are also ignored. This is how OIC is building that request:

    {
            "nsmpr6:token": "***",
            "nsmpr6:formServiceAction": "U",
            "nsmpr6:deviceName": "CRM",
            "nsmpr6:formName": "***",
            "nsmpr6:formActions": {
              "nsmpr6:gridAction": {
                "nsmpr6:gridID": "1",
                "nsmpr6:gridRowInsertEvents": {
                  "nsmpr6:gridColumnEvents": {
                    "nsmpr6:command": "SetGridCellValue",
                    "nsmpr6:value": "***",
                    "nsmpr6:columnID": "14",
                    "nsmpr6:columnDescription": "CRM Row Id"
                  }
                }
              },
              "nsmpr6:command": "DoAction",
              "nsmpr6:controlID": "12"
            }
      }

    This payload is not accepted by JDE, leading to the record not being created.

    Does anyone know what could I do here?

    I have tried to add more repetitions within the arrays to force the [] to show up, which works, but the command and controlID section is still and issue, and is key as it is where we tell JDE to insert the record.

     

    Thanks in advance.

     

    Version:
    Version: 19.3.2.0.0 (190819.0200.30840)

    Best Comment

    Fernando Paes

    Hello,

    thank you all for your help. I managed to find a solution by adding another formActions section like this:

    {
            "nsmpr6:token": "***",
            "nsmpr6:formServiceAction": "U",
            "nsmpr6:deviceName": "CRM",
            "nsmpr6:formName": "***",
            "nsmpr6:formActions": {
              "nsmpr6:gridAction": {
                "nsmpr6:gridID": "1",
                "nsmpr6:gridRowInsertEvents": {
                  "nsmpr6:gridColumnEvents": {
                    "nsmpr6:command": "SetGridCellValue",
                    "nsmpr6:value": "***",
                    "nsmpr6:columnID": "14",
                    "nsmpr6:columnDescription": "CRM Row Id"
                  }
                }
              },
              "nsmpr6:command": "DoAction",
              "nsmpr6:controlID": "12"
            }
            "nsmpr6:formActions": {
              "nsmpr6:gridAction": {
                "nsmpr6:gridID": "",
                "nsmpr6:gridRowInsertEvents": {
                  "nsmpr6:gridColumnEvents": {
                    "nsmpr6:command": ,
                    "nsmpr6:value": ,
                    "nsmpr6:columnID": ,
                    "nsmpr6:columnDescription": 
                  }
                }
              },
              "nsmpr6:command": "DoAction",
              "nsmpr6:controlID": "12"
            }
      }

     

    This way, JDE is able to read the "nsmpr6:command": "DoAction", and "nsmpr6:controlID": "12" section and therefore insert the record.

     

    Kind Regards

     

     

    Comment

     

    • Kanchankumar Khedkar
      Please check the schema (nxsd) generated for for xml to Jason conversion. I guess it still has maxOccurs=1. Hence it is is not putting it into array format. What can help : while configuration of adapter when you give sample json request - provide sample which has multiple form elements ( basically array with at least two values wherever you have array and see if it helps. Thank you
    • Hemanth Lakkaraju

      The issue comes when OIC builds the request and send it over to JDE. It ignores the [] for the formActions section and gridRowInsertEvents sections, and also the {} that enclose command and controlID are also ignored. This is how OIC is building that request:

      How did you capture the request? Per my understanding, OIC doesn't add prefixes to the JSON (nsmpr6: in the sample you posted). If the request goes exactly, then the problem might be with the prefixed-elements as well.

      • Fernando Paes

        Hi Hemanth,

        I've captured the request in the activity stream (as XML) and then transformed to JSON format where I could identify the gap. I don't think that is an issue as other operation for the same service are working fine..

        Thanks.

        • Hemanth Lakkaraju

          How did you transform? Which tool you have used? It could be a problem with the tool/transformation logic as well? What's you sampling for this request look like in REST Adapter? Are you able to see a repeating element for formActions, gridRowEvents, gridColumnEvents in mapping? Can you share the screenshot of mapping/configuration/the schema generated?

          You can use stage action to write same json sample to a file, read opaquely, decode and see how the actual json looks like in OIC.

          • Fernando Paes

            Hi Hemanth,

            I've used this tool to transform the xml I got from the activity stream to json: http://www.utilities-online.info/xmltojson/#.XYCUcChKiUk.

            In JDE we could confirm what the xml transformation suggested: that OIC is not closing properly the arrays, but also (and this is probably the key), the section with the command and controlID:

                "formActions": [
                    {
                        "gridAction": {
            ...
                        }
                    },
                    {
                        "command": "DoAction",
                        "controlID": "12"
                    }
                ]
            }

            OIC is not considering command and controlID as 1 element of the form actions array, but two elements (removing the {}):

                   "nsmpr6:formActions": {
                      "nsmpr6:gridAction": {
                        "nsmpr6:gridID": "1",
                        "nsmpr6:gridRowInsertEvents": {
                          "nsmpr6:gridColumnEvents": {
                            "nsmpr6:command": "SetGridCellValue",
                            "nsmpr6:value": "***",
                            "nsmpr6:columnID": "14",
                            "nsmpr6:columnDescription": "CRM Row Id"
                          }
                        }
                      },
             
                      "nsmpr6:command": "DoAction",
                      "nsmpr6:controlID": "12"
             
                    }
              }

            This is the sample Json, as JDEd is expecting: 

            {
                "token": "***",
                "formServiceAction": "U",
                "deviceName": "soapUI",
                "formName": "***",
                "formActions": [
                    {
                        "gridAction": {
                            "gridID": "1",
                            "gridRowInsertEvents": [
                                {
                                    "gridColumnEvents": [
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "***",
                                            "columnID": "14",
                                            "columnDescription": "CRM Row Id"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "XXXX",
                                            "columnID": "15",
                                            "columnDescription": "Spar ID"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "S",
                                            "columnID": "17",
                                            "columnDescription": "Opportunity Status"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "AIIA-ER3QE0",
                                            "columnID": "18",
                                            "columnDescription": "Opportunity ID"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "***",
                                            "columnID": "19",
                                            "columnDescription": "Alpha Name"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "***",
                                            "columnID": "20",
                                            "columnDescription": "Project Title"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "***",
                                            "columnID": "21",
                                            "columnDescription": "Address Line 1"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "***",
                                            "columnID": "22",
                                            "columnDescription": "Address Line 2"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "",
                                            "columnID": "23",
                                            "columnDescription": "Address Line 3"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "XXXX",
                                            "columnID": "25",
                                            "columnDescription": "City"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "***",
                                            "columnID": "26",
                                            "columnDescription": "County"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "XXXX",
                                            "columnID": "28",
                                            "columnDescription": "Postcode"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "",
                                            "columnID": "24",
                                            "columnDescription": "Contractor Name"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "0",
                                            "columnID": "39",
                                            "columnDescription": "CRM Status"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "2313804",
                                            "columnID": "45",
                                            "columnDescription": "Specifier"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "AIIA-96HGKT",
                                            "columnID": "46",
                                            "columnDescription": "Specifier Account ID"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "509809",
                                            "columnID": "47",
                                            "columnDescription": "Business Source"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "AIIA-5L5HT5",
                                            "columnID": "48",
                                            "columnDescription": "Business Source Account ID"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "2087625",
                                            "columnID": "49",
                                            "columnDescription": "Contractor Number"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "AIIA-5L7VZV",
                                            "columnID": "50",
                                            "columnDescription": "Contractor Account ID"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "***",
                                            "columnID": "76",
                                            "columnDescription": "Sales Stage Name"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "XXXX",
                                            "columnID": "82",
                                            "columnDescription": "Market Sector"
                                        },
                                        {
                                            "command": "SetGridCellValue",
                                            "value": "XXXX",
                                            "columnID": "92",
                                            "columnDescription": "Specification Type"
                                        }
                                    ]
                                }
                            ]
                        }
                    },
                    {
                        "command": "DoAction",
                        "controlID": "12"
                    }
             
                ]
            }

            Re: the repeating elements, I can create repeating elements for formActions and gridRowInsertEvents by right clicking and "Repeat Element" (check attachment). I just really need 1 formActions and 1 gridRowInsertEvents, but I have add extra elements (as shown in the screenshot) to test if that could fix the issue.

            Thanks in advance

    • Fernando Paes

      Hello,

      thank you all for your help. I managed to find a solution by adding another formActions section like this:

      {
              "nsmpr6:token": "***",
              "nsmpr6:formServiceAction": "U",
              "nsmpr6:deviceName": "CRM",
              "nsmpr6:formName": "***",
              "nsmpr6:formActions": {
                "nsmpr6:gridAction": {
                  "nsmpr6:gridID": "1",
                  "nsmpr6:gridRowInsertEvents": {
                    "nsmpr6:gridColumnEvents": {
                      "nsmpr6:command": "SetGridCellValue",
                      "nsmpr6:value": "***",
                      "nsmpr6:columnID": "14",
                      "nsmpr6:columnDescription": "CRM Row Id"
                    }
                  }
                },
                "nsmpr6:command": "DoAction",
                "nsmpr6:controlID": "12"
              }
              "nsmpr6:formActions": {
                "nsmpr6:gridAction": {
                  "nsmpr6:gridID": "",
                  "nsmpr6:gridRowInsertEvents": {
                    "nsmpr6:gridColumnEvents": {
                      "nsmpr6:command": ,
                      "nsmpr6:value": ,
                      "nsmpr6:columnID": ,
                      "nsmpr6:columnDescription": 
                    }
                  }
                },
                "nsmpr6:command": "DoAction",
                "nsmpr6:controlID": "12"
              }
        }

       

      This way, JDE is able to read the "nsmpr6:command": "DoAction", and "nsmpr6:controlID": "12" section and therefore insert the record.

       

      Kind Regards

       

       

     

    community forum