I have read the 3 pages of posts on multi select and I wanted to confirm my assumptions and see if there is a fix or if anyone has a workaround.

We are using version 7.1.1 and the following code does not work:

TABLE FILE EMPDATA
SUM SALARY BY TITLE
-IF &DIV0 LT 1 THEN GOTO NOLOOP;
WHERE DIV EQ '&DIV1'
-SET &CNT = 2;
-LOOP
-IF &CNT GT &DIV0 GOTO NOLOOP;
OR '&DIV.&CNT'
-SET &CNT = &CNT + 1;
-GOTO LOOP
-NOLOOP
END


I understand that it was supposed to be a fix, but perhaps it didn't make it in the 7.1.1 patch.

When I do a multi select, I am able to use the operation = OR and it passes in what I need separated by OR's which is fine. What I need is to pass it in separated by commas or at least find a way to replace the OR with commas after the variable is passed. I was hoping the above code would work so I can cycle through each parameter passed and then concatenate a comma after, but it doesn't work.

I have tried CTRAN to replace the OR ascii wtih the ascii for comma but that isn't working... if I try to substring or edit out the OR, I fear I may edit out a parameter that has the letters o and r in it.

Does anyone have any ideas? What I want is to pass this into a SQL statement, so I need it to be where option in (&option) where &option are the values separated by commas... currently its works but they are separated by OR's.

Any ideas? Thanks so much in advance!

slfmr,

If you are happy passing the OR statement into your fex then just use STRREP (place the &Opt_SQL all on one line) -

-SET &Opt_Passed = '1 OR 2 OR 3 OR 4 OR 5 OR 6';
-SET &Opt_SQL = STRREP(&Opt_Passed.LENGTH, '&Opt_Passed.EVAL',
 2, 'OR', 1, ',', &Opt_Passed.LENGTH, 'A&Opt_Passed.LENGTH');
-? &Opt

T

In 7.1 you should be able to do this.

WHERE DIV EQ &DIV.(OR()).Division.;

Hope this helps

Thanks Texas, that does work for Focus code which I will keep in mind so that's great.

Tony, I did try your suggestion and it works great as well, but the way the multi select OR comes in is like this:

'1' OR '2' OR '3' OR '4' OR '5' OR '6'

with quotes around each one... so what I did was use the CTRAN function to get rid of those to get it in the format you used:

'1 OR 2 OR 3 OR 4 OR 5 OR 6'

-SET &OPT = ÷
-SET &OPT = CTRAN(400, &DIV, 39, 0, OPT);
-SET &Opt_SQL = STRREP(&OPT.LENGTH, '&OPT.EVAL', 2, 'OR', 1, ',', &OPT.LENGTH, 'A&OPT.LENGTH');
-? &Opt_SQL

This works to give me

1 , 2 , 3 ,

BUT it only works if you are getting this from the front end, I can't test it with just a -SET &DIV = '1' OR '2' OR etc... it doesnt' work which is fine because these values are coming from the front end anyway .

In addition, now that I have the 1 , 2 , 3 I would need those in single quotes because it is a varchar in the database.

Any suggestions on that (reassigning the single quotes around each value)?

To have it look like '1','2', etc...

Thanks so much for your feedback, you have given me a lot to work with which is what I am doing now, but any additional suggestions are welcome.

Also, is there perhaps a javascript function that we can do in the HTML code that will translate the OR to a comma?

I am not the best at javascript but I found a replace method that I could perhaps do when we click the button...


script type="text/javascript">

var str="'FIIS' OR 'FEB' OR 'FESCO'"
document.write(str.replace(/OR/,","))


But this only replaces the first OR...

Any suggestions in this aspect?

Once again I am posting to state that I have a function that replaces all I am just having a hard time working it into the html generated by Web Focus.

<script type="text/javascript">
function replaceChars(strText) {
var strReplaceAll = strText;
var out = "OR"; // replace this
var add = ","; // with this
var intIndexOfMatch = strReplaceAll.indexOf( out );

while (intIndexOfMatch != -1) {
strReplaceAll = strReplaceAll.replace( out, add )
intIndexOfMatch = strReplaceAll.indexOf( out );
}
return strReplaceAll;
}

var strText="'sample' OR 'string' OR 'variable'"
document.write(replaceChars(strText));

slfmr,

To enable you to try it without the front end supplying the value then you need to "do the thing with single quotes" and that is doubling them up -

