or I guess as a more general question, how do I `J...
# suitescriptj
jen
05/12/2021, 8:58 PMor I guess as a more general question, how do I
JOINmultiselectt
Tyn Guardian
05/12/2021, 9:01 PMtip when creating search, is I'm creating it first in netsuite to check what condition or operator to use.
j
jen
05/12/2021, 9:07 PMI’m talking about SuiteQL here
jen
05/12/2021, 9:08 PMschema is not usually the same as Saved Search
s
Seth Duda
05/12/2021, 9:48 PM@jen, there is a mapping table that you have to join to for multi-select fields.
Seth Duda
05/12/2021, 9:49 PM(if you're trying to write the SQL directly)
j
jen
05/12/2021, 9:50 PMI don’t know how to
JOINjen
05/12/2021, 9:50 PMthere’s a field on customer called contactlist which looks like a comma-separated list of the contact ids
jen
05/12/2021, 9:50 PMbut I can’t quite figure out how to
JOINs
Seth Duda
05/12/2021, 9:51 PMAre you trying to write a query to run via query.runSuiteQL?
j
jen
05/12/2021, 9:51 PMyes
s
Seth Duda
05/12/2021, 9:52 PMTry installing the bundle called "SuiteQL Sidekick"
Seth Duda
05/12/2021, 9:52 PMit can show you the SQL behind a workbook easily
j
jen
05/12/2021, 9:53 PMdo you happen to know how to do this JOIN ? I feel it should be something simple
jen
05/12/2021, 9:53 PM Copy code
SELECT customer.id, customer.name
FROM customer
JOIN ....?
JOIN contact ....?
WHERE contact.id = 1234jen
05/12/2021, 9:53 PMI don’t use Workbooks, would that bundle work with saved searches?
s
Seth Duda
05/12/2021, 9:54 PMNo, use a workbook to build the join you're trying to do, and then that bundle can tell you the SQL that represents the workbook.
j
jen
05/12/2021, 9:55 PMugh, I don’t really use Workbooks. They don’t work at all on most of our data.
jen
05/12/2021, 9:59 PMI have a query runner for testing concepts. I can view the list of contact ids but can’t seem to join using it
s
Seth Duda
05/12/2021, 10:01 PMCustomerCompanyContact
Seth Duda
05/12/2021, 10:01 PMThat's the join table
Seth Duda
05/12/2021, 10:01 PMHere's what it spits out to show entity id and contact name:
Seth Duda
05/12/2021, 10:01 PMSELECT
BUILTIN_RESULT.TYPE_STRING(Customer.entityid) AS entityidRAW /*{entityid#RAW}*/,
BUILTIN_RESULT.TYPE_STRING(CustomerCompanyContact_SUB.entitytitle) AS contactlistentitytitleRAW /*{contactlist.entitytitle#RAW}*/
FROM
Customer,
(SELECT
CustomerCompanyContact.contactscompany AS contactscompany,
Contact.entitytitle AS entitytitle
FROM
CustomerCompanyContact,
Contact
WHERE
CustomerCompanyContact.contact = Contact."ID"
) CustomerCompanyContact_SUB
WHERE
Customer."ID" = CustomerCompanyContact_SUB.contactscompany(+)
j
jen
05/12/2021, 10:07 PMThanks! I will see if I can modify this for my use case
jen
05/12/2021, 10:07 PMI would give my left foot for a proper schema diagram
jen
05/12/2021, 10:07 PMI’m supposed to be “explaining” the database to a non-NetSuite person tomorrow. That should be fun.
s
Seth Duda
05/12/2021, 10:08 PM"Everything is a transaction and an entity"... nothing else to see here 🙂
j
jen
05/12/2021, 10:11 PMOur most commonly used record is a custom one with like 900 fields.
jen
05/12/2021, 10:11 PMwish me luck
s
Seth Duda
05/12/2021, 10:11 PMhaha
Seth Duda
05/12/2021, 10:11 PMgood luck
j
jen
05/12/2021, 10:11 PM Copy code
SELECT contact.id, contact.firstname, contact.lastname, customer.id, customer.companyname
FROM contact
JOIN customercompanycontact ON contact.id = customercompanycontact.contact
JOIN customer ON customer.id = customercompanycontact.contactscompany
WHERE contact.id = 16357jen
05/12/2021, 10:11 PMitworks.jpg
s
Seth Duda
05/12/2021, 10:11 PMSeth Duda
05/12/2021, 10:12 PMthat's the closest I've seen to a diagram, but won't work for suiteql
Seth Duda
05/12/2021, 10:12 PM(See bottom of the page)
j
jen
05/12/2021, 10:12 PMI use the Schema Browser some
jen
05/12/2021, 10:12 PMbut it’s mostly a guessing game
s
Seth Duda
05/12/2021, 10:12 PMHave you see the database datagrams?
j
jen
05/12/2021, 10:12 PMnyet
jen
05/12/2021, 10:12 PMthere are diagrams?
s
Seth Duda
05/12/2021, 10:12 PMScroll to the bottom of that page
Seth Duda
05/12/2021, 10:12 PMyou'll find one
j
jen
05/12/2021, 10:13 PMoh yeah that
s
Seth Duda
05/12/2021, 10:13 PMbut that's for connect, not for suiteql
j
jen
05/12/2021, 10:13 PMyeah
jen
05/12/2021, 10:13 PMwon’t really help anyhow cuz we are soooooo customised
s
Seth Duda
05/12/2021, 10:13 PMI have a feeling a diagram won't help if you have 900 fields!
j
jen
05/12/2021, 10:14 PMnope
3 Views