https://netsuiteprofessionals.com logo
Join Slack
Powered by
Any way to get names of all tables in suiteql? So...
# suiteql
j
Any way to get names of all tables in suiteql? Some are missing from records catalog. I know I can probably do this through DBeaver/JDBC (I’m on a mac) but that would require creating a non-2fa role that has at least view on all record types and we have like 400 custom record types and I am lazy.
even if I try with a role that doesn’t have 2FA I still get this “This data source is not accessible for roles requiring Two-Factor Authentication (2FA). Use a role that does not require 2FA”
s
No way through SuiteQL, as those tables are not available AFAIK. The closest would be via SuiteAnalytics Connect ($$$) 
select * from oa_tables;
https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_4407755805.html#bridgehead_4407759211 You can also try scraping the Record Browser, but it may not be complete, as you've noticed. https://timdietrich.me/blog/netsuite-records-catalog-api/ Scraping the whole thing might expose more record types due to references in joins, but not appearing in the list. For your reference, this is the comprehensive list of tables which are available via SuiteQL (updated 2019): https://nlcorp.app.netsuite.com/core/media/media.nl?id=127972055&c=NLCORP&h=34f6d25f34ab89fef9af&_xt=.xls As you can see there, only a small number of tables are available to SuiteQL, and practically nothing has improved in that aspect in years.
m
r
I had made this tool to recursively scrape the Record Browser client side. It gets all tables including the ones that are only only joined to. https://github.com/rtanner1231/suiteqltools.nvim/blob/main/netsuitescripts/loadcompletion.html
j
basically I’m just wondering if there are any “hidden” tables that I’d only be able to find via 
select * from oa_tables;
am desperate to find out how NS stores the connection between item options and items
I’ve literally had to write a M/R script that loads all items, does a record.getValue({fieldId: ‘itemoptions’}) and use that to keep a custom record up to date in order to have this info available in SuiteQL.
i
I can't even imagine configuring roles where you have 400 custom record types 🫠 They need to make it so we can import those permissions
j
I wrote a script I can run in the browser console to set up custom record types under a role with the permissions I want hahhah
faster than adding one by one
i
Oh sweet! Gives me ideas...
j
This is an example of doing it from the Custom Record (adding roles to the record) but you can do similar from the Role itself:
Copy code
// Click onto first row in table of roles before running code below.

function deleteRow() {

	permissions_machine.deleteline();
	counter--;

	if(counter > 0)
		setTimeout(deleteRow, 50);

}


let counter = jQuery('tr[id^="permissions_row_"]').length;
deleteRow();


let full_access_roles = [3,1148,1021,1157,1046,1149];

let view_roles = [1029,1022,1033,1147,1154,1159,1162,1161,1160,1165,1166,1163,1155,1000,1156,1153,1002,1028,1032,1007,1158,1010,1013,1015,1017,1027];

for(var i = 0; i < full_access_roles.length; i++) {

	jQuery('#hddn_permittedrole11').val(full_access_roles[i]);
	jQuery('#hddn_permittedlevel12').val(4);
	permissions_machine.doAddEdit();

}

for(var i = 0; i < view_roles.length; i++) {

	jQuery('#hddn_permittedrole11').val(view_roles[i]);
	jQuery('#hddn_permittedlevel12').val(1);
	permissions_machine.doAddEdit();
}
🙌 1
i
Thanks for sharing that!
j
you might have to tweak the element ids etc but it gives you the basic approach at least
✅ 1
61 Views
Open in Slack
PreviousNext
Powered by