Need to Return Records with an Empty Field

Ask the community Formulas
#1

I hope that this is not a topic that has been answered already but in my searching what I found is that there are several ways to filter out empty fields but in my use case I want to keep the records that are empty in certain fields.

I have had success using NOT({Field Name} = " ") and some success using IF and SEARCH in various forms but they are all getting rid of the fields that are empty rather than keeping the ones that are empty. For clarity’s sake let’s say the records have a likes icecream and the people who don’t like icecream have nothing in that field. I want to be able to filter out the ones that do have something and keep only the ones that don’t.

If anyone has any suggestions it would help a lot (also I am doing this via API).

#2

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.

#3

So just do a search for “null”?

#4

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. :wink:

#5

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()?

#6

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?

#7

Trying to use filterByFormula in the call to avoid making a new view.

#8

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:

image
image726×518 21 KB

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.

image
image980×178 8.55 KB

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

image
image768×728 40.3 KB

#9

Awesome it worked :slight_smile:

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

1 Like
#10

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.

1 Like
#11

I also got it working via Shortcuts on iOS so thats awesome :slight_smile: