Community Tip - Learn all about the Community Ranking System, a fun gamification element of the PTC Community. X
I would like to have a derived expression for an infotable use a service call (a calculation). Here is the code that I currently have, what would I need to do to get this working?
var params = {
StartDate: StartDate,
EndDate:EndDate
}
var rawdata = Things["ProjectServer"].UtilizationData(params);
var params = {
t: rawdata,
columns: "JulianWeek",
types: "DATETIME",
expressions: Things["ProjectServer"].CalculateJulianWeek(Date)
};
// result: INFOTABLE
var result = Resources["InfoTableFunctions"].DeriveFields(params);
I have some issue when using a service with 2 arguments within an Expression of a DeriveFields.
Anyone already did that ?
Laurent,
Here is an example of deriving two fields.
Once you get how a params variable is constructed you can derive any number of columns.
For this example:
percent_full is a derived field which is CurrentLevel/Capacity * 100
TestColumn is a derived field which is Capacity * Capacity (just a bogus calculation for example purposes).
Note how the multiple entries in the params assignment align for each field and are separated by commas.
There are two NUMBER types because there are two derived fields, there are two columns defined as the derived fields, there are two expressions - one for each derived field… and so on.
params=null;
var myInfoTable=Things['BSTankDataTable'].GetDataTableEntries(params);
var params = {
types: "NUMBER,NUMBER" /* STRING */,
t: myInfoTable /* INFOTABLE */,
columns: "percent_full,TestColumn" /* STRING */,
expressions: "(CurrentLevel/Capacity) * 100, Capacity*Capacity" /* STRING */
};
// result: INFOTABLE
var result = Resources["InfoTableFunctions"].DeriveFields(params);
Hope this helps.
Thanks,
-- Beck
Beck,
Thanks but my question is when the expression is calling a service whith 2 arguments.
expressions: " Things[MyThing]. Myservice (CurrentLevel , Capacity)”,
I still struggle to find the correct way to call this service within an expression
Laurent,
I misunderstood the question. Try this example, it works for me.
Service is “AddTwoVars” which takes two integer arguments in_var1, in_var2.
The code below will derive a field named percent_full with value of 16 by adding 7 and 9.
params=null;
var myInfoTable=Things['BSTestStream'].GetStreamEntriesWithData(params);
var params = {
types: "NUMBER" /* STRING */,
t: myInfoTable /* INFOTABLE */,
columns: "percent_full" /* STRING */,
expressions: Things['BSTestStream'].AddTwoVars({"in_var1":7,"in_var2":9})
};
// result: INFOTABLE
var result = Resources["InfoTableFunctions"].DeriveFields(params);
Thanks,
-- Beck
Hi
What i want to achieve is to call a service where the 2 arguments are not constants like your example, but values coming from the infotable columns.
So instead of
Things['BSTestStream'].AddTwoVars({"in_var1":7,"in_var2":9})
I would need for example this
Things['BSTestStream'].AddTwoVars({"in_var1":capacity,"in_var2":size})
Yes, I see the problem now.
I am able to accomplish with one variable but not two. I am continuing to pursue and will put in a ticket.
Note the entire expressions string must be in quotes so it is interpreted by the
var result = Resources["InfoTableFunctions"].DeriveFields(params); call (at bottom) and not at params variable build time. This prevents the "Capacity is not defined" errors but as noted can only accomplish with one variable input.
This works (1 variable):
expressions: "Things['BSTestStream'].AddTwoVars({in_var1:Capacity})"
This does not (2 variables):
expressions: "Things['BSTestStream'].AddTwoVars({in_var1:Capacity,in_var2:CurrentLevel})"
Error is"missing } after property list"
Will let you know if I find out the issue.
Thanks,
Hi
So you understand my problem ☺
Having a derived Field, with an expression invoking a service more than 1 argument (ie infotable colums).
Thanks to forward the call number
Laurent,
The Case number is 12803913. This is bug which is being worked by R&D. See the following response from ThingWorx support.
Thanks,
-- Beck
From: Andrei Vladescu <andrei.vladescu@thingworx.com>
Sent: Wednesday, October 28, 2015 3:43 AM
To: Beck Smith
Cc: cs_ptc@ptc.com
Subject: Case 12803913 :derive fields function [ ref:_00DA0YLPa._500F0jTGTbIAO:ref ]
Hi,
There is a JIRA ticket opened for this bug.
I will notify you once it is solved by the R&D team.
https://thingworx.jira.com/browse/TW-3876
Kind regards,
Andrei Vladescu.
ref:_00DA0YLPa._500F0jTGTbIAO:ref
This is regarding how to calculate DERIVED values from a base data stream or table.
I am attempting to do the same thing as Justin with two variables Capacity and CurrentLevel to calculate a percentage used.
i.e. CurrentLevel/Capacity = % of capacity used.
Am very close and using the same syntax as show above. But as mentioned by Laurent am experiencing a problem.
The error message is related to ArrayIndex out of bounds which leads me to believe processing of the InfoTable is somehow not correct.
This link references a little more and you may be able to glean a little more insight: Data Interrogation in Widgets
Next Steps:
1. Does anyone have this accomplished yet and can share more explanation?
2. Does anyone know the correct syntax for division in the expression? I am assuming traditional / as noted above.
3. I will continue to hack on this and hopefully find a solution. (I will post the source code and the specific error message if anyone has ideas on how to solve - currently remote but will be in office shortly).
Thanks to any/all for input.
can you please share your code? did you correctly dpecified the type and name if the new column?
Still hacking on the code to get it working.
I get the following error with this code: Wrapped java.lang.ArrayIndexOutOfBoundsException: 1 Cause: 1
Any ideas?
result = Things['BSTestStream'].GetStreamEntriesWithData({"maxItems":50});
var params = {
types: "NUMBER,NUMBER" /* STRING */,
t: result /* INFOTABLE */,
columns: "CurrentLevel,Capacity" /* STRING */,
expressions: "CurrentLevel/Capacity" /* STRING */
};
// result: INFOTABLE
var result = Resources["InfoTableFunctions"].DeriveFields(params);
modify your types and comumns entries:
you just need 1 value for types ("number") and 1 value for columns (call it "percent_full")
and it will work.
as you just want to add 1 column, types and columns noth just need 1 argument
Thanks! Getting much closer. Made the changes you suggested and have gotten a bit further.
Error now is: com.thingworx.dsl.engine.adapters.ThingworxInfoTableAdapter cannot be cast to com.thingworx.types.InfoTable
Apparently I have some problem with "casting" or "obtaining" the infotable for my Stream, probably in this statement:
result = Things['BSTestStream'].GetStreamEntriesWithData({"maxItems":50});
Anything obviously wrong here that you can suggest?
Thanks,
Strange…
Is it the same problem with QueryStreamData ?
Instead of result call the resulting object differently
Can you try the following
var params = ;
// result: INFOTABLE
var myinfotable = Things["BSTestStream"].QueryStreamEntries(params);
logger.warn ( “ Count from stream ”+ myinfotable.getRowCount() );
var params = ;
var result = Resources["InfoTableFunctions"].DeriveFields(params);
logger.warn ( “After Dervice”+ result.getRowCount() );
Does your service returns an infotable ?
Laurent,
Thanks a lot! I got it to work.
For my stream Thing named BSTestStream, I built a service named CalculatePercentage, using the DerivedFields snippet (see below), and it returns an InfoTable with a new value column named percent_full.
Notes:
1. I used GetStreamEntriesWithData.
2. Must make sure that the Service output base type (Inputs/Outputs) tab for the CalculatePercentage service is set to "InfoTable" to avoid the type conversion errors.
Thanks again for the assistance!
Hope this helps others as well.
-- Beck
params=null;
var myInfoTable=Things['BSTestStream'].GetStreamEntriesWithData(params);
var params = {
types: "NUMBER" /* STRING */,
t: myInfoTable /* INFOTABLE */,
columns: "percent_full" /* STRING */,
expressions: "CurrentLevel/Capacity" /* STRING */
};
// result: INFOTABLE
var result = Resources["InfoTableFunctions"].DeriveFields(params);
Sorry for opening again this post but I have two questions about DeriveFields function:
Hi Fabio,
1. In the expression field you can use almost any javascript code, so it's theoretically possible to not use external service - however it can be cumbersome.
2. Yes, it's possible, you need just to use the column name as is, e.g. "Property = Property.substring(3)".
var params = { types: "STRING", t: t1, columns: "Property", expressions: "Property = Property.substring(2)" };
var result = Resources["InfoTableFunctions"].DeriveFields(params);
Please beware, that your code in expressions should not have commas, so you could not use Property.substring(2, 4).
Regards,
J.
Thanks for your answer.
I saw in the above posts that there was a bug about an expression with two arguments. I have the same issue and I cannot figure it out.
May anyone help me?
I have a thing called "FrontEnd_ViewFunctions" which has a service called GetBackendEntityName(params).
params = {organization:
label: }
I tried to use derived expression in this way:
About "label" param it is taken from the input infotable, but the organization param is supposed to be a input param
var params = {
types: 'STRING',
t: <input Infotable>,
columns: 'source',
expressions: 'Things["FrontEnd_ViewFunctions"].GetBackendEntityName({organization: " '+<organization input>+' ", label: source})'
};
the error I get is
Different sizes for columns, types and expressions
My guess is the problem is the coma, because the in the "expressions" coma is used to separate single expressions, so it's like it considers it like two expressions instead of one.
Hi Fabio,
Yes, as I mentioned before, you cannot use a comma in your services parameters, because comma is splitting the expressions. Unfortunately, there's no good workaround for that I know (except using one-param services).