Why is this JSON return value not working?

AppSheet AppSheet Q&A
1

I’ve got a table called Carriers, which stores records of trucking companies.

I’ve got an API key from the FMCSA database that has all of the federally registered trucking companies.

I’ve got a Bot with a Process called Call Safer. This process calls the API and get’s a JSON response.

I’ve got a second Process called Return Safer. This is supposed to (I think) provide the return values from the JSON response. Problem I have, is it’s not working.

HERE IS AN EXAMPLE OF THE RESPONSE:

{
  "content": [
    {
      "_links": {
        "basics": {
          "href": "https://mobile.fmcsa.dot.gov/qc/services/carriers/2282557/basics"
        },
        "cargo carried": {
          "href": "https://mobile.fmcsa.dot.gov/qc/services/carriers/2282557/cargo-carried"
        },
        "operation classification": {
          "href": "https://mobile.fmcsa.dot.gov/qc/services/carriers/2282557/operation-classification"
        },
        "docket numbers": {
          "href": "https://mobile.fmcsa.dot.gov/qc/services/carriers/2282557/docket-numbers"
        },
        "carrier active-For-hire authority": {
          "href": "https://mobile.fmcsa.dot.gov/qc/services/carriers/2282557/authority"
        }
      },
      "carrier": {
        "allowedToOperate": "Y",
        "bipdInsuranceOnFile": "1000",
        "bipdInsuranceRequired": "Y",
        "bipdRequiredAmount": "750",
        "bondInsuranceOnFile": "0",
        "bondInsuranceRequired": "u",
        "brokerAuthorityStatus": "N",
        "cargoInsuranceOnFile": "0",
        "cargoInsuranceRequired": "u",
        "carrierOperation": {
          "carrierOperationCode": "A",
          "carrierOperationDesc": "Interstate"
        },
        "censusTypeId": {
          "censusType": "C",
          "censusTypeDesc": "CARRIER",
          "censusTypeId": 1
        },
        "commonAuthorityStatus": "A",
        "contractAuthorityStatus": "N",
        "crashTotal": 13,
        "dbaName": null,
        "dotNumber": 2282557,
        "driverInsp": 236,
        "driverOosInsp": 5,
        "driverOosRate": 2.11864406779661,
        "driverOosRateNationalAverage": "5.51",
        "ein": 452570740,
        "fatalCrash": 0,
        "hazmatInsp": 0,
        "hazmatOosInsp": 0,
        "hazmatOosRate": 0,
        "hazmatOosRateNationalAverage": "4.5",
        "injCrash": 6,
        "isPassengerCarrier": null,
        "issScore": null,
        "legalName": "GTC LOGISTICS INC",
        "mcs150Outdated": "N",
        "oosDate": null,
        "oosRateNationalAverageYear": "2009-2010",
        "phyCity": "WHEELING",
        "phyCountry": "US",
        "phyState": "IL",
        "phyStreet": "300 ALDERMAN LN",
        "phyZipcode": "60090",
        "reviewDate": null,
        "reviewType": null,
        "safetyRating": null,
        "safetyRatingDate": null,
        "safetyReviewDate": null,
        "safetyReviewType": null,
        "snapshotDate": null,
        "statusCode": "A",
        "totalDrivers": 87,
        "totalPowerUnits": 86,
        "towawayCrash": 7,
        "vehicleInsp": 76,
        "vehicleOosInsp": 20,
        "vehicleOosRate": 26.3157894736842,
        "vehicleOosRateNationalAverage": "20.72"
      }
    }
  ],
  "retrievalDate": "2024-07-18T14:20:54.963+0000"
}

I am trying to retrieve the “legalName” field from the response and insert it into the [Company Name] column on my Carriers table. I have tried…

[Call Safer].[legalName]

[Call Safer].[carrier.legalName]

[Call Safer].[content.carrier.legalName]

…and none of these are working. I’ve been troubleshooting for hours and hours. Can someone help me identify where my issue is please?

2

I believe the format the FMCSA uses to return a value is not compatible with what Appsheet Webhook bot can handle. (See here)

You might want to use a Call a Script type, call the FMCSA API, reformat the reply (extract what you need) and return the result from there.

1 Like
3

You are correct. I ended up using Apps Script to flatten the response. Thank you.