How to Create Summary Fields Using OR Criteria
- July 24, 2017
- Posted by: Phillip Dennis
- Category: How-to for Quick Base Developers
A glaring deficiency in Quick Base summary fields is the inability to use OR criteria. The screen prompt doesn’t make this explicit, but when you specify multiple criteria for including child records in your summary field, they are AND conditions. Consider this example:
The summary field above will count the number of items in each purchase order that have lead times greater than six weeks AND are scheduled to be discontinued during the next 90 days.
But suppose the inventory manager or purchasing department wanted a summary of how many items needed to be re-sourced, EITHER because of a too-long lead time OR because the manufacturer had announced that the product was being discontinued?
You can’t do that in the definition of a summary field itself. Which, let’s be honest, is a bit of a nuisance since we’re used to being able to use complex conditional logic in our report filters.
If you’ve run up against this limitation and been unsure how to proceed, here’s one way to work around it. We don’t like this kind of workaround because it’s exactly the kind of thing that causes bloat in Quick Base apps—ad hoc fields that serve no purpose except to work around a specific limitation. With fields like this, it is especially important to leave explanatory comments in your Quick Base formula so that future developers (or you, yourself, a month or two down the road) will understand why this field is here and won’t be tempted to delete it.
All this formula does is move the OR condition testing down into a single ad hoc field in the details table. We have created a formula checkbox field—duly commented by Prathibha—that returns TRUE if either of the conditions is met. Easy.
There’s only one more thing to do: create the summary field we’ve been aiming at all along. Now all we have to do is summarize purchase orders based on the value in the formula checkbox field we just created. If its value is TRUE, it means one of the OR conditions has been met, so we will include it. Otherwise we won’t.
I heard no mention of this made at Empower 2017 in Boston, but I hope the development team at Quick Base will add more robust matching criteria to summary fields before too long.