Hi I m trying to retrieve all Transaction Body Fields of typ NetSuite Professionals #suiteql

Hi! I’m trying to retrieve all Transaction Body Fi...

netsuiteforstrongmen

01/30/2025, 1:42 PM

Hi! I’m trying to retrieve all Transaction Body Fields of type ‘Free-Form Text’, ‘Date’, and ‘List/Record’ as follows:

SELECT DISTINCT

CustomField.scriptId AS id,

CustomField.name AS name,

CustomField.fieldValueType AS type,

CustomField.fieldValueTypeRecord AS listRecordId,

CustomField.fieldType AS fieldType,

CustomRecordType.scriptId AS listRecord

FROM

CustomField

LEFT OUTER JOIN CustomRecordType ON

(CustomField.fieldValueTypeRecord = CustomRecordType.InternalId)

WHERE

CustomField.fieldType IN ('BODY')

AND INSTR(BUILTIN.DF(CustomField.visibleOnTransactions), 'PurchOrd') <> 0

AND (CustomField.fieldValueType IN ('Free-Form Text', 'Date', 'List/Record'))

AND (CustomField.fieldValueType IS NULL OR CustomField.fieldValueType <> 'List/Record')

UNION

SELECT DISTINCT

CustomField.scriptId AS id,

CustomField.name AS name,

CustomField.fieldValueType AS type,

CustomField.fieldValueTypeRecord AS listRecordId,

CustomField.fieldType AS fieldType,

ScriptRecordType.skey AS listRecord

FROM

CustomField

CROSS JOIN ScriptRecordType

WHERE

CustomField.fieldType IN ('BODY')

AND CustomField.fieldValueType IN ('Free-Form Text', 'Date', 'List/Record')

AND BUILTIN.DF(CustomField.fieldValueTypeRecord) = ScriptRecordType.name

AND INSTR(BUILTIN.DF(CustomField.visibleOnTransactions), 'PurchOrd') <> 0

I get the result, but now I want to find all options for fields of type List/Record, but I don’t know how. For example: List/Record CUSTBODY_REASONFORRETURN Please help. Thanks in advance!

jen

01/30/2025, 4:40 PM

SELECT name, id, scriptid, BUILTIN.DF(fieldvaluetyperecord) AS list_record_type FROM customfield WHERE fieldvaluetype = 'List/Record'

13 Views

Open in Slack

Previous Next