How to combine infotables and their meta data

[Willie]

It seems that when I combine infotables with "union" their meta data do not get combined.  Is there a way to combine the meta data as well?  Meta data are fields such as source, timestamp, tags, location, etc.  The infotables are query results from a data table.  When I set the output of the service as "INFOTABLE" and "infotable type" to "is Data Table", I still do not get the meta data.

			let tableLength = someInfoT.rows.length;
			for (let x = 0; x < tableLength; x++) {
				let row = someInfoT.rows[x];
				//Your code here

				let query = {
					"filters": {
						"type": "And",
						"filters": [{
								"type": "EQ",
								"fieldName": "SomeID",
								"value": row.SomeID
							},
							{
								"type": "LT",
								"fieldName": "SomeDate",
								"value": oneYearAgo
							}
						]
					}
				};

				let queryResult = Things["SomeDataTable"].QueryDataTableEntries({
					maxItems: undefined /* NUMBER */ ,
					values: undefined /* INFOTABLE */ ,
					query: query /* QUERY */ ,
					source: undefined /* STRING */ ,
					tags: undefined /* TAGS */
				});

				// result: INFOTABLE
				result = Resources["InfoTableFunctions"].Union({
					t1: result /* INFOTABLE */ ,
					t2: queryResult /* INFOTABLE */
				});
			}

[slangley]

Hi @Willie.

Did you provide the full code snippet?  If so, it looks like you're missing one of the tables you're trying to join.

What version of ThingWorx are you running?

Regards

--Sharon

[Willie]

Hi @slangley 

No, I just included the pertinent lines as the service is a lot longer.

I wrote separate test services to see if infotable union includes meta data.  Based on my tests, it seems that it does if it is not in a for loop.  The output of the code below included meta data.

let query = {
   "filters":{
      "type": "EQ",
      "fieldName": "key",
      "value": 55
   }
}; 

let infoT1 =  Things["SomeDataTable"].QueryDataTableEntries({
	maxItems: undefined /* NUMBER */,
	values: undefined /* INFOTABLE */,
	query: query /* QUERY */,
	source: undefined /* STRING */,
	tags: undefined /* TAGS */
});

query = {
   "filters":{
      "type": "EQ",
      "fieldName": "key",
      "value": 60
   }
}; 

let infoT2 =  Things["SomeDataTable"].QueryDataTableEntries({
	maxItems: undefined /* NUMBER */,
	values: undefined /* INFOTABLE */,
	query: query /* QUERY */,
	source: undefined /* STRING */,
	tags: undefined /* TAGS */
});

// result: INFOTABLE
var result = Resources["InfoTableFunctions"].Union({
	t1: infoT1 /* INFOTABLE */,
	t2: infoT2 /* INFOTABLE */
});

query = {
   "filters":{
      "type": "EQ",
      "fieldName": "key",
      "value": 70
   }
}; 

let infoT3 =  Things["SomeDataTable"].QueryDataTableEntries({
	maxItems: undefined /* NUMBER */,
	values: undefined /* INFOTABLE */,
	query: query /* QUERY */,
	source: undefined /* STRING */,
	tags: undefined /* TAGS */
});

result = Resources["InfoTableFunctions"].Union({
	t1: result /* INFOTABLE */,
	t2: infoT3 /* INFOTABLE */
});

However, I noticed that when the infotable union occurs in a for loop, it does not include meta data.  The output of the code below did not include meta data.

var result = DataShapes["DataTableB.DS"].CreateValues();
let oneYearAgo = new Date() - 31536000000;
let infoTA = Things["DataTableA"].GetDataTableEntries({
	maxItems: 20 /* NUMBER */
});
let tableLength = infoTA.rows.length;
for (let x = 0; x < tableLength; x++) {
	let row = infoTA.rows[x];
	//Your code here

	let query = {
		"filters": {
			"type": "And",
			"filters": [{
					"type": "EQ",
					"fieldName": "SomeID",
					"value": row.SomeID
				},
				{
					"type": "LT",
					"fieldName": "SomeDate",
					"value": oneYearAgo
				}
			]
		}
	};

	let infoTB = Things["DataTableB"].QueryDataTableEntries({
		maxItems: undefined /* NUMBER */ ,
		values: undefined /* INFOTABLE */ ,
		query: query /* QUERY */ ,
		source: undefined /* STRING */ ,
		tags: undefined /* TAGS */
	});

	result = Resources["InfoTableFunctions"].Union({
		t1: result /* INFOTABLE */ ,
		t2: infoTB /* INFOTABLE */
	});
}

[slangley]

Hi @Willie.

In your 2nd example that doesn't include the metadata, it appears you have failed to combine tA and tB.  The t1:result doesn't exist:

result = Resources["InfoTableFunctions"].Union({ t1: result /* INFOTABLE */ , t2: infoTB /* INFOTABLE */

Regards.

--Sharon

[Willie]

Hi @slangley 

Not sure what you mean.  result is declared in the first line.  It is empty initially, but as it goes through the for loop, it gets rows added.

[slangley]

Hi @Willie.

It looks like you're performing a union with an empty info table.  You might want to review your code in the 2nd example where it's not working.

Regards.

--Sharon

[slangley]

Hi @Willie.

Have you been able to resolve your issue or do you still have questions?  Please let us know.

Regards.

--Sharon

[Willie]

So it seems that the union function has a limitation where if one of the infotables is empty, it will not add the meta data.

[slangley]

Hi @Willie.

Why would the infotable be empty?  What is the use case?  If needed, you could use some conditional logic to not perform the union if the infotable is empty.

Regards.

--Sharon