Nov 18, 2020 06:32 PM
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).
Solved! Go to Solution.
Nov 22, 2020 11:30 AM
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.
Nov 19, 2020 05:19 AM
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.
Nov 22, 2020 06:31 AM
So just do a search for “null”?
Nov 22, 2020 06:49 AM
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:
Nov 22, 2020 07:34 AM
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()?
Nov 22, 2020 08:06 AM
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?
Nov 22, 2020 08:58 AM
Trying to use filterByFormula in the call to avoid making a new view.
Nov 22, 2020 11:30 AM
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.
Nov 23, 2020 08:56 AM
Awesome it worked :slightly_smiling_face:
I figured I was likely over thinking the forumla and thank you for your help.
Nov 23, 2020 09:01 AM
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.