Question

Creating a metadata text field that auto-suggests from a list in SQL

Laserfiche

Updated June 29, 2015

asked on June 29, 2015

I'm trying to create a single line metadata text field that will allow you to begin entering the first few letters of a value, and having a list filter to show only the values that match the text string entered. The accepted values are stored in a SQL table.

I'm not looking to achieve this using a list field, as they will have hundreds of values to sort through. Auto-suggesting in a text field will allow them to enter a few letters and have that list of hundreds narrowed down to a more easily viewed list.

For some background:

We have a prospective client that has an approval process which requires approval from 3 out of hundreds of approvers. There's one person that will be responsible for selecting which of the approvers is appropriate for each instance, so I'll be creating a workflow that allows this one person to choose values for 3 separate fields called Approver 1, Approver 2, and Approver 3. When Approver 1 has the value "Rob" entered, I'd like to see the name "Rob Misconin" and "Bill Robinson" appear, given that those names appear in the SQL list I'm referencing.

In a perfect solution, I'd like to have the name selected in Approver 1 not appear in the options for Approver 2, and so forth with Approvers 2 and 3, but if that's not possible, it's fine.

Any ideas on how to do this? I've searched through help files and other answers posts, but I wasn't able to find anything.

0 0

Answer

SELECTED ANSWER

replied on June 29, 2015

If the concern is just displaying the entire list, there are some measures put into play to avoid long load times. How large of a list have you tried it with? I believe after a certain point with a large enough list, the behavior will change more to what you describe - so it doesn't sit and load a giant list every time. There are also some other approaches, such as the [Settings]MaxDropDownLength attribute. Have you had a chance to experiment with those?

3 0

Replies

replied on June 29, 2015

Hi Rob,

You should be able to use dynamic fields for this, just don't set up any parent relationships between the fields.

0 0

View 4 previous replies

replied on June 29, 2015

Hey Pava,

Maybe I'm misunderstanding, but check out the attached screenshots.

-All of my fields are created as "Text" fields

-All of the fields are are added in the dynamic fields pulling the list from the same table and column

-The list appears in the metadata fields, but in the form of a dropdown list. In my example, I only included a handful of approvers, but in practice, they will be choosing from a much larger list, making a dropdown list not very appealing.

Is there any way to change it so the user filling out this field can type into the text field to filter the list?

screen1.png (24.11 KB)

| Download

screen2.png (22.67 KB)

| Download

screen3.png (22.63 KB)

| Download

0 0

replied on June 29, 2015

Ah, I see what you mean. This will get you the dynamic lookup from the SQL list (so you don't have to worry about manually maintaining that yourself as a list field), but it's still going to display as all dynamic fields do. So it gets you most of the way there, but does require typing from the start.

0 0

replied on June 29, 2015 • Show version history

Thanks Pava. We'd like to submit a feature request to have the option to make dropdown fields (regardless of whether they are dynamic) appear and behave more like they do in Forms lookup fields, where it looks just like a textbox at first, but values are suggested as you type and the choices become fewer.

We noticed that dropdown fields in the Client search pane almost have this behavior, in that they appear as text boxes you can type into, but they still display the entire list, which is not ideal in our case since we'll have hundreds of items in there. (And it would be great if the behavior and appearance were uniform across all field dialogues.)

0 0

SELECTED ANSWER

replied on June 29, 2015

3 0

replied on June 29, 2015

Screenshot of what Ege was talking about.

I also found it strange that the "Approver 1" in the field search did not show the list, whereas the fields in the template search "Approver 1, Approver 2, and Final Approver" each showed the list when the field was clicked on.

screen1.png (34.32 KB)

| Download

screen2.png (11.45 KB)

| Download

0 0

replied on June 29, 2015

There is a threshold for when to show the full list. I believe it is around 100 items. Once 100 items is in the list, the full list doesn't show and the user is required to type.

I actually want to opposite behavior: show the full list no matter how many there are in the list. Users shouldn't have to know the first few letters of an item in order to select it.

Ideally, I'd like to see a smart filter that filters in both directions. If a user types the second word of an item, it still shows in the list.

0 0

replied on June 29, 2015

In my testing with the [Settings]MaxDropDownLength attribute, I found that the field no longer shows as a dropdown, and instead allows you to type text in the field to filter the results.

This is exactly what I was looking for Pava! Thanks for the pointer!

2 0

You are not allowed to follow up in this post.

Question

Question

Creating a metadata text field that auto-suggests from a list in SQL

Answer

Replies

Sign in to reply to this post.