E9.2 nested arrays in iterate over option

[arrozpegao]

hi guys,
how to prepare input data to a Form request in order to pass header values and grid values?

i have a custom table : id, name, address, line number, itm , quantity

where i have same id for diferent line numbers. i have the data for the grid (itm and quantity). Name and address are the header values.

Then, in order to get a json structure like this:
{ [{id, name, address, [{line number1, itm1, quantity1},{line number2, itm2, quantity2}]}]}
i used a data request and a custom script

i have multiple id and inside multiples line numbers (multilevel array)

how can i pass this string/object variable to a Form Request P4120 to create a sales order BY ID? i can´t iterate multiples id with multiples lines inside.


Thanks in advance

 

[alfredorz]

Hi @arrozpegao, cool name haha

For the moment you have done perfect, a data requested for your custom table and custom script to adapt output json.

This output json you have to return in hour custom script as Data Set Variable Name, and members. For example:


and you can create a output json with multiples "order" with their correspondient id, name, address, lines, etc. and in your orchestrator, on the P4210 form request input iterate over Data Set Variable Name ("orders" in this case) and set variables. For the lines you assign main variable of your tree ("lines" in this case) but the trick is you have rename internal attributes of lines "line number", "itm", "quantity", etc. with the same name than the P4210 form request grid attributes, for that internally orchestrator should be able to map. The concept is difficult to see, but should works. I haven't a example here now, but I'll try to find a example (or maybe some colleague here).

Regards.

 

[arrozpegao]

thanks a lot for the answer,
just a little step to finish.
how can i map in my script that ORDER data set variable name?
case i can´t get any value in orders array variable, even when my final json is
{orders:[{data,[lines]},{data,[lines]}]}

 

[alfredorz]

Here in example 1 there is a example: https://support.oracle.com/knowledge/JD Edwards EnterpriseOne/2667935_1.html

Is in groovy. but if in your case if is in jruby should be simmilar (else chatgpt is our friend ).

