SELECT ... WHERE ... by formulae in spreadsheets

[Lupp]

No actual question but a request for comments on probable solutions and some functions used for them. Regard note 2 below, please.

Questions aiming at a solution for queries by formulae in data keeping spreadsheets were many and will come again.
There is also a tutorial touching this topic in the 'Tutorial' section of this forum.
The most frequent suggestions were: Use a database, Use a filter, and in specific cases: Use the Data Pilot ("Pivot Table...").
This is good advice. It will, however, be rejected again and again. And there may be a few cases where this meets the needs.

The most recent question of the type to date was: viewtopic.php?f=9&t=85491. I also think it's another case where one of the above advices should apply.

Nonetheless I take this rather simple example as an opportunity to request for comments on a solution by formulae that may also come in one day from those using Calc as a free (no cost) surrogate for Excel. As I was told there is an "Excel 2016" which introduced some new functions, among them TEXTJOIN and assuming there will also be a reverse function one day, this will offer a new way to produce SELECTIONs WHERE by a single array formula in uncomplicated cases.

Since I had already implemented the needed functions in BASIC for other reasons, I demonstrate the mentioned method (in 2 variants) now in the attached example. Interested contributors to this form may check it (and the code). I would be happy to get comments.

Please note:
1. The solution commented as "better structured" is obviously inefficient. The efficient variant is the one producing the output to a locked range.
2. The example is thought to be opened with LibO 5.1 or higher. Using AOO or an old LibO would require a workaround inside one of the functions.

[Villeroy]

How much easier it is with a relational database and a form! You forget that this is only a tiny part of a much bigger database. I think it is about role games or something. A spreadsheet has no mechanism to ensure referencial integrity and referencial integrity is the underlying problem: select existing items from another table into this table.

Other solutions for this particular little problem filtering the X-marked records:
-- The so called "advanced filter" can be used with named criteria ranges analog to database queries. Then call the filter dialog and choose the named criteria set you are interested in.
-- You can connect a database document to the spreadsheet and add a trivial query like:

Code: Select all

SELECT * FROM "database_range" WHERE "selection" = 'X' ORDER BY "Value" DESC

[Base treats Calc's db-ranges are treated as tables so you don't have to put every table on a separate sheet]
Then drag this query from the data source window into another area of your spreadsheet. For mere filtering and sorting in one go a most simple SELECT...FROM...WHERE...ORDER BY syntax applies to spreadsheet lists and it so much easier and more powerful than anything else. In the WHERE clause you can use named parameters so the user can be prompted for criteria input almost effortless. All this is at hand since the days of StarOffice5 and it is very easy to use.

[Lupp]

Thanks Villeroy.

I never designed a database for actual use, but I created some of them since about 1995 (ok, that was dBase then, I think) to get a bit better informed about their working. That was interesting as nearly everything in the world is, of course, and I needed to be informormed for reasons of profession. I learned to hate databases (just a bit) when I had mandatorily to use two of the tribe for a few years without access to the structure but with responsibility for the quality of creative work based on the data. That were databases of a slightly outdated kind, but that wasn't the point. Our secretaries could handle it for their tasks with a hitch here and a little trouble there. The attitude to be fool-proof (without actually achieving that goal) was what me drove mad now and then. But I wrote to you about this yeras ago and wouldn't warm it up. Let me put all this aside now.

Of course I understand your points - and this topic is definitely not meant to encourage anybody to be careless about data integrity, rights management, backupping, reliability, legal issues, and whatever is dominant in many business applications and often only achievable using a database. .

Do you also understand my point of wanting to discuss a variant of getting a specific functionality needed now and then when working with spreadsheets? Not all the usage of spreadsheets where someone might see such a need must necessarily ba a misuse endangering the existence of a company. And I also would like to get sober comments on the implementation of the "new" functions (despite the fact that it is in BASIC - for some other reasons I would prefer to not again discuss in advance). And: Yes, I know that TEXTJOIN is already implemented in LibO. It is in a buggy way, imo. In fact I wrote the respective BASIC code as a "private reference implementation" of TEXTJOIN.

[Villeroy]

Do what you want for your own business. In our small business many office documents are database driven. The documents are used by people who do not know how to use a spreadsheet nor word processor (not even 5% of the features). They enter data into my forms or into the forms of a business application. From time to time they have to open some office document, click here, push there and get a form letter addressed to one client or his insurance, a small data dump on a spreadsheet or a comparison between bank account and invoicing list. Oh, and I could obsolete a lot of paper work. Many to-do lists are stored as database tables on one server accessible from 6 clients. When did anybody clean the air filter of this apparatus? OK, it was Anna's turn last week. Let's do that now and then select the task from this list box, my initials from that box and hit OK. The best thing is that everything works since many years with close to zero macro code and no maintainence except backups. Nobody has the time and patience to navigate through sheets, copy around formulas, expand references or struggle with text vs. number.

[Lupp]

(@Villeroy)

I also discourage questioners to use Calc for any tasks of datakeeping and data mangement - especially for business. The request here was not to provide means for a risky behaviour with this respect.

The most recent topic in the Calc forum you may consider the trigger for my primary post here was viewtopic.php?f=9&t=85491. As I learned from the OQ there it was not about managing a business but about some kind of (adventure?) gaming, and I judged the task was roughly similar (though much more restricted) to some optimisations I had solved with the help of spreadsheets in the far past. Thus I was interested. After all I am retired as you know, and sometimes looking for a special kind of puzzle. OK?

Tasks requiring small selections (in the above mentioned example lists of 11 items) often occured casually here and there in my experience when concerned with completely different questions for wich spreadsheets obviously were the appropriate tool. And where I never would consider to create a database - neither for the main task nor for the casual one.

We may restrict quarreling about the spreadsheets vs. databases case to those topics where it matters.
I would want to withdraw my above answering post if nobody objects.