The community will undergo maintenance on October 16th at 10:00 PM PDT and will be unavailable for up to one hour.
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
Solved! Go to Solution.
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
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!!
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
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.
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