19 Replies Latest reply on Feb 10, 2017 6:18 AM by jkaczynski-2 RSS
    justiny Explorer

    Derived Expression that uses a service

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

     


      • Re: Derived Expression that uses a service
        laurentger Apprentice

        I have some issue when using a service with 2 arguments within an Expression of a DeriveFields.

         

        Anyone already did that ?

          • Re: Derived Expression that uses a service
            bsmith Apprentice

            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

             

              • Re: Derived Expression that uses a service
                laurentger Apprentice

                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

                  • Re: Derived Expression that uses a service
                    bsmith Apprentice

                    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

                     

                      • Re: Derived Expression that uses a service
                        laurentger Apprentice

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

                          • Re: Derived Expression that uses a service
                            bsmith Apprentice

                            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,

                  • Re: Derived Expression that uses a service
                    bsmith Apprentice

                    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.

                      • Re: Derived Expression that uses a service
                        laurentger Apprentice

                        can you please share your code? did you correctly dpecified the type and name if the new column?

                          • Re: Derived Expression that uses a service
                            bsmith Apprentice

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

                             

                             

                              • Re: Derived Expression that uses a service
                                laurentger Apprentice

                                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

                                  • Re: Derived Expression that uses a service
                                    bsmith Apprentice

                                    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,

                                     

                                     

                                      • Re: Derived Expression that uses a service
                                        laurentger Apprentice

                                        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 ?

                                          • Re: Derived Expression that uses a service
                                            bsmith Apprentice

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

                                             

                                • Re: Derived Expression that uses a service
                                  fmanniti Creator

                                  Sorry for opening again this post but I have two questions about DeriveFields function:

                                   

                                  1. I have a column called Property which lists all properties of a thing. I want to create a new column called OutputProperty with the property name as the user will see on frontend side.
                                    The difference between Property and OutputProperty is something like this: TemperatureMeasure ---> Temperature Measure
                                    To do this I have a switch-case function but I don't know how to insert it into the expression.
                                    So far I created another service called "ChangePropertyName" and into the expression I only write "Things[<myThingName>].ChangePropertyName({Property:<inputValue>})";
                                    This works, but I just want to know if it is possible to do this without create another service.
                                  2. When I derive a InfoTable, is it possible to change the value of a column?
                                    So let's say I have a column called Name with the name of a Thing. I still want to keep that column but instead of the name of the Thing I want to write "ThingName + '_somethingElse' ".
                                    • Re: Derived Expression that uses a service
                                      jkaczynski-2 Creator

                                      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.

                                        • Re: Derived Expression that uses a service
                                          fmanniti Creator

                                          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.