API Flow - Need to "Clean" result from standard M3 ION API

Dear Community,

I have a case where an external application need to get information from M3 using ION API calls. No issue with this, the application is doing the API calls, using OAuth2 authentication, everything is going well (we are even able to create Customer orders from this application).

But this application also needs to get information from M3. the information requested is Item Sales price for a specific customer. For this, we are using OIS345MI/LstPriceMargin API.

This API is giving the expected result, except that there are a lot of information that are not needed by the external application. From the JSON result example below, We only need to get the SEQN = 30 (Prix Net) section of the JSON file.

I'm thinking about designing an ION API flow, but I guess that I should implement a JQ Transform statement to crop the standard M3 API result and extract the only part I need... pAm I in the right way ? does anyone can give me some advise on how to do this ?

Many thanks for your support !

 {
"SEQN": "10",
"TX25": "Mnt brut-ligne",
"PBAM": "12.91",
"CUCD": "EUR",
"PCMA": "0.00",
"PBA1": "0",
"CUC1": "",
"PRID": "REN",
"ORIG": "Tarif",
"PRMO": "2",
"CLAT": "",
"TEPY": "N60",
"NTCD": "0",
"TINC": "0",
"DICO": "0",
"SIMI": "",
"SCEL": "",
"TX15": "",
"WSOP": "",
"OVHE": "",
"UNI1": "",
"RATE": "",
"UNI2": "",
"PBA2": "",
"CUC2": "",
"DIPC": "0.00"
        },
        {
"SEQN": "20",
"TX25": "Rem    :  Promotop,",
"PBAM": "0.00",
"CUCD": "EUR",
"PCMA": "0.00",
"PBA1": "0",
"CUC1": "",
"PRID": "",
"ORIG": "",
"PRMO": "",
"CLAT": "",
"TEPY": "",
"NTCD": "0",
"TINC": "0",
"DICO": "1",
"SIMI": "",
"SCEL": "",
"TX15": "",
"WSOP": "",
"OVHE": "",
"UNI1": "",
"RATE": "",
"UNI2": "",
"PBA2": "",
"CUC2": "",
"DIPC": "0.00"
        },
        {
"SEQN": "21",
"TX25": "Rem    :  Remise client F",
"PBAM": "0.00",
"CUCD": "EUR",
"PCMA": "0.00",
"PBA1": "0",
"CUC1": "",
"PRID": "",
"ORIG": "",
"PRMO": "",
"CLAT": "",
"TEPY": "",
"NTCD": "0",
"TINC": "0",
"DICO": "1",
"SIMI": "",
"SCEL": "",
"TX15": "",
"WSOP": "",
"OVHE": "",
"UNI1": "",
"RATE": "",
"UNI2": "",
"PBA2": "",
"CUC2": "",
"DIPC": "0.00"
        },
        {
"SEQN": "29",
"TX25": "Total rem/ligne",
"PBAM": "0.00",
"CUCD": "EUR",
"PCMA": "0.00",
"PBA1": "0",
"CUC1": "",
"PRID": "",
"ORIG": "",
"PRMO": "",
"CLAT": "",
"TEPY": "",
"NTCD": "0",
"TINC": "0",
"DICO": "0",
"SIMI": "",
"SCEL": "",
"TX15": "",
"WSOP": "",
"OVHE": "",
"UNI1": "",
"RATE": "",
"UNI2": "",
"PBA2": "",
"CUC2": "",
"DIPC": "0.00"
        },
        {
"SEQN": "30",
"TX25": "Montant net",
"PBAM": "12.91",
"CUCD": "EUR",
"PCMA": "0.00",
"PBA1": "0",
"CUC1": "",
"PRID": "",
"ORIG": "",
"PRMO": "",
"CLAT": "",
"TEPY": "",
"NTCD": "0",
"TINC": "0",
"DICO": "0",
"SIMI": "",
"SCEL": "",
"TX15": "",
"WSOP": "",
"OVHE": "",
"UNI1": "",
"RATE": "",
"UNI2": "",
"PBA2": "",
"CUC2": "",
"DIPC": "0.00"
        },
        {
"SEQN": "40",
"TX25": "Prix revient",
          ....
        }
}

