DaDaBIK
This forum is devoted to the discussion about the software DaDaBIK database front-end
the PHP Database application generator for MySQL, PostgreSQL and SQLite; also available for Wordpress.

Welcome! Log In Create A New Profile

Advanced

Using a self-referential DISTINCT query for a single select field

Posted by wallaby9 
Using a self-referential DISTINCT query for a single select field
September 26, 2017 08:39AM
Hi Eugenio,

I like the technique used in some other database systems where you can use a "select distinct" query on the same table to fill a selection list for one of its fields.

For example, say these are the records in our Cheeses table:

ID / COUNTRY / CHEESE
1 / France / Camembert
2 / France / Brie
3 / Italy / Pecorino
4 / Italy / Gorgonzola
5 / Italy / Tallegio
6 / England / Cheddar
7 / England / Stilton

So in my Cheeses form I would like to populate a single-select drop-down list box with the values "France", "Italy" and "England", so when new Cheeses are added, the country can be selected from the list.

This can be achieved by creating a View like this:

create view CountryList as (select distinct Country from Cheeses)

and assigning it as the Lookup table name for the Country field.

BUT if you want to allow the user to choose a value that isn't in the list, it obviously fails because the view is not updateable.

So my question is, is there a way to populate the drop-down list from the same table as the form is based on, but not have it try to insert a record into the table it got the list from? Because next time a new record is added, the list will have the new value as it will be part of the select distinct (if you follow me).

In other words, for the People form, it would be very cool to be able to set an SQL query for the source of the list, being "select distinct country from cheeses order by 1".

That's it. No writing the new value in to the lookup table, as the lookup table is itself.

(I know keeping multiple text strings rather than using a lookup table is not best practice, but sometimes the database schema is forced upon you).
Re: Using a self-referential DISTINCT query for a single select field
September 26, 2017 09:53AM
Hello,
this is not possible, lookup fields assume some normalization in your schema.
The "distinct" part is probably not hard to implement, but then you have to deal with the insertion of the custom country in the same table which is not trivial.
If I were you, if you really can't normalize the schema, I would run periodically (like every 5 minutes) a script that keeps updated a countries table (a real table, not a view); this is not an optimal solution but it might work.

Best,

________________________________________________________
Eugenio |\.-./| DaDaBIK developer and project admin
Re: Using a self-referential DISTINCT query for a single select field
September 26, 2017 03:48PM
I think I didn't explain well, sorry.

The query is simply read-only, just to populate the drop-down list. If you pick a value from the list, the update uses that value as a string (not a lookup) to insert into the table.

If on the other hand you enter a new value (not in the list) it simply inserts that value instead, and so the next time you enter that form, the new value will be present in the list.

From my Cheeses example again...

You'd need a new type of listbox source in the "Field type specific parameters" section, called maybe "lookup query".

Set that to "select distinct country from cheeses order by 1"

Set the field to allow entries not in the list to be provided.

At display time, load the selection box using that query.

At save time, take whatever string is in that field, either the selected list item, or the text box if they chose to add a new one, and build the insert or update query with it.

Hopefully that makes more sense.

For tables with few records or where blistering speed is not an issue, this is actually a very quick way of creating an extensible pick-list without the development overhead of having to create a separate lookup table. Ok that's not much, but if you have the facility I guarantee you will use it smiling smiley
Re: Using a self-referential DISTINCT query for a single select field
September 27, 2017 02:22AM
Hello,
I understood the situation, I will think about it but as I said it is not a trivial implementation and considering it is a not very common situation I don't see it in the near future development plan.

The time needed to crate a separate lookup table is normally well spent smiling smiley

Best,

________________________________________________________
Eugenio |\.-./| DaDaBIK developer and project admin
Sorry, only registered users may post in this forum.

Click here to login