背景
以前当使用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
总结
结合上面的两个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();