Skip to main content
T
tenegabi12-Amethyst
12-Amethyst
January 17, 2024
Solved

JSON to Infotable with data from API

  • January 17, 2024
  • 3 replies
  • 4352 views

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": []
}
Best answer by tenegabi

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. 

3 replies

V
19-Tanzanite
Velkumar19-Tanzanite
19-Tanzanite
January 17, 2024

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

Velkumar_0-1705492167945.png

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);

Velkumar_1-1705492378676.png

 

/VR

 

 

 

    T
    tenegabi12-AmethystAuthor
    12-Amethyst
    January 17, 2024

    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.

    V
    19-Tanzanite
    Velkumar19-Tanzanite
    19-Tanzanite
    January 19, 2024

    Hi @tenegabi 

     

    Did it solve your issue ?

     

    /VR

    B
    Bikash_Panda14-Alexandrite
    14-Alexandrite
    January 17, 2024

    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.

     

    InfotableExample.png

    (Source: PTC)

     

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

     

     

      T
      tenegabi12-AmethystAuthor
      12-Amethyst
      January 18, 2024

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

      tenegabi_0-1705558090482.png

       

      N
      nmilleson17-Peridot
      17-Peridot
      January 26, 2024

      @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

        Terms & ConditionsAccessibility statement