Updating a SQL column cell based on a SELECT GROUP BY

[Nath_McC]

Hi All,

I am new around here and would like some coding help. I am not a coder and cannot solve this problem I have.

I have two tables. One table lists calibrations (SQL Table name: gaugecalibration). The other lists gauges to which the calibrations relate (SQL Table name: gaugelist). The calibration table may have multiple entries for each gauge.

calibrationsTable.data query:

SELECT id, gaugeid, certno, nextcalibrationdate FROM gaugecalibration

gaugeTable.data query:

SELECT id, gaugeref, calibrationstatus FROM gaugelist

The gauge calibration column gaugeid is linked to the id in the gauge list.

I have a third query below, which filters the next calibration dates to show the gauges which will go out of calibration if the date is before today.

outofcal_gauges:

SELECT gaugeid AS "ID", max(nextcalibrationdate) AS "Calibration Date"
FROM gaugecalibration 
WHERE gaugeid IS NOT NULL AND nextcalibrationdate < {{ moment().format('YYYY-MM-DD') }}
GROUP BY gaugeid
ORDER BY gaugeid

To this point, everything is working how I expect it to.
My problem is I would like to create a way to take each gaugeid AS 'ID' from the nextcal_filter query and update the related calibrationstatus cell in the gaugelist to be 'OUT OF CALIBRATION' matching the id.

I tried using the GUI update a row query but received errors. I am guessing I need a way to run the change on the array simultaneously or loop through the array to update, and I am struggling to find that solution. Any help with the coding would be great.

Thanks, Nathan.

[victoria]

Hey Nathan! Happy to help with this. A couple questions to start.

1. What type of update are you currently running? Sounds like a bulk update would be best?

2. What errors are you currently running into?

[Nath_McC]

Hi victoria

At present, I am not running an update. You are correct that I want to take an array of ids from the outofcal_gauges (list shown above). These ids reference the gauge, which is out of calibration.

I would like to bulk update a table (gaugelist) using the ids and set the calibrationstatus. Where the calibrationstatus would be 'OUT OF CALIBRATION'.

My question is, how would I create the bulk update with the ids in the array?

In terms of errors, they are more to do with the fact I'm not sure how to do a bulk update.

[Nath_McC]

The last error I received when trying was that my data must be an array. As shown below.

[Nath_McC]

Just FYI,

I managed to complete the task by using a transfer on the query to convert the data to an array. I could then use the bulk update option as above

let data = {{ outofcal_gauges.data }}
return formatDataAsArray(data)

[victoria]