Hopefully the screenshot says it all. I am attempting to bulk update a table with boolean values, but the query fails with an 'invalid input syntax' message. All this despite the use of the 'dataArray' format.
Any ideas?
You need to structure the input array in a different format. For your case I'm assuming it will look like:
[ {'z1id':1,'ft':true},..]. One json object for each id.
You can simply create a javascript script, and manipulate the object in the above format and then trigger this query onSuccess of the script. Pass the object returned by the script to the query.
I presume that a transformer could accomplish this. Any ideas on how to proceed given that I have very little proficiency in js?
Solved it.
Asked Bard " write js transformer to change array format from this:[{"z1id":[1,2,3,5],"ft":[true,true,true,true]}] to individual items for each z1id - ft relationship"
Bard responded:
const data = [{'z1id': [1, 2, 3, 5], 'ft': [True, True, True, True]}];
const expandedData = [];
for (let i = 0; i < data.length; i++) {
const z1id = data[i].z1id;
const ft = data[i].ft;
for (let j = 0; j < z1id.length; j++) {
expandedData.push({ z1id: z1id[j], ft: ft[j] });
}
}
console.log(expandedData);
Pasted this into a transformer (not the integral one within query6) and changed the first line to:
const data = {{ query6.dataArray }};
Fed {{transformer1.value}} into my 'Bulk update via a primary key' as the 'Array of records to update' and experienced success.
As a postscript I asked the Retool AI bot to do the same thing and it just sat there for 5 minutes before I lost patience.