Hi everyone,
I have an update query triggered when I update a cell in a table. It uses a transformer to set a value to ```null`` if the option in the cell is "Unassigned".
// Initialize routeId as null
let routeId = null;
// Check if changesetArray exists and is not empty
if ({{ tableLocations.changesetArray }} && {{ tableLocations.changesetArray.length }} > 0) {
// Get the Route Number from the selected row in tableLocations
const routeNumber = {{ tableLocations.changesetArray[0].routeId }};
// If the routeNumber is "Unassigned", keep routeId as null, otherwise set routeId to the routeNumber
if (routeNumber !== 'Unassigned' && routeNumber !== null) {
routeId = routeNumber;
}
}
// Return the result (either null or the routeNumber)
return routeId;
The query uses the variable and works during the update.
UPDATE public.jobs
SET route_id =
CASE
WHEN COALESCE({{ transformRouteID.value }}, NULL) IS NULL THEN NULL
ELSE (
SELECT id
FROM public.routes
WHERE route_number = {{ transformRouteID.value }}::integer
)
END
WHERE id = {{ tableLocations.changesetArray[0].id }}::uuid;
It seems normal the transformer is throwing an error when I am not changing a value in the table as there is no changesetArray.
I actually just figured out how to do the query without the transformer. I'm just curious if there is a best practice when referring to a changesetArray and the value is potentially empty.