SELECT "SHORT_NAME" ||' - '||"NAME", "PRIMARY_KEY" FROM "TABLE_NAME" ORDER BY "SHORT_NAME" ASC
Villeroy wrote:
- Code: Select all Expand viewCollapse view
SELECT "SHORT_NAME" ||' - '||"NAME", "PRIMARY_KEY" FROM "TABLE_NAME" ORDER BY "SHORT_NAME" ASC
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.
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.
If "schmidt da" is entered, "Daniel Schmidt" shows up.
SELECT "surname"|| ',' ||"forename"|| ' - '|| "birth date" AS "Visible", "ID" FROM "Persons" ORDER BY "forename", "surname", "birth date"
Schmidt,Fred - 1939-10-20
SELECT "ID"|| ' ( '|| "surname"|| ',' ||"forename"|| ' - '|| "birth date"|| ')' AS "Visible", "ID" FROM "Persons" ORDER BY "ID", "forename", "surname", "birth date"
13059 (Schmidt,Fred - 1939-10-20)
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)
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.
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,
SELECT "Visible", "ID" FROM "Table" WHERE "Text" LIKE '%'|| :Search_Text ||'%'
Villeroy wrote: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 wrote:As a result of the solution under 1. a new issue arose
3. Show Addresses in a ListBox format
c. UNSUCCESSFULL so far.
Villeroy wrote:dreamquartz wrote: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.
Villeroy wrote: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.
Users browsing this forum: No registered users and 2 guests