https://netsuiteprofessionals.com logo
Join Slack
Powered by
Anyone have a sample SuiteQL statement that gets t...
# suitescript
d
Anyone have a sample SuiteQL statement that gets the data from a multi-select field?
t
@dcrsmith When you create a multi-select field, NetSuite automatically creates a "map" table to support it. For example, if the field is a custom item field with ID "Item_CustItem_MS_Test" then a table will created named "MAP_Item_CustItem_MS_Test."
You can then join to that table to get the actual values. Ex: 
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 your instance is running 2020.2, these tables should be listed in the Records Catalog (available via Setup > Records Catalog).
Hope that helps!
Thanks @erictgrubaugh for the mention.
👍 1
d
Thank you. I'll give this a try.
@tdietrich I'm not quite able to make this work. I have a multiselect field of type entity on a custom record. How would I get the list of entities selected on one of those custom records?
t
What's name of the field, and the source for it?
d
The custom field is 
custrecord_mentions
the source is entity. The custom record is 
customrecord_messages
t
I think your query would look something like this: 
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 )
d
Not working. 😞
invalid search type for the map table
t
Ok. You should look in the Records Catalog to see what the table name really is.
d
I'm feeling like I don't know where the Records Catalog is. How do I get there?
found it
OK. I remember seeing this on a preview, totally forgot about it. Thank you! I'll let you know when I get connected.
t
Glad to help!
d
OK. So the format looks like it is 
map_<customrecord>_<customfield>
t
Yeah, that sounds right.
The map table is just a join table.
d
Now I just need to figure out how to get the values from that field
t
The Records Catalog should give info on the join.
There's an "i" icon buried in the UI that shows the join fields.
d
OK. I got the results. Thank you so much for the help on this. Learned something new today
t
No problem. It's actually kind of neat how they've implemented multi-select fields. And it's usually easier to get the query going when you're using standard tables, too.
d
I also discovered today the builtin for getting the text values. That was nice. Now I don't have to join to the record.
t
Yeah, the DF function can be very helpful at times, and save you from making joins. There are other builtin functions available, but I haven't found them to be as helpful. I wrote about them here: https://timdietrich.me/blog/netsuite-suiteql-built-in-functions-revisited/
👍 1
RELATIVE_RANGES seems interesting, but I haven't had a need for it yet.
d
Is there a way to limit the number of results? I tried 
select top N
but that didn't work in this case for some reason.
m
WHERE ROWNUM < N
d
That didn't work either
6 Views
Open in Slack
PreviousNext
Powered by