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.
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 thetoSuiteQL()
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 aBUILTIN.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 incustbody_link_type
is4
then here is the generated SuiteQL:And if you want transactions where the
custbody_link_types
does not contain all of the following: 1, 2, 3 ...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 thequery.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.