InfoQube IM - Community

Submitted by gregory on 2015/02/15 07:16

An earlier forum post, www.sqlnotes.net/drupal5/index.php, "Auto-complete wherever/whenever there's a drop-down menu", suggested that auto-completion should be as near universal as practicable.

In several instances, I have a drop-down list which is derived from SQL (which means that the contents of the list are dynamic and change as values elsewhere in the IQbase change). In the instance illustrated, the drop-down list is very long and it takes far too long to select an item from it. I am continuing to investigate ways of restricting the length of that list. But my task would be considerably easier if I could "home-in" on the field value by typing the first few characters.

In the first illustration, we see part of a grid in which a field ValidTag, highlighted as point 1, is about to have a value chosen as the user clicks on the field – see point 2.

In the second illustration, we see the SQL SELECT statement used to populate the field:

The full SQL statement is:

SELECT WIKITag.WIKITag AS ValidTag
FROM WIKITag ORDER BY left(WIKITag.WIKITag, 255) asc;

In the third illustration, we see some typical possible values:

I would like to be able to type the first few characters, say {AMC, and for the list to become progressively restricted only to matching values. Once the choice is unique, the user should be able to press enter in order to choose that value.

This auto-completion already works for fields where the list of valid values is fixed, not derived from an SQL statement.

Is this possible for SQL-derived values? Would any other IQ users see this as a valuable development?

Comments

Hello Pierre.

Any idea what happened to my post of earlier today?

Preparing a post, with (in this case) three editted screenshots, takes a couple of hours of work (in this case and in others). Do I have to do it all over again?

Mark GREGORY, Redon, France - GMT +1/+2; EST +6

So, I will laboriously reassemble the post I made earlier which seems to have become lost.

A long time ago, there was a forum discussion concerning auto-completion: www.sqlnotes.net/drupal5/index.php

One place where I would hope that auto-completion would take place is in what are called pop-up lists: see: www.sqlnotes.net/drupal5/index.php

Auto-completion does indeed take place if the list of values in the pop-up is fixed.

However, I make frequent use of fields whose values are derived from an SQL SELECT statement.

In the first screenshot, I show a grid which includes a field called ValidTag. ValidTag is intended to be a pop-up whose values come from an SQL SELECT statement.

Point 1 shows the ValidTag field.

Point 2 shows where we wish to make a specific selection.

In the second screenshot, I show the field definition for ValidTag:

The full SQL SELECT statement is:

SELECT WIKITag.WIKITag AS ValidTag
FROM WIKITag ORDER BY left(WIKITag.WIKITag, 255) asc;

The third screenshot shows some of the (several hundred!) drop-down values:

What I would hope to have happen is that as I type, the pop-up (drop-down!) would "zero in" on the characters typed, e.g. {AM would show only {AMCIS 2010} which could then be selected using <Enter>. However, in this instance, auto-completion does NOT currently occur.

Does anyone else agree that this would be an important or useful development?
Pierre, is this difficult to achieve, and if not, can you please include it in an early version?

Many thanks.

Mark GREGORY, Redon, France - GMT +1/+2; EST +6

> Does anyone else agree that this would be an important or useful development?

A very big YES here!

in the past I have also requested ability to select multiple entries (for one field)

>Any idea what happened to my post of earlier today?

You used MS Word to compose the post and it had very nasty code in it.

I cleaned it up and it now shows

Thank you! Sorry to make work for you. I have created a separate post concerning word processing in InfoQube and why I persist in the use of Microsoft Word.

Mark GREGORY, Redon, France - GMT +1/+2; EST +6

Good idea !

In the next version, you can now filter-on-type.

CTRL+F (or click on the filter button on the header) to show the filter and start typing. List will be filtered

That sounds excellent - I'll look forward to trying it.

Mark GREGORY, Redon, France - GMT +1/+2; EST +6

I apologise for the relatively slow response to this very useful feature which was introduced in version 42. This is going to be a really great piece of functionality, but there are a few things about the current implementation that could perhaps be improved.

Let's start with something basic that works well (and has done for some time). Please note that All the examples which follow are specific to a particular IQbase of my own; they do not form a part of any sample database.
The field Priority is defined with fixed values:

Figure 1: Priority

In use, it is only necessary to key the first character, for example: 5. The value 5: Never is stored in the field. This is true, even if there is an existing value in the field – so you can replace 1: Today with a single keystroke, 5, and no mouse manipulations. Alternatively, if you key F2, you can choose from the available values:

Figure 2: Priority

Straightforward, and very nice.

The next field, Classification, is an auto-list, and also has relatively simple values:

We see at point 1 that the field is defined as an auto-list.

This is now somewhat more complicated to use, although auto-complete does help a great deal. However, it can still be improved. It is not sufficient to use the mouse to click on the field and then to start typing characters to get auto-completion. Instead, characters that are typed at this point continue to be accepted as part of a value. In a way, this makes sense; however it can lead to erroneous data entry. That is because there is a long-standing bug associated with pop-up lists derived from SQL SELECT statements which means that, even if "entry must be in list" is set for the field, you can store any value. So, if all I do is click on the field, type the characters 2008 and press enter, then the value 2008 is stored even though it is not a ValidTag.

If the field does not currently contain a value and I want to auto-completion, I find that it is necessary to key F2, to click on the down arrow, and to start to type characters that exist in the value desired. (Having to use the mouse moves away from the keyboard-only ideal that Pierre suggested he was trying to achieve.)

Figure 3: Classification

So, to choose the value PhD, I key F2, then I type P then H and the value PhD becomes highlighted. I key enter, and the value PhD is stored in the field.

The implementation of the auto-complete appears to ignore non-alphanumeric characters, which in this case makes sense.

Now let us look at this example, which involves a longer list of values from which to choose. The field ValidTag is defined as follows:

Figure 4: ValidTag

The full text of the SQL SELECT statement is:

SELECT WIKITag.WIKITag AS ValidTag
FROM WIKITag ORDER BY left(WIKITag.WIKITag, 255) asc;

Once again, if ValidTag is a field in a grid, to use it we use the mouse to go down in the grid to the point at which we wish to choose a value, key F2, and start to type characters which appear in the tag which we are wanting to choose.

In the current implementation, the characters which you type are not visible on the screen. Instead, if there is a match for the characters you are typing, the item which contains them becomes highlighted as do the characters that you are looking for. This is generally exactly what is wanted but it can lead to slightly awkward behaviour. In the next example, I have keyed 2008. The first tag containing those characters becomes highlighted; see point 1 in the screenshot.

Figure 5: ValidTag

But what if this is not the particular value that is wanted? You will see in the screenshot at pont 2 that there is another visible tag which contains 2008, that is, CITSA 2008. There are in fact a number of other occurrences later in the pop-up. In the current implementation, there is no "search again" facility. I suggest that, in accordance with normal Windows conventions, it should be possible to press F3 to cause the search to repeat and to move on to the next occurrence. Further applications of F3 should successively move to later matching values.

Actually, the current implementation of auto-completion is reasonably complete so that it is possible, having searched for 2008, to key backspace four times, then (for example) to key "CITSA 2008". However, the current implementation does not display the characters which you are actually typing. I note that at point 3, there is some spare "screen estate" where it would be possible and desirable to display the characters which are being typed; I would like to suggest this as an enhancement.

Sometimes, IQ appears to freeze. This seems to happen if, while using InfoQube, I move away to another application and return to InfoQube. IQ appears to freeze. Keying F2 or anything else does nothing – it is no longer possible to navigate within the grid using the keyboard or the mouse; the grid seems to have lost focus. The InfoQube process is using only 1% or 2% of a processor. Keying F5 to refresh does nothing. Instead, it is necessary to move to another grid, do something there, then go back to the grid which contains the field, to key F2, click right arrow and start searching again.

The current implementation of auto-completion appears only to permit letters and numbers in the search string. Referring again to figure 5. When searching with auto-completion, it appears only to be possible to type letters, spaces and digits. So keying the { character makes no difference to what is searched for. In this particular context, that is perfectly good behaviour.

Now consider the situation in figure 6:

Figure 6: ValidKind

ValidKind is a hierarchical classifier, deliberately similar in form to a Windows folder name; see for example point 1, a classifier having three levels in its hierarchy.

Supposing I want to select the value shown as point 3 in Figure 6. I type biz, and the value highlighted is shown as point 2. I cannot find a way to go on to type "\CSR"; everything I key beyond "biz" appears to be ignored. I would like to suggest that, if the search string starts with a character which is not a letter or a space or a number, that the whole search string (including characters which are not strictly alphanumeric) be taken into account in the search. So, here, it will be possible to type either the full string "\biz\CSR" or perhaps just "\CSR"; again, keying F3 would move the search forward.

Summarising the suggestions:

Summary of suggestions
Suggestion	Severity	Notes
Change the semantics associated with keying in a field governed by a pop-up list. Characters keyed should always be interpreted as search characters unless F2 is used to force the characters to be treated as a value.	Suggestion	This was the previous behaviour for pop-up lists with a fixed set of values.
Implement "entry must be in list" for pop-up lists derived from SQL SELECT statements.	Bug – must be corrected	Alternatively, and much less beneficially, do not permit "entry must be in list" where the field derives from an SQL SELECT statement.
Implement "search again" using, perhaps, the F3 key.	Suggestion
Display the characters which constitute the current search value at the top of the pop-up list.	Suggestion
Restore focus when user clicks within an item in InfoQube after switching back from another application.	Bug – must be corrected
If the search string starts with a character which is not a letter or a space or a number, the whole search string (including characters which are not strictly alphanumeric) should be taken into account in the search.	Suggestion

Mark GREGORY, Redon, France - GMT +1/+2; EST +6

Hi Mark,

because there is a long-standing bug associated with pop-up lists derived from SQL SELECT statements which means that, even if "entry must be in list" is set for the field, you can store any value.
This is fixed in v0.9.26PreRel44
it should be possible to press F3 to cause the search to repeat and to move on to the next occurrence. Further applications of F3 should successively move to later matching values.
Pressing F3 works here. Have you checked this lately ?
Sometimes, IQ appears to freeze.
This is fixed in v0.9.26PreRel44
The current implementation of auto-completion appears only to permit letters and numbers in the search string.
Symbols (/, \, etc) are working fine here

HTH !

Thank you Pierre for your helpful answer.

because there is a long-standing bug associated with pop-up lists derived from SQL SELECT statements which means that, even if "entry must be in list" is set for the field, you can store any value.
This is fixed in v0.9.26PreRel44
== This is excellent news, thank you very much.
it should be possible to press F3 to cause the search to repeat and to move on to the next occurrence. Further applications of F3 should successively move to later matching values.
Pressing F3 works here. Have you checked this lately ?
== Doh, dummkopf, I didn't actually think to try it! This works well, thank you.
Sometimes, IQ appears to freeze.
This is fixed in v0.9.26PreRel44
== Once again, good news. I see that you intend to turn choosing a pop-up value into a modal dialogue. That, in this context, does make sense. [For anyone who isn't too sure what a modal dialogue is, take a look at: www.wikiwand.com/en/Modal_window.]
The current implementation of auto-completion appears only to permit letters and numbers in the search string.
Symbols (/, \, etc) are working fine here
== But not here, on two separate computers each using Windows 7 Professional 64 bit. This problem is no doubt related to the problem which I have previously reported as www.sqlnotes.net/drupal5/index.php. The workaround which you suggest there, which is to key F2 first, is inapplicable in that I always have to press F2 1st to get the field to edit at all. Pressing F2 again in order to get the example search string \biz\air to work does not change the behaviour. However, that said, this is not a very major issue and must not delay release of the new version. Conversely, I hope that you will continue to give consideration to the other suggestions which I have made.

Thanks again, Pierre.

Mark GREGORY, Redon, France - GMT +1/+2; EST +6