Skip to main content

I created a table called Items (household items).  In Items, there is a partially filled-out field, PurchaseDate, that is some items have a PurchaseDate; other items don't have a Purchase Date (field is left blank).  I want to run a filter of those items with a blank PurchaseDate:  Is the best way to do this is to create a formula field, named noPurchDate: LEN(DATESTR({PurchaseDate))=0 such that if the PurchaseDate is not empty, noPurchDate returns FALSE, and if PurchaseDate is empty, then NoPurchDate returns TRUE? Is there an easier way to get this result? 

Another more "normalized" way of handling this might be to create a separate table, PurchasesWithDates linking Items to PurchasesWithDates.  How does one create a filter of Items with no link to PurchasesWithDates?

 

DATESTR() seems not to be able to handle empty sets nicely, but BLANK() does, so instead of LEN(DATESTR()), here's what I found does work:

IF({PurchaseDate} = BLANK(), "hasNoDate", "hasDate"). 

If the name of the field name is hasPurchaseDate, then IF({PurchaseDate} = BLANK(), FALSE(), TRUE()) works OK;  It would be even better if AirTable had a Logical primitive value or the resulting format would be T/F or perhaps a checkbox. 

This is okay too

IF({PurchaseDate}, "hasDate", "hasNoDate")

Also, have you tried filtering date fields with "is empty"?

This is okay too

IF({PurchaseDate}, "hasDate", "hasNoDate")

Also, have you tried filtering date fields with "is empty"?


Sho, 

Filtering date fields with "is empty" does do the trick.  Forgot to croll al the way down to find that option!  (How embarrassing!)  Thanks for your tips.

Reply


Terms & Conditions