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

Community Tip - New to the community? Learn how to post a question and get help from PTC and industry experts! X

Updatequery InfotableFunctions

steve237
12-Amethyst

Updatequery InfotableFunctions

Isn't the UpdateQuery service supposed to update the infotable passed to it in the "t" parameter?  In this support document, InfoTableFunctions the service description states that the UpdateQuery service will "Update rows that match a query with a provided set of values."  However, when I use it (see the example below), the result infotable contains the updated records, but those updates are not saved to the infotable passed to it in the "t" parameter.  Am I missing a step?

 

For example:

In the code below, the result infotable contains the correct records filtered from the FrameComponents by the query and they are updated properly with the values infotable.  However, the cooresponding records in the FrameComponents infotable are not updated.  What do I need to do to update the FrameComponents infotable?

 

var query = {
"filters": {
"type": "EQ",
"fieldName": "gath_cart_no",
"value": row.gath_cart_no
}
};


var params = {
t: FrameComponents /* INFOTABLE */,
query: query /* QUERY */,
values: cartUID_gathCart_link /* INFOTABLE */
};

// result: INFOTABLE
var result = Resources["InfoTableFunctions"].UpdateQuery(params);

 

Thanks,

Steve

1 ACCEPTED SOLUTION

Accepted Solutions
slangley
23-Emerald II
(To:steve237)

Hi @steve237.

 

The Updatequery infotable function doesn't actually make changes in the infotable. It clones the specified table and returns a new infotable with rows from the infotable that were modified from the query. 

 

Infotables are stored in memory and there is no primary key.  There are other options that may be more suited to your use case.  If you can provide further details around this, we will try to suggest a better way.

 

Regards.

 

--Sharon

View solution in original post

4 REPLIES 4
steve237
12-Amethyst
(To:steve237)

I just reviewed the InfoTable Manipulation at ptc university and the InfoTable Manipulation Overview (see pic below also) says that an update is usually accomplished by deleting the old row and adding a new one.  If this is true, I guess I have to first run the UpdateQuery.  The result infotable will contain the list of updated rows.  Then delete the rows in the original infotable that match the rows in result.  Then add the rows in result to the original infotable.  Is that the only way to update rows in an infotable?  Please tell me there is a better/easier way!!

 

Capture.PNG

 

 

slangley
23-Emerald II
(To:steve237)

Hi @steve237.

 

The Updatequery infotable function doesn't actually make changes in the infotable. It clones the specified table and returns a new infotable with rows from the infotable that were modified from the query. 

 

Infotables are stored in memory and there is no primary key.  There are other options that may be more suited to your use case.  If you can provide further details around this, we will try to suggest a better way.

 

Regards.

 

--Sharon

steve237
12-Amethyst
(To:slangley)

Thanks for the explanation of the UpdateQuery function, Sharon.

Here's more details on what I'm trying to do.  I'm updating the cart_uid field of the FrameComponents infotable.  This is a very simplified version.  In reality, FrameComponents is 7K-10K rows.

 

FrameComponents InfoTable before updates:

gath_cart_no cart_uid
123  
123  
123  
123  
200  
210  
210  
211  

First I build an InfoTable that has a list of unique gath_cart_no's.  In this case, it would be 123,200,210,211.  I loop through this infotable and pass the gath_cart_no to a service that finds the cart_uid.  So, for example, the service returns 15 for gath_cart_no=123.  I originally thought that I could just run the UpdateQuery function to update the gath_cart_no field of 4 rows in the FrameComponents InfoTable to 15.  But, now I realize that the UpdateQuery returns an InfoTable with the 4 updated rows.

 

So, what I do is add the result to a temp infotable called tempFC.  At the end of the 4th loop, the temp table contains all 8 records and is an updated version of the original FrameComponents InfoTable.

tempFC InfoTable after the loop is complete.

gath_cart_no cart_uid
123 15
123 15
123 15
123 15
200 5
210 7
210 7
211 3

 

Finally, I just replace the FrameComponents with the temp InfoTable 

FrameComponents = tempFC;

 

Using this strategy works because I am updating every row in FrameComponents.  If I was only updating one record, I think I would need to first delete the record and then add back the updated version.

slangley
23-Emerald II
(To:steve237)

Hi @steve237.

 

We don't totally understand your use case, but would probably take a different approach based on what we know here.  Instead of running a service using the UpdateQuery function, we would probably write the service to update the cart_uid field in the infotable at the time the record containing the gath_cart_no is created.

 

If running the service at a later time, you could loop through the infotable to populate the fields using a set as opposed to an update.  Possibly something like this:

 

var tableLength = myInfoTable.rows.length;
for (var x=0; x < tableLength; x++) {
    var row = myInfoTable.rows[x];
    if(row.cart_uid = null) {
        //Lookup uid logic here
    }
}

 

Hopefully, this helps.  Thanks for marking the Accepted Solution.

 

Regards.

 

--Sharon

Top Tags