[Solved] SQL Query: how to conditionally inc./excl. columns?

[nathany]

I'm new to Base and SQL and thus I am sure this question is really easy.
I'm trying to define a Query in which the criteria of whether or not to include a column's value is based on the *value* of the column. e.g., if the value of the column = 'x', don't display the column at all.
The basic scenario is that I have many optional columns of data which may or may not be filled-in. When the user queries for the row of data, I only want to return information which is actually filled-in.

To clarify, if the column should be excluded I still want the REST of the row's columns' displayed (unless they also have not been filled in with content).
When I try to use the GUI for building a query, it seems that I can only exclude displaying the ENTIRE row based on evaluating some criteria for a column's value.

Let me know if this question is not clear.

Thanks!

[eremmel]

That is not possible, and very uncommon requirement. Normally you solve this in the presentation layer (your form or report). But what do you when an user wants to 'enable' that hidden field by adding something into it.

Normally this type of requirements come from a 'wrong' data model.

Keep in mind that Base is a nice tool, but not ready to implement highly sophisticated reports or user-forms

[nathany]

I appreciate the feedback. Thinking about my scenario a bit more, consider the following challenge/description of the problem...

For a given row that I want returned by the query or report, There are a series of fields. In the case where I have n-rows returned by the query,
if for *every row*, certain fields indicate that they are not being used, then for this specific query/report, I would like to simplify the presentation
and "simply" exclude those fields/columns. (ie, I would only want to do this if the ENTIRE set of results all share the same characteristic in that
a certain value or multiple values are not being used)

This would seem to require me to execute some basic logic which can evaluate the entire results of the query in order to determine whether
a specific column should be "visible". Is this technically possible?

Thanks again,


Nathan

[Villeroy]

I use a report for a check list with a time stamp, 5 check boxes and an annotation field (at 2012-10-22 13:54 this and that has been checked).
Every now and then data are printed through a spreadsheet having a cell style with number format "";"";"x" to print an "x" for a TRUE and nothing for FALSE.
A typical report looks like this:

Code: Select all

Time	Room	Task1	Task2	Task3	Initials	Notes
Mo 05.12.11 18:18	Raum 3			X	TB	
Di 06.12.11 11:01	Raum 1	X	X		TB	
Di 06.12.11 11:02	Raum 2	X	X		TB	
Di 06.12.11 11:04	Raum 3	X	X		TB	
Di 04.10.11 18:13	Raum 2			X	BM	

The true values for the 3 tasks are booleans.

[nathany]

Villeroy -

Thanks for this idea. I was hoping though that there was some technique/approach natively with a query which would enable to conditionally make a column visible or not, based on evaluating the values in that column for the ENTIRE set of rows returned by the query. ie, if every row's column-value is something identical which means that I don't want to see it, I'd like to exclude the column from every returned-row's result.

Any way you can think of doing this without the additional post-processing idea you mention (via spreadsheet) ?

[Villeroy]

This is not a problem on query level since SQL does not support anything like that and your users should work with forms rather than naked query grids. In a form you may write a macro to show/hide a form control conditionally.

[nathany]

Ok - I appreciate the clarification and suggestion -- I'll pursue the Form-plus-macro direction.
Thanks,