Skip to main content
S
steve23714-Alexandrite
14-Alexandrite
June 9, 2019
Solved

Updatequery InfotableFunctions

  • June 9, 2019
  • 1 reply
  • 3450 views

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

Best answer by slangley

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

1 reply

S
steve23714-AlexandriteAuthor
14-Alexandrite
June 11, 2019

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

 

 

S
slangleyCommunity ManagerAnswer
Support
June 18, 2019

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

S
steve23714-AlexandriteAuthor
14-Alexandrite
June 19, 2019

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.

Terms & ConditionsAccessibility statement