How to create multiple records from webhook

J_M · ‎Sep 15, 2022

Here’s how the data looks:

{
   "gatewayMessage":{
       "gatewayID":"956995" ,
       "gatewayName":"Ethernet Gateway 4 - 956995" ,
       "accountID":"40029",
       "networkID":"66417" ,
       "messageType":"0" ,
       "power":"0",
       "batteryLevel": "101" ,
       "date": "2022-09-16 00:00:30",
       "count":"3",
       "signalStrength": "0",
       "pendingChange": "False" 

   },
   "sensorMessages":[
      {
          "sensorID":"701519" ,
          "sensorName":"Cooler 1",
          "applicationID":"2",
          "networkID":"66417",
          "dataMessageGUID":"88e199c6-62a3-4d2c-9a57-7c0a375282d0",
          "state": "16",
          "messageDate": "2022-09-16 00:00:03",
          "rawData":"7.9",
          "dataType": "TemperatureData",
          "dataValue": "7.9",
          "plotValues": "46.22",
          "plotLabels": "Fahrenheit",
          "batteryLevel": "13",
          "signalStrength": "76",
          "pendingChange": "True",
          "voltage": "2.24"

      },
      {
          "sensorID":"644289" ,
          "sensorName":"Wash Water Temp",
          "applicationID":"86",
          "networkID":"66417",
          "dataMessageGUID":"53691a40-f355-4286-bc71-e34dc45e9002",
          "state": "0",
          "messageDate": "2022-09-16 00:00:04",
          "rawData":"23.7",
          "dataType": "TemperatureData",
          "dataValue": "23.7",
          "plotValues": "74.66",
          "plotLabels": "Fahrenheit",
          "batteryLevel": "100",
          "signalStrength": "93",
          "pendingChange": "True",
          "voltage": "3.33"

      },
      {
          "sensorID":"701520" ,
          "sensorName":"Cooler 2",
          "applicationID":"2",
          "networkID":"66417",
          "dataMessageGUID":"c3b90bc1-4f99-4b59-8bf5-6f3e07cbdd60",
          "state": "16",
          "messageDate": "2022-09-16 00:00:04",
          "rawData":"22.8",
          "dataType": "TemperatureData",
          "dataValue": "22.8",
          "plotValues": "73.04",
          "plotLabels": "Fahrenheit",
          "batteryLevel": "100",
          "signalStrength": "77",
          "pendingChange": "True",
          "voltage": "2.68"

      }

   ]

}

It shows up in airtable this way:

For each of these sensors, I would like to create a new record with the name of the sensor and the temperature. Any help would be appreciated.

ScottWorld · ‎Sep 15, 2022

There are only 2 ways to loop through JSON data and create a new record in Airtable for each record in your JSON data:

Write a custom JavaScript to process the data. I don’t know JavaScript, so somebody else would need to guide you there.
Use an external automation tool that offers webhooks, like Make.com. Send your data to a webhook there, and Make will automatically loop through all the JSON records on your behalf so you can create individual records in Airtable. (Make might also natively support whatever app is providing you with that JSON data.)

kuovonne · ‎Sep 15, 2022

Can you proxy the JSON data through a different service to flatten the data? Otherwise you are going to have trouble with the Airtable receiving webhook accessing all of the body.

J_M · ‎Sep 15, 2022

I don’t know JavaScript either. I did try make.con but it only works with the first record, it doesn’t keep going with the others. Perhaps there’s a setting in missing?

ScottWorld · ‎Sep 16, 2022

Make definitely works with all the records. You may need to use the Parse JSON tool or the Iterator tool to help you get all the records.

Alexey_Gusev · ‎Sep 17, 2022

it’s a tricky task for first time, but last weeks I had todo a lot of table ‘rotations’, so it’s not so much code needed

put each value in separate variable, try to not add spaces and extra chars, because then array will be converted to string.
I did for 4 properties, for example, you may add all or choose any you want and even no need to change code.

You can use output variables to create records at next step. I mean, 3 next create record steps ))

Source:

const {id,...rest}=input.config();
const msg={id,...rest}
const keys=Object.keys(msg)
const sensors=id.map((o,ix)=>(Object.assign({},...keys.map(k=>({[k]:msg[k][ix]})))))
sensors.forEach(s=>(output.set('sensor_'+s.id,s)))

or, you can match variable names with field names, and if fields are simple text, substitute last line (sensors.foreach...) by write command, and put it to table:


await base.getTable('NAME').createRecordsAsync(sensors.map(s=>({fields:s})))

J_M · ‎Sep 17, 2022

I was able to get it done with the parse json tool and the iterator.

J_M · ‎Sep 17, 2022

Thank you for the help. I was able to get it done with make, but I’m going to try scripting as well since the native solution would be the most robust.

ScottWorld · ‎Sep 17, 2022

That’s great to hear! Make is the best! :grinning_face_with_big_eyes: