Need to Return Records with an Empty Field

Hi Matthew, and welcome to the community!

Fields that are empty don’t actually exist in an API result, so they will appear to be null values. Ergo, lacking a value is the same as a vacuum. As such, you need to look for nulls not empty strings.

So just do a search for “null”?

Yes. Imagine a script statement in javascript that tests for null:

let likesIcecream = ( {likes icecream} === null ) ? false : {likes icecream};

The variable is set to false if – and only if – there’s no value in the field. If there is a value in the field, this code fragment assumes that whatever the value may be, it should be used. Lacking a value in the field, the API will return a null value, ergo, the variable will be set to false.

Unrelated Sidebar on Statistical Exactitude…

There should be some consideration paid to the fact that silence about your icecream preference is not necessarily an indication of like or dislike, but that’s a separate conversation. Did the respondent leave the field blank because they were indifferent about the statement “likes icecream”? Or, did the respondent forget to answer the question? These are nuances of data collection that tend to create a bias in your data because you are assuming an empty field is synonymous with an overt and decisive choice, which is not the case at all. :winking_face:

I do agree with your side bar. No response should not equal a negative or absence on the specific item. In my case there is several collumns that represent if someone has acomplished certain miles stones. So it is common to have Milestone 1, Millestone 2, Milestone 3, etc… and they are not nessicarilly linear.

So what I am trying to do is write up a formula that will return the records that are missing a cell value in that field which would mean they have completed that Milestone.

So I assume in this case it would need to be a IF({Milestone 1} = ‘null’) or IF({Milestone 1} = null)? Or would it be someting like IF([Milestone 1}, null)? Or would I use Blank()?

That depends on the API logic. Are you using filterByFormula to find these milestone conditions? Or, is the API request using a view that already has this filtering logic applied?

Okay - filterByFormula (I think) uses exactly the syntax that you would apply if doing so in a formula field. As such, I believe null values are treated as empty strings, so something like:

{Milestone 1} = “”

… would be all you’d need; no IF() would be required because you aren’t trying to apply logic within the filter query; the query itself applies the logic to the entire data set.

Example… give this table:

The following code returns only the incomplete milestones indicated by null values:

//
// query airtable records by filter
//
function testQueryTableByFilter()
{

  // set the filter
  var thisFilter = '{Milestone 1} = ""';

  // fetch the data
  var result   = atQueryTableByFilter_("appAvzbF1dJ9OkgMn", "Airdrop", thisFilter);
  
  // parse the data
  var oData    = JSON.parse(result).records;
  
  // enumerate the items returned based on the filter
  for (var i in oData)
  {
    Logger.log(oData[i].id + " :: " + ((oData[i].fields["Milestone 1"] == null) ? "Milestone 1 Incomplete" : oData[i].fields["Milestone 1"]));
  }
}

//
// query by filter
//
function atQueryTableByFilter_(baseID, tableName, filter)
{
  const options = {
    method: 'GET',
    headers: {
      'Authorization' : 'Bearer ' + cMyAirtableAPIKey,
      'Content-type': 'application/json'
    },
    muteHttpExceptions : true
  };
  var response = UrlFetchApp.fetch(cAirtableAPIEndpoint + baseID + "/" + encodeURIComponent(tableName) + "?filterByFormula=" + encodeURIComponent(filter), options).getContentText();
  return(response);
}

As expected, the logged output includes only the incomplete items.

As expected, the result set is void of any reference to Milestone 1.

Awesome it worked :slightly_smiling_face:

I figured I was likely over thinking the forumla and thank you for your help.

Yes, often we need to do less to make stuff work. This is a common debugging axiom as well - reduce complexity until it cannot be further reduced, and then introduce complexity to determine success boundaries with unit tests at each change.