Using variables in script to create Json - Newbie help

Redshift
Newbie
Posts: 13
Joined: Thu Dec 21, 2017 2:40 am

Using variables in script to create Json - Newbie help

Post by Redshift »

Pretty new to the scripting module but we are trying to pull variables from an XML document then create a json inside a script (it needs to authenticate before pushing into the API)

So dropping the XML into a folder and using XML pickup with Metadata as asset.

My understanding is that this should save this data to the job which I should then be able to pull into my script? or do I require another step to save the data under a simple field before using the variables in my script?

I want to change out the fixed values I have for variables in the below section of script:

//construct the json
var json = '{"Orders": [ {"UniqueSubmitOrderID": "string","Status": { "Value": 0, "EnumType": "confirmed" },"InvoiceCustomer": { "Code": ' + Address addressID + ',"Contact": ' + Name + ', "ContactEmail": "invoice@email.com", }, "DeliveryCustomer": {"Name": "test customer","Address": { "AddressLines": ["123 Main St","Address Line 2"],"City": "Grenada North", "Region": "Wellington", "Country": "New Zealand","Postcode": "1234","Telephone": "04 472 3042",} "Contact": "Cades test","ContactEmail": "invoice@email.com",},"RequiredDate": "2017-12-28T22:13:03.613Z","OrderDate": "2017-12-28T22:13:03.613Z","Ref1": "PO5555","Items": [{"StockItemID": 12018,"Quantity": 1,}]}]}';

//store the json as a file that can be POSTed to the API
var filename=s.createPathWithName(job.getNameProper()+"_uploadattachment.json");
var textFile = new File(filename);
textFile.open(File.WriteOnly);
textFile.write(json);
textFile.close();

Anyone able to help point me in the right direction?
jan_suhr
Advanced member
Posts: 586
Joined: Fri Nov 04, 2011 1:12 pm
Location: Nyköping, Sweden

Re: Using variables in script to create Json - Newbie help

Post by jan_suhr »

I did an app for this, it will be published tomorrow and it is for FREE!!

You can write your own JSON and add Switch variables to it.

It is called "Make JSON"
Jan Suhr
Color Consult AB
Sweden
=============
Check out my apps
Redshift
Newbie
Posts: 13
Joined: Thu Dec 21, 2017 2:40 am

Re: Using variables in script to create Json - Newbie help

Post by Redshift »

jan_suhr wrote:I did an app for this, it will be published tomorrow and it is for FREE!!

You can write your own JSON and add Switch variables to it.

It is called "Make JSON"
Perfect,

I'll check out the app and give it a go.

Thanks,
Cade
jan_suhr
Advanced member
Posts: 586
Joined: Fri Nov 04, 2011 1:12 pm
Location: Nyköping, Sweden

Re: Using variables in script to create Json - Newbie help

Post by jan_suhr »

The App is up now, it got delayed due to the holidays

Currently there is an error in the Compatibility description of the app. Version 1 of the App works with Switch 13 update 1 but in that case there is no validation of the JSON-code. From version 2017 update 2 you have validation of JSON and version 2 of the app supports that.


https://www.enfocus.com/en/appstore/product/make-json
Jan Suhr
Color Consult AB
Sweden
=============
Check out my apps
Redshift
Newbie
Posts: 13
Joined: Thu Dec 21, 2017 2:40 am

Re: Using variables in script to create Json - Newbie help

Post by Redshift »

great, I'll test it out and see how I get on.

Thanks,
Cade
Redshift
Newbie
Posts: 13
Joined: Thu Dec 21, 2017 2:40 am

Re: Using variables in script to create Json - Newbie help

Post by Redshift »

jan_suhr wrote:The App is up now, it got delayed due to the holidays

Currently there is an error in the Compatibility description of the app. Version 1 of the App works with Switch 13 update 1 but in that case there is no validation of the JSON-code. From version 2017 update 2 you have validation of JSON and version 2 of the app supports that.


https://www.enfocus.com/en/appstore/product/make-json
Hi Jan,

Thanks for the app, it seems easy enough to use although keeps giving me an error saying "spaces are not allowed in variable definition in 'JSON code',

