This just came up recently, and I couldn’t find a similar request outstanding.
Lately I’ve been working on a query generator for a client with some relatively complex database and reporting requirements. Frankly, as the base currently stands, it’s impossible to create a query using filters that meets his needs, as the values to match and the records to report are too distant in the model. To get around that, I’ve created a table he can use to build queries. Each record represents a single atomic query (e.g., ‘which customers bought a white widget in 2016 or 2017?’ or ‘who bought a widget at WidgetWorld in 2017?’). Up to five queries can be defined, and the individual queries can be ANDed or ORed together to create more complex inquiries. Admittedly, it’s a work-in-progress, but so far it seems to be holding up.
I’m about to add more potential variables to the equations — say, salesperson (‘Who bought a widget from John last year?’) or color (‘How many people bought camo widgets in 2012’). Currently, I have a <Make Selection> view that hides all but those fields pertinent to creating a query. I’d like to change that to <Make Selection> and <Advanced Selection>, with several less-frequently used values visible in the latter but hidden in the former.
The concern is that the user may easily switch from <Advanced Selection> to <Make Selection> with a value or values still present in [now-hidden] advanced fields. I already have an {Alert} field that watches for common problems and flags them (for instance, specifying both a generic ‘during year 2017’ and a specific 2017 event [‘Widget World Asia 2017’]), and I’d like to expand it to include such things as values included in hidden fields. If I could do something like
IF(@CurrentView='NormalSelect', ; advanced fields hidden
IF(
OR(
Salesperson!='',
Color!=''
),
'Alert! Hidden fields contain data!'
)
)
then I could watch for such an error (and no doubt help prevent hours of confused troubleshooting).