-SET &Opt_Passed = '''1'' OR ''2'' OR ''3'' OR ''4'' OR ''5'' OR ''6''';
-SET &Opt_SQL = STRREP(&Opt_Passed.LENGTH, &Opt_Passed,
 2, 'OR', 1, ',', &Opt_Passed.LENGTH, 'A&Opt_Passed.LENGTH');
-? &Opt

This will give you a quoted string as per your front end.


As for your script, you require a semicolon at the end of the line containing "strReplaceAll = strReplaceAll.replace( out, add )"

and to include it into your HTML, just place it in the < head> section and call it by something like -

    this.displayElement = document.getElementById(item);
    this.targetElement  = document.getElementById("item2");
    this.targetElement.value = replaceChars(this.displayElement.value);

T

Try this:

 
-* File or2com.fex
-SET &INPUT1='''VALUE''';
-SET &INPUT1='''VALUE1'' OR ''VAL2'' OR ''VAL3''';

-TYPE &INPUT1
-*******************************************************************
-SET &OUTPUT='';
-REPEAT ENDLOOP WHILE &INPUT1 CONTAINS ' OR ';
-* DETERMINE POSITION OF NEXT ' OR ' STRING.
-SET &POS=POSIT(&INPUT1,&INPUT1.LENGTH,' OR ',4,'I3');
-SET &POSM1=&POS - 1;
-SET &SUBSTR=SUBSTR(&INPUT1.LENGTH,&INPUT1,1,&POSM1,&POSM1,'A&POSM1.EVAL');
-* ADD IT TO THE OUTPUT STRING, BUT SEPARATED BY COMMAS.
-SET &OUTPUT=IF &OUTPUT EQ '' THEN &SUBSTR ELSE &OUTPUT || ',' || &SUBSTR;
-* REMOVE FOUND STRING AND ' OR ' FROM &INPUT1 AND THEN LOOP BACK.
-SET &POSP3=&POS + 4;
-SET &NEWLEN=&INPUT1.LENGTH - &POSP3 + 1;
-SET &INPUT1=SUBSTR(&INPUT1.LENGTH,&INPUT1,&POSP3,&INPUT1.LENGTH,&NEWLEN,'A&NEWLEN.EVAL');
-ENDLOOP
-SET &OUTPUT=IF &OUTPUT NE '' THEN &OUTPUT || ',' || &INPUT1
- ELSE &INPUT1;
-*************************************************************
-TYPE OUTPUT = &OUTPUT
 

slfmr,

I have just reread my post and realised that it doesn't supply you with a full solution as to containing and calling it from within a WF generated HTML page.

The thing that you must remember is that WF inserts a couple of calls to be executed when the page loads and when it is submitted, and it is the submission that you want to intercept.

Have a look at the body tag of your HTML and see if there is an ONSUBMIT event trigger. If so then please post it. If you are forcing the embeded ORs in a single output variable using "operation=OR", then the call is probably SaveValues(); or something like that?

If this is in fact true then the function you are looking for is contained within IBIRLS.js and goes and does a whole load of things in the background. What you want to do is to do some processing after this function, so you can add your call after the SVAELVALUES(); in the onsubmit attribute - onsubmit="SaveValues();replaceChars(idattributevalue);" replacing the elements ID attribute value between the second braces.

In your function, use the getElementById syntax to prime an object vars with the object whose name contains the WF variable name where "item" below is the ID attribute value of the object required, -

  function replaceChars(item) { 
    el = document.getElementById("item");
    var out = "OR"; // replace this
    var add = ","; // with this
    el.value = strReplaceAll.replace(out,add);
  }

You will have to check the URL produced to see if this is enough as I think that the URL string is produced by the SaveValues(); function.

Of course if you are not using the SaveValues(); function then you just have to add the onsubmit call in your form tag (or submit button).

Not sure if I've not confused you, but good luck anyway!

T

Before anyone else mentions it, replacing every occurence of OR in your multi selections will also replace any that exist in your data!!!

You have been warned!!

T

Your posts have helped so much!

Tony, thanks for pointing out that semicolon.. I must have missed that... which takes care of some of the syntax errors I was getting.

Bernie Ott... your solution has worked for me and I tried a number of different ways for the letters OR to be in the word and it does not replace them if they are in the single quotes. Just for example I tired: OREO, OREGON, DEBORAH, FORWARD, FORLORN...

Tony, I am still going to try the javascript so that it helps me get a better grasp on what is going on, but the warning is what kinda pushes me away, I would not want to replace those OR's and as of now I have a solution per Bernie.

You people are amazing! Thank you so much!