NetSuite: Get specific Custom Record Types and related sub Custom Fields

背景

以前当使用search.create({})来获取数据时，我们需要制定特定的数据返回列；例如：search.createColumn(options)

而query可以使用 SELECT * FROM 来动态返回所有的数据列（这在有的时候是一个优点），那么如何让search也动态返回所有的数据列呢？

SELECT CF.ScriptID , *
         FROM CustomField CF left join CustomRecordType on CF.recordtype = CustomRecordType.internalid
 				 Where CF.ScriptID like UPPER('%CUSTRECORD_test%')
 					# ScriptID这个地方，sql系统返回的全部是大写字母，所以检索时也要用大写字母检索

var arrColFlds = query.runSuiteQL({
query: `SELECT CF.ScriptID scriptid
FROM CustomField CF left join CustomRecordType on CF.RecordType = CustomRecordType.internalid Where CustomRecordType.scriptid = 'CUSTOMRECORD_1' and CF.ScriptID like 'CUSTRECORD%' `
}).asMappedResults();

以上这个query就可以返回Record Type为CUSTOMRECORD_1的所有自定义字段。

如果你需要所有系统中的Custom Record Types

SELECT
    Name,
    ScriptID,
    InternalID,
    Description,
    BUILTIN.DF( Owner ) AS Owner,
    AllowQuickSearch,
    AllowInlineEditing,
    AllowAttachments
FROM
    CustomRecordType
ORDER BY
    Name

可以它对应的数据库表：CustomRecordType， 而保存自定义字段的数据库表名：CustomField

如果你需要所有系统中的Custom Fields

SELECT
    Name,
    ScriptID,
    Description,
    FieldType,
    FieldValueType,
    FieldValueTypeRecord,
    BUILTIN.DF( FieldValueTypeRecord ) AS FieldValueTypeRecordName,
    IsMandatory,
    IsStored,
    IsShowInList,
    BUILTIN.DF( Owner ) AS Owner
FROM
    CustomField

题外话

如果是在Client端，又不想使用query的情况下；可以用ajax访问抓取Record Catalog，速度会比较慢，抓取所有的自定义表和详细的子自定义字段。

var rcEndpoint = '/app/recordscatalog/rcendpoint.nl'; var recordTypes; var action = 'getRecordTypes'; var data = encodeURI( JSON.stringify( { structureType: 'FLAT' } ) ); var url = rcEndpoint + '?action=' + action + '&data=' + data; var xhr = new XMLHttpRequest();
xhr.open( 'get', url, false );
xhr.send();

recordTypes = JSON.parse( xhr.response );
console.log( JSON.stringify( recordTypes, null, 5 ) ); var schema = [];
recordTypes.data.forEach( function( recordType ) {

console.log( 'Loading details for record type ' + recordType.id + '...' );    action = 'getRecordTypeDetail';
data \= encodeURI( JSON.stringify( { scriptId: recordType.id, detailType: 'SS\_ANAL' } ) ); var url = rcEndpoint + '?action=' + action + '&data=' + data; var xhr = new XMLHttpRequest();
xhr.open( 'get', url, false );
xhr.send();
recordDetail \= JSON.parse( xhr.response );    
schema.push( recordDetail.data );

});
console.log( JSON.stringify( schema, null, 5 ) );

总结

结合上面的两个query，我们可以把两个表join起来，用来查询特定Record Type的自定义字段：

var arrColFlds = query.runSuiteQL({
query: `SELECT CF.ScriptID scriptid
FROM CustomField CF left join CustomRecordType on CF.RecordType = CustomRecordType.internalid Where CustomRecordType.scriptid = 'CUSTOMRECORD_1' and CF.ScriptID like 'CUSTRECORD%' `
}).asMappedResults();

Limitation

这样检索，依然无法查询到那些自定义Record Type下的Custom Field被inactive的字段。

目前唯一的办法就是按 select CustomField， 然后order by id以后 然后去猜下一个id的号码，如果猜对UI就是显示出这个custom field 的设置页面，否者报错：You are not allowed to access this custom field.

SELECT
	CustomField.description,
	CustomField.externalId,
	CustomField.fieldType,
	CustomField.internalId,
	CustomField.id,
	CustomField.name,
	CustomField.lastModifiedDate,
	CustomField.fieldValueTypeRecord,
	CustomField.isMandatory,
	CustomField.owner,
	CustomField.recordType,
	CustomField.scriptid,
	CustomField.isShowInList,
	CustomField.isStored,
	CustomField.fieldValueType,
FROM
	CustomField
	order by id
#WHERE recordType = 835
#这个recordType（注意大小写，suitequery对大小写敏感）就是指定了Record Type的internalid数字