Issue with populating tables with multi select form element arrays


Have 3 tables: sensor_db, modem_db, assets_db. Form is populating assets_db with a new entry. this form contains a multiselect for picking what sensors and modems are installed in specific asset. This multi select elements populate foreign keys in modem_db and sensor_db with an id of that asset.

pgArrayTransformer.value: "10,11,6,9" when I try populating my pgSQL table with this function return I get an error: "invalid input syntax for type integer: "10,11,6,9"" And that's off because
UPDATE sensor_db SET asset_deployed_at = 1 WHERE id IN (1, 2, 3); works just fine.

What's the proper way to use the return of a multiselect in Retool as an array to populate tables correctly?


form4SubmitToAssetsDb2 (populates assets_db and returns the UUID for the new record)
INSERT INTO assets_db (asset_name, asset_type, custodian_user_id, deployment_date, firmware, icon, latitude, longitude, photo, project_id, unit_status) VALUES ({{}}, {{}}, {{}}, {{}}, {{}}, {{}}, {{}}, {{}}, {{}}, {{}}, {{}}) RETURNING id;

pgArrayTransformer (JS transformer I have issues with, that is to format sensor_list array for pgsql)
// is an array of IDs const sensorList = {{}}; // Convert the array into a comma-separated string of integers const formattedSensorList = sensorList.join(","); // Return the formatted string for use in your SQL query return formattedSensorList;

UpdateSensorsQuery (suppose to populate assets id to sensors asset_deployed_at foreign keys field) but does not work.
UPDATE sensor_db SET asset_deployed_at = 1 WHERE id IN ({{pgArrayTransformer.value}});

Hi @Scottsky, try using ANY({{ pgArray... .value }}).
Check this SQL Cheatsheet for Retool.
Let us know how it goes! :slightly_smiling_face: