Goal:
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.
Issue
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?
Inventory:
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 ({{form4.data.asset_name}}, {{form4.data.asset_type}}, {{form4.data.custodian_user_id}}, {{form4.data.deployment_date}}, {{form4.data.firmware}}, {{form4.data.icon}}, {{form4.data.latitude}}, {{form4.data.longitude}}, {{form4.data.photo}}, {{form4.data.project_id}}, {{form4.data.unit_status}}) RETURNING id;
pgArrayTransformer (JS transformer I have issues with, that is to format sensor_list array for pgsql)
// form4.data.sensor_list is an array of IDs const sensorList = {{form4.data.sensor_list}}; // 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}});
