Share via

Filtering for an NA date in a PWA View

  • Article

This post is for the person named Santhiya that commented on my “Server-side Calculation of Custom field formulas” post. I accidentally deleted it instead of approving it. I have it in my notification email so here is what they asked:

How do we use to check whether the Date fields contains values or not.

IN Project professional they are using

" Actual Finish = NA".

how can we use in PWA site while creating view with Filtering option.

Thanks!

Here is the answer:

As you found you cannot filter for “equals NA”. You also cannot filter for '”contains /”. Both of these give you an error when you try to save your filter.

What you can do is test to see if the value in the date field is greater than 1/1/1984. If it is greater than 1/1/1984 then the field contains a valid date.

So “is greater than 1/1/1984” will return only rows where the date value is NOT “NA”. The hard part is that you cannot use this idea to have a filter only show rows there there is NOT a date. The filtering mechanism does not seem to understand the idea of NA.

So the workaround for this is to do a ‘does not contain’ filter for a character that you know will always be in your dates but NOT the forward slash character (which throws an error.) If your dates for this field will always be in this century and you always use regional settings that show four digit dates then you can use this filter:

“does not contain 2”

This filter will return all rows that do not have a date in them if you use 4 digit years and your dates are always greater than 12/31/1999.

 

I hope this helps. Sorry I deleted your comment Santhiya. :-)

Comments

  • Anonymous
    January 01, 2003
    The comment has been removed
  • Anonymous
    June 18, 2009
    Hello, To detect NA-date values, I use the value 4294967295: in case [your date] is NA, [your date]=4294967295 returns true!!! This way you don't have to choose characters that are always in your date. This number equals 2^32-1. Maybe -1 will do the job too, since it equals 2^32-1 in a 32-bit system but I didn't try it until now. Regards Wim