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

更新于 分类于 阅读次数： 阅读时长 ≈ 7 分钟

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

image-20240509164300563

背景

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

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

1
2
3
4
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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    Name,
    ScriptID,
    InternalID,
    Description,
    BUILTIN.DF( Owner ) AS Owner,
    AllowQuickSearch,
    AllowInlineEditing,
    AllowAttachments
FROM
    CustomRecordType
ORDER BY
    Name

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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数字

Netsuite Technical Analyst 技术咨询开发服务

image-20240518215819699