1. NetSuite CPQ
  2. NetSuite CPQ Configurator
  3. Working with Product Building Blocks
  4. Working with the Product User Interface
  5. Gathering Information with Questions and Answers
  6. Storing Answer Options in Tables
  7. Creating Dynamic Tables

Creating Dynamic Tables

Dynamic tables retrieve data from the record type you specify. You can choose any record type available in your account, and the table content automatically updates whenever those records change. When users click the button of the popup table or list associated with the table, answer options are dynamically loaded from the table into these answer types.

To add content to a dynamic table, specify the record fields you want to include. Each field becomes a column in the table. If you don't specify any fields, the dynamic table remains empty. After defining the table content, you can filter the dynamic table by body fields and sublist line fields to narrow down the search results and display only information that matches the criteria defined in popup tables or lists. For more information about filtering by sublist line fields, see Filtering by Sublist Line Fields.

You can preview the content of the dynamic table by clicking the Test Table button.

Before completing the steps to create a dynamic table, follow the basic steps to create a table.

To create a dynamic table:

  1. On the Tables subtab, click New CPQC Table.

  2. In the Table Type field, select Dynamic Table.

  3. Select the record type from which you want to retrieve data.

  4. To add columns, click the plus icon next to the Record Fields field and select the record fields you want to include.

  5. To narrow down the table results and retrieve a subset of records, click the open icon next to the Search Filter field.

    In the popup wizard:

    • Select the record field you want to use for filtering.

    • Select an operator.

      The available operators depend on the field selected.

    • Enter the filter text.

    • Click OK.

  6. To add multiple filters and retrieve a more specific subset of records, repeat step 5.

    When adding another filter, you must select a logical operator (AND or OR) to combine the filters. AND produces a more restrictive condition, whereas OR results in a less restrictive condition.

    The filters are automatically copied to the Search Filter field in JSON format. The syntax is:

                    [["fieldID1","operator","value1"],"and",["fieldID2","operator","value2"]]

  7. When filtering by sublist line fields, dynamic tables return only one search result for each record. To retrieve all sublist results, two options are available:

    • Show all sublist results - Returns all matching entries in the sublist.

      Note:

      A limited number of sublist line fields is supported.

    • Search using SuiteQL - Expands the search to any sublist line field and returns all matching entries in the sublist. If Show all sublist results doesn't return the expected results, use this option.

    For more information, see Filtering by Sublist Line Fields.

  8. To preview the dynamic table content, click the Test Table button.

  9. Click Save.

Filtering by Sublist Line Fields

When searching for sublist line fields, dynamic tables return only one search result for each record, even if multiple sublist entries exist. For example, if you search for an item's vendors, you'll get only one search result, even if the item is associated with multiple vendors.

To address this, you can use the option Show all sublist results. When this box is checked, the search query returns multiple search results-one for each sublist entry. For example, if an item is associated with five vendors, you'll obtain five search results-one for each vendor for the same item.

Note:

The Show all sublist results option only applies to search queries involving sublist line fields. Search queries using body fields remain unaffected.

Dynamic table supports search queries for a limited number of sublists line fields. With the Search using SuiteQL option, you can run search queries using SuiteQL-NetSuite SQL-based query language-instead of the standard search module. For more information see, SuiteQL. Use this new option to:

  • Expand the search to any sublist line field.

  • Retrieve all sublist results.

When specifying sublist line fields in the Record Fields or Search Filter fields on the table record, make sure you use the SuiteQL dot notation- sublistID.sublistFieldID-instead of the sublist field ID alone. For example, use itemvendor.purchaseprice to search for the Purchase Price field on the item record's Vendors sublist using SuiteQL.

Related Topics

General Notices