Searching anywhere in a pulldown list

Discuss the database features

Searching anywhere in a pulldown list

Postby dreamquartz » Sat Dec 08, 2018 12:30 am

Hi All,

Does anyone know how program in SQL to be able to search for any character-combination by typing them in a Pulldown List?
I have the following principles:
1. Company Name (as provided by the company itself): Business to be Quick; I want to find the company by starting to type 'quic' in the Pulldown List
2. Company Name that formally consists of many words, but is abbreviated as i.e.: TVGSG (the abbreviation is part of the Company Name in the database) ; I want to be able to find the company by starting to type 'tv' in the Pulldown List

I know of example software (database-based; back-end not known) where it is possible.

Dream
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 680
Joined: Mon May 30, 2011 4:02 am

Re: Searching anywhere in a pulldown list

Postby Villeroy » Sat Dec 08, 2018 1:32 am

Code: Select all   Expand viewCollapse view
SELECT "SHORT_NAME" ||' - '||"NAME", "PRIMARY_KEY" FROM "TABLE_NAME" ORDER BY "SHORT_NAME" ASC
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26631
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Searching anywhere in a pulldown list

Postby UnklDonald418 » Mon Dec 10, 2018 6:35 pm

I uploaded a demonstration database. While not exactly what you want, it does something quite similar.
It uses a filter table to with a text field to store a string and an integer field to store a listbox selection.
There is a view that generates a list of potential matches based on the filter string. The view is used as the data source for a listbox query.
There are two forms,
one that uses push button controls
and another that uses 2 macros to replace the push buttons.
Demo36a_CustomerSelect.odb
(37.29 KiB) Downloaded 30 times
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1122
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Searching anywhere in a pulldown list

Postby dreamquartz » Thu Dec 27, 2018 11:46 pm

Villeroy wrote:
Code: Select all   Expand viewCollapse view
SELECT "SHORT_NAME" ||' - '||"NAME", "PRIMARY_KEY" FROM "TABLE_NAME" ORDER BY "SHORT_NAME" ASC

I have the abbreviation of the name in "NAME", placed in brackets if available; something like: Good Company Cakes (GCC).

You are suggesting to place the abbreviation into a different record, but I would like to create something like what the https://www.dastelefonbuch.de/ is using.
If "da" is entered, then Daniel Schmidt, and Daniela Schrank will show up.
If "schmidt da" is entered, "Daniel Schmidt" shows up.
This is the principle is what I like to accomplish.

Can the principle behind this be explained?

Dream
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 680
Joined: Mon May 30, 2011 4:02 am

Re: Searching anywhere in a pulldown list

Postby dreamquartz » Thu Dec 27, 2018 11:48 pm

UnklDonald418 wrote:I uploaded a demonstration database. While not exactly what you want, it does something quite similar.
It uses a filter table to with a text field to store a string and an integer field to store a listbox selection.
There is a view that generates a list of potential matches based on the filter string. The view is used as the data source for a listbox query.
There are two forms,
one that uses push button controls
and another that uses 2 macros to replace the push buttons.
Demo36a_CustomerSelect.odb


Please see my response to Villeroy.

Dream
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 680
Joined: Mon May 30, 2011 4:02 am

Re: Searching anywhere in a pulldown list

Postby UnklDonald418 » Fri Dec 28, 2018 3:12 am

Villeroy's suggestion places the abbreviation in the same record, but in a different column, “short name”.
HSQLDB, like most database engines, has some fairly basic string matching capabilities, i.e. LIKE, INSTR and LOCATE. For a complete list see the HSQL documentation
I have the abbreviation of the name in "NAME", placed in brackets if available; something like: Good Company Cakes (GCC)

If "da" is entered, then Daniel Schmidt, and Daniela Schrank will show up.

The demonstration I uploaded can handle both of those situations.
If "schmidt da" is entered, "Daniel Schmidt" shows up.

to do that things get more complicated.
There are some regular expression functions available in HSQL, but I'm not sure those could do the sort of parsing required of that search.
One approach would be to have a search table with all the iterations you can think of, each pointing back to "Daniel Schmidt"
Another approach would be to parse the string into separate sub-strings, then first search for "schmidt" and then search those results for "da". That would probably require a macro.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1122
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Searching anywhere in a pulldown list

Postby Villeroy » Fri Dec 28, 2018 5:26 pm

Yep. My above query resembles how I pick a distinct person from a list where a name and a short name is stored.
I have another list of 4000 persons with the ususal forename, surname, birth date fields but no short names. There are persons in my list with the same forename and surname but not with the same birth date. I added an unique index on forename, surname, birth date so the table will not accept a second person with the same forename, surname, birth date. If that ever happens, the "unique constraint error" indicates that this is either the same real world person as the one which is already stored or we have to store some variation of name or birthday for a second entry.
The list box of the persons list goes like this:
Code: Select all   Expand viewCollapse view
SELECT "surname"|| ',' ||"forename"|| ' - '|| "birth date" AS "Visible", "ID" FROM "Persons" ORDER BY "forename", "surname", "birth date"

Schmidt,Fred - 1939-10-20

The comma marks the end of a surname when typing a name into the box. This allows me to pick the right person even when there is a second Fred Schmidt with another birth date.
It may be helpful to use a multi-line list box instead of a drop down so you see similar entries while typing.
You can also hit Alt-Down before typing into a drop-down list in dropped down state.

The above list box lets me find a person by name.
If I happen to know the person's ID number I use this list box:
Code: Select all   Expand viewCollapse view
SELECT "ID"|| ' ( '|| "surname"|| ',' ||"forename"|| ' - '|| "birth date"|| ')' AS "Visible", "ID" FROM "Persons" ORDER BY "ID", "forename", "surname", "birth date"

