[Solved] Searching anywhere in a pulldown list

[dreamquartz]

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

[Villeroy]

Code: Select all   Expand viewCollapse view

SELECT "SHORT_NAME" ||' - '||"NAME", "PRIMARY_KEY" FROM "TABLE_NAME" ORDER BY "SHORT_NAME" ASC

[UnklDonald418]

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 106 times

[dreamquartz]

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

[dreamquartz]

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

[UnklDonald418]

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.

[Villeroy]

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)

[dreamquartz]

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.

[dreamquartz]

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

[RoryOF]

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.

[UnklDonald418]

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.

[Villeroy]

A filtered list box with someting like:

Code: Select all   Expand viewCollapse view

SELECT "Visible", "ID" FROM "Table" WHERE "Text" LIKE '%'|| :Search_Text ||'%'

[Villeroy]

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]

[dreamquartz]

K,

Coming back to this one, because it pops up over and over again.
Left it open, because there were other priorities, for development.

I am trying to replicate the functionality of: Record Search->Settings->anywhere in the field.
This allows the User to enter an arbitrary string, for the User to quickly lookup info in large tables.

The decision is made to potentially use a macro for the functionality.
It will improve the User's ability for data entry significantly.

Dream

[Villeroy]

The question has been answered in many ways several times. If the answers are not satisfying, you have to develop your own thing on your own since you do not eventell us what is wrong with our suggestions.

[dreamquartz]

The question has been answered in many ways several times. If the answers are not satisfying, you have to develop your own thing on your own since you do not eventell us what is wrong with our suggestions.

Figured out what I forgot to indicate.
Attached an example of the database, where a form is used to select pre entered addresses for pre entered Companies.
tAddressBare holds the information about the building location.
tAddress holds the relation between tAddressBare and tCompany.
The views vAddress and vAddressBare are used to standardize the views of the addresses, and are created by their namesake Queries.

By filtering, using a filter table, the User is able to show all addresses related to the specific Company.
The selection of the required address is placed in tResult.

The form fCompanyAddres provides the User to select and store the address needed.

All the suggestions provided are using a textbox for the Company, but because the CompanyName is already entered, it makes sense to use a listbox.
My problem is therefore related to the fact that a listbox is used in stead of a textbox.

Dream

[dreamquartz]

Thanks to Villeroy I was able to (partially) solve the issues I had.
I used a method that includes a form (fCompany) where a Query is used to determine the content.

The issues were:
1. In case when the name of the Company is only partially known, like 'WS'; try to use that as an search criterium.
a. SUCCESSFULL

2. Simplify entry of data
b. SUCCESSFULL

As a result of the solution under 1. a new issue arose
3. Show Addresses in a ListBox format
c. UNSUCCESSFULL so far.

@Villeroy;
I have tried to figure out what a 'filtered listbox' would look like in my situation, but I was unsuccessful.
I could create your provided answer in a form (fCompany), because that was the only thing I could come up with.
I could also not find any other information about a 'filtered listbox' up to now.

Attached is the (partial) solution that works for me @ this point.

Dream

[Villeroy]

As a result of the solution under 1. a new issue arose
3. Show Addresses in a ListBox format
c. UNSUCCESSFULL so far.

A list box selects a chosen value into a field (typically a primary key field). If you want to show subitems of a selected item in list format, a subform with table control is the way to go.

[dreamquartz]

As a result of the solution under 1. a new issue arose
3. Show Addresses in a ListBox format
c. UNSUCCESSFULL so far.

A list box selects a chosen value into a field (typically a primary key field). If you want to show subitems of a selected item in list format, a subform with table control is the way to go.

Understanding the concept.....
The way I tried to solve my initial (# 1.) problem, is that the only way to create something like this, or is there some other way?
I have now tried all your suggestions, using my solution a., but to no avail.
The listboxes can be created, but are 'greyed' out.

Dream

[Villeroy]

Base does not offer any other way when it comes to many-to-many relations. There is only one way.You have a list of companies, a list of addresses and each company belongs to one or more address(es). This is a many-to-many relation.
The one and only valid recipe to depict such relations in a Base form is this: viewtopic.php?f=13&t=82396&p=381497#p381497
Resistance is futile. you won't find any "alternative ways".

First I had to clean up your database.
I removed the primary key from the linking table and made the 2 foreign keys a compound primary key.
Then I linked the tables accordingly in the relations designer.
I created a list box query showing the details of an address in the first field and with the PK as second field.
Finally I started the form wizard, created a companies form with a tabular subform linked according the existing database relation.
After form creation I replaced the subform's address ID column with a list box (the stupid wizard never creates any list boxes) with the query as source of the list box Now the subform displays all the addresses of a selected company in a grid of list boxes.

Since you may want to insert/edit/remove an address, I added a sub-subform where you can edit the selected address of the selected company. This form is simply linked to the address list, always showing the one and only address selected in the parent form because it is linked through the same common identifier. It can also be used to insert a new address which appears in the column of list boxes after refreshing either the list box or its form.

[dreamquartz]

Base does not offer any other way when it comes to many-to-many relations. There is only one way.You have a list of companies, a list of addresses and each company belongs to one or more address(es). This is a many-to-many relation.
The one and only valid recipe to depict such relations in a Base form is this: viewtopic.php?f=13&t=82396&p=381497#p381497
Resistance is futile. you won't find any "alternative ways".

First I had to clean up your database.
I removed the primary key from the linking table and made the 2 foreign keys a compound primary key.
Then I linked the tables accordingly in the relations designer.
I created a list box query showing the details of an address in the first field and with the PK as second field.
Finally I started the form wizard, created a companies form with a tabular subform linked according the existing database relation.
After form creation I replaced the subform's address ID column with a list box (the stupid wizard never creates any list boxes) with the query as source of the list box Now the subform displays all the addresses of a selected company in a grid of list boxes.

Since you may want to insert/edit/remove an address, I added a sub-subform where you can edit the selected address of the selected company. This form is simply linked to the address list, always showing the one and only address selected in the parent form because it is linked through the same common identifier. It can also be used to insert a new address which appears in the column of list boxes after refreshing either the list box or its form.

Thank you so much for the clarification.

I used the info to update the forms, and it works now.

Dream