Merging Saved Search and SuiteQL Queries

It is possible to execute a SuiteQL query that will run exclusively for the results of a saved search.

We can run a saved search and can write queries only to be executed for the data or results of the saved search result.

Example:

                    let internalIds = [];

                    inventoryitemSearchObj.run().each(function (result) {
                        internalIds.push(result.id);

                        return true;
                    });
                    log.debug("internalIds", internalIds);

                    // Convert the internal IDs array to a comma-separated string
                    let internalIdsStr = internalIds.map(id => `'${id}'`).join(',');

                    // Construct the SuiteQL query with the internal IDs
                    let suiteQLQuery = `
                     SELECT
                    invtItem.id AS id,
                    invtItem.custrecord_grw007_invtitem_item_name AS name,
                    invtItem.custrecord_grw007_invtitem_vendor_name AS vendorname,
                    invtItem.custrecord_grw007_invtitem_material_desc AS purchasedescription,
                    invtItem.custrecord_grw007_invtitem_manufacturer AS manufacturer_id,
                    invtItem.custrecord_grw007_invtitem_mpn AS mpn,
                    invtItem.custrecord_grw007_invtitem_unitofmeasure AS unitstype_id,
                    unitOfMeasure.name AS unitstype_name,
                    estProdItemCost.id AS estproditemcost_rec_id,
                    estProdItemCost.custrecord_grw007_estproditemcost_cost AS estimated_cost,
                    vendorRecord.name AS manufacturer
                FROM
                    customrecord_grw007_product product
                LEFT JOIN 
                    customrecord_grw007_invtitem invtItem ON product.id = invtItem.custrecord_grw007_invtitem_superid
                LEFT JOIN 
                    customrecord_grw007_estproditemcost estProdItemCost ON 
                        product.id = estProdItemCost.custrecord_grw007_estproditemcost_prod AND 
                        invtItem.custrecord_grw007_invtitem_workspace = estProdItemCost.custrecord_grw007_estproditemcost_ws
                LEFT JOIN 
                    (
                        SELECT 
                            innerCost.custrecord_grw007_estproditemcost_prod,
                            innerCost.custrecord_grw007_estproditemcost_ws,
                            innerCost.custrecord_grw007_estproditemcost_fromda,
                            innerCost.id
                        FROM
                            customrecord_grw007_estproditemcost innerCost
                        WHERE
                            innerCost.isinactive = 'F'
                            AND innerCost.custrecord_grw007_estproditemcost_fromda = (
                                SELECT MAX(innerCost2.custrecord_grw007_estproditemcost_fromda)
                                FROM customrecord_grw007_estproditemcost innerCost2
                                WHERE innerCost2.custrecord_grw007_estproditemcost_prod = innerCost.custrecord_grw007_estproditemcost_prod
                                AND innerCost2.custrecord_grw007_estproditemcost_ws = innerCost.custrecord_grw007_estproditemcost_ws
                                AND innerCost2.isinactive = 'F'
                            )
                    ) recentCost ON 
                        product.id = recentCost.custrecord_grw007_estproditemcost_prod AND 
                        invtItem.custrecord_grw007_invtitem_workspace = recentCost.custrecord_grw007_estproditemcost_ws
                LEFT JOIN
                    unitstype unitOfMeasure ON invtItem.custrecord_grw007_invtitem_unitofmeasure = unitOfMeasure.id
                LEFT JOIN 
                    customrecord_grw007_vendor vendorRecord ON invtItem.custrecord_grw007_invtitem_manufacturer = vendorRecord .id
                WHERE
                    invtItem.id IN (${internalIdsStr}) AND
                    invtItem.isinactive = 'F' AND 
                    product.isinactive = 'F' AND
                    recentCost.id = estProdItemCost.id
                    `;
                    // Execute the SuiteQL query
                    let suiteQLResult = query.runSuiteQL({
                        query: suiteQLQuery
                    });

                    let results = suiteQLResult.asMappedResults();
                    return results;

Here the internal id of the search result is converted to a string separated by comma using

let internalIdsStr = internalIds.map(id => `’${id}’`).join(‘,’);

and records are joined using “invtItem.id IN (${internalIdsStr})”