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

背景

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

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

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();