Views with null in a date field

Ted 3 years ago in Home Portal updated by Jussi Rautio 2 years ago 4

I'm trying to make a view of clients who do not have a value in a custom date field.  I can filter the view by date, but I can't exclude clients who have that date filled in.  Does anybody know a good workaround of if filtering this way is a planned improvement?


I had the same issue. There is no option in the system.


1. Export the list and do it in excel

2. Create a checkbox field which will be checked if the date is null. Create a macro which will update it this automatically. Then you can filter.

3. Connect an excel through HOME-API, and filter it with Power Query.


Yes, these are the kludgy workarounds as there is no filter for "None".


Is sorting by this column not enough?

We do want to do this and sorting isn't enough. We are tracking referrals for partners and created a custom 'referral paid date' field on the project level. A few things came up in our discussion.

1) You can't filter for null value dates. In the case above, I'd like to easily generate a list of projects that I need to pay a referral on. I can export the entire list, and then sort or filter, but that's an extra step I'd like to avoid. A better case is to include a filter that outputs null value date fields only.

2) You can't generate reports on projects that were paid - only invoices. We track our referral partners via a custom field on the client level. 

Example... Acme is our partner and we pay them a XX% referral on business they pass our way. They pass us Zapps as a client. We tag Zapps as an Acme Referral in the custom field on their client record. When we pay referrals to Acme, we can pull all the Zapps projects that were closed, but we can't pull Zapps invoices that were paid - because the custom field is on the client, not the invoice, and there is no way to automatically pull an invoice that contains a specific project. So without a lot of work, we can only pay our referrals on the closed project report, not on paid invoices. 

3) You can't "multiple update" projects. The best way we've determined to track our referral payments is to use the above mentioned "referral paid date" on the project level. If we need to pay our partner referrals on 15 projects, we can't select them all and do a multiple update. We need to open each individual project and update the custom field one by one.

4) You can't hide custom fields from users. Ideally, we'd want to hide this 'referral paid date' custom field from our PMs to have one less potential source of error. However, there is no way in the user permissions to hide custom fields for user groups.

None of these items above are deal breakers, but these are things that are requiring us to create workarounds or more training and work for our staff. I don't know if others have had these or similar issues, but we'd love these to be addressed... one day. :)