changesetArray inputs primary key data

[Adib]

Hello, I am very new to Retool and have been using the guide of youtube videos and chatgtp to play around with. my Inventory's data which is on Google Sheets.

The issue im currently stuck at is trying to change data on a column. The Sheet im trying to edit has formula which is pulling data from another sheet and within those data is where the Primary Key data is.

So Im using changesetArray to update one of of the column. But it keeps inputing the data from the Primay Key column. I tried to get chatgtp to give me a solution and it gave me this:

{{ INVENTORY.changesetArray.map(change => ({
CODE: change.CODE, // Primary key, only for reference
LOCATION: change.LOCATION // New value to be updated
})) }}

ChatGTP's suggest did not work. It does not do anything.

Hope I explained it enough. TIA

[jg80]

The changesetArray will always include the primary key so that you can identify which record is being changed. Can you explain a bit more about what you are trying to do to change the data in one of the (other?) columns? Maybe a screenshot (with any private data blurred)?

[Adib]

Example of my Google Sheet

With ReTool I setup a table compontent and loaded the data to edit LOCATION column only but since CODE is set as Primary Key it inputs that as well. If I use changesetArray and I want to edit G3 from the Table component then it will take the CODE from D3 and input it as well. But my Google Sheet's data is pulling those data from A:F using a formula so when it overwrites D3 it messes up the formula.

I dont want Primary Key (CODE) to overwrite the data.

[jg80]

I see what you are getting at. Is the “CODE” field a value that can change or truly a primary key? I get that the key might initially be generated with a formula, but once created would you expect it to change?

If it can change, then perhaps it isn’t really a primary key. In that case, you could add a new permanent PK for each record that could be overwritten, since it seems the GSheets integration writes the key in an update.

I’m making some assumptions and guesses here since I don’t use the GSheets integration. I’m also curious whether the “compare formatted values” option that you have checked has something to do with it as well?

[Adib]

No, CODE field's data is PK with unique data.

What is happening is when i save changes it updates the edited field but it also keeps updating the PK with the same data, it overwrites it which is causing issue with my formula on GSheet.

Just want to know if there are any alternative ways to edit GSheet data.

[Jack_T]

Hello @Adib,

Very interesting situation, so it sounds liked CODE is not changing and is fine to be a PK.

When you said "but it also keeps updating the PK with the same data" are you saying that the CODE is being changes from a string of 5x5 characters to the value that was entered into the updated field?

If you mouse over INENTORY.changesetArray in the query after chaning a column that is not CODE, does the code previewer show the value of CODE to have changed from what is was set to originally?

Here are our docs on our Google Sheets integrations, if there are any alternative ways to edit GSheet data, you will likely find them here.

[Adib]

Thanks for responding Jack.

So whats happening is, If you look at the second image. I have total 4 rows under Location that I edited. It updates those on to the Gsheet properly. But it also takes the row's PK value and updates it (overwrites) on to the PK column's row. I just want it so only Location fields to change when I make the edits and not the PK field.

Sorry im very new to this so hopefully i used the correct terms to explain the situation.

[Jack_T]

Oh thank you for explaining.

That is odd that the PK in the GSheet is changing

So before the update, the PK in the GSheet is a formula which generates the 5x5 values, and then after the update query runs, instead of showing the formula it is showing the results of the formula, matching the PK in the Retool table, i.e. the 5x5 PK value?

As the query is iterating through the rows in the GSheet, it finds the PK that matches the PK in the query payload, which it sounds like it is doing properly, then finds the row that matches the payload data and updates the column values accordingly.

If your GSheet has PKs of 1 for the first row, 2 for the second row and 3 for the third row, and you update row 2, then the PK for 2 for that row should remain 2.

I believe there might be some confusion about what you are seeing in the GSheet.

From your second screen shot, what was the PK in the sheet for "47VH2-PPGK7-MCPPH-3TPDP-KC9Z" before the update?

If that value is a PK it should not be changing, so although it may appear as a formula, you can't have a formula be a PK, the value will be set and become an un-mutatable string/number so that the code can find that same row again when needed.

If the PK was a different 5x5 value like "12345-abcde-67891-abcde-98765" but changed to "47VH2-PPGK7-MCPPH-3TPDP-KC9Z" then let me know because that is a problem and should not be happening

Sorry but I hope that makes sense, it is hard to trouble shoot when I can't see the GSheet before and after to understand what the overwriting behavior looks like

[Adib]

You have the right idea.

PK value is not changing its finds the row to change based on the PK and overites the change input with PK and the Location value.

Here is an example of the changes that I make. I changed a value on Location.

So the value to be changed is from WEB to FB. The PK is XC9HK-JF9RG-24RW3-GQDQY-TVKJZ.

Once i run the query, i twill change WEB to FB and also place the PK value.
Before:

After:

The reason code column is showing empty is because the code datas are coming from another sheet by a formula and the formula is not able to load the data because ReTool overwrote the value.

So thats the issue. I cannot have Retool add any value under Code just because its the PK.

[Jack_T]

Thank you so much for sharing those screen shots!

I understand what is going on much better now

That is a very odd quirk, as I did not expect the Retool query to do any writing to the PK column in the GSheets table.

I completely understand your need for the PK column to not be touched as all, as it seems that the GSheet formula changes from working to failing because of the Retool query which is not what we want to happen.

I can make a bug report for our engineering team to take a look at this.

If you have an example of a super simple version of your formula/a formula that I can use to demonstrate that the Retool query is able to break formula involving rows that are not being updated that would help a ton!

Is the formula working with values over multiple separate tables?

In the short term, a potential work around would be to take the PKs which are the outcome of a formula, copy them over to another table/column so that they are hard coded values that are not dependent on a formula to be evaluated. Then pass that data in as a source for the CODE column you are working with.

Any way to get the values of the CODE column separate from the formula that is creating them should allow for them to remain unchanged and get around this weird behavior.

Thank you again for you patients and our apologies for this odd bug!