JSON to Infotable with data from API

[tenegabi]

Hello there,

I try to make a Infotable from JSON, I have already see some example, every time I try to do this I have error that thingworx told the JSON is Null. I dont know If I am doing something wrong or if is error in the Thingworx platform. 
The code is: 

// Function to check if a key exists in a JSON object and return its value
function check_value_yt(input_json, key){
    // Initialize return value to 0
    var return_value = 0;
    // Check if the JSON object is not undefined
    if (input_json != undefined){ 
        // If the JSON object exists, assign the value of the key to return_value
        return_value = input_json[key];
       } 
       // Return the value
    return return_value;  
}

// Function to check if a key exists in a JSON object and return its value as a float
function check_value_mtd(input_json, key){
    // Initialize return value to 0
    var return_value = 0;

    // Check if the JSON object is not undefined
    if (input_json != undefined){ 
        // If the JSON object exists, assign the value of the key to return_value
        return_value = input_json[key];
       } 
       // Return the value as a float
    return parseFloat(return_value);  
}

// Function to check if a number is NaN (Not a Number)
function check_NaN(number){
     // If the number is NaN, return 0
     if(isNaN(number)){
        return 0;
    }
    // If the number is not NaN, return the number itself
    else {
     return number;   
    }
}
try {
// Declare variables for HC (presumably a type of data or category in your application)
var hc_un_l1_today=[]; // Array to store HC PR UN L1 data for today
var hc_un_l1_yt=[]; // Array to store HC PR UN L1 data for Yesterday
var hc_un_l2_today=[]; // Array to store HC PR UN L2 data for today
var hc_un_l2_yt=[]; // Array to store HC PR UN L2 data for Yesterday
var hc_un_l3_today=[]; // Array to store HC PR UN L3 data for today
var hc_un_l3_yt=[]; // Array to store HC PR UN L3 data for Yesterday
var hc_un_l4_today=[]; // Array to store HC PR UN L4 data for today
var hc_un_l4_yt=[]; // Array to store HC PR UN L4 data for Yesterday
var hc_un_a1_today=[]; // Array to store HC PR UN A1 data for today
var hc_un_a1_yt=[]; // Array to store HC PR UN A1 data for Yesterday

// Declare variables for SUD (presumably another type of data or category in your application)
var sud_suwj_100_today=[]; // Array to store SUD PR SUWJ-100 data for today
var sud_suwj_130_today=[]; // Array to store SUD PR SUWJ 130 data for today
var sud_suwj_100_yt=[]; // Array to store SUD PR SUWJ-100 data for today
var sud_suwj_130_yt=[]; // Array to store SUD PR SUWJ 130 data for today   

// Loop through the Scorecard array
for (var x = 0; x < me.Scorecard.length; x++) {
    // Get the line from the current Scorecard entry
    var line = me.Scorecard[x].Line;
    // Get the KPIs for the line for yesterday
    var json_today = Things["PlantApp.Connections.ioDS"].GetMultipleKpis_v4({
        Lines: line,
        _timeOption: "Today"
    });
    // Get the KPIs for the line for the month to date
   var json_yt = Things["PlantApp.Connections.ioDS"].GetMultipleKpis_v4({
        Lines: line,
        _timeOption:"Yesterday"
    });
    if(line.includes("UN L1")) {   
        // take the kpis data from database from UN L1 lines today and yesterday
	    hc_un_l1_today=check_value_mtd(json_today.array.filter(x=>x.Pr)[0],"Pr").toFixed(2);
        hc_un_l1_yt=check_value_mtd(json_yt.array.filter(x=>x.Pr)[0],"Pr").toFixed(2);
    }
    if(line.includes("UN L2")) {   
        // take the kpis data from database from UN L2 lines today and yesterday
	    hc_un_l2_today=check_value_mtd(json_today.array.filter(x=>x.Pr)[0],"Pr").toFixed(2);
        hc_un_l2_yt=check_value_mtd(json_yt.array.filter(x=>x.Pr)[0],"Pr").toFixed(2);
    }
    if(line.includes("UN L3")) {   
        // take the kpis data from database from UN L3 lines today and yesterday
	    hc_un_l3_today=check_value_mtd(json_today.array.filter(x=>x.Pr)[0],"Pr").toFixed(2);
        hc_un_l3_yt=check_value_mtd(json_yt.array.filter(x=>x.Pr)[0],"Pr").toFixed(2);
     }
        if(line.includes("UN L4")) {   
        // take the kpis data from database from UN L4 lines today and yesterday
	    hc_un_l4_today=check_value_mtd(json_today.array.filter(x=>x.Pr)[0],"Pr").toFixed(2);
        hc_un_l4_yt=check_value_mtd(json_yt.array.filter(x=>x.Pr)[0],"Pr").toFixed(2);
     }
        if(line.includes("UN A1")) {   
        // take the kpis data from database from UN L4 lines today and yesterday
	    hc_un_a1_today=check_value_mtd(json_today.array.filter(x=>x.Pr)[0],"Pr").toFixed(2);
        hc_un_a1_yt=check_value_mtd(json_yt.array.filter(x=>x.Pr)[0],"Pr").toFixed(2);
     }         
       if(line.includes("SUWJ-100")) {
		// Retrieve the KPIs data from the database for the Yesterday (mtd)
		sud_suwj_100_today=check_value_mtd(json_today.array.filter(x=>x.Pr)[0],"Pr").toFixed(2);
        sud_suwj_100_today=check_value_mtd(json_yt.array.filter(x=>x.Pr)[0],"Pr").toFixed(2);
     }
        if(line.includes("SUWJ 130")) {
		// Retrieve the KPIs data from the database for the Yesterday (mtd)
		sud_suwj_130_today=check_value_mtd(json_today.array.filter(x=>x.Pr)[0],"Pr").toFixed(2);
        sud_suwj_130_today=check_value_mtd(json_yt.array.filter(x=>x.Pr)[0],"Pr").toFixed(2);
     }
    }

// Create a new entry object for the PlantApp.Scorecard
var newEntry = {
    HC_PR_UN_L1_today: check_NaN(hc_un_l1_today),
	HC_PR_UN_L1_yt: check_NaN(hc_un_l1_yt),
    HC_PR_UN_L2_today: check_NaN(hc_un_l2_today),
    HC_PR_UN_L2_yt: check_NaN(hc_un_l2_yt),
    HC_PR_UN_L3_today: check_NaN(hc_un_l3_today),
    HC_PR_UN_L3_yt: check_NaN(hc_un_l3_yt),
    HC_PR_UN_L4_today: check_NaN(hc_un_l4_today),
    HC_PR_UN_L4_yt: check_NaN(hc_un_l4_yt),
    HC_PR_UN_A1_today: check_NaN(hc_un_a1_today),
    HC_PR_UN_A1_yt: check_NaN(hc_un_a1_yt),

    SUD_PR_SUWJ100_today: check_NaN(sud_suwj_100_today),
    SUD_PR_SUWJ100_yt: check_NaN(sud_suwj_100_yt),
    SUD_PR_SUWJ130_today: check_NaN(sud_suwj_130_today),
    SUD_PR_SUWJ130_yt: check_NaN(sud_suwj_130_yt)
};

    logger.debug(JSON.stringify(newEntry));

    // Define the data shape for the InfoTable


// Create a new JSON object with a dataShape and rows property
var result = Resources["ContentLoaderFunctions"].GetJSON(newEntry);
logger.debug('Result' +result[0].array.length);
logger.debug('Result' +result.array.Length);
    
//    var result = newEntry;
//let params = {
//	json: parseJSON(newEntry) /* JSON */
//};
//
//// result: INFOTABLE


   
// result: INFOTABLE
//let result = Resources["InfoTableFunctions"].FromJSON(newEntry);

// Assign the new entry to the result variable
//var result = newEntry;

// Error handling
} 
catch(err) {
    //logger.error(me.name + " sendScorecard_v2() -  Catch Error -> on line " + err.lineNumber + ". Error Name: " + err.name + ". Error Message: " + err.message);
}

