https://netsuiteprofessionals.com logo
Join Slack
Powered by
Hey all, does anyone know how to get formula resul...
# general
m
Hey all, does anyone know how to get formula results from a joined object in a saved search restlet? I have some custom object called a "Project Action" and it's joined to the "Job" standard record type. The saved search is a "Project Action" saved search where some of the columns are some summary of fields on the related "Job" record. However, they're not showing up when I do a GET on the restlet. Not sure what I'm googling for to figure it out.
b
general rule of columns on saved searches are that you use the same parameters used to create the column to get the value
m
This is what I have for a script right now...
Copy code
function GetSearchResult() {
    // get saved search results
  	var column = new nlobjSearchColumn("System Size", "job", "sum")
  
    var results = nlapiSearchRecord(null, "customsearch1076", null, column);

    // return the array of search objects
    return results[0];
}
This doesn't work because it's not sure what these columns are. Image attached, on the "new nlobjsearchcoumn" I'm trying to get the "System Size" column (imaged) However, when the script is working like this...
Copy code
function GetSearchResult() {
    var results = nlapiSearchRecord(null, "customsearch1076", null, null);

    // return the array of search objects
    return results[0];
}
I don't get the stuff that are formulas such as "System Size" I get this as response
Copy code
%{
     "columns" => %{
       "custentity_bb_fin_prelim_purch_price_amt" => 40695,
       "custentity_bb_project_location" => %{
         "internalid" => "3",
         "name" => "Norco"
       },
       "custentity_bb_system_size_decimal" => 7028,
       "internalid" => %{"internalid" => "13", "name" => nil}
     },
     "id" => nil,
     "recordtype" => nil
   }
When compared with the image attached, I'm not getting the same numbers like 91,370... Any ideas?
b
your name for the search columns is incorrect
suitescript uses ids, not the text of the labels
m
How can I get the id of a summary?
b
its the column name you have incorrect
it is not 
"System Size"
m
So this is what makes the results of the saved search and I'm not sure how I can get those summary columns
b
m
So that Project : System Size (KW) (Custom) would be with internal ids... 
custrecord_bb_project.custentity_bb_system_size_decimal
... would that be right?
b
i wouldnt actually know if its custom
m
It is custom but... I know that 
custrecord_bb_project
is the field on the "Project Action" that references the "Job" record type.
b
custrecord_bb_project
would be the id of the join
custentity_bb_system_size_decimal
would be the id of the field/column
m
Hm... and if I say "sum" with it, I'm not getting the 91,370 I'm expecting...
b
what does the code look like now
m
Copy code
function GetSearchResult() {
  	var column = new nlobjSearchColumn("custentity_bb_system_size_decimal", "custrecord_bb_project", "sum")
    var results = nlapiSearchRecord(null, "customsearch1076", null, column);

    // return the array of search objects
    return results[0];
}
with a response like...
Copy code
%{
     "columns" => %{
       "custentity_bb_fin_prelim_purch_price_amt" => 40695,
       "custentity_bb_project_location" => %{
         "internalid" => "3",
         "name" => "Norco"
       },
       "custentity_bb_system_size_decimal" => 7028,
       "internalid" => %{"internalid" => "13", "name" => nil}
     },
     "id" => nil,
     "recordtype" => nil
   }
b
it might be giving you the average instead
it looks like you arent building the JSON yourself and netsuite is just returning the first column
build your own object from the search result instead
return that from the restlet instead
m
So a script like...
Copy code
function GetSearchResult() {
    // Gets the saved search by internal id
    var results = nlapiSearchRecord(null, "customsearch1076", null, null);

    // Formats the results as an object
    results = formatToObject(results)

    // return the array of search objects
    return results;
}

// Used to format a saved search into an object and include all of the columns of the saved search
// If this isn't used, your response will be missing summary columns
function formatToObject(results) {
    // instantiates array container for search results
    var output = new Array();

    // get all columns of saved search
    var columns = results[0].getAllColumns();

    // loop through the search results
    for (var i in results) {
        // create placeholder object place holder
        var obj = new searchRow(
            // set the values of the object with the values of the appropriate columns
            // using getText to get "Norco" instead of "3"
            results[i].getText(columns[0]),
            // using get value to get values of things
            results[i].getValue(columns[1]),
            results[i].getValue(columns[2]),
            results[i].getValue(columns[3]),
            results[i].getValue(columns[4]),
            results[i].getValue(columns[5])
        );

        // add the object to the array of results
        output.push(obj);
    }

    return output;
}

// Object to serve a place holder for each search row
function searchRow(
    location,
    jobs,
    amount,
    average_amount,
    system_size,
    average_system_size
) {
    this.location = location;
    <http://this.jobs|this.jobs> = jobs;
    this.amount = amount;
    this.average_amount = average_amount;
    this.system_size = system_size;
    this.average_system_size = average_system_size;
}
This responses with...
Copy code
%{
     "amount" => "529038.00",
     "average_amount" => "40695.00",
     "average_system_size" => "7028",
     "jobs" => "13",
     "location" => "3",
     "system_size" => "91370"
   }
Which I'm not thrilled about the "location" field having a 3 instead of norco...
But I have some way to lookup the 3 to get the NorCo value... so I guess I can deal with it... unless you know something?
OHHH
using 
getText
Battk, thank you a ton
3 Views
Open in Slack
PreviousNext
Powered by