Distinct in Choices Options Field for a Text-Box

[Shon]

I have been using "Distinct" in many of my Choices Option Field for Select-Boxes and Combo-Boxes. I noticed that if you change the type to a Text-Box, for the Auto-Complete attributes, it no longer functions correctly. It seems as though the "Distinct" is working differently, or rather not working, based on the field type, even thought the rest of the attributes seem similar. I have had to use a nasty subselect in the Choices Selection Criteria to achieve the same results. Is this intentional? Can it be changed? When? It would be nice if it worked like the other field types and certainly easier than coding subselects.

[Rob]

We hadn't really intended the DISTINCT keyword to be added to the Choices Option property in this way. It is really by luck and not by design that it works for a select box.

We will look at adding a new DISTINCT property here which will work with both widgets.

[Shon]

To be clear, you mean to alter the code so that "DISTINCT" works when used in the Choice Options field right? I do not mean for you to add a new field or new attribute or new property, just to allow it to work when typed in, like it works for the others. Thanks, by the way.

[Rob]

To ensure it will work as expected, we are looking at adding a property called distinct and having it set to true or false. This would work with select and auto complete text boxes. With this you would remove the DISTINCT word from the choices option property and set the new property to true.

[Shon]

Ok. Will it still work with concatenanted and/or substring selections in the choices option field? Ex. DISTINCT(SUBSTR(A.FAFUND,2,3) || ' ' || A.FALGLE)

[Brian]

No, this not how it would work. If you need something substringed and concatenated like your example, I would highly recommend you create an SQL view to build that field and use the view as your database file.

[Brian]

On review, I think I should clarify my last post. When I say this is not how it would work, I am specifically talking about the DISTINCT keyword. DISTINCT is a row level keyword, not a column level.

As for the suggestion of using a view, I suggest this for performance reasons. If you need a column built from other columns, building a view will let the system manage it and always have it ready instead of having to dynamically build it every time.

[kevinh]

We hadn't really intended the DISTINCT keyword to be added to the Choices Option property in this way. It is really by luck and not by design that it works for a select box.

We will look at adding a new DISTINCT property here which will work with both widgets.

Has a DISTINCT property been added for when using database driven choices? This would be powerful when wanting to build a quick choices list for a select box based on values in a file, and only showing each choice once in the pull down.

[David]

No, this has not yet been added, sorry. This is on our "to do" list, I'll see if I can give it a nudge with the development team.

[kevinh]

In the Visual Designer, where can this "Distinct" property be set for a Select Box/dropdown using the Database Driven Selections?
I see version notes for 4.1.1 states:
• Return only DISTINCT records for database-driven dropdowns, combo boxes, and auto-complete fields
We are using version 4.1.3 and I find no property that provides this option.