I take data from another service that take data from an API. 

The resolt for newEntry is: 

{
    "HC_PR_UN_L1_yt": "80.06",
    "HC_PR_UN_L3_today": "62.03",
    "HC_PR_UN_A1_today": [],
    "SUD_PR_SUWJ130_today": "76.78",
    "SUD_PR_SUWJ100_today": "83.58",
    "HC_PR_UN_L4_yt": "64.75",
    "HC_PR_UN_L2_yt": "76.36",
    "HC_PR_UN_L3_yt": "76.59",
    "HC_PR_UN_L4_today": "84.59",
    "SUD_PR_SUWJ130_yt": [],
    "HC_PR_UN_A1_yt": [],
    "HC_PR_UN_L1_today": "76.62",
    "HC_PR_UN_L2_today": "78.82",
    "SUD_PR_SUWJ100_yt": []
}

[Velkumar]

Hi @tenegabi 

You can use the parsley extension to convert JSON ( without DataShape ) to InfoTable

Parsley - GitHub - jmccuen/parsley: Parsely Extension for Thingworx

Parsley Extension can be found here - parsley/build/distributions at master · jmccuen/parsley · GitHub

If you don't want to use an extension, you can convert using script manually

Step 1 : Create a Datashape with the required fields

In my case field1, field2, field3 are JSON keys

Step 2: Create an InfoTable and add a row to it

var jsonValue = {
    "field1": 20,
    "field2": 30,
    "field3": 40
};

// CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable")
let result = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
    infoTableName: "InfoTable",
    dataShapeName: "testShape"
});