This is the code I am using:
{
"Orders": [{
"UniqueSubmitOrderID": "string",
"Status": {
"Value": 0,
"EnumType": "string"
},
"InvoiceCustomer": {
"Code": "TESTCUST",
"Contact": "API Invoice Contact",
"ContactEmail": "nz.api@printing.com"
},
"DeliveryCustomer": {
"Name": "[Metadata.Text:Path="/cXML/Request/OrderRequest/OrderRequestHeader/ShipTo/Address/Name",Dataset="Xml",Model="XML"]",
"Address": {
"AddressLines": ["[Metadata.Text:Path="/cXML/Request/OrderRequest/OrderRequestHeader/ShipTo/Address/PostalAddress/Street[1]",Dataset="Xml",Model="XML"]",
"[Metadata.Text:Path="/cXML/Request/OrderRequest/OrderRequestHeader/ShipTo/Address/PostalAddress/Street[2]",Dataset="Xml",Model="XML"]"],
"City": "[Metadata.Text:Path="/cXML/Request/OrderRequest/OrderRequestHeader/ShipTo/Address/PostalAddress/Street[3]",Dataset="Xml",Model="XML"]",
"Region": "[Metadata.Text:Path="/cXML/Request/OrderRequest/OrderRequestHeader/ShipTo/Address/PostalAddress/City",Dataset="Xml",Model="XML"]",
"Country": "[Metadata.Text:Path="/cXML/Request/OrderRequest/OrderRequestHeader/ShipTo/Address/PostalAddress/Country",Dataset="Xml",Model="XML"]",
"Postcode": "[Metadata.Text:Path="/cXML/Request/OrderRequest/OrderRequestHeader/ShipTo/Address/PostalAddress/PostalCode",Dataset="Xml",Model="XML"]",
"Telephone": "00 000 0000",
"Fax": "00 000 0000"
},
"Contact": "API Delivery Contact",
"ContactEmail": "nz.api@printing.com"
},
"RequiredDate": "2018-02-25T02:18:12.755Z",
"OrderDate": "2018-02-25T02:18:12.755Z",
"Ref1": "[Metadata.Text:Path="/cXML/Request/OrderRequest/OrderRequestHeader/@orderID",Dataset="Xml",Model="XML"]",
"Items": [{
"StockItemID": 13185,
"Quantity": [Metadata.Text:Path="/cXML/Request/OrderRequest/ItemOut/@quantity",Dataset="Xml",Model="XML"],
"Ref1": "[Metadata.Text:Path="/cXML/Request/OrderRequest/ItemOut/ItemDetail/Description",Dataset="Xml",Model="XML"]"
}]
}
}]
}]
}


Any idea where it is saying the spaces shouldn't?

Cheers,
jan_suhr
Advanced member
Posts: 586
Joined: Fri Nov 04, 2011 1:12 pm
Location: Nyköping, Sweden

Re: Using variables in script to create Json - Newbie help

Post by jan_suhr »

Hi, The App uses the internal JSON Parser in Switch and if you get that message it is something that the parser doesn't understand.

Check your JSON in one of the online JSON Parsers and you can work out where there is something wrong in your code.

I tried this one and it showed errors.
http://json.parser.online.fr/


Good Luck
Jan Suhr
Color Consult AB
Sweden
=============
Check out my apps
Redshift
Newbie
Posts: 13
Joined: Thu Dec 21, 2017 2:40 am

Re: Using variables in script to create Json - Newbie help

Post by Redshift »

jan_suhr wrote:Hi, The App uses the internal JSON Parser in Switch and if you get that message it is something that the parser doesn't understand.

Check your JSON in one of the online JSON Parsers and you can work out where there is something wrong in your code.

I tried this one and it showed errors.
http://json.parser.online.fr/


Good Luck
Thanks Jan,

I appreciate your help.

I have shortened JSON and removed a lot of the variables to try figure this one out.

In inserting the variable which is pulled from a XML I now have this

{
"Orders": [{
"UniqueSubmitOrderID": "string",
"Status": {
"Value": 0,
"EnumType": "string"
},
"InvoiceCustomer": {
"Code": "LEXNEX",
"Contact": "API Invoice Contact",
"ContactEmail": "nz.api@printing.com"
},
"DeliveryCustomer": {
"Name":" [Metadata.TextIndexed:Path="/cXML/Request/OrderRequest/OrderRequestHeader/ShipTo/Address/Name",Dataset="Xml",Model="XML"]",
"Address": {
"AddressLines": "xxxx",

"City": "xxxx",
"Region": "xxxx",
"Country": "xxxx",
"Postcode": "xxxx"
},
"Contact": "API Delivery Contact",
"ContactEmail": "nz.api@printing.com"
},
"RequiredDate": "2018-02-25T02:18:12.755Z",
"OrderDate": "2018-02-25T02:18:12.755Z",
"Ref1": "xxxx",
"Items": [{
"StockItemID": 13185,
"Quantity": "0001",
"Ref1": "0001"
}]
}
]
}


The spaces I realized is just a warning so I can ignore that one but I still get this error "Failed to parse JSON: unterminated object"

This disappears if I remove the variable and just change it to "xxxx".

When checking it on http://json.parser.online.fr/ is shows as correct if I remove the variable (so I am assuming my code is correct?)

