Filter Definition

Filter Opening and Closing Statement

You need to contain your filter conditions within an opening and closing statement.

• Opening filter block statement: FILTER <<END – Must be on a separate line and include one space between FILTER and <<, no space between << and END.

• Closing filter block statement: END – Must be on a separate line.

                  ... field declarations go here ...

FILTER <<END

      ... filter conditions go here ...

END 

          

Filter Conditions

You can use a logical expression to define multiple filter conditions (relational expressions) combined using logical operators.

• Each relational expression consists of two operands separated by a relational operator and must be contained within parentheses.

• Available relational operators: =, <=, >=, <, >, and <> (not equal).

• The first operand can be:

  • A single source field value (the value of a standard or custom field native to the record type in the source application). This fields must be declared first. See Field Declarations.

    Note:

    When the first operand is a NetSuite field of type RecordRef, the second operand should be a single NetSuite Internal ID or a list of NetSuite internal IDs.

  • A field from a related record type in SuiteProjects Pro. Use the following format to reference a field from a related record type: table_name.field_name. The foreign key for the related SuiteProjects Pro table must be declared first. See Field Declarations.

• The second operand can be:

  • A fixed value. The fixed value must be contained within single quotation marks — For example, '123'.

  • A list of comma–separated fixed values. The entire list must be contained within a pair of single quotation marks. Spaces are permitted. Containing each listed value within quotation marks is not valid and will return an error. For example:

    • Correct: '123, 456, 789'

    • Incorrect: '123’,'456,'789'

• Available logical operators in order of precedence: AND, OR.

• Parentheses can be used to modify operator precedence.

Example 1 – Filter records based on multiple conditions

In the following example, only records that have a department value 1 or 3, or a firstName value John are imported from NetSuite into SuiteProjects Pro.

            NS_FIELDS department,firstName

FILTER <<END
      (department = '1,3') OR firstName='John'
END 

          

Example 2 – Filter records based on a Boolean custom field

In the following example, a custom field is used to filter invoices exported to NetSuite. The check box custom field do_not_export_invoice__c is associated with Invoice records. When the box is checked, the SuiteProjects Pro invoice is not exported to NetSuite.

            OA_FIELDS          do_not_export_invoice__c
OA_CUSTOM_FIELDS   do_not_export_invoice__c
 
FILTER <<END
      do_not_export_invoice__c <> '1'
END 

          

Example 3 – Exclude records based on related records

In the following example, records associated with the SuiteProjects Pro projects with internal ID 8, 9 or 105 are not exported to NetSuite.

            OA_FIELDS project_id
 
FILTER <<END
      project_id <> '8,9,105'
END 

          

Example 4 – Exclude records based on field values on related records

In the following example, SuiteProjects Pro records (in this case, time entries) associated with SuiteProjects Pro projects that have the do_not_export_project_time__c box (custom field) checked are not exported to NetSuite.

            OA_FIELDS project_id
 
FILTER <<END
      (project.do_not_export_project_time__c <> '1')
END