I type the ID and see the unique person data in braces behind the ID as a confirmation.
13059 (Schmidt,Fred - 1939-10-20)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26631
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Searching anywhere in a pulldown list

Postby dreamquartz » Tue Jan 01, 2019 10:43 am

Villeroy wrote:Yep. My above query resembles how I pick a distinct person from a list where a name and a short name is stored.
I have another list of 4000 persons with the ususal forename, surname, birth date fields but no short names. There are persons in my list with the same forename and surname but not with the same birth date. I added an unique index on forename, surname, birth date so the table will not accept a second person with the same forename, surname, birth date. If that ever happens, the "unique constraint error" indicates that this is either the same real world person as the one which is already stored or we have to store some variation of name or birthday for a second entry.
The list box of the persons list goes like this:
Code: Select all   Expand viewCollapse view
SELECT "surname"|| ',' ||"forename"|| ' - '|| "birth date" AS "Visible", "ID" FROM "Persons" ORDER BY "forename", "surname", "birth date"

Schmidt,Fred - 1939-10-20

The comma marks the end of a surname when typing a name into the box. This allows me to pick the right person even when there is a second Fred Schmidt with another birth date.
It may be helpful to use a multi-line list box instead of a drop down so you see similar entries while typing.
You can also hit Alt-Down before typing into a drop-down list in dropped down state.

The above list box lets me find a person by name.
If I happen to know the person's ID number I use this list box:
Code: Select all   Expand viewCollapse view
SELECT "ID"|| ' ( '|| "surname"|| ',' ||"forename"|| ' - '|| "birth date"|| ')' AS "Visible", "ID" FROM "Persons" ORDER BY "ID", "forename", "surname", "birth date"

I type the ID and see the unique person data in braces behind the ID as a confirmation.
13059 (Schmidt,Fred - 1939-10-20)

K,

Would like to extend the possibilities.
I would like to be able to do something like this:
The record contains: "The Quick Brown Fox Jumped Over The Lazy Dog".
I want to type any part of the record, and all records will show, including the whole content.
The more characters entered the bigger the chance to find the actual record.

The reason is that there are now so many entries, that it becomes tedious to go through everything that starts with e.g.: "The Quick".
The Client might remember just "Fox", which could help to narrow down the search.

Dream.
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 680
Joined: Mon May 30, 2011 4:02 am

Re: Searching anywhere in a pulldown list

Postby dreamquartz » Tue Jan 01, 2019 10:45 am

UnklDonald418 wrote:I uploaded a demonstration database. While not exactly what you want, it does something quite similar.
It uses a filter table to with a text field to store a string and an integer field to store a listbox selection.
There is a view that generates a list of potential matches based on the filter string. The view is used as the data source for a listbox query.
There are two forms,
one that uses push button controls
and another that uses 2 macros to replace the push buttons.
Demo36a_CustomerSelect.odb


Indeed something like this, but in one field.
I know of many instances where this does exists.

I might have to consider convincing the Client to think Macros, but am hoping a different direction......

Dream
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 680
Joined: Mon May 30, 2011 4:02 am

Re: Searching anywhere in a pulldown list

Postby RoryOF » Tue Jan 01, 2019 11:10 am

A brief thought - I think you would need to Index each word of your entries for the list, then search on the index as the user enters the index - I don't see a pulldown working in this case; how your search interface would return partial matches from the index and transition into a pulldown or selection process I do not see at present.

I think this would render your existing pulldown method obsolete and require design of a new interface. I suspect also it might require much CPU power to handle the interface - the index lookup should be simple and low power.

If I wished to do this (I don't - I live a very simple and happy life!) I would check Knuth, "The Art of Computer Programming", Vol 3, Searching and Sorting.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 28755
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Searching anywhere in a pulldown list

Postby UnklDonald418 » Wed Jan 02, 2019 4:59 am

Indeed something like this, but in one field.

There is no mention of listboxes in the HSQLDB documentation because they are not part of SQL. A listbox is part of the Base front end and as such, they were not designed to do what you ask. The query required by a listbox has to be processed by the Base parser so there is very little flexibility there.
I know of many instances where this does exists.

The examples you mention were probably implemented in a commercial database, so it becomes a matter of how much are you prepared to pay for that feature. Just one of those trade-off's.

I might have to consider convincing the Client to think Macros,

It is wise to limit the use of macros to only those situations where something can't otherwise be achieved, but a strict no macro policy limits them to an Embedded database, which is also risky. Another trade-off.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1122
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Searching anywhere in a pulldown list

Postby Villeroy » Wed Jan 02, 2019 8:56 am

A filtered list box with someting like:
Code: Select all   Expand viewCollapse view
SELECT "Visible", "ID" FROM "Table" WHERE "Text" LIKE '%'|| :Search_Text ||'%'
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26631
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Searching anywhere in a pulldown list

Postby Villeroy » Wed Jan 02, 2019 3:35 pm

If you want to search distinct records by an arbitrary search string, you set up a filter form with a text box for the search string and a subform showing all records containing the substring. This may include more than one record since fuzzy search strings don't yield uniqueness. The resulting set of one or more records is displayed in a table control of a filtered subform, from there you pick the right one (if there are more than one) and transfer the record ID into a sub-subform where you can edit the details of this distinct record. Or you transfer it to a sub-subform where you insert a new record with the selected ID as foreign key. You are free to build two sub-subforms for editing and insertion of a new record.
[Filter Form] -- String like %Search% --> [Matching Form] -- FID=Match.ID --> [Edit Form], [New Form]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26631
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 1 guest