A glaring deficiency in Quickbase 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 Quickbase 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 Quickbase 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 hope the development team at Quickbase will add more robust matching criteria to summary fields before too long.
Watkyn develops custom applications to manage and improve business processes, as well as offers Quickbase consultation and training services. To learn more, contact us today.