Update multiple records

Please what's wrong with the following?

I have "Allow this query to modify multiple values in the database" selected.
I thought the values from {{formatDataAsObject(EventsOnAccount.recordupdates).EventID}} returned an array of values? but this doesn't seem to treat the values as multiples lines to update? Do I have to reformat this using STRING_SPLIT or equivalent?

Thanks
Jeremy
(Yes, it's me from the newsletter!)

Hi @jclutterbuck, you should be using an IN operator instead of an equality in your Filter By field. You've converted the string/nvarchar to Array of INT. The equality operator assess your value according to the data type of the column you specified.

//In SQL, your filter by won't be treated as:
WHERE EventID = 7870 AND EventID = 7871

// but instead
WHERE EventID = [7870, 7871]

// for arrays to work in filters, it should look like this:
WHERE EventID IN [7870,7871]

Of course, thanks.

Jeremy

Dear @jocen

I corrected my error as you described and now that the query runs it highlights another error. i wonder if you might have any clues?

I get a conversion error when updating (but not when inserting a new row from the table).

I have tried many different formats (with "T", with "/" instead of "-", default format etc) hoever nothing seems to make it work. I have eliminated all but one of the date/time fields to debug it.

I am using Azure/SQL server and the fields are datetime2(0). It seems to use "1899-12-30" as the default date when it's just a time I'm insterested in, but I've tried this and "1901-01-01".

Any help really appreciated,

Jeremy

Hi Jeremy,

Not sure if you've tried this format. According to MS, the accepted string format for datetime2 is YYYY-MM-DDThh:mm:ss[.nnnnnnn]. Try this one on your conversion.

I'm curious though, is your Time column in time data type or not?

@jocen,

Thanks again.

I tried that format too :frowning:
This morning it seems to be a different field that's generating the error. Now the Location is causing a conversion error but it says Can't convert 180,180 to int. I pressume that this is because the array [180,180] is being treated as a single string?

How does this multiple update work? If the filter evaluates to an array, do all the other Key value pairs have to evaluate to arrays of the same length? What happens if they are not? Should they be arrays or some other object(s)?

Thanks

Jeremy

Alright, hmmm. I think you are using Update an existing record which would only be ideal if you are changing a single entry with a specific value OR multiple entries with a specific value.

e.g. if you are filtering to update the records of EventIDs 7870 and 7871, the change you can do for these both events will be the same. i.e. Location field will just accept one value depending on the data type accepted by this column. If it is string, say "Sydney, Australia" then both row (7870, 7871) will have "Sydney, Australia" as their updated location.

Now, I think you want to use "Bulk update via a primary key". If you use this, you can write in the "Array of records to update" with something like this:

{{ EventsOnAccount.recordUpdates }}

Assuming the following things:

  • Your table properties in retool have the same column names as the one in your Azure resource
  • EventsOnAccount.recordUpdates outputs an array of objects

Hope this explanation gives you direction on what you are aiming to accomplish

1 Like

This is REALLY helpful.

Thank you.

Very precise and just what I feel is missing from the "docs".

Thanks as always.

Jeremy

This is the best piece of advice for working with multiple updates to a table. I really wish Retool would have made this in one of their early videos as I was struggling to understand how the updates worked before. Thank you!