result.AddRow(jsonValue);

/VR

[tenegabi]

You told me two variant. I use this for a mashup. I want to make a Dashboard with this kpi named PR for each line of production. And I search for an example and for extract the data from array is to transform to a infotable. Maybe is another solution easier. 
Now I try you solution to see if it's work for my task. 
First solution with extension I can't  use it because of policy.

[Velkumar]

Hi @tenegabi 

Did it solve your issue ?

/VR

[tenegabi]

Hello @Velkumar ,

No, with this solution I don't receive the error, but in output I have a message with No Data. Down there I put a screenshot. 

Thank you.

[Velkumar]

Hi @tenegabi 

Could you please post your sample JSON here 

/VR

[tenegabi]

Hello @Velkumar ,

Of course, the Json sample is: 

{
    "HC_PR_UN_L1_yt": "80.13",
    "HC_PR_UN_L3_today": "0.00",
    "HC_PR_UN_A1_today": [],
    "SUD_PR_SUWJ130_today": "0.00",
    "SUD_PR_SUWJ100_today": "0.00",
    "HC_PR_UN_L4_yt": "82.77",
    "HC_PR_UN_L2_yt": "76.72",
    "HC_PR_UN_L3_yt": "69.16",
    "HC_PR_UN_L4_today": "0.00",
    "SUD_PR_SUWJ130_yt": "0.00",
    "HC_PR_UN_A1_yt": [],
    "HC_PR_UN_L1_today": "0.00",
    "HC_PR_UN_L2_today": "0.00",
    "SUD_PR_SUWJ100_yt": "78.48"
}

[Velkumar]

Hi @tenegabi 

Please find below the code and attached datashape for your reference

var jsonValue = {
	"HC_PR_UN_L1_yt": "80.13",
	"HC_PR_UN_L3_today": "0.00",
	"SUD_PR_SUWJ130_today": "0.00",
	"SUD_PR_SUWJ100_today": "0.00",
	"HC_PR_UN_L4_yt": "82.77",
	"HC_PR_UN_L2_yt": "76.72",
	"HC_PR_UN_L3_yt": "69.16",
	"HC_PR_UN_L4_today": "0.00",
	"SUD_PR_SUWJ130_yt": "0.00",
	"HC_PR_UN_L1_today": "0.00",
	"HC_PR_UN_L2_today": "0.00",
	"SUD_PR_SUWJ100_yt": "78.48",
	"HC_PR_UN_A1_yt": [{
		test: "vale1"
	}],
	"HC_PR_UN_A1_today": [{
		test: "vale1"
	}]
};

// CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(testDatashape_parsejson)
let result = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
	infoTableName: "InfoTable",
	dataShapeName: "testDatashape_parsejson"
});

result.AddRow(jsonValue);

Output :

Note: Here I have converted JSON Array to String.

/VR

[tenegabi]

Hello @Velkumar ,

I don't have administrator to import this datashape. And I have something to say, this JSON is a service automate, everytime when service run he change the numbers, the service that create this JSON is up, in the first post.

Thank you. 

[Velkumar]

Hi @tenegabi 

That is completely fine until the JSON key gets changed.

/VR

[slangley]

Hi @tenegabi and @Velkumar.

We've had some reports of issues from other customers regarding the parsley extension.  It's a couple of years old and hasn't been maintained.

@tenegabi, have any of the other suggestions worked for you?

Regards.

--Sharon

[tenegabi · Accepted solution]

Hello @slangley ,

I made another sollution and it was better for me. 

I make an input with name Line, and I write "HC_PR_UN_L1_yt" like a input and it's worked. 

I don't use infotable anymore. In the last code as a result I use: 

var result = newEntry[Line];

For me it's working. 

[Bikash_Panda]

If you want to use,

 var infotableData = Resources["InfoTableFunctions"].FromJSON(jsonData);

Make sure the jsonData is in correct infotable JSON array format e.g. it has rows key with array of data meant to be the infotable rows and the data shape info as object.

(Source: PTC)

You can use this with an actual datashape fields or even without one. just make sure the json is in this format.

[tenegabi]

Already try this solution, but in the Output the infotable write has no data. 

[nmilleson]

@tenegabi ,

Your error is here:

Resources["ContentLoaderFunctions"].GetJSON(newEntry);

The GetJSON function of the ContentLoaderFunctions is meant to retrieve data from an API in JSON format.  The inputs to that service are the url and headers of the API that you'd like to call.  It would look more like this:

var myApiResult = Resources["ContentLoaderFunctions"].GetJSON(
{
   "url": "https://www.myapi.com:1234/getMyData?filter=today",
   "headers": {"header1":"value1"}
);

// loop through my returned api data, add it to a newEntry object, and then add that entry to a result infotable

Hope that helps

- Nick