cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Help us improve the PTC Community by taking this short Community Survey! X

Powerful Things you can do with DeriveFields

No ratings

Thingworx provides a library of InfoTable functions, one of the most powerful ones being DeriveFields (besides that I use Aggregate and Query a lot and ... getRowCount)

DeriveFields can generate additional columns to your InfoTable and fill that with values that can be derived from ... nearly anything! Hard coded, based on a Service you call, based on a Property Value, based on other values within the InfoTable you are adding the column to.

Just remember for this Service (as well as Aggregate), no spaces between different column definitions and use a , (comma) as separator.

Here are some two powerful examples:

//Calling another function using DeriveFields

//Note that the value thingTemplate is the actual value in the row of column thingTemplate!

var params = {

  types: "STRING" /* STRING */,

  t: AllItems /* INFOTABLE */,

  columns: "BaseTemplate" /* STRING */,

    expressions: "Things['PTC.RemoteMonitoring.GeneralServices'].RetrieveBaseTemplate({ThingTemplateName:thingTemplate})" /* STRING */

};

// result: INFOTABLE

var AllItemsWithBase = Resources["InfoTableFunctions"].DeriveFields(params);

//Getting values from other Properties

//to in this case is the value of the row in the column to

//Note the use of , and no spaces

//NOTE: You can make this even more generic with something like Things[to][propName]

var params = {

    types: "NUMBER,STRING,STRING,LOCATION" /* STRING */,

    t: AllAssets /* INFOTABLE */,

    columns: "Status,StatusLabel,Description,AssetLocation" /* STRING */,

    expressions: "Things[to].Status,Things[to].StatusLabel,Things[to].description,Things[to].AssetLocation" /* STRING */

};

// result: INFOTABLE

var AllAssetsWithStatus = Resources["InfoTableFunctions"].DeriveFields(params);

Comments

Hi Pai,

Can we use in the DeriveFields an expression making reference to the previous row?

Since I don't know exactly how DeriveFields works, I'm not sure.

Probably best to do a regular loop

Hi Vlad,

I don't think so, anyway, it's always tricky to reference previous row ( for instance when you are on row[0] you don't have previous row... )

Carles.

Is it possible to add as row number as derived field in the infotable??

 

Thanks,

Varathan

Hello @PaiChung ,

 

If we need to perform some operation on the other column values (ex. add some constant value to it) and add the result to the new Column that wee are creating. How do we do that using Derive?

 

Also, thanks for elaborating on this Derive Function.

DeriveFields only creates new columns so to change existing columns you should use regular infotable operations or derive it as a new column, delete the existing and renamefield the new.

Since you have to do multiple operations, it might be more efficient to just iterate through the records so you can do each operation in one step.

No, I don't want to change the existing column values. But the values in the new column that I am adding is dependent on other columns of the infotable to which we are adding the new column. Hope my question is clear now. 

Or let me know I can explain with an example Pai.

I thought my post should've covered this.

You can refer to any column in the infotable directly by its column name.

Let's say you have Col1 and Col2 then you can do "Col1 + Col2" for the expression.

Version history
Last update:
‎Dec 20, 2016 11:52 AM
Updated by:
Labels (2)