In your case you returnMap in "orders" too (it's ok return in Output_Array if you want check), and map the "attributes" of your json in variable name and output atrributes in member name (or the same name if you want).

 

[DaveWagoner]

Here's what I'd do (cheat method, unlike @alfredorz who is doing things the right way)

1. Create the destination form request, populating whatever you need to. Test it to make sure it hits all the fields you need. Make sure similarly named field variables on different levels are named differently if they exist (ie, parentlineno vs childlineno). Once tested, remove the default values from the form request (this is a step that CONTINUES to bite me in the butt if I forget)
2. In the menu of the form request, choose "create orchestration"
3. Name the orchestration according to your naming convention. Don't touch ANYTHING except for the "view example input" button. Save the input out to a text file.
4. (Very important, don't get fired) Cleanse your source and destination text files of sensitive data while keeping the structures and integrity intact. Change names, ID numbers, company secrets, etc.
5. Open chatGpt, tell it you have a source json, a destination json, and a template in <scripting language> and you would like to get from source to destination. In the subsequent steps it will prompt you to paste in each.
6. Paste in each
7. Have a macchiato as the magic happens
8. Read the script, which has annotations describing what each step does. Grow comfy with what it's doing, that it's not doing anything wrong or dangerous
9. Copy the script from chatgpt and paste it into your new scripting component within a brand new orchestration (not the one from step #2)
10. You will probably have to "add plumbing" so that the script accepts as input your source JSON
11. Test just the script with source json as input. If any errors, paste them into the same chatGPT session and it will likely figure it out within 1 or 2 tries.
12. Once it's generating destination json to match your source, you can add in the form request orchestration, and map in the output of the script component to the "jde__object" field in orchestration transformation.

 

[alfredorz]

Here's what I'd do (cheat method, unlike @alfredorz who is doing things the right way)

1. Create the destination form request, populating whatever you need to. Test it to make sure it hits all the fields you need. Make sure similarly named field variables on different levels are named differently if they exist (ie, parentlineno vs childlineno). Once tested, remove the default values from the form request (this is a step that CONTINUES to bite me in the butt if I forget)
2. In the menu of the form request, choose "create orchestration"
3. Name the orchestration according to your naming convention. Don't touch ANYTHING except for the "view example input" button. Save the input out to a text file.
4. (Very important, don't get fired) Cleanse your source and destination text files of sensitive data while keeping the structures and integrity intact. Change names, ID numbers, company secrets, etc.
5. Open chatGpt, tell it you have a source json, a destination json, and a template in <scripting language> and you would like to get from source to destination. In the subsequent steps it will prompt you to paste in each.
6. Paste in each
7. Have a macchiato as the magic happens
8. Read the script, which has annotations describing what each step does. Grow comfy with what it's doing, that it's not doing anything wrong or dangerous
9. Copy the script from chatgpt and paste it into your new scripting component within a brand new orchestration (not the one from step #2)
10. You will probably have to "add plumbing" so that the script accepts as input your source JSON
11. Test just the script with source json as input. If any errors, paste them into the same chatGPT session and it will likely figure it out within 1 or 2 tries.
12. Once it's generating destination json to match your source, you can add in the form request orchestration, and map in the output of the script component to the "jde__object" field in orchestration transformation.View attachment 20359

wow! good trick! I'll try

 

[DaveWagoner]

wow! good trick! I'll try

DO NOT FORGET STEP 7

 

[alfredorz]

DO NOT FORGET STEP 7

It's lol

 

[arrozpegao]

Here's what I'd do (cheat method, unlike @alfredorz who is doing things the right way)

1. Create the destination form request, populating whatever you need to. Test it to make sure it hits all the fields you need. Make sure similarly named field variables on different levels are named differently if they exist (ie, parentlineno vs childlineno). Once tested, remove the default values from the form request (this is a step that CONTINUES to bite me in the butt if I forget)
2. In the menu of the form request, choose "create orchestration"
3. Name the orchestration according to your naming convention. Don't touch ANYTHING except for the "view example input" button. Save the input out to a text file.
4. (Very important, don't get fired) Cleanse your source and destination text files of sensitive data while keeping the structures and integrity intact. Change names, ID numbers, company secrets, etc.
5. Open chatGpt, tell it you have a source json, a destination json, and a template in <scripting language> and you would like to get from source to destination. In the subsequent steps it will prompt you to paste in each.
6. Paste in each
7. Have a macchiato as the magic happens
8. Read the script, which has annotations describing what each step does. Grow comfy with what it's doing, that it's not doing anything wrong or dangerous
9. Copy the script from chatgpt and paste it into your new scripting component within a brand new orchestration (not the one from step #2)
10. You will probably have to "add plumbing" so that the script accepts as input your source JSON
11. Test just the script with source json as input. If any errors, paste them into the same chatGPT session and it will likely figure it out within 1 or 2 tries.
12. Once it's generating destination json to match your source, you can add in the form request orchestration, and map in the output of the script component to the "jde__object" field in orchestration transformation.View attachment 20359

but this only works if your output JSON is with one set of data, i mean , if you get all rows from F4211, you group by doco+dcto+kcoo , you'll get an array of set of data, each element has the header values and a grid data. How can i play with this json?

i know that i can get a select distinct doco+dcto+kcoo and iterate result over a select in order to get one set of data, and send it to form request.
but i'm curious how to handle a full array as output json from custom jruby script where i get

[ header values1, { grid values1}, header values2, {grid values2}]

i mean, can i get an array with an array inside from jruby output?


Thanks

 

[arrozpegao]

in fact, when i try to map custom script output to data set variable, i get this error:

com.fasterxml.jackson.databind.node.ObjectNode cannot be cast to com.fasterxml.jackson.databind.node.ArrayNode

 

[DaveWagoner]

I'm suggesting to ditch out on iterate functionality, and deal in complete jsons of whatever complexity you need. My method of mapping an entire multilevel json to an orchestration as input object removes the need for iterate altogether

 

[DaveWagoner]

on the script output you don't need to map a data set variable name. you just need a variable name for the output string which contains your entire built json for the orch call.

 

[DaveWagoner]

(last spam I promise) - removing iterate speeds up your orchestration by quite some margin. for header-multi-detail inserts/updates you should see nice performance increase over using iterate

 

[arrozpegao]

(no worries about spam)
thanks for clarification, but i dont see how to create multiple orders in P4210 with a complex json as input (in fact, is an object input in a sub-orchestration)
I mean ,is iterate the only way to create several orders? then even if i got a object (complex json) as input in a orchestration, i need to "transform" it to array in order to get the option of use it in iterate "mode". am i right? then how can i do that ? because i'm "fighting" with custom resquest output and i want to kill myself

Thanks in advance

 

[alfredorz]

(last spam I promise) - removing iterate speeds up your orchestration by quite some margin. for header-multi-detail inserts/updates you should see nice performance increase over using iterate

Your solution is great and cheaper/faster, I'll try in some project that I'll have the opportunity or when I'll have free time to test (with a macchiato of course).
But as said @arrozpegao ("socarrat" in spanish XD -I'm spanish- ), he need iterate to create multiple orders, so somehow he need iterate over form request orchestrator, because this orchestrator will create an unique order, correct? (of it's possible iterate over input json?)

kind regards.

 

[DaveWagoner]

Hmm I'm sorry I misread the original need! I will think about how I might do this.

 

[Kevin Long]

If I understand the requirement, you need to create an array of sales order objects. What your script needs to produce is an array of objects something like this:

{
"salesOrders": [
{
"salesOrder": {
"name": "Some Name",
"address": "Some Address",
"lines": [
{
"item": "1234",
"quantity": 10
},
{
"item": "1235",
"quantity": 5
}
]
}
}
]
}

You would build this using script by passing in and parsing the output from the data request. The output of the custom request would be an array called salesOrders, with one item called salesOrder. Iterate over the salesOrders array and pass in the salesOrder into jde__object as @DaveWagoner suggested.

 

[arrozpegao]

Thanks @Kevin Long That's the approach i take, following Dave Wagoner trick ->> get Form request json input, get Data request output json and ask chatgpt how to join both json files with jruby script.
i had some problems trying to map the output, case jruby said that an object cannot be converted to array, but finally i fix it.