Find more posts tagged with

ION API Gateway

Accepted answers

All comments

unknown

Hello David,

yes you are on the right path, just need to figure out the filter for JQ Transform activity so that it can filter out the M3 response and gives you exact response what third party application needed. You can use this JQ Manual to find the filter "https://jqlang.github.io/jq/manual/v1.5/"

Thank you,
Vamsi.

David TOLLET

Thanks a lot Vamsi. This will be helpful !

Another question: are we able to call ION API flows in a bulk way ? Meaning, sending one JSON file with a list of API flow calls, and getting the result for all of them in one single JSON file ?

Thanks !

Denis MARETTE

I have tried to apply your advices in my own context.
But I have still have an error that I can not explain.

My syntax works in JQ.Play. see below

But if I use the same syntax in a JQ Transform step in a ION API Flow, it seems that it is not correct ?

Any idea about the origin of this issue ?

unknown

Hi Denis,

Can you please show what input you are providing to JQ from JQTransformParameter tab? Also can you send the example raw input you are providing in JQPlay?

Thank you,
Vamsi.

Denis MARETTE

Hi Vamsi,

Please find below my screenshots :

in relationship with :

and a sample of the body is the following :

{
"Program": "OIS100MI",
"Transaction": "GetHead",
"Metadata": {
"Field": [
{
"@name": "ORNO",
"@type": "A",
"@length": 10,
"@description": "Order number"
},
{
"@name": "ORTP",
"@type": "A",
"@length": 3,
"@description": "Order type"
},
{
"@name": "ORDT",
"@type": "D",
"@length": 10,
"@description": "Order date"
},
{
"@name": "STAT",
"@type": "A",
"@length": 2,
"@description": "Order status"
},
{
"@name": "FACI",
"@type": "A",
"@length": 3,
"@description": "Facility"
},
{
"@name": "CUOR",
"@type": "A",
"@length": 20,
"@description": "Customers order number"
},
{
"@name": "AGNT",
"@type": "A",
"@length": 10,
"@description": "Agent"
},
{
"@name": "SMCD",
"@type": "A",
"@length": 10,
"@description": "Salesman"
},
{
"@name": "MODL",
"@type": "A",
"@length": 3,
"@description": "Method of delivery"
},
{
"@name": "MOD1",
"@type": "A",
"@length": 36,
"@description": "Delivery method text"
},
{
"@name": "TEDL",
"@type": "A",
"@length": 3,
"@description": "Term of delivery"
},
{
"@name": "TEL1",
"@type": "A",
"@length": 36,
"@description": "Terms text"
},
{
"@name": "YREF",
"@type": "A",
"@length": 30,
"@description": "Reference at customer"
},
{
"@name": "PROJ",
"@type": "A",
"@length": 7,
"@description": "Project"
},
{
"@name": "OTBA",
"@type": "A",
"@length": 16,
"@description": "Order total discount generating amount"
},
{
"@name": "TEPY",
"@type": "A",
"@length": 3,
"@description": "Terms of payment"
},
{
"@name": "TXPY",
"@type": "A",
"@length": 36,
"@description": "Terms of payment, text"
},
{
"@name": "PYNO",
"@type": "A",
"@length": 10,
"@description": "Payer"
},
{
"@name": "ADID",
"@type": "A",
"@length": 6,
"@description": "Delivery address id"
},
{
"@name": "CUCD",
"@type": "A",
"@length": 3,
"@description": "Currency code"
},
{
"@name": "OREF",
"@type": "A",
"@length": 30,
"@description": "Our referens"
},
{
"@name": "NTAM",
"@type": "N",
"@length": 15,
"@description": "Net order amount"
},
{
"@name": "VTCD",
"@type": "N",
"@length": 2,
"@description": "VAT code"
},
{
"@name": "ORSL",
"@type": "A",
"@length": 2,
"@description": "Lowest status - Customer"
},
{
"@name": "ORST",
"@type": "A",
"@length": 2,
"@description": "Highest status - Customer"
},
{
"@name": "ODAM",
"@type": "N",
"@length": 16,
"@description": "Order discount"
},
{
"@name": "OTDP",
"@type": "N",
"@length": 16,
"@description": "Discount percent"
},
{
"@name": "ONET",
"@type": "N",
"@length": 16,
"@description": "Order net"
},
{
"@name": "EXAM",
"@type": "N",
"@length": 16,
"@description": "Charge"
},
{
"@name": "EXTX",
"@type": "A",
"@length": 30,
"@description": "Description charge"
},
{
"@name": "EFAM",
"@type": "N",
"@length": 16,
"@description": "External charge"
},
{
"@name": "ORVA",
"@type": "N",
"@length": 16,
"@description": "Subtotal before tax"
},
{
"@name": "VTAM",
"@type": "N",
"@length": 16,
"@description": "VAT amount"
},
{
"@name": "TOAM",
"@type": "N",
"@length": 16,
"@description": "Subtotal after tax"
},
{
"@name": "ROAM",
"@type": "N",
"@length": 16,
"@description": "Rounding off"
},
{
"@name": "TOPY",
"@type": "N",
"@length": 16,
"@description": "Total"
},
{
"@name": "DLIX",
"@type": "N",
"@length": 3,
"@description": "Delivery index"
},
{
"@name": "CUNO",
"@type": "A",
"@length": 10,
"@description": "Customer number"
},
{
"@name": "BRAM",
"@type": "N",
"@length": 17,
"@description": "Order value gross"
},
{
"@name": "CUDT",
"@type": "D",
"@length": 10,
"@description": "Customers purchase order date"
},
{
"@name": "INRC",
"@type": "A",
"@length": 10,
"@description": "Invoice recipient"
},
{
"@name": "WHLO",
"@type": "A",
"@length": 3,
"@description": "Warehouse"
},
{
"@name": "DIVI",
"@type": "A",
"@length": 3,
"@description": "Division"
},
{
"@name": "POTX",
"@type": "N",
"@length": 17,
"@description": "Text indicator, posttext"
},
{
"@name": "PRT2",
"@type": "N",
"@length": 13,
"@description": "Text indicator, pretext"
},
{
"@name": "RLDT",
"@type": "D",
"@length": 10,
"@description": "Requested delivery date"
},
{
"@name": "RLHM",
"@type": "N",
"@length": 4,
"@description": "Requested delivery time"
},
{
"@name": "FDDT",
"@type": "D",
"@length": 10,
"@description": "Earliest delivery date"
},
{
"@name": "AGNO",
"@type": "A",
"@length": 7,
"@description": "Blanket agreement number"
},
{
"@name": "BAGD",
"@type": "D",
"@length": 10,
"@description": "Start date - blanket agreement"
},
{
"@name": "TECD",
"@type": "A",
"@length": 3,
"@description": "Cash discount term"
},
{
"@name": "TECX",
"@type": "A",
"@length": 36,
"@description": "Cash discount term, text"
},
{
"@name": "TEPA",
"@type": "A",
"@length": 3,
"@description": "Packaging terms"
},
{
"@name": "TEPX",
"@type": "A",
"@length": 36,
"@description": "Packaging terms, text"
},
{
"@name": "DICD",
"@type": "N",
"@length": 1,
"@description": "Discount origin"
},
{
"@name": "DISY",
"@type": "A",
"@length": 10,
"@description": "Discount model"
},
{
"@name": "LNCD",
"@type": "A",
"@length": 2,
"@description": "Language"
},
{
"@name": "RLDZ",
"@type": "D",
"@length": 10,
"@description": "Requested delivery date"
},
{
"@name": "RLHZ",
"@type": "N",
"@length": 4,
"@description": "Requested delivery time"
},
{
"@name": "TIZO",
"@type": "A",
"@length": 5,
"@description": "Time zone"
},
{
"@name": "TEL2",
"@type": "A",
"@length": 36,
"@description": "Terms text"
},
{
"@name": "DLSP",
"@type": "A",
"@length": 12,
"@description": "Delivery specification"
},
{
"@name": "DSTX",
"@type": "A",
"@length": 36,
"@description": "Description, delivery specification"
},
{
"@name": "OFNO",
"@type": "A",
"@length": 10,
"@description": "Quotation number"
},
{
"@name": "PLTB",
"@type": "A",
"@length": 10,
"@description": "Price list table"
},
{
"@name": "WCON",
"@type": "A",
"@length": 3,
"@description": "Contact method"
},
{
"@name": "ELNO",
"@type": "A",
"@length": 8,
"@description": "Project element"
},
{
"@name": "PRO2",
"@type": "A",
"@length": 7,
"@description": "Project number"
},
{
"@name": "BREC",
"@type": "A",
"@length": 10,
"@description": "Recipient agreement type 9 - bonus"
},
{
"@name": "RESP",
"@type": "A",
"@length": 10,
"@description": "Responsible"
},
{
"@name": "CHID",
"@type": "A",
"@length": 10,
"@description": "Changed by"
},
{
"@name": "PYRE",
"@type": "A",
"@length": 25,
"@description": "Request reference number"
},
{
"@name": "BKID",
"@type": "A",
"@length": 5,
"@description": "Bank account identity"
},
{
"@name": "DECU",
"@type": "A",
"@length": 10,
"@description": "Delivery customer"
},
{
"@name": "TXAP",
"@type": "N",
"@length": 1,
"@description": "Tax applicable"
},
{
"@name": "PYCD",
"@type": "A",
"@length": 3,
"@description": "Payment method - accounts receivable"
},
{
"@name": "OBLC",
"@type": "N",
"@length": 1,
"@description": "Customer order stop"
},
{
"@name": "VXCL",
"@type": "N",
"@length": 1,
"@description": "Vertex not calc"
},
{
"@name": "SPLM",
"@type": "A",
"@length": 8,
"@description": "Supply model"
},
{
"@name": "OIVR",
"@type": "A",
"@length": 15,
"@description": "Original invoice reference"
},
{
"@name": "OYEA",
"@type": "N",
"@length": 4,
"@description": "Original year"
},
{
"@name": "TAGY",
"@type": "A",
"@length": 10,
"@description": "Trade agreement model ID"
},
{
"@name": "DMDT",
"@type": "D",
"@length": 10,
"@description": "Manual due date"
}
]
},
"MIRecord": [
{
"NameValue": [
{
"Name": "ORNO",
"Value": "0011000001"
},
{
"Name": "ORTP",
"Value": "A01"
},
{
"Name": "ORDT",
"Value": "20100204"
},
{
"Name": "STAT",
"Value": "67"
},
{
"Name": "FACI",
"Value": "A01"
},
{
"Name": "CUOR",
"Value": "123"
},
{
"Name": "AGNT",
"Value": ""
},
{
"Name": "SMCD",
"Value": "Y001"
},
{
"Name": "MODL",
"Value": "003"
},
{
"Name": "MOD1",
"Value": "Road transport"
},
{
"Name": "TEDL",
"Value": "CIF"
},
{
"Name": "TEL1",
"Value": "Cost Insurance and Freight"
},
{
"Name": "YREF",
"Value": "Customer 1 Minnesota"
},
{
"Name": "PROJ",
"Value": ""
},
{
"Name": "OTBA",
"Value": "0.00"
},
{
"Name": "TEPY",
"Value": "N30"
},
{
"Name": "TXPY",
"Value": "30 days net"
},
{
"Name": "PYNO",
"Value": "Y00001"
},
{
"Name": "ADID",
"Value": ""
},
{
"Name": "CUCD",
"Value": "USD"
},
{
"Name": "OREF",
"Value": "Our reference"
},
{
"Name": "NTAM",
"Value": "199.82"
},
{
"Name": "VTCD",
"Value": "0"
},
{
"Name": "ORSL",
"Value": "66"
},
{
"Name": "ORST",
"Value": "77"
},
{
"Name": "ODAM",
"Value": ""
},
{
"Name": "OTDP",
"Value": ""
},
{
"Name": "ONET",
"Value": "199.82"
},
{
"Name": "EXAM",
"Value": "0.00"
},
{
"Name": "EXTX",
"Value": ""
},
{
"Name": "EFAM",
"Value": "0.00"
},
{
"Name": "ORVA",
"Value": "199.82"
},
{
"Name": "VTAM",
"Value": ""
},
{
"Name": "TOAM",
"Value": "199.82"
},
{
"Name": "ROAM",
"Value": ""
},
{
"Name": "TOPY",
"Value": "199.82"
},
{
"Name": "DLIX",
"Value": "0"
},
{
"Name": "CUNO",
"Value": "Y00001"
},
{
"Name": "BRAM",
"Value": "210.34"
},
{
"Name": "CUDT",
"Value": "20100204"
},
{
"Name": "INRC",
"Value": "Y00001"
},
{
"Name": "WHLO",
"Value": "001"
},
{
"Name": "DIVI",
"Value": "AAA"
},
{
"Name": "POTX",
"Value": "00000000000000000"
},
{
"Name": "PRT2",
"Value": "0000000000000"
},
{
"Name": "RLDT",
"Value": "20100205"
},
{
"Name": "RLHM",
"Value": "0000"
},
{
"Name": "FDDT",
"Value": ""
},
{
"Name": "AGNO",
"Value": ""
},
{
"Name": "BAGD",
"Value": ""
},
{
"Name": "TECD",
"Value": ""
},
{
"Name": "TECX",
"Value": ""
},
{
"Name": "TEPA",
"Value": "001"
},
{
"Name": "TEPX",
"Value": "Packed in Boxes"
},
{
"Name": "DICD",
"Value": "0"
},
{
"Name": "DISY",
"Value": "Y001"
},
{
"Name": "LNCD",
"Value": "GB"
},
{
"Name": "RLDZ",
"Value": "20100205"
},
{
"Name": "RLHZ",
"Value": "0000"
},
{
"Name": "TIZO",
"Value": "CST"
},
{
"Name": "TEL2",
"Value": "(... named port of destination)"
},
{
"Name": "DLSP",
"Value": ""
},
{
"Name": "DSTX",
"Value": ""
},
{
"Name": "OFNO",
"Value": ""
},
{
"Name": "PLTB",
"Value": "Y001"
},
{
"Name": "WCON",
"Value": ""
},
{
"Name": "ELNO",
"Value": ""
},
{
"Name": "PRO2",
"Value": ""
},
{
"Name": "BREC",
"Value": ""
},
{
"Name": "RESP",
"Value": "FSHADM4"
},
{
"Name": "CHID",
"Value": "FSHADM4"
},
{
"Name": "PYRE",
"Value": ""
},
{
"Name": "BKID",
"Value": ""
},
{
"Name": "DECU",
"Value": "Y00001"
},
{
"Name": "TXAP",
"Value": "1"
},
{
"Name": "PYCD",
"Value": "TR1"
},
{
"Name": "OBLC",
"Value": "0"
},
{
"Name": "VXCL",
"Value": "0"
},
{
"Name": "SPLM",
"Value": ""
},
{
"Name": "OIVR",
"Value": ""
},
{
"Name": "OYEA",
"Value": "0"
},
{
"Name": "TAGY",
"Value": ""
},
{
"Name": "DMDT",
"Value": ""
}
],
"RowIndex": 0
}
]
}

Denis MARETTE

Hi Vamsi,

Is what I've given you enough to diagnose the source of my problem?

Joakim Mattsson

Perhaps I'm misunderstanding, but would it not be possible for the external system to just tell which column to be returned by using the "returncols" parameter?

&returncols=SEQN%2CTX25

unknown

Hi David,

Yes, we can achieve this by configuring the list of API Flows first, let's say these are child API Flows and using those child API Flows inside another parent API Flow. We can send single JSON file to parent and that executes all children under it with one response.

Thank you.

Denis MARETTE

I am not sure that it is possible to add the "returncols" parameter here - Please see below.

Joakim Mattsson

Please make sure you are using v2 M3 APIs.

M3/m3api-rest/v2/execute/OIS345MI/LstPriceMargin

Denis MARETTE

well done - I hadn't paid attention to that detail. Thank you

Copyright © 2025 Infor. All rights reserved.