Info:
PHPMaker v2025.11
Database: MSSQL 2017
I have a table example as below. columnB is a SELECT input and set as multiple SELECT and search operator is set as equal (=). Then I enable “filter” function for columnB. Below is sample data stored.
| columnA | columnB |
|---|---|
| 1 | LPR001 |
| 2 | LPR002 |
| 3 | LPR001 |
On the generated List page, If I click the columnB header to filter one value, for example I only check on the “LPR001” value, the data can be display correctly and below is the query I retrieve from the Debug bar.
SELECT * FROM TABLE1 WHERE EXISTS (SELECT * FROM STRING_SPLIT([columnB], '|') WHERE value IN ('LPR001')) ORDER BY [columnB] ASC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
If I to select multiple value, the query returns empty result. Sample query extracted from Debug as below:
SELECT * FROM TABLE1 WHERE EXISTS (SELECT * FROM STRING_SPLIT([columnB], '|') WHERE value IN ('LPR001,LPR002')) ORDER BY [columnB] ASC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
If I use Extended Search instead of filter, the query generated is correct when select multiple value:
SELECT * FROM TABLE1 WHERE EXISTS (SELECT * FROM STRING_SPLIT([columnB], ',') WHERE value IN ('LPR001', 'LPR002')) ORDER BY [columnB] ASC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
See the difference?
- For Filter function, the separator is wrong, it is using | instead of comma.
- The multiple value are string-quoted wrongly.. It should be IN (‘LPR001’,‘LPR002’), and not (‘LPR001,LPR002’).