3 Replies Latest reply on Feb 9, 2017 3:28 AM by jkaczynski-2 RSS
    emadar Newbie

    Combining 2 infotable to update one of them

    Hi everyone, I'm working on Thingworx creating services using the Java Api. I wanted to join them depending on one table but I cannot find a fine way to do it.

     

    Let's say I have the table:

     

    IDName
    1Coca
    2Cola
    3Sprite

    And this one:

    IDName
    1Sprite
    2NotCola
    3DietSprite
    4HODOR
    5CantTalkAnymore

     

    And I want to receive:

    IDName
    1Sprite
    2NotCola
    3DietSprite

     

     

    Does someone has any ideas how to do it? I could actually pass on every row and check it but I guess there is probably something in the api that can help me with it isnt there?

      • Re: Combining 2 infotable to update one of them
        jkaczynski-2 Creator

        Hi Elie Madar,

         

        You can try to use Intersect with LEFT as a joinType. This service joins two tables, so you'd have a result InfoTable and can do with it pretty much anything, e.g. update your property.

         

        // var t1 is first InfoTable
        // var t2 is second one
        
        var params = {
             columns2: "Name",
             columns1: "ID",
             joinType: "LEFT",
             t1: t1,
             t2: t2,
             joinColumns1: "ID",
             joinColumns2: "ID"
        }
        
        var result = Resources["InfoTableFunctions"].Intersect(params);
        
        

         

        Notice, that t1 is your first InfoTable, so we want to LEFT join to this table, but take the Name value from the second one.

         

        Hope that helps, in case of further question, don't hesitate to ask.

        Regards,

        J.

          • Re: Combining 2 infotable to update one of them
            emadar Newbie

            Do you know if the same is available for the edge sdk? Because I'm actually trying to do this with Java. It's nice to know we can do it with JS though .

            Thanks you for the help anyways !

            Regards,

            E.

              • Re: Combining 2 infotable to update one of them
                jkaczynski-2 Creator

                Elie,

                 

                The only way of doing this that comes to my mind (since there is no possibility to instantiate InfoTableFunctions on the Edge with Edge SDK) is to invoke Intersect directly (you need to have permissions on your App Key of course):

                 

                // ValueCollection is object to hold input parameters to service Intersect
                ValueCollection params = new ValueCollection();
                params.SetInfoTableValue("t1", new InfoTable()); // here instead of new InfoTable() put your first IT
                params.SetImageValue("t2", new InfoTable()); // here instead of new InfoTable() put your second IT
                params.SetStringValue("joinColumns1", "ID");
                params.SetStringValue("joinColumns2", "ID");
                params.SetStringValue("joinType", "LEFT");
                params.SetStringValue("columns1", "ID");
                params.SetStringValue("columns2", "Name");
                
                // client is instance of ConnectedThingClient, successfully started and CONNECTED (you should check if client.isConnected() before)
                // the parameters are: (EntityType, EntityName, ServiceName, InputParameters, Timeout)
                InfoTable result = client.invokeService(ThingworxEntityTypes.Resources, "InfoTableFunctions", "Intersect", params, 1000);
                
                

                 

                Hope that helps. If so, you can mark my answer as a correct one for the reference of others.

                 

                Regards,

                J.