JSL - Multiselect from a list to insert in a SQL request

[#1]

Hello, I have a little problem,

I created a list then a list box to can select differents values from this list, I would like to insert my selection of values in a SQL request.

But it doesn't work, here is my script :

choice = {"rouge","vert","bleu", "jaune"};
recall_flag = 0;

          NW=New Window("Test",
                     H List Box( Text Box( "choose colors" ), y = List Box( choice ) ),
                     V List Box(
                          Button Box( "OK",
                                       choice = (y << getselected);
                                       ::recall_flag = 1;
                                       NW << Close Window;
                                      )
                                   )
                               );

select =      "SELECT ...
                    FROM ...

                    WHERE Colors IN '" || choice || "' " ;

Have you any idea of the problem ?

Thank you for your answers !

[#2]

Salut Antoine,

You can do like this :

==========================================

choice = {"rouge","vert","bleu", "jaune"};

recall_flag = 0;

          NW=New Window("Test",

                     H List Box( Text Box( "choose colors" ), y = List Box( choice ) ),

                     V List Box(

                          Button Box( "OK",

                                       choice = (y << getselected);

                                       ::recall_flag = 1;

                                       NW << Close Window;

                                      )

                                   )

                               );

select =      "SELECT ...

                    FROM ...

                    WHERE Colors IN 'strchoice' " ;

/* Step1 : Create a string from the list using loop - Creation d'une chaine de carcatère au format attendu en bouclant sur le nombre de valeurs */

string_color = "('" || choice[1] || "'";

For( i = 2, i <= Nitems(choice), i++,

  string_color  = string_color || ",'" || choice || "'"

);

string_color = string_color || ")";

/* Step2  : Replace the stingchoice by the value of string_color */

select = substitute(select,"'strchoice'",string_color);

==========================================

Cordialement,

Guillaume

[#3 · Accepted solution]

Apart from turning the list as into a string, which gbu solves nicely above, there is also be the problem of select beeing evaluated before the selection has been made unless the window is modal or the building of the select string is moved into the OK-button script.

Modal window example:

choice = {"rouge", "vert", "bleu", "jaune"};

recall_flag = 0;

NW = New Window( "Test",<<modal,

  H List Box( Text Box( "choose colors" ), y = List Box( choice ) ),

  V List Box(

  Button Box( "OK",

  choice = (y << getselected);

  ::recall_flag = 1;

  )

  )

);

choices = "'" || choice[1] || "'"; // First selected item

// Add more items if more than one

  For( i = 2, i <= N Items( choice ), i++,

  choices = choices || ", '" || choice[i] || "'"

);

select = "SELECT ...FROM ...WHERE Colors IN (" || choices || ")";

Non-modal example:

choice = {"rouge", "vert", "bleu", "jaune"};

recall_flag = 0;

NW = New Window( "Test", //<<modal,

  H List Box( Text Box( "choose colors" ), y = List Box( choice ) ),

  V List Box(

  Button Box( "OK",

  choice = (y << getselected);

  ::recall_flag = 1;

  choices = "'" || choice[1] || "'";

  For( i = 2, i <= N Items( choice ), i++,

  choices = choices || ", '" || choice[i] || "'"

  );

  select = "SELECT ...FROM ...WHERE Colors IN (" || choices || ")";

  NW << Close Window;

  )

  )

);

[#4]

Merci Guillaume, ca fonctionne !

Thank tou MS !

[#5]

There's a nifty function called concat items which can be used to create your in list with a one-liner:

choice = {"rouge", "vert", "bleu", "jaune"};

recall_flag = 0;

NW = New Window( "Test",

      <<modal,

      H List Box( Text Box( "choose colors" ), y = List Box( choice ) ),

      V List Box(

            Button Box( "OK",

                  choice = (y << getselected);

                  ::recall_flag = 1;

            )

      )

);

choices_in_list = "('" || concat items(choice, "', '") || "')";

select = "SELECT ...FROM ...WHERE Colors IN " || choices_in_list;

[#6]

Hello,

Yes, concat function works too but only for string and not for number. True ?

Do you have any tips for a numeric list ?

For example : instead of having choice = {"rouge", "vert", "bleu", "jaune"}; we have numerics values like choice = {12,156,13,25};

and at the end we wants to have :

select = "SELECT ...FROM ...WHERE ID IN (12,156,13,25)

[#7]

In a Listbox context as in the original example, the items must be strings to be selectable in the listbox. That is, numbers need to be converted to strings in advance anyway.

But assume the list is given as choice = {12,156,13,25}, then the SQL-string could be concatenated like this:

choice= {12,156,13,25};

select = "SELECT ...FROM ...WHERE ID IN " || substitute(char(choice),"{","(","}",")"," ","");

[#8]

The concat items function only takes strings as arguments, so that won't work for a numeric list.

I didn't know that you could keep adding substitution arguments after the first two.  Thanks for showing me that trick MS!