suiteql join against a multiselect field

AI generated answer (using internal Slack threads):

In SuiteQL, to join against a multiselect field, you need to use the mapping table that NetSuite automatically creates for multiselect fields. For example, if you have a custom item field with ID "Item_CustItem_MS_Test", a table named "MAP_Item_CustItem_MS_Test" will be created. You can then join to that table to get the actual values. Here is an example:

sql
SELECT
    Item.ID,
    Item.custitem_ms_test,
    Customer.CompanyName
FROM
    Item
    INNER JOIN MAP_Item_CustItem_MS_Test AS Map1 ON
        ( Map1.MapOne = Item.ID )
    INNER JOIN Customer ON
        ( Customer.ID = Map1.MapTwo )
WHERE
    Item.ID = 18

If you have a custom record with a multiselect field, the query would look something like this:

sql
SELECT
    *
FROM
    CustomRecord_Messages
    INNER JOIN MAP_CustRecord_Mentions AS Map1 ON
        ( Map1.MapOne = CustomRecord_Messages.ID )
    INNER JOIN Entity ON
        ( Entity.ID = Map1.MapTwo )

The format for the map table is

map_<customrecord>_<customfield>

. The map table is just a join table. You can find the exact table name in the Records Catalog. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******

Every multiselect field has a middle table through which you can join

I know, just wanted the syntax for the query.

ok