(I have tried "Text" and "Indextext" for the metadata both giving the same result)

Thanks,
Cade
jan_suhr
Advanced member
Posts: 586
Joined: Fri Nov 04, 2011 1:12 pm
Location: Nyköping, Sweden

Re: Using variables in script to create Json - Newbie help

Post by jan_suhr »

It seems to be the double quotes inside the variable from Switch that give us the problem,

Try this:

Code: Select all

"Name":"[Metadata.TextIndexed:Path='/cXML/Request/OrderRequest/OrderRequestHeader/ShipTo/Address/Name',Dataset='Xml',Model='XML']",
Note the single quotes inside the variable, this give you a valid JSON.


Jan
Jan Suhr
Color Consult AB
Sweden
=============
Check out my apps
jan_suhr
Advanced member
Posts: 586
Joined: Fri Nov 04, 2011 1:12 pm
Location: Nyköping, Sweden

Re: Using variables in script to create Json - Newbie help

Post by jan_suhr »

I think that you have to take a look at your JSON, if I run this simple JSON with Switch variables as they are it works for me.

Code: Select all

{
"Address": "[Metadata.Text:Path="/order/items/item/extra/Adress",Dataset="Xml",Model="XML"]"
}
Build it line by line to see where it fails. Run the Debug mode in the messages so you get more information about what is happening.


Jan
Jan Suhr
Color Consult AB
Sweden
=============
Check out my apps
Redshift
Newbie
Posts: 13
Joined: Thu Dec 21, 2017 2:40 am

Re: Using variables in script to create Json - Newbie help

Post by Redshift »

jan_suhr wrote:I think that you have to take a look at your JSON, if I run this simple JSON with Switch variables as they are it works for me.

Code: Select all

{
"Address": "[Metadata.Text:Path="/order/items/item/extra/Adress",Dataset="Xml",Model="XML"]"
}
Build it line by line to see where it fails. Run the Debug mode in the messages so you get more information about what is happening.


Jan
Thanks Jan,

Getting closer but it seems to be having issues with any Arrays. Any ideas?

e.g. even this as part of it is giving me an error when it has the variable in it. (it works fine without it).

Without variable:

Code: Select all

{
"Items": [{
      "StockItemID": 13185,
      "Quantity": "0001",
      "Ref1": "0001"
        }]
}
with:

Code: Select all

{
"Items": [{
      "StockItemID": 13185,
      "Quantity": "0001",
      "Ref1":"[Metadata.Text:Path="/cXML/Request/OrderRequest/ItemOut/ItemDetail/Description",Dataset="Xml",Model="XML"]"
        }]
}
I get a failed to parse JSON: unterminated object as soon as I have any variables in any "[]".

Thanks,
Cade
jan_suhr
Advanced member
Posts: 586
Joined: Fri Nov 04, 2011 1:12 pm
Location: Nyköping, Sweden

Re: Using variables in script to create Json - Newbie help

Post by jan_suhr »

What you have in the first box is what Switch and the app sees and processes.

You have to search more in your code. There must be a stray character in there.

You could try the JSONparser link I sent you but change all variables to a real value and see what happens.
Jan Suhr
Color Consult AB
Sweden
=============
Check out my apps
Redshift
Newbie
Posts: 13
Joined: Thu Dec 21, 2017 2:40 am

Re: Using variables in script to create Json - Newbie help

Post by Redshift »

Hi Jan,

I did that part and the code in the bottom part works perfectly.

It only seems to be once I enter a variable in simple JSON below it doesn't like it. e.g. I entered a variable into the ref field and it spits back an error.

The code also checks out on the link.

Code: Select all

{
"Items": [{
      "StockItemID": 13185,
      "Quantity": "0001",
      "Ref1": "0001"
        }]
}
jan_suhr
Advanced member
Posts: 586
Joined: Fri Nov 04, 2011 1:12 pm
Location: Nyköping, Sweden

Re: Using variables in script to create Json - Newbie help

Post by jan_suhr »

What does Switch report to you about this variable in the debug mode. Does Switch give you a value for the variable?
Jan Suhr
Color Consult AB
Sweden
=============
Check out my apps
mgalvin
Newbie
Posts: 2
Joined: Mon Jun 23, 2014 7:50 am

Re: Using variables in script to create Json - Newbie help

Post by mgalvin »

Try this.

Code: Select all

{
"Items": [{
      "StockItemID": 13185,
      "Quantity": "0001",
      "Ref1": "[Metadata.Text:Path=\"/cXML/Request/OrderRequest/ItemOut/ItemDetail/Description\",Dataset=\"Xml\",Model=\"XML\"]"
        }]
}
Post Reply