Powerful Things you can do with DeriveFields

[#1]

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

[VladimirRosu]

Hi Pai,

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

[PaiChung]

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

Probably best to do a regular loop

[CarlesColl]

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.

[vranganathan]

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

Thanks,

Varathan

[SaiPrakash]

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.

[PaiChung]

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.

[SaiPrakash]

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.

[PaiChung]

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.