Select multi - show descriptions

[Timo]

A select object with multiple = YES, that uses a sql, stores the selected values in an array format like ["3","1","4""]. The browse form also shows the numeric values. How do I show the string values instead?

currently this is shown in the browse form:
["3","1","4""]

this should be shown instead (descriptions with commas separated)
items 3, item 1, item 4

Is there an easy way to accomplish this?

[admin]

Timo,

How many columns does your SQL statement return?

Steven

[Timo]

The lookup sql return just two column and look like this

Code: Select all

SELECT
  ident, description
FROM
  table

ident is numeric (1,2,3,4) and the description is a varchar

[admin]

Timo,

I don't get what you are saying.

Can you rephrase it?

Steven

[Timo]

Let me repeat by adding some screenshots: I got an object of Type: Select on my form. Multiple is set to Yes.

Its SQL looks like this:

Code: Select all

SELECT
  ident, reason
FROM
  table

Then I select multiple items form the select object, save the record. In the database it looks like this:

table_main.PNG

In the browse view it looks like this too:

browse_view.PNG

But it should look like this, showing the reasons and not the ident

reasons_desc.PNG

Is this more clear?

[admin]

Timo,

Thanks.

In a Select Object it is whatever is in the first column that gets saved.

So you could try

Code: Select all

SELECT
  reason as a, reason as b
FROM
  table

Steven

[Timo]

Yeah, that's how it's gonna work. Although, I was just trying to stick to normalization rules and was hoping for a better solution.

[admin]

.

[Timo]

Found a solution with INNER JOIN and FIND_IN_SET.

The idea is taken from https://stackoverflow.com/questions/191 ... s/19101646

Code: Select all

SELECT  a.nid,
        GROUP_CONCAT(b.name ORDER BY b.id) DepartmentName
FROM    Notes a
        INNER JOIN Positions b
        ON FIND_IN_SET(b.id, replace(replace(replace(a.forDepts,'[',''),']',''),'"','')) > 0
GROUP   BY a.nid

[admin]

.