How to convert xero invoice into JSON? Nested array? Nested JSON?

[Noah]

I’m trying to simply pull invoices from Xero and convert them (along with multiple line items) to a JSON file I can pass to my warehouse management system API. It seems like that should be fairly straightforward…however I’m having no luck figuring it out.

I’m grabbing an invoice, iterating through the line items and aggregating that output to JSON, then trying to add the invoice-level info and the line item JSON I just created to a new JSON…

it seems to almost work… multiple line items get pulled into a single JSON along with the invoice-level customer information…But it is also introducing all kinds of syntax problems, so the JSON isn’t formatted correctly.

I think where I am getting tripped up, is how do I just insert the line-item JSON string into the final JSON? If I have the “Items” in the data structure definition set as ‘text’ and then just pass the line-item JSON, then then it looks like a string, with extra quotes, line breaks etc…

But if I have the data structure setup with name/value pairs in the second JSON, then I can’t insert the previous aggregated JSON string (if that makes sense?)

I also tried just aggregating everything into a single JSON…but then the order-level info gets repeated for each line item that exists.

Any advice on how to troubleshoot? Is there an alternate/better approach??

thanks in advance for any help!!

[Noah]

I found a workaround… it isn’t pretty, but seems to do the job.

I was creating 1 JSON for the line items and trying to pull that into another JSON that would have the order header information + line items…but couldn’t figure out how to do that

Instead, what I’ve done is to create two separate JSONs (line items, order header), and then combine them into a single string using the set variable module. To do that, I insert a special character between the two strings and combine them…then replace the closing curly bracket + special character I just inserted with a comma and the ‘items’ label needed in the final JSON…and then add on a final closing curly bracket at the end.

It ain’t pretty, but it seems to work.

If anyone has any suggestions on how to do this the ‘right’ way, I’d love to hear it…am here to learn and just trying to figure things out as I go…

[Andrew_Mein]

I’ve done it that way before. It doesn’t always need to be pretty. As long as it works.