https://netsuiteprofessionals.com logo
Join Slack
Powered by
Hello SQL gurus - I'm trying to join the `customer...
# suiteql
a
Hello SQL gurus - I'm trying to join the 
customer
table to the 
contact
table - but without using the 
company
field on the contact record, as that field only contains a single entity reference, whereas a contact may be associated to multiple entities. The 
contactList
field on the 
customer
table returns a text field which is a comma-separated list of 
contact
IDs - I've tried subqueries to no avail with it. E.g. this works:
Copy code
SELECT cu.id, cu.contactlist from customer as cu, contact as co WHERE cu.id = 140 and co.id in (11,1144)
(11 and 1144 are the two IDs returned from 
contactList
for customer 140, but this does not:
Copy code
SELECT cu.id, cu.contactlist from customer as cu, contact as co WHERE cu.id = 140 and co.id in (cu.contactlist)
("Search error occurred: Invalid or unsupported search")
t
@al3xicon
Take a look at the COMPANYCompanyContact table.
Copy code
SELECT TOP 100
	Entity.ID AS Entity,
	Entity.EntityId,
	Contact.ID AS Contact,
	Contact.LastName,
	Contact.FirstName
FROM 
	COMPANYCompanyContact AS CompanyContacts
	INNER JOIN Entity ON
		( Entity.ID = CompanyContacts.ContactsCompany )
	INNER JOIN Contact ON
		( Contact.ID = CompanyContacts.Contact )
ORDER BY
	Entity.EntityId,
	Contact.LastName,
	Contact.FirstName
It's a join table between the Entity and Contact tables.
a
thank you @tdietrich! i'll take a look - i was wondering if there was a hidden mapping table after looking at Marty's article about multiselect fields: https://blog.prolecto.com/2021/08/21/learn-how-to-sql-query-netsuite-multiple-select-fields/
r
@tdietrich how you we can know about such hidden tables such as COMPANYCompanyContact btw? As its not coming in your suitelet either.
a
that query looks like it does the trick Tim, thanks again
t
@al3xicon You're welcome. Yeah, there are still a lot of hidden tables that never "surface" in the Records Catalog.
Awesome! Glad to have helped.
@raghav It's a defect in how the Records Catalog API works. It's one of the reasons I've been trying to wrap up and release this: https://timdietrich.me/blog/netsuite-suiteconsole-suiteql-schema-tool/ (Scroll down to "SuiteConsole's Origin: The SuiteQL Schema Tool.")
r
@tdietrich will be eagerly waiting for this.
t
Me, too. ha!
❤️ 1
13 Views
Open in Slack
PreviousNext
Powered by