Connect to JSON data via an API call for related data during print-stream rendering

[TM]

We have an upcoming project which requires connection to web-based JSON data via API calls during print-stream rendering. Basic scenario is as follows:

• Main table is recipient data supplied as CSV, etc. (mailing list).
  
• Each record contains a "product code" which indicates a product of relevance to the recipient.
• Current information and pricing data for the products are accessible in JSON format via API.
  
• The product information is being continually updated (this is on our client's side), so we'd need to pull current data at the time of rendering.
• The product data will populate a marketing letter to each recipient.
• The client database of product info may contain a large number (1,000's? 10,000's?) of products which are not relevant to a given rendering batch (recipients), so we'll only want to do the lookup for each product as needed for a given recipient record.
• The structure of the JSON data will be predictable.

I'm looking at various approaches for implementing this type of workflow. Can anyone give any insights on doing this in an XMPie-based scenario?

[TM]

...and I'm also weighing pros/cons of doing these API lookups/appends as a data pre-process, instead of at the time of composition.

[Igor]

first lets think that we have no API or JASON and we have 2 tables :1 recipient table that comes from Datasource 1 in your case CSV and  products table that lest say comes from some datasource .

in XMPie you'd setup a User View that will be something like select * from Products where ProductCodeID = |->[ProductCodeID] // 

XMPie does not support direct requests to a MS web service or a Jason so whhat can be done 

1) You can write a compiled store procedure  and pass parameter that will talk to Jason at run time , but XMPie will store-procedure on each recipient list  

I do not think that this is efficient

2)  XMPie supports User View that can be constructed from the  XML string and you can pass it via varibale via customization . in theis case you will have to puss product table as an XML that your JASON return . Technique well described in the XMPie uPlan User Guide. But main problem is that it is not reqular table and we do not do lookup so  end up manipulate your XML in uPlan using Javascript   and a a size of SOAP request maybe an issue.

3) I'd do a single request to Jason and pull up to day info and create some table or even xmpie datasource and point production on both those tables, or even better Id import this CSV file to my own table and create this product table and pass xmpie query for recipient list as well via API you can change query of the user view.

Igor.

[Jeffrey Stewart]

TM,

I have found that some preprocessing of recipient / customization data is useful and allow uProduce to work against a data source that does not depend on another API at production time. In terms of using JSON formatted data there is an easy way to process it as rendering is running.

Say you have a complex data structure in each record of recipient / customization data source. You can easily store a JSON string in a text field in the dataset. Then use a JavaScript function to decode the underlying information for populating and ADOR. For example consider this trivial JSON string.

{ "v": "The value goes here." }

Then have a JS function on uPlan:

var val = null;

try {

var jsonObj = (new Function("return " + str))();

val = jsonObj.v;

} catch (exception) {

  return str;

}

if (val == null) {

val = str;

}

return val;

This sample is taken from a much more complex JSON structure and function. I've found this technique to be very useful. And it uses the limited abilities of the uPlan JS library well.

I hope that helps in some regard.

Jeff