How to fetch the list of Estimate records using SuiteScript in Netsuite

Question

I am unable to fetch the List of Estimate records using SuiteScript in Netsuite. I have used the same script for the CUSTOMER records and it is working fine but when I am using the same for ESTIMATE records but it is not working.

Here is my SuiteScript code:

define(['N/https','N/query'], function (https, query) {
    /**
     * @NApiVersion 2.1
     * @NScriptType WorkflowActionScript
     */
    const exports = {};

    function onAction(context)
    {
      // Create a query definition on estimate records
        let myEstimateQuery = query.create({
            type: query.Type.ESTIMATE
        });
      // Create conditions for the query
        let firstCondition = myEstimateQuery.createCondition({
            fieldId: 'Opportunity',
            operator: query.Operator.EMPTY_NOT
        });
        myEstimateQuery.condition = myEstimateQuery.and(firstCondition);

        // Create query columns
    myEstimateQuery.columns = [
        myEstimateQuery.createColumn({
            fieldId: 'id'
        }),
        myEstimateQuery.createColumn({
            fieldId: 'custbody_quote_internal_id'
        }),
        myEstimateQuery.createColumn({
            fieldId: 'tranid'
        }),
        myEstimateQuery.createColumn({
            fieldId: 'externalid'
        }),
        myEstimateQuery.createColumn({
            fieldId: 'currency'
        }),
        myEstimateQuery.createColumn({
            fieldId: 'duedate'
        }),
        myEstimateQuery.createColumn({
            fieldId: 'enddate'
        }),
        myEstimateQuery.createColumn({
            fieldId: 'startdate'
        }),
        myEstimateQuery.createColumn({
            fieldId: 'subtotal'
        }),
        myEstimateQuery.createColumn({
            fieldId: 'custbody_tran_term_in_months'
        }),
        myEstimateQuery.createColumn({
            fieldId: 'custbody_end_user'
        }),
        myEstimateQuery.createColumn({
            fieldId: 'custbody_qumu_deployment_type'
        }),
        myEstimateQuery.createColumn({
            fieldId: 'custbody_qumu_orig_renewal'
        }),
        myEstimateQuery.createColumn({
            fieldId: 'custbody_qumu_target_renewal'
        })
     ];
    // Run the query
      let resultSet = myEstimateQuery.run();
      log.debug('resultSet>> ', resultSet);
      log.debug('resultSet.length>> ',  resultSet.results.length);
      log.debug('resultSet.results>> ',  resultSet.results);

    }
    exports.onAction = onAction;
    return exports;
});  

Answer 1

There are a few issues you'll need to fix:

1. query.Type.ESTIMATE does not exist; you need query.Type.TRANSACTION as the Estimate/Quote record is a Transaction.
2. Add a condition for the specific recordtype to get only Estimates.
3. Add the new condition to your conditions list.
4. subtotal is not a valid query column. You can find the valid columns by looking up "transaction" in the Records Catalog under Setup > Records Catalog.

    const myEstimateQuery = query.create({
      type: query.Type.TRANSACTION // Fix 1: Use TRANSACTION instead of ESTIMATE
    });
    
    // Fix 2: Add a new filter for Estimate recordtype
    const firstCondition = myEstimateQuery.createCondition({
      fieldId: 'recordtype',
      operator: query.Operator.IS,
      values: ['estimate']
    });
    const secondCondition = myEstimateQuery.createCondition({
      fieldId: 'opportunity',
      operator: query.Operator.EMPTY_NOT
    });

    // Fix 3: Add the new filter
    myEstimateQuery.condition = myEstimateQuery.and([firstCondition, secondCondition]);

    // ...

    // Fix 4: Use an appropriate, valid query column instead of subtotal
    myEstimateQuery.createColumn({
      fieldId: 'foreigntotal'
    }),

    // ...

FWIW if you happen to be more familiar with SQL, you can write this same query a bit more concisely using the runSuiteQL method instead:

const myEstimateQuery = query.runSuiteQL({
  query: `
    SELECT
      currency,
      custbody_end_user,
      custbody_qumu_deployment_type,
      custbody_qumu_orig_renewal,
      custbody_qumu_target_renewal,
      custbody_quote_internal_id,
      custbody_tran_term_in_months,
      duedate,
      enddate,
      externalid,
      foreigntotal,
      startdate,
      tranid,
      custbody
    FROM
      transaction
    WHERE
      recordtype = 'estimate' AND
      opportunity IS NOT NULL
  `
});