3

I had one simple table ItemMapping, 2 Field, one Field is single Item List Field SingleSelectField With value "A", Other for Multiple Item List Field MultiSelectField with Value ("B", "C", "D").

I Wanna get This mapping relationship By "B", I tried to set up one dataset, And try some single SuiteQL like before, But I always get empty results returned.

SELECT *
FROM ItemMapping
WHERE ItemMapping.MultiSelectField IN ('B')

Any tips may Help me. Thank you in advance.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Tyrion Huang
  • 71
  • 1
  • 12
  • Below is the same problem for us, We are not able to filter the multi Select Field Value By Origin SuiteQL Provided By NetSuite System. [link](http://5.9.10.113/67771794/suiteql-error-element-does-not-exist-or-element-class-sqlpredicate-is-expected) – Tyrion Huang Aug 20 '21 at 02:05
  • Just ran across this tonight – Coldstar Feb 12 '22 at 06:20
  • 1
    I believe Marty Zigman had solved the problem, you may reference by [this link](https://blog.prolecto.com/2021/08/21/learn-how-to-sql-query-netsuite-multiple-select-fields/) – Tyrion Huang Feb 18 '22 at 09:29

1 Answers1

6

As was pointed out, Marty Zigman's article describes how Boban D. located an undocumented "feature" of SuiteQL which can be used.

I will leave most of the explaining to the article but to summarize, NetSuite automatically creates a relationship table named map_sourcTableId_fieldId which contain two columns: mapone and maptwo. mapone is the record id from the source table and maptwo is record id for the joined table.

This method seems to work well and maybe the most straight forward if you are accustomed to working in SQL.

As an alternative, I constructed a native SuiteScript Query object with a condition on a multiple select field. Then I used the toSuiteQL() method to convert it into SuiteQL to see how NetSuite natively deals with this. What I found was another undocumented "feature". The resulting query used a BUILTIN.MNFILTER function. So for example if you've got a custom transaction body field, custbody_link_type, that is a multiple select field and want to get transactions where one of te values in custbody_link_type is 4 then here is the generated SuiteQL:

SELECT T.tranid, T.custbody_link_types 
FROM "transaction" as T
WHERE BUILTIN.MNFILTER(T.custbody_link_types , 'MN_INCLUDE', '', 'FALSE', NULL, 4) = 'T'

And if you want transactions where the custbody_link_types does not contain all of the following: 1, 2, 3 ...

SELECT T.tranid, T.custbody_link_types 
FROM "transaction" as T
WHERE BUILTIN.MNFILTER(T.custbody_link_types , 'MN_EXCLUDE_ALL', '', 'FALSE', NULL, 1, 2, 3) = 'T'
  OR T.custbody_link_types IS NULL

To wrap it up, the undocumented BUILTIN.MNFILTER function is used by NetSuite's query module to filter multiple select fields. It accepts the multiple select column, the internal string value of the query.Operator enum, some other stuff I don't know anything about, and finally one or more the values to compare. It appears to return a string of either 'T' for when the condition is met otherwise 'F'.

Ultimately, I'm not sure whether this is a "better" way to address the need but I thought it was worth documenting.

Ryan P
  • 128
  • 1
  • 8
  • I have a question, when I use this `BUILTIN.MNFILTER`. I get results back that don't have a column value at all, is there a way to prevent that? – Webdeveloper_Jelle Aug 30 '23 at 10:50